Using the WITH VERCOLS Option

Use the WITH VERCOLS keywords to create two shadow columns that Informix® uses to support update operations on secondary servers.

The first column, ifx_insert_checksum, contains a checksum of the row when it was first created. The second column, ifx_row_version, contains a version number of the row. When a row is first inserted, ifx_insert_checksum is generated, and ifx_row_version will be set to one. Each time the row is updated, ifx_row_version is incremented by one, but ifx_insert_checksum does not change. These two columns are visible shadow columns because they can be indexed and can be viewed in system catalog tables.

For most database operations, the ifx_insert_checksum and ifx_row_version columns are hidden. For example, if you include the WITH VERCOLS keywords when you create a table, the ifx_insert_checksum and ifx_row_version columns have the following behavior:
  • They are not returned by queries that specify an asterisk ( * ) as the projection list, as in the statement:
    SELECT * FROM tablename;
  • They appear in DB-Access when you ask for information about the columns of the table.
  • They are included in the number of columns (ncols) in the systables system catalog table entry for tablename.
To view the contents of ifx_insert_checksum and ifx_row_version, you must explicitly specify the column names in the projection list of a SELECT statement, as the following example shows:
SELECT ifx_insert_checksum, ifx_row_version FROM tablename;

When row versioning is enabled, ifx_row_version is incremented by one each time the row is updated; however, row updates made by Enterprise Replication do not increment the row version. To update the row version on a server using Enterprise Replication, you must include the ifx_row_version column in the replicate participant definition.

For more information about how to use this option, refer to the HCL Informix Administrator's Guide.


Copyright© 2019 HCL Technologies Limited