Db2 Utilities that are Compatible/Non-compatible with running an online backup.

Using Db2 Backup command you can take copy of entire database. This backup copy includes database system files, data files, log files, control information and so on. You can take backup while working offline as well as online.

In this article we will see and learn about Db2 utilities which are compatible and non-compatible with running an online backup.

Db2 Utilities compatible with online backup

• EXPORT

• ONLINE INSPECT

Db2 Utilities not compatible with online backup

• RESTORE

• ROLLFORWARD

• REORG TABLE

• OFFLINE LOAD

• ONLINE BACKUP

• SET WRITE

Db2 Utilities compatible with online backup only under certain circumstances

• RUNSTATS : Runstats is compatible with online backup except when the system catalog table space is a SMS table space. This is because if the SYSIBM.SYSTABLES resides in a SMS table space, then runstats and online backup will hold incompatible table lock on the table, causing lock waits.
  

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

• ONLINE LOAD : Online load is not compatible with online backup when the LOAD command is issued with the COPY NO option. In this mode the utilities both modify the table space state, causing one of the utilities to report an error. Online load is compatible with online backup when the LOAD command is issued with the COPY YES option, although there may still be some compatibility issues. In SMS mode, the utilities can execute concurrently, but they will hold incompatible table lock modes and consequently may be subject to table lock waits. In DMS mode, the utilities both hold incompatible "Internal-B" (OLB) lock modes and may be subject to waits on that lock. If the utilities execute on the same table space concurrently, the load utility may be forced to wait for the backup utility to complete processing of the table space before the load utility can proceed.
  

• ONLINE TABLE REORG : The clean up phase of online table reorganization cannot start while an online backup is running. A customer can pause the table reorganization, if required, to allow the online backup to finish before resuming the online table reorg.
  

• Online Create Index : In SMS mode, online index create and online backup will not be compatible due to the ALTER TABLE lock. Where 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 indexes that the online index create will internally acquire an online backup lock that will conflict with any concurrent online backup.
  

• ONLINE INDEX REORG : In SMS mode, online index reorganization and online backup are incompatible in the same way that online index create and online backup are incompatible. In DMS mode, online index reorganization and online backup can run concurrently in most cases. The only case where they are not compatible is the same case that is described above for online index create and online backup. In addition, online index reorganization quiesces the table before the switch phase and gets a Z lock, which prevents an online backup.
  

• REBALANCE : When online backup and rebalancer are running concurrently, online backup will pause the rebalancer and does not wait for it to complete.
  

• DDLs that require a Z lock (such as ALTER TABLE, DROP TABLE and DROP INDEX) : 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.

That's all in this post. If you liked this blog and interested in knowing more about IBM Db2. Please Like, Follow, Share & Subscribe to www.ImJhaChandan.com