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