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

Step by step htadmin config to use with Websphere Application Server

Here you can find step by step configuration for use IHS htadmin

Step 1 generate user :

/opt/ibm/HTTPServer/bin/htpasswd -cm /opt/ibm/HTTPServer/conf/admin.passwd htadmin

Step 2 edit admin.conf :

vi /opt/ibm/HTTPServer/conf/conf/admin.conf

#Change listen port:
Listen 8008

#Change running user
User wwwrun
Group www

#Set serverName
ServerName servername.mydomain.com

Step 3 add user and group to run htadmin:
groupadd www
useradd wwwrun -g www

Step 4 run htadmin:

/opt/ibm/HTTPServer/bin/adminctl start

Generate command list for all instance’s Dbs

-) Step 1 : this scrip is an example to prune all logs and backup of a db.

#################### prune.sh
db2 connect to $1
db2 PRUNE HISTORY 20131007 AND DELETE

 

-) Step 2 : generate a command list to prune object for all Dbs in the instance

db2 list database directory | grep alias | awk ‘{print “./prune.sh ” $4}’ > cmdlist

Solve problem with mysql closed pool by server

This configuration solve problem with following error:


org.springframework.web.util.NestedServletException: Request processing failed; nested exception is org.springframework.dao.RecoverableDataAccessException: PreparedStatementCallback; SQL XXXXXXXXXXXXXXXXXX; Communications link failure

The last packet successfully received from the server was 30,184,929 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.; nested exception is com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure

The last packet successfully received from the server was 30,184,929 milliseconds ago. The last packet sent successfully to the server was 0 milliseconds ago.
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:894)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

<Resource auth="Container1"
          driverClassName="com.mysql.jdbc.Driver"
          maxActive="20"
          maxIdle="10"
          maxWait="120"
          testWhileIdle="true"
          validationQuery="select 1"
          name="jdbc/myapp"
          password="passwd" 
          type="javax.sql.DataSource"
          url="jdbc:mysql://127.0.0.1/mydb?autoReconnect=true&amp;useOldAliasMetadataBehavior=true"
          username="user"/>

In detail this lines validate connections before use and eliminates closed ones:

maxActive=”20″
maxIdle=”10″
maxWait=”120″
testWhileIdle=”true”
validationQuery=”select 1″

Execute command on all Db2 instances

Here the script I use to verify all backup on all dbs on all instances.
I use it with the “db2-check-backup.sh” documented Here

#!/bin/sh
UNIQUE=`date +%s`
LPATH=$(pwd)
INST=/tmp/instances.$UNIQUE
OUT=/tmp/outallinst.$UNIQUE
 
/opt/ibm/db2/V9.7/bin/db2ilist > $INST
 
while read LN; do
 echo "###########"  $LN  >> $OUT
 $LPATH/db2-check-backup.sh $LN >>$OUT
done < $INST
 
cat $OUT
rm $OUT
rm $INST