Limitations of logical backup and restore

You must be aware of some limitations for a logical backup or logical restore operation.

Limitations of logical backup

  • Logical backup and restore is supported on linuxamd64 and linuxppc64le platforms only.
  • Accessing cloud storage by using the -s3, -cos, or -path options with DB2REMOTE identifiers is supported only on the linuxamd64 platform.
  • 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.
  • The backup of a schema that is enabled for row modification tracking fails if Row and Column Access control (RCAC) is in effect for any data inside the schema.
  • 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. Use the LOGICAL_BACKUP_DETAILS stored procedure or the LOGICAL_BACKUP_DETAILS_TAB table function 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.
    • The MASK and PERMISSION commands.
  • Ensure that only one user runs a logical backup for an individual schema. Having different users create full, incremental, or delta backups of a schema at different times can cause failures when restoring the incremental or delta backup images.
  • 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. For this statement, failure happens when run against any table space, and not just table spaces that are used by a given schema to backup.
    • DROP SCHEMA
    • RENAME INDEX
    • REORG SYNOPSIS FOR TABLE ... REBUILD
    • ALTER TABLE … ADD FOREIGN KEY
    • ALTER TABLE … DROP COLUMN
    • ALTER TABLE ... RENAME COLUMN
    • ALTER TABLE ... SET DATA TYPE
    • ALTER TABLE ... ADD COLUMN with DEFAULT value being a special register like CURRENT USER, CURRENT SCHEMA, CURRENT DATE, CURRENT TIME, etc.
    Respond to the failed backup operation 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 statement. Other tables in the schema are unaffected, with incremental changes only being 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 logical 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.
  • If logical backup image is made on v12.1, then it cannot be restored on any earlier versions, for example, v11.5.9, v11.5.8, and older.
Special considerations for table space restore and rollforward when used in conjunction with logical 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 logical backup, S1.T1 has rows with values 1, 2, and 3. A restoration of the final cumulative incremental logical backup results in S1.T1 having rows with values 1 and 3. 
•	CREATE LOGICAL S1 ENABLE ROW MODIFICATION TRACKING
•	CREATE TABLESPACE TBSP1 …
•	CREATE TABLE S1.T1 (C1 INT) IN TBSP1
•	Db2 tablespace backup of TBSP1
•	FULL logical backup of S1
•	Insert into table S1.T1 values (1), commit
•	Insert into table S1.T1 values (2), commit
•	DELTA incremental logical 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 logical backup of S1
•	Cumulative INCREMENTAL logical backup of S1
Use of database and logical backup and restore features in conjunction
It is not recommended to mix these types of backup and restore operations.

Restoring a database-level image that is taken at a timestamp invalidates all logical backup images taken after that timestamp. A logical backup does not contain information about database-level objects, such as TABLESPACE, and a logical 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 logical backup images are required for the logical restore. Before running the logical 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 logical 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 no longer exits in S2 at the time of restore, then the restore fails.