Limitations of schema-level and table-level backup and restore

You need to be aware of some limitations that exist for a schema-level and table level backup and restore operation.

Limitations of schema-level and table-level backup

  • Logical backup and restore is supported on linuxamd64 and linuxppc64le platforms only.
  • Accessing cloud storage using the -s3 or -cos options is supported on linuxamd64 platform only.
  • When backing up to a file system by using the -path option in a DPF environment, the specified path must be a shared file system that is mounted by all hosts of the DPF system.
  • Logical backup and restore is not supported on pureScale deployments.
  • Concurrent Select, Insert, Update, and Delete operations are supported against the row modification tracking schema being backed up. Some DDL statements remain blocked. Full access to other schemas that are not under backup remain unchanged, with no restrictions.
  • An incremental or delta backup of a schema fails with SQL1797N error when any table with Row and Column Access Control (RCAC) is created or renamed after the corresponding full backup.
  • You can backup an entire schema but not a single or select number of tables.
  • You cannot backup a schema that contains a period (.) in its name.
  • The DB2 LIST HISTORY command does not show schema level backups or restores. Call SYSPROC.LOGICAL_BACKUP_DETAILS() to get a list of timestamps for backup images that are available on the specified target path or media.
  • The point-in-time rollforward option is not supported.
  • Label-based access control (LBAC) information is not supported on columnar tables and not scoped by a schema. As a result, this information is not included in a schema backup.
  • Typed tables and views not supported.
  • JAR objects that are stored in a database for use in procedures are not captured by the schema backup. These objects are installed by using the SQLJ.INSTALL_JAR command. This behavior is no different than that of any other external procedure, like a C routine. You need to save and restore these objects yourself.
  • You cannot backup a schema that has tables with single or double quotes in the name.
  • When specifying a schema by using the -schema option, if the schema name contains double quotes symbols or is case-sensitive, it must be enclosed with single and double quotes. For example, schema”1” must be specified as -schema ‘”Schema”1””’)
  • Use of the following database objects can cause your schema backup to fail, whether full or incremental:
    • Row-organized tables.
    • Tables that contain geospatial data.
    • Tables with large object (LOB) columns defined (greater-than or equal to 64K).
    • Tables with double-byte character large object (DBCLOB) columns defined (greater-than or equal to 32K).
    • An index in the schema is defined on a table in a different schema (cross-schema dependency).
    • A table in the schema has an index defined in a different schema (cross-schema dependency).
    • Methods that are created using the CREATE METHOD command.
    • Triggers that are created using the CREATE TRIGGER command.
    • Reference types
    • Anchor data types.
    • Any functions that are defined with CREATE FUNCTION...AS TEMPLATE.
  • If any of the following DDL statements are issued between the previous schema backup and the current schema incremental backup, the incremental backup fails:
    • DROP/RENAME TABLESPACE. Note this applies to any table space and not just table spaces used by a given schema to backup.
    • DROP SCHEMA
    • ALTER TABLE … ADD FOREIGN KEY
    • RENAME INDEX
    Respond to the backup failure by running a full schema backup.
  • If any of the following DDL statements are issued between the previous schema backup and the current schema incremental backup, a full backup is run of the table that is referenced in the DDL. Other tables in the schema are unaffected: Incremental changes only are captured:
    • CREATE TABLE
    • TRUNCATE TABLE
    • LOAD using REPLACE option
    • IMPORT using REPLACE option
    • ALTER TABLE …​ ACTIVATE NOT LOGGED INITIALLY WITH EMPTY TABLE
  • You cannot backup a schema that is disabled for row modification.
  • You cannot backup a schema where at least one table in the schema is not organized by column and at least one table in the schema is disabled for row modification, unless you use the -backup-migrate option.
  • You cannot take a backup from a schema if the tables of the schema have foreign key dependencies to tables in other schemas. Even if the backup is a multi-schema backup, where all schemas involved are being backed up with same logical_backup call, cross-schema foreign keys are still not supported between tables in those schemas.

Limitations of schema-level and table-level restore

Be aware of the following known issues and restrictions before attempting to user the LOGICAL_RESTORE feature:

  • Concurrent RW access to the schema or table undergoing a restore operation is blocked. For more information, see Concurrent access to the schema during LOGICAL_RESTORE.
  • You cannot restore a single schema or table(s) from a database-level backup.
  • You cannot restore a database from a schema-level backup.
  • You cannot restore a schema with a leading space in the name.
  • You cannot restore a table with a leading space in the name of primary key.
  • Table names that are specified by using the -table option are case-insensitive
  • If a table that is specified by using the -table option needs to be case-sensitive, it must be enclosed with single and double quotes. For example, Table1 must be specified as -table ‘”Table1”’).
  • Table names that are specified in file by using the -tablefile option are case sensitive.
  • The INSPECT table command is not allowed on tables in the schema that are locked by a restore operation.
  • A restore operation with the -keep-rcac option fails (SQL1797N error) if the target schema or table set includes an RCAC table and any of these conditions apply:
    • Target schema does not exist.
    • RCAC table is missing from the corresponding full backup image.
    • RCAC table in the backup image is not present at restore time.
    • RCAC table's DDL at restore time does not match DDL in the FULL backup image.
    • The -enable-row-mod-tracking option is specified.
    • Any RCAC table is system maintained temporal table.
    • Any RCAC table has an MQT defined on it.
Special considerations for table space restore and rollforward when used in conjunction with schema backup and restore
It is recommended not to mix these types of backup and restore operations since some inconsistencies can arise.
In the following sample code, S1.T1 has rows with values 1 and 3. However, if you restore the final DELTA schema backup, S1.T1 has rows with values 1, 2, and 3. A restoration of the final cumulative incremental schema backup results in S1.T1 having rows with values 1 and 3. 
•	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 S
Use of database and schema backup and restore features in conjunction
It is not recommended to mix these types of backup and restore operations.

In Db2 11.5.8 and later, restoring a database-level image that is taken at a timestamp invalidates all schema level backup images taken after that timestamp. A schema backup does not contain information about database-level objects, such as TABLESPACE, and a schema restore does not ensure that database-level objects are recreated if they are not present. In addition, a full restore of the database also restores the data stored in the SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY table, putting it back in time.

Information in the SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY table is used by LOGICAL_RESTORE to determine which backup images are required for the schema restore. Before running the schema restore, complete the following tasks:
  1. Store data from the history table:
    
`db2 "create external table '/saved/path/history.bkp' as (SELECT * from SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY)" `
  2. Restore the database.
  3. Restore the history table data: 
    db2 "insert into SYSLOGICALBAR.LOGICAL_BACKUP_HISTORY select * from external '/saved/path/history.bkp'"
Concurrent access to the schema during LOGICAL_RESTORE
Schema in process of being re-created by LOGICAL_RESTORE is locked until 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 existing schema is to be dropped by the inclusion of the -drop-existing option, then the schema is not locked. However, once the schema is dropped, any newly created schema is locked. As such, concurrent access to objects in a schema might hinder the drop and cause the restore operation to fail.

Issues with cross-schema dependencies

The schema backup feature captures only objects within the schema, so any cross-schema dependencies can lead to problems during a restore operation. 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 a restore of S1 is dependent on the existence of the type in S2 at the time of the restore. If the type is not S2, then the restore fails.

For some types of non-table database objects that have dependencies outside of their own schema, their absence from a schema backup might be tolerated during a restore operation. These objects include functions, procedures, views, materialized query tables (MQT), and variables. In this situation, a warning is issued during the restore operation, but the backup is restored successfully. However, if a required table object is not available during a restore operation for any reason, an error is logged and the restore operation fails.