DB2 - Changing Database and Log Path on an active database

This ties up with something that I need to do, where we have a DB2 database that has been created in the instance account's home directory rather than being created on a given path.

This example uses three subdirectories underneath the instance's home directory, BUT that's merely an example; in the "real" world, the data and logs should go onto dedicated and, perhaps, separate file-systems.

This post assumes that one is using the DB2 Sample database, as created using the db2sampl command.

Create the Backup Subdirectory

mkdir /home/db2inst1/backups

Create the Target Data and Log Subdirectories

mkdir /home/db2inst1dbdata

mkdir /home/db2inst1 dblogs

Start the DB2 Instance

db2start

Connect to the Database

db2 connect to sample

Quiesce the Database

db2 quiesce database immediate force connections

Drop the Connection

db2 connect reset

Backup the Database

db2 backup database sample to /home/db2inst1/backups/ compress without prompting

Validate the Backup

ls /home/db2inst1/backups/

SAMPLE.0.db2inst1.DBPART000.20141105200247.001

Connect to the Database

db2 connect to sample

Unquiesce the Database

db2 unquiesce database

Drop the Connection

db2 connect reset

Drop the Database

db2 drop db sample

Restore the Database into the new path

db2 restore database sample from /home/db2inst1/backups/ on /home/db2inst1/dbdata/ dbpath on /home/db2inst1/dbdata/

Validate the Restore

ls /home/db2inst1/dbdata/

db2inst1

Connect to the Database

db2 connect to sample

Update the Log Path

db2 update database configuration using NEWLOGPATH /home/db2inst1/dblogs/

Terminate the Connection

db2 terminate

Restart the Database Manager

db2stop

db2start

Connect to the Database

db2 connect to sample

Query the Employee Table

db2 "select * from db2inst1.employee"

Validate that the Log Path is being used

ls /home/db2inst1/dblogs/

NODE0000

The job, she is a good 'un

Kudos to this for guidance on the DB2 BACKUP command: -

DB2 Backup and Restore Commands