Schemas enabled for row modification tracking (RMT)

Activating row modification tracking (RMT) on a schema allows you to track incremental insertions and deletions that occur between backup operations. You activate the RMT feature by including the ENABLE ROW MODIFICATION TRACKING clause as part of a CREATE SCHEMA or ALTER SCHEMA statement.

Note: RMT 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 an RMT-enabled schema

When you create a column-organized table in a schema that has RMT enabled, that table inherits row modification tracking as well. In order to track incremental changes to the table data between a full and incremental backup, three implicitly hidden columns are 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 require additional space, so a table that is enabled for RMT consumes additional pages.

Table 1 lists three columns that are added to a table that is enabled for RMT.

Table 1. Columns used for RMT
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 remains even if the row is updated. Be aware that the above column names are reserved by IBM® for use in tables enabled for RMT. Users attempting to use the same column name in the table receive an error when creating or altering a table.

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

Temporary tables
External tables
Materialized query tables
Tables not in the list above that 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 do not see them when doing a SELECT statement against the table. You do not need to specify these columns in any INSERT, UPDATE, or DELETE statement. Attempting to modify any of the three columns by running insert, update, delete, or alter operations is either ignored or results in an error.

Db2® limitations for RMT-enabled schemas

Some limitations exist for Db2 database objects when using RMT-enabled schemas.

Exception tables for db2load and INGEST operations cannot be column-organized. Instead, create a row-organized table in another schema, so that the row-organized table causes any incremental schema backup to fail. 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 exception table must have a similar definition, including the hidden columns defined for RMT, but the SYSROWID column cannot be an identity column. Identity columns are not supported in exception tables.
The maximum number of user defined columns in a table enabled for RMT is reduced to 1009.
IMPORT operations that use CREATE or REPLACE_CREATE options to import into an RMT-enabled table can fail.
An ADMIN_COPY_SCHEMA operation with a copymode of COPY or COPYNO fails if a table is defined as DISTRIBUTE BY RANDOM, or if the table contains a GENERATED ALWAYS AS IDENTITY column.
You cannot use an ALTER TABLE statement against an RMT-enabled table to create a materialized query table.
Neither SYSROWID, CREATEXID, nor DELETEXID are supported within the distribution key of the table. As a result, 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 RMT.
Neither SYSROWID, CREATEXID, nor DELETEXID 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 that have RMT enabled. Application-period temporal tables do support RMT.
No support for CREATE VIEW … WITH ROW MOVEMENT with RMT enabled.
REORG TABLE … RECLAIM EXTENTS reclaims space from deleted SYSROWID, CREATEXID, and DELETEXID columns. Space is reclaimed for rows that were deleted prior to the last -type ONL, or full-schema, backup.

Catalog changes

In the SYSCAT.SCHEMATA catalog view there are two columns to be aware of for RMT:

ROWMODIFICATIONTRACKING: Indicates whether the schema is enabled for RMT.; Values: N or Y.; Default: N
QUIESCED: indicates whether the schema is locked by an in-progress LOGICAL_RESTORE() operation.; Values: N or Y

The following example shows the catalog view is enabled for RMT:

db2 DESCRIBE TABLE SYSCAT.SCHEMATA
                                Data type                     Column
Column name                     schema    Data type name      Length     Scale Nulls
------------------------------- --------- ------------------- ---------- ----- ------
SCHEMANAME                      SYSIBM    VARCHAR                    128     0 No
OWNER                           SYSIBM    VARCHAR                    128     0 No
OWNERTYPE                       SYSIBM    CHARACTER                    1     0 No
DEFINER                         SYSIBM    VARCHAR                    128     0 No
DEFINERTYPE                     SYSIBM    CHARACTER                    1     0 No
CREATE_TIME                     SYSIBM    TIMESTAMP                   10     6 No
AUDITPOLICYID                   SYSIBM    INTEGER                      4     0 Yes
AUDITPOLICYNAME                 SYSIBM    VARCHAR                    128     0 Yes
AUDITEXCEPTIONENABLED           SYSIBM    CHARACTER                    1     0 No
DATACAPTURE                     SYSIBM    VARCHAR                      1     0 No
ROWMODIFICATIONTRACKING         SYSIBM    VARCHAR                      1     0 No
QUIESCED                        SYSIBM    VARCHAR                      1     0 No
REMARKS                         SYSIBM    VARCHAR                    254     0 Yes

db2 SELECT CAST(SCHEMANAME as CHAR(5)) as SCHEMANAME, ROWMODIFICATIONTRACKING FROM SYSCAT.SCHEMATA WHERE SCHEMANAME='S1'
SCHEMANAME ROWMODIFICATIONTRACKING
---------- -----------------------
S1         Y
  1 record(s) selected.

Migration considerations

Users can alter their schema to enable RMT, however doing so does not modify any of the tables in the schema to enable RMT.

Use the following syntax for the alter schema operation:

Values that are stored in the SYSROWID, CREATEXID, and DELETEXID columns need additional space, so an RMT-enabled table consumes additional pages. Only new tables created in the schema are enabled for RMT. All columnar tables in the schema must be recreated after a schema is enabled for RMT before logical schema backup is possible.

Table migration: You can recreate tables using ADMIN_MOVE_TABLE, CREATE TABLE … AS … WITH DATA, or any SELECT * operation from one table into a new table in the same schema. The target table is created as enabled for RMT if the target schema is enabled for RMT and target table type supports RMT. Whether the source table is enabled for RMT has no impact on what is chosen for the target table. If a user attempts to select the SYSROWID, CREATEXID, or DELETEXID columns from the source table where the target is created in a schema enabled for row modification, an error is generated. The action is treated as if the user has attempted to create a table with a user column of the same name so, because the column name already exists, an error is generated. Values for the SYSROWID, CREATEXID, and DELETEXID columns are not carried over from the source to the target during a CREATE TABLE … AS (fullselect) WITH DATA operation.
Table data migration using logical backup and restore: To assist with migration, the SYSPROC.LOGICAL_BACKUP command can take a logical backup image of a schema that is not enabled for RMT by specifying -backup-migrate option.
Note: For the whole duration of this backup only read access is allowed to tables in the schema.
Once the backup is complete, you can restore the backup image by running the SYSPROC.LOGICAL_RESTORE(-enable-row-modification-tracking) option. The schema is created with RMT enabled and all tables in the schema are attempted to be enabled as well. Any table DDL statement or condition in the original schema that prevents a table from being enabled for RMT causes the backup to fail.