Db2 : restore a database from an instance to another and set grant to administer database

This post describe how to restore a database backup from an instance to another and set grant; I assume you have successfully installed Db2 and configured a new instance.

Step 1 restore db:

Use this command to restore db*:

db2 restore db <dbname> from <backupdir> ON <datapath> NEWLOGPATH <tranlogpath>

<dbname> = original database name
<backupdir> = path where backup is located (if more then one backup is present you must specify timestamp with comand TAKEN AT)
<datapath> = where you want to store your database path tipically /home/<instancename>/
<tranlogpath> = where you want to store tranlog (I use : /home/<instancename>/tranlog)

* A database backup contains settings for path so if you move Db from one server to another you have to specify new filesystem path

Step 2 grant :

Use this command to set grant:

db2 CONNECT TO <dbname> user <databaseowner> using <databaseownerpassword>;
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER <newuser>;
db2 CONNECT RESET;

<dbname> = original database name
<databaseowner> = grant must be set from the old db owner, to do this I usually create a new user whit old name on new server.
<databaseownerpassword> = password
<newuser> = new owner

 


Example:

Immagine you have a server named server1 with instance db2inst1 and a database named wpsdb.
Now immagine you have to move a wpsdb to server2 with instance name db2wps.
What you have to do is :

– Restore db: db2 restore db wpsdb from /backup ON /home/db2wps NEWLOGPATH /home/db2wps/tranlog
– create user db2inst1 on server2:(banally “useradd db2inst1” and “passwd db2inst1” to set password)
– grant:

db2 CONNECT TO wpsdb user db2inst1 using password
db2 GRANT DBADM,CREATETAB,BINDADD,CONNECT,CREATE_NOT_FENCED_ROUTINE,IMPLICIT_SCHEMA,LOAD,CREATE_EXTERNAL_ROUTINE,QUIESCE_CONNECT,SECADM ON DATABASE TO USER db2wps
db2 CONNECT RESET

Leave a Reply

Your email address will not be published. Required fields are marked *

Antispam Question * Time limit is exhausted. Please reload CAPTCHA.

This site uses Akismet to reduce spam. Learn how your comment data is processed.