Tag: db2

Start a db2 instance as daemon

Just because I always have problem with db2 autostart I’ve decided to use this simply, fast and flexible script:


#!/bin/sh
### BEGIN INIT INFO
# Provides:          IBM-Db2-db2inst1
# Required-Start:    $local_fs $remote_fs $network $syslog sshd
# Required-Stop:     $local_fs $remote_fs $network $syslog sshd
# Default-Start:     3 5
# Default-Stop:      0 1 6
# Short-Description: Start/stop IBM DB2 instance
### END INIT INFO
#
# IBM Db2 : This init.d script starts the db2inst1 instance

NAME=`basename $0`

instance_User=db2inst1



start() {
    echo -n $"Starting ${NAME} service: "
       $tds_start ; > /dev/null
	   su - $instance_User -c "db2start"  
    ret=$? 
    if [ $ret -eq 0 ]
    then
            echo "${NAME} Started."
    else
            echo "${NAME} Starting Failed!"
            exit 1
    fi
    echo
}

stop() {
    echo -n $"Starting ${NAME} service: "
       $tds_start ; > /dev/null
	   su - $instance_User -c "db2stop"  
    ret=$? 
    if [ $ret -eq 0 ]
    then
            echo "${NAME} Started."
    else
            echo "${NAME} Starting Failed!"
            exit 1
    fi
    echo
}

case "$1" in
    start)
        start
        ;;
    stop)
        stop
        ;;		
    *)
        echo $"Usage: $0 {start|stop}"
        exit 1
esac
exit 0

Db2 set online logs and enable autodelete for logs and backup

A simple script to set logs for online backup :

 


#!/bin/sh

#First of all create archive logs dir in the home path of the instance owner.
mkdir -p /$HOME/archiveLog

#Change log settings.
db2 UPDATE DB CFG FOR $1 USING logarchmeth1 "DISK:/data/archiveLog" logprimary 15 logsecond 15 logfilsiz 8000

#Backup Db.
db2 BACKUP DATABASE $1 TO "/backup/db2";

#Update log and backup retention (I set to 2).
db2 update db cfg for $1 using rec_his_retentn 2

#Enable auto delete.
db2 update db cfg for $1 using auto_del_rec_obj on

#I setup backup to maintain the last 2 backup.
db2 update db cfg for $1 using num_db_backups 2

You can copy and paste in a script named “setLogs” give grant and run by instance owner:

./setLogs DatabaseName

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

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

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