Restore DB script returns error: "database is in use" during drop database command

grofaty · #1 (**permalink**) 03-18-09, 02:54

Hi,
db2 v9.5 ese fp2 on Linux I have daily restore script:

Code:

1. db2 force application all
2. sleep 5
3. db2 drop database $DBname
4. db2 terminate
5. db2 "restore database ORIGDB from $BackupDir to $RestoreDir into $DBname logtarget /restore_logs"
6. db2 "rollforward db $DBname to end of logs and stop overflow log path (/restore_logs)"

Note: I have put numbers in this script to easily read output of commands bellow.

The output is:

Code:

1. DB20000I  The FORCE APPLICATION command completed successfully.
   DB21024I  This command is asynchronous and may not be effective immediately.
2. {Note: sleep command does not have output}
3. SQL1035N  The database is currently in use.  SQLSTATE=57019
4. DB20000I  The TERMINATE command completed successfully.
5. SQL2528W  Warning!  Restoring to an existing database that is the same as the
   backup image database, but the alias name "MYDB" of the existing database
   does not match the alias "ORIGDB" of backup image, and the database name
   "MYDB" of the existing database does not match the database name "ORIGDB" of
   the backup image.  The target database will be overwritten by the backup
   version.
   Do you want to continue ? (y/n) SQL2001N  The utility was interrupted.  The output data may be incomplete.
   SQL1035N  The database is currently in use.  SQLSTATE=57019

This problem appears ones per few days and repeating above script solves the problem.

According to commands output some applications are not forced from database, so drop database command fails. Then when restore is executed restore operation finds out that database already exists (because drop database failed) and asks if restore should overwritten data already included in database.

How should I write script that would force all applications and wait until force application is completed and then execute "drop database"? There is also possible that during force applications new application connects to database, so this also should be prevented.

Regards

Marcus_A · #2 (**permalink**) 03-18-09, 03:18

I would try this:

1. db2 force application all
2. sleep 2
3. db2 force application all
4. sleep 1
5. db2 drop database $ImeBaze2

nick.ncs · #3 (**permalink**) 03-18-09, 05:07

how about using the good old do while loop in your shell script.....

Quote:

var1=`db2 list applications for db $ImeBaze2 | wc -l`
while [ $var1 != 0 ]
do
db2 force application all
sleep 5
var1=`db2 list applications for db $ImeBaze2 | wc -l`
done
db2 drop database $ImeBaze2

ARWinner · #4 (**permalink**) 03-18-09, 08:23

You can also get this if the database was explicitly activated with the ACTIVATE DATABASE command. In which case you will need to issue explicitly the DEACTIVATE DATABASE command, even if there are no connections to it.

Andy

grofaty · #5 (**permalink**) 03-19-09, 12:35

Hi,
I have changed code little bit and now it works without problem.

Code:

var1=1  # set variable to non-zero
while [ $var1 != 0 ]
do
     db2 force application all
     sleep 5
     var1=$(db2 list applications for db $DBname | grep -v SQL1611W | wc -l)
done
db2 drop database $DBname

Note: SQL1611W is returned if no application are connected to database.

Thanks for help.
Regards

grofaty · #6 (**permalink**) 03-20-09, 03:04

Hi,
funny thing... today backup failed again and there was 0 application connected and db was not activated.

I found out that one of DBAs has quiesced database. I executed bellow command:
db2pd -db mydb | grep -i "Database Partition 0 -- Database"
and output is:
"Database Partition 0 -- Database MYDB -- Quiesced"

So I have executed:
db2 connect to mydb
db2 unquiesce database
db2 connect reset

The db2pd command now returned:
Database Partition 0 -- Database MYDB -- Active

I repeated the newly written restore script and it worked!
Problem solved.

Thanks for help.
Grofaty

Internet Services

Web Development

Digital Marketing

Consumer Tech