Enabling LOGICAL_BACKUP and LOGICAL_RESTORE for a schema

You can use the Db2 LOGICAL_BACKUP and LOGICAL_RESTORE stored procedures to do full, cumulative incremental, or delta incremental backups of a schema, followed by full restore of the schema or one or more tables within the schema.

Before you begin

Before enabling the logical schema backup and restore features, ensure that you have completed the following tasks:

Invoke the SYSPROC.SYSINSTALLOBJECTS('LBAR','C',NULL,NULL) stored procedure. The following tables that are required by the logical schema backup and restore feature are created:
- SYSDDLHISTORY: Stores DDL statements for tables in RMT schemas that are consumed by incremental (INC) or delete (DEL) backup operations.
- SYSINVISIBILITYLIST: Stores a list of in-flight transactions that filter out records that should not be in the backup image. For example, records that are added after the backup is started.
- SYSBACKUPIDS: Stores a list of backup images and their types. This table is used to identify previous backup images of a particular schema of a particular type. For example, the last full backup taken when preparing to take an INC.
- LOGICAL_BACKUP_HISTORY or LOGICAL_RESTORE_HISTORY: Lists backup and restore operations for a particular schema or table. This table is maintained to provide a simple way for customers to view the backup and restore history.
Attention: If the tables do not exist when you first call either the LOGICAL_BACKUP stored procedure or LOGICAL_RESTORE stored procedure, the procedure calls the SYSPROC.SYSINSTALLOBJECTS('LBAR','C',NULL,NULL) stored procedure.
Set the database manager configuration parameter PYTHON_PATH to point to the location of your Python executable. The logical schema backup and restore features rely on Python scripts to run effectively.
Install the ibm_db on your Db2® system. This utility is not included with the Db2 install image. For more information, see Setting up the Python environment for IBM database servers.

About this task

If you are backing up a column-organized schema that is used for analytic workloads, then you need to enable row modification tracking on your target schema. You enable the logical schema backup and restore features by setting the ENABLE ROW MODIFICATION TRACKING option in either a CREATE SCHEMA statement or an ALTER SCHEMA statement.

With row modification tracking enabled, any table that is created within your target schema is also created with row modification tracking enabled. This setting enables the logical schema backup feature to capture table data from column-organized tables.

To track changes to column-organized tables, three implicitly hidden columns are added to each table when the tables are created. Values stored in these three new columns need additional space, so a table that is enabled for row modification tracking also consumes more pages.

Table 1 lists the three new columns that are added to each table.

Table 1. The three new columns added to a table with row modification tracking enabled.
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.

When a row is inserted and assigned an SYSROWID value, the value remains unchanged even if the row is updated.

Note: The names that are used for the new columns are reserved by IBM® for use in tables enabled for row modification tracking. If you try to use any of these column names when creating or altering a table that has row modification tracking enabled, an error is returned.

Only permanent column-organized tables created in a schema enabled for row modification tracking are enabled for row modification tracking. The following tables are not enabled for row modification tracking:

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.)

Attention: Since the columns are hidden implicitly, with values that are generated internally, you do not see them when running a select * statement against the table. You do not need to specify these hidden columns when running an insert, update or delete statement. Attempts to modify any of the three columns through an insert, update, delete, or alter operation is either ignored or results in an error.

Procedure

Create a new schema or alter an existing schema with the ENABLE ROW MODIFICATION TRACKING attribute set. Permanent organized-by-column tables that are created within this schema are enabled for row modification tracking and contain three hidden columns for tracking incremental modifications to the rows between the full install and an incremental install.
Call the SYSPROC.LOGICAL_BACKUP() stored procedure and SYSPROC.LOGICAL_RESTORE() stored procedures, as needed.

Examples

The following example shows the command syntax for running a CREATE SCHEMA statement with the ENABLE ROW MODIFICATION TRACKING attribute set:

CREATE SCHEMA S1 ENABLE ROW MODIFICATION TRACKING

The following examples show tables created from running the SYSPROC.SYSINSTALLOBJECTS procedure prior to running the logical schema backup and restore features.

 SYSLOGICALBAR.SYSDDLHISTORY(
      ROWTIME TIMESTAMP(6) NOT NULL,
      OPTYPE SMALLINT NOT NULL,
      MEMBER SMALLINT NOT NULL,
      TRANSACTIONID BIGINT NOT NULL,
      TABSCHEMA VARCHAR(128 OCTETS),
      TABNAME VARCHAR(128 OCTETS),
      DDLTEXT CLOB (1G OCTETS) INLINE LENGTH 2689 NOT NULL)
      ORGANIZE BY ROW DATA CAPTURE NONE COMPRESS YES ADAPTIVE DISTRIBUTE BY HASH(MEMBER)
  GRANT SELECT ON TABLE SYSLOGICALBAR.SYSDDLHISTORY TO PUBIC

 SYSLOGICALBAR.SYSINVISIBILITYLIST(
      BACKUPID VARCHAR(128 OCTETS) NOT NULL,
      MEMBER SMALLINT NOT NULL,
      TRANSACTIONID BIGINT NOT NULL)
      ORGANIZE BY ROW DATA CAPTURE NONE COMPRESS YES ADAPTIVE DISTRIBUTE BY HASH(MEMBER)
  GRANT SELECT ON TABLE SYSLOGICALBAR.SYSINVISIBILITYLIST TO PUBIC

SYSLOGICALBAR.SYSBACKUPIDS(
      BACKUPIDFULL VARCHAR(128 OCTETS) NOT NULL,
      BACKUPIDLAST VARCHAR(128 OCTETS) NOT NULL,
      SCHEMANAME VARCHAR(128 OCTETS),
      OPTIONS CHAR(8 OCTETS) NOT NULL)
      ORGANIZE BY ROW DATA CAPTURE NONE COMPRESS YES ADAPTIVE DISTRIBUTE BY HASH(BACKUPIDFULL)
  GRANT SELECT ON TABLE SYSLOGICALBAR.SYSBACKUPIDS TO PUBIC

 SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY (
      TIMESTAMP VARCHAR(15),
      START_TIME VARCHAR(15),
      END_TIME VARCHAR(15),
      OPERATIONTYPE VARCHAR(15),
      SCOPE VARCHAR(20),
      SCHEMA_NAME VARCHAR(128),
      LOCATION CLOB(2M),
      VERSION VARCHAR(128))
      ORGANIZE BY ROW DATA CAPTURE NONE COMPRESS YES ADAPTIVE DISTRIBUTE BY HASH(TIMESTAMP)
  GRANT SELECT ON TABLE SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY TO PUBIC
  GRANT INSERT,DELETE ON TABLE SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY TO PUBLIC

SYSLOGICALBAR.LOGICAL_RESTORE_HISTORY (
      TIMESTAMP VARCHAR(15),
      START_TIME VARCHAR(15),
      END_TIME VARCHAR(15),
      OPERATIONTYPE VARCHAR(15),
      SCOPE VARCHAR(20),
      SOURCE_SCHEMA VARCHAR(128),
      LOCATION CLOB(2M),
      TABLE_NAME VARCHAR(128))
      ORGANIZE BY ROW DATA CAPTURE NONE COMPRESS YES ADAPTIVE DISTRIBUTE BY HASH(TIMESTAMP)
  GRANT SELECT ON TABLE SYSLOGICALBAR.LOGICAL_RESTORE_HISTORY TO PUBIC
  GRANT INSERT,DELETE ON TABLE SYSLOGICALBAR.LOGICAL_RESTORE_HISTORY TO PUBLIC

The following example shows the command syntax and output from running a DESCRIBE statement to view the three hidden columns in a table that is configured for row modification tracking

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.

The ROWMODIFICATIONTRACKING column from the SYSCAT.SCHEMATA catalog view indicates whether the schema is enabled for row modification tracking. The QUEIESCED column of the SYSCAT.SCHEMATA catalog view indicates whether the schema is locked by an in-progress LOGICAL_RESTORE() operation. Returned values for these columns are N or Y.

The following example shows the command syntax and output from running a db2 SELECT CAST statement for the schema name S1, where row modification tracking is enabled:

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.