DB2 - Moving databases

This is definitely a Your Mileage May Vary (YMMV) post.

If in doubt, please check with IBM Support *BEFORE* following the steps outlined here …

So I had a requirement to rename some IBM BPM databases from their default names of BPMDB, CMNDB and PDWDB.

This is related to IBM BPM 8.6 on DB2 v11.1.2.2 although the same approach works for DB2 v10.5 as well.

Thankfully DB2 comes with a useful db2relocate tool, as described here:-

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_10.5.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0004500.html

So, before doing this for real, I wanted to test it using the SAMPLE database.

This is what I did ….

Switch to the instance owner

su - db2inst1

Create the 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 the current catalog

db2 list db directory

 System Database Directory

 Number of entries in the directory = 4
…

Database 4 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         =

Validate the current DB storage

ls -al /home/db2inst1/db2inst1/NODE0000/SAMPLE

total 4
drwx--x--x   8 db2inst1 db2iadm1  114 Jul 23 13:39 .
drwxrwxr-x. 11 db2inst1 db2iadm1 4096 Jul 23 13:36 ..
-rw-------   1 db2inst1 db2iadm1    0 Jul 23 13:36 .SQLCRT.FLG
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000000
drwx--x--x   3 db2inst1 db2iadm1   43 Jul 23 13:37 T0000001
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000002
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000003
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000004
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:39 T0000005

Connect to SAMPLE

db2 connect to sample

   Database Connection Information

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

Check that we can access data

db2 "select * from db2inst1.employee where empno = '000010'"

EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE   JOB      EDLEVEL SEX BIRTHDATE  SALARY      BONUS       COMM       
------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
000010 CHRISTINE    I       HAAS            A00      3978    01/01/1995 PRES          18 F   24/08/1963   152750.00     1000.00     4220.00

  1 record(s) selected.

Terminate the connection

db2 terminate

DB20000I  The TERMINATE command completed successfully.

Create a template configuration file

This defines the FROM and TO states

vi sample.cfg

DB_NAME=SAMPLE,SAMPLENE
DB_PATH=/home/db2inst1
INSTANCE=db2inst1

Move the database from the old container to the new container

Note that this works for me because my database has a single partition, and is located in the instance owner's home directory

- This is where YOUR mileage MAY/WILL vary

mv /home/db2inst1/db2inst1/NODE0000/SAMPLE /home/db2inst1/db2inst1/NODE0000/SAMPLENE

Validate the new DB storage layout

 ls -al /home/db2inst1/db2inst1/NODE0000/SAMPLENE/

total 4
drwx--x--x   8 db2inst1 db2iadm1  114 Jul 23 13:39 .
drwxrwxr-x. 11 db2inst1 db2iadm1 4096 Jul 23 14:28 ..
-rw-------   1 db2inst1 db2iadm1    0 Jul 23 13:36 .SQLCRT.FLG
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000000
drwx--x--x   3 db2inst1 db2iadm1   43 Jul 23 13:37 T0000001
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000002
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000003
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:36 T0000004
drwx--x--x   2 db2inst1 db2iadm1   43 Jul 23 13:39 T0000005

Run the db2relocate command to update the catalog

db2relocatedb -f sample.cfg

Files and control structures were changed successfully.
Database was catalogued successfully.
DBT1000I  The tool completed successfully.

Validate the updated catalog

db2 list db directory

 System Database Directory

 Number of entries in the directory = 4
…
Database 4 entry:

 Database alias                       = SAMPLENE
 Database name                        = SAMPLENE
 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         =

Connect to SAMPLENE

db2 connect to samplene

   Database Connection Information

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

Check that we can access data

db2 "select * from db2inst1.employee where empno = '000010'"

EMPNO  FIRSTNME     MIDINIT LASTNAME        WORKDEPT PHONENO HIREDATE   JOB      EDLEVEL SEX BIRTHDATE  SALARY      BONUS       COMM       
------ ------------ ------- --------------- -------- ------- ---------- -------- ------- --- ---------- ----------- ----------- -----------
000010 CHRISTINE    I       HAAS            A00      3978    01/01/1995 PRES          18 F   24/08/1963   152750.00     1000.00     4220.00

  1 record(s) selected.

Terminate the connection

db2 terminate

DB20000I  The TERMINATE command completed successfully.

Again, this is definitely a Your Mileage May Vary (YMMV) post.

If in doubt, please check with IBM Support *BEFORE* following the steps outlined here …