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.
[email protected]:/backup>
$ db2 connect to sample
Database Connection Information
Database server = DB2/LINUXX8664 11.5.6.0
SQL authorization ID = DB2INST1
Local database alias = SAMPLE
[email protected]:/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
[email protected]:/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.
[email protected]:/home/db2inst1>
$ cd /backup
[email protected]:/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
[email protected]:/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.
[email protected]:/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.[email protected]:/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
-- ****************************************************************************
[email protected]:/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.
[email protected]:/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.
[email protected]:/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
[email protected]:/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.Want to thank me?
