Schema enabled for row modification tracking

Schema is enabled for row modification tracking using the ENABLE ROW MODIFICATION TRACKING clause as part of the CREATE SCHEMA or ALTER SCHEMA statement. The main purpose of such schemas is to allow incremental schema backups to capture data modified since the last full schema backup.

Note: Row modification tracking is not enabled by default. Therefore to take advantage of the incremental schema backup and restore, explicit schema creation using the ENABLE ROW MODIFICATION TRACKING clause is necessary.

Tables created in a schema enabled for row modification tracking

Column-organized tables created in a schema enabled for row modification tracking will be enabled for row modification tracking. In order to track incremental changes to the table data between a full and incremental backup, three implicitly hidden columns will be added for each table when it is created. For more information, refer to Incremental schema backup and restore.

Values stored in these three new columns will require additional space, and therefore a table that is enabled for row modification tracking will consume more pages.

A table enabled for row modification tracking will have the following three new columns added:

Table 1. The three new columns added to a table with row modification tracking
Column Name	Datatype	Allow NULL	Description
SYSROWID	BIGINT	No	Column that uniquely identifies each row in the table. The ID is unique across all database partitions. The values for this column are generated by a SEQUENCE.
CREATEXID	BIGINT	No	Stores the ID of the transaction that added the row to the table.
DELETEXID	BIGINT	No	Stores the ID of the transaction that deleted the row. It will be zero if the row is not deleted.

After a row is inserted and assigned a value for SYSROWID, this value will not change even if the row is updated. Be aware that the above column names will now be reserved by IBM® for use in tables enabled for row modification tracking. Users attempting to use the same column name in the table will receive an error when creating or altering a table.

Only permanent column-organized tables created in a schema enabled for row modification tracking will be enabled for row modification tracking. The following tables are not enabled for row modification tracking and thus will not contain the extra three columns:

Temporary tables
External tables
Materialized query tables
Tables not in the list above which are also not column-organized (for example tables organized by row, insert time, dimensions-clause, etc.)

Here is an example of the three extra columns in a table. Users can see the extra columns for the table when doing a describe of the table:

db2 "CREATE TABLE S1.T1 (C1 INTEGER)"
db2 "DESCRIBE TABLE S1.T1"

                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SYSROWID                        SYSIBM    BIGINT                       8     0 No
CREATEXID                       SYSIBM    BIGINT                       8     0 No
DELETEXID                       SYSIBM    BIGINT                       8     0 No
C1                              SYSIBM    INTEGER                      4     0 Yes

  4 record(s) selected.

Since the columns are implicitly hidden with values internally generated, users will not see them when doing a select * from the table for example, and users do not need to specify them when doing an insert, update or delete statement. Attempting to modify any of the three columns (by insert/update/delete/alter) will either be ignored or result in an error.

Db2® database limitations for objects in schema enabled for row modification tracking

Exception tables for db2load and INGEST cannot be column-organized and thus the recommended approach to use an exception table for db2load or INGEST into a table enabled for row modification tracking is to create a row organized table in another schema (different schema because the row organized table will cause incremental schema backup to fail). In addition to that, the exception table must have a similar definition including the hidden columns defined for row modification tracking except the SYSROWID can not be an identity column since these are not supported in exception tables. For example:
- CREATE SCHEMA S1 ENABLE ROW MODIFICATION TRACKING
- CREATE SCHEMA S2
- CREATE TABLE S1.T1 (C1 INT)
- CREATE TABLE S2.LOADEXTBL (SYSROWID BIGINT NOT NULL, CREATEXID BIGINT NOT NULL, DELETEXID BIGINT NOT NULL, C1 INT)
- LOAD FROM <file> OF DEL INSERT INTO S1.T1 FOR EXCEPTION S2.LOADEXTBL
The maximum number of user defined columns in a table enabled for row modification tracking is reduced to 1009.
IMPORT using CREATE or REPLACE_CREATE options into a table enabled for row modification tracking may fail.
ADMIN_COPY_SCHEMA with a copymode of ‘COPY’ or ‘COPYNO’ will fail if at least one table is defined as DISTRIBUTE BY RANDOM or contains GENERATED ALWAYS AS IDENTITY column.
No support for alter of a table enabled for row modification tracking to be a materialized query table.
None of the three new hidden columns (SYSROWID, CREATEXID, DELETEXID) are supported within the distribution key of the table (this means they are not allowed in the DISTRIBUTE BY HASH column list).
No support for db2move if the source or target is a table in a schema enabled for row modification tracking.
None of the three new hidden columns can be included as part of an index key or in an enforced constraint.
No support for System-period temporal tables or Bitemporal-period tables with row modification tracking enabled, however Application-period temporal table will support row modification tracking.
No support for CREATE VIEW … WITH ROW MOVEMENT with row modification tracking enabled.
REORG TABLE … RECLAIM EXTENTS reclaims space from deleted SYSROWID/CREATEXID/DELETEXID columns. Space is reclaimed only for rows that were deleted prior to last -type ONL (full schema) backup.