Backup and restore using utilities

You can backup and restore databases using remote storage groups using Db2 backup and restore utilities.

Using the Db2 backup and restore utilities

Db2® backup and restore utilities can be used to perform backup and restore in environments that have table spaces defined on cloud object storage using Native Cloud Object Storage (NCOS). Refer to Backing up a Db2 Warehouse database for specific information on each backup and restore utility.

Using the Db2 restore utility to redefine table spaces

The Db2 restore utility can be used to redefine table spaces. You can accomplish this by using either the ON or REDIRECT options, or both, from the RESTORE command. There are several examples of using these commands and options to redefine table spaces.

For the purpose of understanding the restore examples, the storage hierarchy of the SAMPLE database is set up as follows:

Table 1. Storage hierarchy of the SAMPLE database
Storage level Definition
Storage alias ALIAS1 – vendor=S3; container=mybucket; object=default
Storage groups
  • Local: IBMSTOGROUP (/path1) [default]
  • Local: MYSG1 (/path2)
  • Remote: MYDATASG1 (DB2REMOTE://ALIAS1//
  • Remote: MYTMPSG1 (DB2REMOTE://ALIAS1//)
Table spaces
  • SYSCATSPACE (DMS), STMP1 (SMS), and USRTMP1 (SMS) reside in IBMSTOGROUP
  • USR1 (DMS) resides in MYSG1
  • USR2 (DMS) resides in MYDATASG1
  • USRTMP2 (DMS) resides in MYTMPSG1
Database backup image B1 containing all table spaces

Certain table space types cannot be redefined, however. SYSCATSPACE and any SMS table space cannot be redefined to use a remote storage group. To overcome this limitation, these type of table spaces should be placed into their own local storage group(s), isolated from the remaining table spaces.

Any user temporary DMS table space using a remote storage group cannot be redefined to use a local storage group. The table space will not be restored, so it must be re-created. If this table space shares a storage group with any non-temporary table space, then that other table space also cannot be redefined. Any user temporary DMS table space should be placed into its own remote storage group, isolated from the remaining table spaces.

LIMITATIONS

These operations are not supported for remote storage:
  • db2inidb MIRROR and STANDBY options
  • split mirror backup [BACKUP on a database after db2inidb as MIRROR] on databases with a remote storage group
  • integrated snapshot backup/restore [USE SNAPSHOT] on databases with a remote storage group
  • restore with ON clause using a remote storage path
  • restore where trying to redefine a local storage group that SYSCATSPACE uses to a remote storage path
  • restore where trying to redefine a local storage group that a SMS table space uses to a remote storage path
  • restore where trying to redefine a remote storage group that a user temporary table space uses to a local storage path
  • restore transport schema [TRANSPORT] where the target database uses a default remote storage group
  • restore transport schema [TRANSPORT] with USING STOGROUP clause using a remote storage path
  • HADR in environments that support Native COS

Example 1: redefine all table spaces

Situation: On the target host, the remote storage path represented by DB2REMOTE://ALIAS1// is not available.

Through restore, redefine all table spaces in the database to use a single local storage path under /path3. This can be accomplished using the ON clause: 
 db2 restore db sample on /path3 
   SQL2563W  The restore process completed successfully. However, one or more table spaces from the backup image were not restored.

All storage groups are now using /path3.

User temporary DMS table space USRTMP2 is not available as it cannot be redefined to use a local storage path. USRTMP2 can be dropped and re-created.

Example 2: redefine only table spaces using remote storage

Situation: On the target host, the remote storage path represented by DB2REMOTE://ALIAS1// is not available.

Through restore, redefine only the table spaces using remote storage to use a single local storage path under /path2. This can be accomplished using the REDIRECT clause and the SET STOGROUP PATHS statement.
    db2 restore db sample redirect 
    SQL1277W  A redirected restore operation is being performed. During a table
    space restore, only table spaces being restored can have their paths
    reconfigured. During a database restore, storage group storage paths and DMS
    table space containers can be reconfigured.
    DB20000I  The RESTORE DATABASE command completed successfully.

    db2 set stogroup paths for MYDATASG1 on '/path2'
    DB20000I  The SET STOGROUP PATHS command completed successfully.

    db2 set stogroup paths for MYTMPSG1 on '/path2'
    SQL0298N  Bad container path.  SQLSTATE=428B2

    db2 restore db sample continue
    SQL2563W  The restore process completed successfully.  However, one or more
    table spaces from the backup image were not restored.

The storage group MYDATASG1 is now using /path2. The storage group IBMSTOGROUP is still using /path1. The storage group MYSG1 is still using /path2.

The storage group MYTMPSG1 is still using DB2REMOTE://ALIAS1//. Since the storage group contains the user temporary DMS table space USRTMP2, the storage group cannot be redefined to use a local storage path. Table space USRTMP2 and remote storage group MYTMPSG1 can be dropped and re-created using local storage:

Example 3: redefine only local storage table spaces

Through restore, redefine only the table spaces using local storage that are compatible with remote storage to use a single remote storage path under DB2REMOTE://ALIAS1/. You can do this by using the REDIRECT clause and the SET STOGROUP PATHS statement.


    db2 restore db sample redirect 
    SQL1277W  A redirected restore operation is being performed. During a table
    space restore, only table spaces being restored can have their paths
    reconfigured. During a database restore, storage group storage paths and DMS
    table space containers can be reconfigured.
    DB20000I  The RESTORE DATABASE command completed successfully.

    db2 set stogroup paths for IBMSTOGROUP on 'DB2REMOTE://ALIAS1'
    SQL1668N  The operation failed because the operation is not supported with
    this environment. Reason code: "17".

    db2 set stogroup paths for MYSG1 on 'DB2REMOTE://ALIAS1'
    DB20000I  The SET STOGROUP PATHS command completed successfully.

    db2 restore db sample continue
    DB20000I  The RESTORE DATABASE command completed successfully.

The storage group MYSG1 is now using DB2REMOTE://ALIAS1//. The storage group IBMSTOGROUP is still using /path1. The storage groups MYDATASG1 and MYTMPSG1 are still using DB2REMOTE://ALIAS1//.

The local storage group IBMSTOGROUP cannot be redefined to use remote storage paths since the storage group contains SYSCATSPACE and SMS table spaces.

Example 4: redefine all table spaces using remote storage

Situation: On the target host, the remote storage path represented by DB2REMOTE://ALIAS1// is not available.

Through restore, redefine all table spaces using remote storage to a new storage access alias DB2REMOTE://ALIAS2//. Also, redefine all local storage and the database path to /path3. This can be accomplished using the ON and REDIRECT clause and the SET STOGROUP PATHS statement.
    db2 restore db sample on /path3 dbpath on /path3 redirect 
    SQL1277W  A redirected restore operation is being performed. During a table
    space restore, only table spaces being restored can have their paths
    reconfigured. During a database restore, storage group storage paths and DMS
    table space containers can be reconfigured.
    DB20000I  The RESTORE DATABASE command completed successfully.

    db2 set stogroup paths for MYDATASG1 on 'DB2REMOTE://ALIAS2'
    DB20000I  The SET STOGROUP PATHS command completed successfully.

    db2 set stogroup paths for MYTMPSG1 on 'DB2REMOTE://ALIAS2'
    DB20000I  The SET STOGROUP PATHS command completed successfully.

    db2 restore db sample continue
    DB20000I  The RESTORE DATABASE command completed successfully.
The storage groups IBMSTOGROUP and MYSG1 are now using /path3. The storage groups MYDATASG1 and MYTMPSG1 are now using DB2REMOTE://ALIAS2//.