Incremental schema backup and restore

Incremental schema backup and restore is a feature that provides the ability to capture cumulative incremental or delta incremental backup of a schema followed by a full restore of the schema or table(s) within the schema.

Note: Both cumulative and delta incremental schema backups are mentioned in this topic. A reference to an ‘incremental backup’ in the topic is, unless specifically mentioned, either a cumulative or delta incremental backup.

Introduction

In order to take advantage of this new feature, the following is required:

A new schema created with the “ENABLE ROW MODIFICATION TRACKING” attribute. Permanent column-organized tables created within this schema will be enabled for row modification tracking, meaning they will contain three additional hidden columns for tracking incremental modifications to the rows between two backup’s.
Use of the new options provided in the db_backup and db_restore commands for schema incremental backup.

Schema backup (both full and incremental) of a schema enabled for row modification tracking will allow read access as well as concurrent insert, update, and delete operations against tables undergoing backup. For more information, refer to Schema enabled for row modification tracking.

General awareness for cross schema dependencies

Schema backup will only capture objects in the schema to backup and thus any cross-schema dependencies could lead to problems during restore. For example, if a DISTINCT TYPE is created in schema S2 and then used in a table in schema S1, a schema backup of S1 followed by restore of S1 would depend on the existence of the type in S2 at the time of the restore or else the restore will fail.

Failure to create some types of non-table objects inside of S1 which depend on objects outside of the schema may be tolerated during restore, a warning will be issued during restore and the restore will return as a success. The list includes:

Functions
Procedures
Views
Materialized query tables (MQT)
Variables

Failure to create a table object for any reason will log an error during restore and fail the restore.

Handling for different table types under schema backup

External Tables – The definition of the external table will be captured and restored but not the contents.
Materialized query tables - The definition of the MQT will be captured and restored but not the contents. The user will need to refresh the MQT after restore.
Temporary tables – Not captured by schema backup.
Any permanent tables not column-organized will cause the schema backup of a schema enabled for row modification tracking to fail (since these tables would not be enabled for row modification tracking).

Restore from an incremental backup

Restore of a cumulative or a delta incremental backup always starts with restore of a full backup image first. All backup images required to restore the current cumulative or delta incremental backup image are automatically determined and restored in chronological order.

Migration considerations

Users may alter their schema to enable row modification tracking, however this does not modify any of the tables in the schema to enable row modification tracking.

Only new tables created in the schema will be enabled for row modification tracking. All column-organized tables in the schema must be re-created after a schema is enabled for row modification tracking, before incremental schema backup will be possible.

Table migration

Recreating tables can be done through any means, for example using ADMIN_MOVE_TABLE, or CREATE TABLE … AS … WITH DATA (select * from one table into a new table in the same schema).

The target table will be created as enabled for row modification tracking if the target schema is enabled for row modification tracking and target table type supports row modification tracking.

Whether the source table is enabled for row modification tracking or not has no impact on what will be chosen for the target table. If the user attempts to select any of the new hidden columns from the source table where the target is created in a schema enabled for row modification, this will be treated as if the user has attempted to create a table with a user column of the same name and result in an error (column name already exists). Values for the new hidden columns will not be carried over from the source to the target during a CREATE TABLE … AS (fullselect) WITH DATA operation.

Schema migration

Migrating schemas can be done through ADMIN_COPY_SCHEMA or db_restore -enable-row-modification-tracking.

To use ADMIN_COPY_SCHEMA source schema must be enabled for row modification tracking via ALTER SCHEMA … ENABLE ROW MODIFICATION TRACKING.

Both operations will copy/restore all tables, but only column-organized tables will be enabled for row modification tracking. Other tables must be manually dropped or moved to another schema before incremental schema backup will be possible.

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.

db_backup limitations

Schema backup limitations:

Incremental schema backup (-type inc and -type del) is only supported for schemas which are enabled for row modification tracking.
Concurrent Select/Insert/Update/Delete operations are supported against the row modification tracking schema being backed up. Some DDL will remain blocked. Full access to other schemas not under backup will remain unchanged (which means no restrictions).
Multi-schema backup is allowed for schema enabled, for row modification tracking. If any one of the specified schemas in a multi-schema backup is not enabled for row modification tracking, incremental and delta backup is not allowed.
Can only backup entire schema and not a single or select number of tables.
Cannot backup a schema that contains a period (.) in its name.
The DB2 LIST HISTORY command will not show schema backups or restores. Use db_backup/db_restore -history instead.
Point in time roll forward is not supported.
Label-based access control (LBAC) information is not supported on column-organized tables and not scoped by a schema so this information will not be included in a schema backup.
Typed tables and views not supported.
JAR objects stored in the database (via SQLJ.INSTALL_JAR) for use in procedures will not be captured by the schema backup. This is no different than the behavior for any other external procedure like a C routine. It is recommended customers save and restore these themselves.
Cannot backup a schema that has tables with single/double quotes in the name.
If schema specified via -schema option has double quotes in the name or is case-sensitive, it must be enclosed with single and double quotes (e.g. Schema”1” must be specified as -schema ‘”Schema”1””’).
Incremental schema backup (-type inc and -type del) is not allowed if a corresponding full (-type onl) backup is taken using older version of db_backup.
Using the -keep-rcac option is not allowed when at least for one table in target schema RCAC rule definition includes dependency on another table and or object. Even in the same schema as RCAC table. You can use the option if both row and column access control and all RCAC rules are disabled on that table.

Limitations for backing up of schemas enabled for row modification tracking

If your schema contains any of the following, then schema backup (full or incremental) will fail:

Tables that are row organized. Not supported.
Tables with geospatial data. Not supported.
Tables with LOBs columns defined >= 64K or DBCLOB >= 32K Not supported.
An index in your schema to backup is defined on a table in a different schema, or a table in your schema to backup has an index defined in a different schema. This would create a cross schema dependencies that is very likely to cause restore to fail and thus is blocked at backup time.
Methods: Created via CREATE METHOD
Triggers: Created via CREATE TRIGGER
Reference types
Anchor data types
Function templates: Any functions defined with CREATE FUNCTION … AS TEMPLATE
MASK/PERMISSION that applies to a table outside of the schema, or the other way around, if MASK/PERMISSION in another schema applies to a table in the schema that is to be backed up.

If any of the following DDL was issued between the previous schema backup and the current schema incremental backup, the incremental backup will fail (the response here is to instead run a full schema backup):

DROP/RENAME TABLESPACE.
Note: This applies to any tablespace and not just tablespaces used by a given schema to backup.
DROP SCHEMA
ALTER TABLE … ADD FOREIGN KEY
RENAME INDEX

If any of the following DDL was issued between the previous schema backup and the current schema incremental backup it will result in a full backup of the table referenced in the DDL (other tables in the schema will be unaffected, which means only the incremental changes are captured):

CREATE TABLE
TRUNCATE TABLE
LOAD using REPLACE option
IMPORT using REPLACE option
ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE

Special considerations for tablespace restore and rollforward when used in conjunction with schema backup and restore. It is recommended not to mix these types of backup and restore since some inconsistencies could arise, for example:

CREATE SCHEMA S1 ENABLE ROW MODIFICATION TRACKING
CREATE TABLESPACE TBSP1 …
CREATE TABLE S1.T1 (C1 INT) IN TBSP1
Db2® tablespace backup of TBSP1
FULL schema backup of S1
Insert into table S1.T1 values (1), commit
Insert into table S1.T1 values (2), commit
DELTA incremental schema backup of S1
Db2 tablespace restore and rollforward (of TBSP1) to just after insert of 1, and prior to insert of 2.
Insert into table S1.T1 values (3), commit
DELTA schema backup of S1
Cumulative INCREMENTAL schema backup of S1

Currently S1.T1 has rows with values 1 and 3. However, if the user does a restore of the final DELTA schema backup captured above S1.T1 would have rows with values 1, 2, and 3. Restore of the final cumulative incremental schema backup would result in S1.T1 having rows with values 1 and 3.

Using -image-check rowcount is not applicable to tables with any of the following column types:

LONG VARCHAR or LONG VARGRAPHIC columns.
BLOB or CLOB columns defined with length of 64K or more.
DBCLOB columns defined with length of 32K or more.
Any spatial data type.

Use of database and schema backup and restore in conjunction

It is not recommended to mix these types of backup and restore. Restoring a database image taken at a timestamp invalidates all schema backup images taken after that timestamp. Schema backup does not contain information about database objects (like TABLESPACE) and schema restore does not ensure they are re-created if not present. In addition to that, database restore and rollforward will overwrite (put back in time) the history table IBM_SAILFISH.GRANULAR_BACKUP. For more information, refer to History table considerations after full database restore.

db_restore limitations

General limitations:

Access to tables is blocked during restore. For more information, refer to Concurrent access to the schema during db_restore.
Cannot restore a single schema or table(s) from a database backup.
Cannot restore a database from a schema backup.
Cannot restore a schema with a leading space in the name.
Cannot restore a table with a leading space in the name of primary key.
Table names specified via the -table option are case-insensitive.
If table specified via -table option is case-sensitive, it must be enclosed with single and double quotes (for example Table1 must be specified as -table ‘”Table1”’)
Table names specified in file via -tablefile option are case sensitive.
New schema name provided via the -target-schema option must be non-unicode.

Concurrent access to the schema during db_restore

Schema created by db_restore is locked until the end of restore. DDL, Read, and Write operations affecting tables undergoing restore are fully blocked during the operation. On successful exit schema and all tables are unlocked and are fully accessible.

Non-table objects (TYPE/METHOD/etc) are not locked and are accessible for the whole duration of restore. If the existing schema is to be dropped (via -drop-existing option), this schema is not locked. However, once dropped, the newly created schema is locked. As such, concurrent access to objects in schema may hinder drop and cause restore to fail.

On failure, restore does not unlock schema. In this case one of the following actions must be taken:

Re-run db_restore with -drop-existing option
Run db_restore -unlockschema to unlock the schema and allow access to all objects in it. Previously failed restore would not be completed, and any tables that were created or partially populated with data will be left as is.
Run db_restore -cleanup-failed-restore to drop schema and stop there. Errors due to failure to drop objects in schema (dropped by ADMIN_DROP_SCHEMA) are ignored, and schema is still unlocked at the end.

History table considerations after full database restore

Full restore of database will also restore (put back in time) the data stored in the IBM_SAILFISH.GRANULAR_BACKUP table. Information in that table is used by db_restore to determine all relevant backup images required for the schema restore. Users must preserve the history table explicitly, and then restore the relevant information after restoring the database in order to be able to run the schema restore after:

Store data from the history table:

db2 "create external table '/scratch/history.bkp' as (SELECT * from IBM_SAILFISH.GRANULAR_BACKUP)"

Restore the database:

 db_restore -type frc -path <path> -timestamp <timestampA>

Restore the history table data

db2 "insert into IBM_SAILFISH.GRANULAR_BACKUP select * from external '/scratch/history.bkp' where TIMESTAMP > <timestampA>"

Using IBM Spectrum Protect Server (TSM) to store backup images

Incremental schema backup and restore can use TSM to store backup images if -tsm option is specified. TSM server must be set up, and TSM client must be configured on all host nodes - see https://www.ibm.com/docs/en/ias?topic=versions-manage-tsm-command for details.
All physical nodes in the system are required to use the same proxynode name. When restoring to a system that is different from the source of the backup, the proxynode name must match the proxynode name of the source (backup) system.
The backup image will be stored in the /DB2 file space under the /<timestamp>/ folder, where <timestamp> is a timestamp assigned to the backup image. Restore relies on backup image to reside in that folder and on restore no path information is required to be entered, besides the timestamp.
When a backup image is stored on IBM Spectrum Protect server, -history option output will contain "IBM Spectrum Protect" as a value for LOCATION column. Only backups originated on the Db2 Warehouse server will be reflected in history table on current server, and will not include backup images originated on another Db2 Warehouse server in case multiple Db2 Warehouse servers use the same TSM server. To list all backup images on the TSM server use db_restore -tsm -list-backup command.
In case the backup image on TSM is no longer needed, it can be deleted using db_restore -tsm -delete-backup command. Timestamp can be obtained either from history table, db_backup script output, or log file created by backup script. If backup operation using TSM as target media fails, partial backup image may not be removed automatically.