Monday, 10 September 2018

Using DB2 BACKUP to relocate a database

Following on from an earlier post: -

this MAY be an alternate approach to moving databases …...

Again, remember YMMV

Create Sample database

db2sampl 

...
Creating database "SAMPLE"...
Connecting to database "SAMPLE"...
Creating tables and data in schema "DB2INST1"...
Creating tables with XML columns and XML data in schema "DB2INST1"...

'db2sampl' processing complete
.
...

Validate Database Directory

db2 list db directory

...
System Database Directory

Number of entries in the directory = 1

Database 1 entry:

Database alias                       = SAMPLE
Database name                        = SAMPLE
Local database directory             = /home/db2inst1
Database release level               = 14.00
Comment                              =
Directory entry type                 = Indirect
Catalog database partition number    = 0
Alternate server hostname            =
Alternate server port number         =

...

Create Backup Directory

mkdir /tmp/sample

Backup Sample database

db2 backup database sample to /tmp/sample compress without prompting

...
Backup successful. The timestamp for this backup image is : 20180910112120
...

Validate backup file

ls -al /tmp/sample/

...
total 24660
drwxr-xr-x.  2 db2inst1 db2iadm1       59 Sep 10 11:21 .
drwxrwxrwt. 11 root     root         4096 Sep 10 11:21 ..
-rw-------.  1 db2inst1 db2iadm1 25247744 Sep 10 11:21 SAMPLE.0.db2inst1.DBPART000.20180910112120.001

...

Drop Sample database

db2 drop db sample

...
DB20000I  The DROP DATABASE command completed successfully.
...

Restore Sample database to NEW location

db2 restore database sample from /tmp/sample/ to /dbhome/

...
DB20000I  The RESTORE DATABASE command completed successfully.
...

Validate database directory

db2 list db directory

...
 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Local database directory             = /dbhome
 Database release level               = 14.00
 Comment                              =
 Directory entry type                 = Indirect
 Catalog database partition number    = 0
 Alternate server hostname            =
 Alternate server port number         =

...

Connect to Sample database

db2 connect to sample

...
Database Connection Information

Database server        = DB2/LINUXX8664 11.1.2.2
SQL authorization ID   = DB2INST1
Local database alias   = SAMPLE

...

Retrieve some data

db2 "select * from employee"

...
200280 EILEEN       R       SCHWARTZ        E11      8997    24/03/1997 OPERATOR      17 F   28/03/1966    46250.00      500.00     2100.00
200310 MICHELLE     F       SPRINGER        E11      3332    12/09/1994 OPERATOR      12 F   21/04/1961    35900.00      300.00     1272.00
200330 HELENA               WONG            E21      2103    23/02/2006 FIELDREP      14 F   18/07/1971    35370.00      500.00     2030.00
200340 ROY          R       ALONZO          E21      5698    05/07/1997 FIELDREP      16 M   17/05/1956    31840.00      500.00     1907.00

  42 record(s) selected.

...

Terminate connection

db2 terminate

No comments: