IBM Db2 restore redirect
In the last article we installed IBM Db2 11.5 on Red Hat, and we also created the SAMPLE database using the db2sampl command, so in this article we will see how to use the restore redirect script to move that database to a different location.
So let's connect to the database and see where our files are located.
db2inst1@redhat:/backup>
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.6.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2inst1@redhat:/backup>
$ db2 "select char(type, 30) type, char(path,80) path
from sysibmadm.dbpaths with ur"
TYPE PATH
------------------- ---------------------------------------------------------
LOGPATH /home/db2inst1/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
DB_STORAGE_PATH /home/db2inst1/
LOCAL_DB_DIRECTORY /home/db2inst1/db2inst1/NODE0000/sqldbdir/
DBPATH /home/db2inst1/db2inst1/NODE0000/SQL00001/
DBPATH /home/db2inst1/db2inst1/NODE0000/SQL00001/MEMBER0000/
5 record(s) selected.All our files are on /home/db2inst1 home directory of the db2inst1 user, where the db2sampl command was executed with no parameters like dbpath
db2inst1@redhat:/home/db2inst1>
$ db2sampl -?
db2sampl command
Purpose:
Creates a sample database with database objects and data used by DB2 samples.
Syntax:
db2sampl [-dbpath <path-name>] [-name <database-name>] [-force]
[-verbose | -quiet] [-sql] [-xml] [-v8] [-encrypt
[-encropts <options>]] [-?]
Options:
-? or ? or help : Display the db2sampl command syntax.
-dbpath <path-name> : Store the database files in the path <path-name>.
-name <database-name> : Set the database name to <database-name>.
-force : Drop existing database with the same name.
-sql : Create SQL database objects and data.
-xml : Create XML database objects and data.
-verbose : Print status messages to standard output.
-quiet : Suppress printing of status messages.
-v8 : Create SAMPLE database from DB2 UDB V8.
-encrypt : Create SAMPLE database with default encryption options.
-encropts <options>
: Customize the encryption options used in the -encrypt command.
Before doing anything we execute an offline database backup, so if anything happens we can recover from it.
db2inst1@redhat:/home/db2inst1>
$ cd /backup
db2inst1@redhat:/backup>
$ db2 backup db sample to . compress
Backup successful. The timestamp for this backup image is : 20220222110628And we check the integrity of the backup running db2ckbkp
db2inst1@redhat:/backup>
$ db2ckbkp SAMPLE.0.db2inst1.DBPART000.20220222110628.001
[1] Buffers processed: #######
Image Verification Complete - successful.We can now run the restore command with the redirect generate script, where we provide where our current backup file is /backup, the timestamp 20220222110628 of the backup file and the new destination of the database /db2inst1/sample and as mentioned on this blog post The key to a restore with automatic storage is the ON parameter.
db2inst1@redhat:/backup>
db2 "restore db sample from /backup taken at 20220222110628 ON '/db2inst1/sample' into sample redirect generate script SAMPLE_restore.db2"
DB20000I The RESTORE DATABASE command completed successfully.db2inst1@redhat:/home/db2inst1>
$ cat SAMPLE_restore.db2
-- ****************************************************************************
-- ** automatically created redirect restore script
-- ****************************************************************************
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON;
SET CLIENT ATTACH_MEMBER 0;
SET CLIENT CONNECT_MEMBER 0;
-- ****************************************************************************
-- ** automatically created redirect restore script
-- ****************************************************************************
RESTORE DATABASE SAMPLE
-- USER <username>
-- USING '<password>'
FROM '/backup'
TAKEN AT 20220222110628
ON '/db2inst1/sample'
DBPATH ON '/db2inst1/sample'
INTO SAMPLE
-- NEWLOGPATH DEFAULT
-- WITH <num-buff> BUFFERS
-- BUFFER <buffer-size>
-- REPLACE HISTORY FILE
-- REPLACE EXISTING
REDIRECT
-- PARALLELISM <n>
-- COMPRLIB '<lib-name>'
-- COMPROPTS '<options-string>'
WITHOUT ROLLING FORWARD
-- WITHOUT PROMPTING
;
-- *****************************************************************************
-- ** storage group definition
-- ** Default storage group ID = 0
-- ** Number of storage groups = 1
-- ****************************************************************************
-- ****************************************************************************
-- ** Storage group name = IBMSTOGROUP
-- ** Storage group ID = 0
-- ** Data tag = None
-- ****************************************************************************
-- SET STOGROUP PATHS FOR IBMSTOGROUP
-- ON '/home/db2inst1'
-- ;
-- ****************************************************************************
-- ** table space definition
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = SYSCATSPACE
-- ** Tablespace ID = 0
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Regular table space.
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 4
-- ** Using automatic storage = Yes
-- ** Storage group ID = 0
-- ** Source storage group ID = -1
-- ** Data tag = None
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 20480
-- ** Number of usable pages = 20476
-- ** High water mark (pages) = 16404
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = TEMPSPACE1
-- ** Tablespace ID = 1
-- ** Tablespace Type = System managed space
-- ** Tablespace Content Type = System Temporary data
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Total number of pages = 1
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = USERSPACE1
-- ** Tablespace ID = 2
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Storage group ID = 0
-- ** Source storage group ID = -1
-- ** Data tag = -1
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 4096
-- ** Number of usable pages = 4064
-- ** High water mark (pages) = 1824
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = IBMDB2SAMPLEREL
-- ** Tablespace ID = 3
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Storage group ID = 0
-- ** Source storage group ID = -1
-- ** Data tag = -1
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 4096
-- ** Number of usable pages = 4064
-- ** High water mark (pages) = 608
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = IBMDB2SAMPLEXML
-- ** Tablespace ID = 4
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 32
-- ** Using automatic storage = Yes
-- ** Storage group ID = 0
-- ** Source storage group ID = -1
-- ** Data tag = -1
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 4096
-- ** Number of usable pages = 4064
-- ** High water mark (pages) = 1440
-- ****************************************************************************
-- ****************************************************************************
-- ** Tablespace name = SYSTOOLSPACE
-- ** Tablespace ID = 5
-- ** Tablespace Type = Database managed space
-- ** Tablespace Content Type = All permanent data. Large table space.
-- ** Tablespace Page size (bytes) = 8192
-- ** Tablespace Extent size (pages) = 4
-- ** Using automatic storage = Yes
-- ** Storage group ID = 0
-- ** Source storage group ID = -1
-- ** Data tag = -1
-- ** Auto-resize enabled = Yes
-- ** Total number of pages = 4096
-- ** Number of usable pages = 4092
-- ** High water mark (pages) = 104
-- ****************************************************************************
-- ****************************************************************************
-- ** start redirected restore
-- ****************************************************************************
RESTORE DATABASE SAMPLE CONTINUE;
-- ****************************************************************************
-- ** end of file
-- ****************************************************************************
db2inst1@redhat:/home/db2inst1>
$Once reviewed, let's run it.
$ db2 -stvf SAMPLE_restore.db2
UPDATE COMMAND OPTIONS USING S ON Z ON SAMPLE_NODE0000.out V ON
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
SET CLIENT ATTACH_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
SET CLIENT CONNECT_MEMBER 0
DB20000I The SET CLIENT command completed successfully.
RESTORE DATABASE SAMPLE FROM '/backup' TAKEN AT 20220222110628 ON '/db2inst1/sample' DBPATH ON '/db2inst1/sample' INTO SAMPLE REDIRECT WITHOUT ROLLING FORWARD
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.
RESTORE DATABASE SAMPLE CONTINUE
DB20000I The RESTORE DATABASE command completed successfully.
db2inst1@redhat:/home/db2inst1>
$ db2 terminate
DB20000I The TERMINATE command completed successfully.
and now we can connect to the database and check again the PATH of the files.
db2inst1@redhat:/home/db2inst1>
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.6.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
db2inst1@redhat:/home/db2inst1>
$ db2 "select char(type,30)type,char(path,80)path from sysibmadm.dbpaths"
TYPE PATH
------------------ ----------------------------------------------------------
LOGPATH /db2inst1/sample/db2inst1/NODE0000/SQL00001/LOGSTREAM0000/
DB_STORAGE_PATH /db2inst1/sample/
LOCAL_DB_DIRECTORY /db2inst1/sample/db2inst1/NODE0000/sqldbdir/
DBPATH /db2inst1/sample/db2inst1/NODE0000/SQL00001/
DBPATH /db2inst1/sample/db2inst1/NODE0000/SQL00001/MEMBER0000/
5 record(s) selected.
