Compatibility of online backup and other utilities

In SMS mode, online index create and online backup do not run concurrently due to the ALTER TABLE lock. Online index create acquires it in exclusive mode while online backup acquires it in share.

In DMS mode, online index create and online backup can run concurrently in most cases. There is a possibility if you have a large number of tables in the same tablespace as the one in which you are creating the index, that the online index create will internally acquire an online backup lock that will conflict with any concurrent online backup.

As with online index create, in SMS mode, online index reorganization do not run concurrently with online backup due to the ALTER TABLE lock. Online index reorganization acquires it in exclusive mode while online backup acquires it in share. In addition, an online index reorganization operation, quiesces the table before the switch phase and acquires a Z lock, which prevents an online backup. However, the ALTER TABLE lock should prevent an online backup from running concurrently before the Z table lock is acquired.

In DMS mode, online index reorganization and online backup can run concurrently.

In addition, online index reorganization quiesces the table before the switch phase and gets a Z lock, which prevents an online backup.

The import utility is compatible with online backup except when the IMPORT command is issued with the REPLACE parameter, in which case, import gets a Z lock on the table and prevents an online backup from running concurrently.

For SMS (System Managed Space), the TRUNCATE statement is not compatible with online backup. TRUNCATE gets a Z lock on the table. The online backup utility acquires an IN lock for each table that is backed up and resides in an SMS tablespace, which is incompatible with the Z lock acquired by TRUNCATE.

TRUNCATE does not progress if an online backup is active. In this case, TRUNCATE does not timeout, regardless of the system defined timeout value and waits until the online backup is completed.

Load operations that specify the ALLOW READ ACCESS parameter are not compatible with online backups when the LOAD command is issued with the COPY NO parameter. In this mode the utilities both modify the table space state, causing one of the utilities to report an error. Load operations that specify the ALLOW READ ACCESS parameter do not lead to an error if they also specify the COPY YES option. although there might still be some compatibility issues. In SMS mode, the utilities can execute concurrently, but they hold incompatible table lock modes and consequently might be subject to table lock waits. In DMS mode, the utilities both hold incompatible online backup (OLB) lock modes and might be subject to waits on that lock. If the utilities execute on the same table space concurrently, the load utility might be forced to wait for the backup utility to complete processing of the table space before the load utility can proceed.

LOAD is not compatible when an integrated snapshot backup (or when the backup is issued with the USE SNAPSHOT option) is being performed. During an integrated snapshot backup, in addition to the database being put into set write suspend state, it also acquires and holds various locks that conflict with LOAD. This protects the integrity of the snapshot backup image by preventing inconsistencies between what is included in the image and what is included in the meta data that Db2 manages, such as the history file and log control files.

If a LOAD issued with COPY YES parameter is started while an integrated snapshot backup is in-progress, the LOAD is blocked until the backup completes. If the integrated snapshot backup is started while a LOAD issued with COPY YES parameter is in-progress, the backup is blocked from starting, and may eventually timeout and fail with SQL1550N as it cannot acquire the necessary locks (LOAD only holds the conflicting locks for a short duration, so this is a rare timing condition).

If a LOAD issued with COPY NO parameter is started while an integrated snapshot backup is in-progress, the LOAD will fail. If the integrated snapshot backup is started while a LOAD issued with COPY NO parameter is in-progress, the backup will fail.

The cleanup phase of online table reorganization cannot start while an online backup is running. You can pause the table reorganization, if required, to allow the online backup to finish before resuming the online table reorganization.

You can start an online backup of a DMS table space when a table within the same table space is being reorganized online. There might be lock waits associated with the reorganization operation during the truncate phase.

You cannot start an online backup of an SMS table space when a table within the same table space is being reorganized online. Both operations require an exclusive lock.

Online DMS table space backup is compatible with DDLs that require a Z lock.

Online SMS table space backup must wait for the Z lock to be released.

The RUNSTATS command is compatible with online backup except when the system catalog table space is an SMS table space. If the system catalog resides in an SMS table space, then the RUNSTATS command and the online backup hold incompatible table locks on the table causing lock waits.

Operations that enable or disable autoresize, or alter autoresize containers, are not permitted during an online backup of a table space.

When online backup and rebalancer are running concurrently, online backup pauses the rebalancer and does not wait for it to complete.

You can perform an online table-space-level restore if the backup being performed is also a table-space-level backup, and the table-space being restored is different from the one being backed up.

You can perform an online table-space-level rollforward if the backup being performed is also a table-space-level backup, and the table-space being rolled forward is different from the one being backed up.

This applies to database-level online backups and table-space-level online backups (if they involve the same table space or table spaces).