SQLSTATE Error Backing up DB

[Tim2525]

Hi All,

I'm attempting to force application via script. I get a message stating completed. When I attempt to backup the DB, I get a get a SQLSTATE error. I am very new to db2. If anyone has any suggestions, they would be most welcome.


Error...

FORCING APPLICATION TO CLOSE...
DB20000I The FORCE APPLICATION command completed successfully.
DB21024I This command is asynchronous and may not be effective immediately.

BEFORE SLEEP MODE 15 SECONDS...
SLEEP CYCLE IS COMPLETE. MOVING ON TO BACKUP...
BACKUP PROCESS STARTED...
SQL1035N The database is currently in use. SQLSTATE=57019
BACKUP PROCESS COMPLETED...
building file list ... done


Code...
#!/usr/bin/ksh

#echo Started back up on `date` > /db2_backup/timer.txt

dbname=campmor5
if [ "$dbname" = "" ]
then
echo "Usage : " $0 "<dbname>"
exit
fi

listcmd="db2 list applications for database $dbname"

$listcmd
printf '%s %s\n' "DEACTIVATING CAMPMOR5 DATABASE... $dbname"
db2 deactivate database $dbname
$listcmd | tail +5 | nawk '{print $3}' | while read applid
do
if [ "$applid" != "" ]
then
printf '%s %s\n' "FORCING APPLICATION TO CLOSE..."
#db2 "force application($applid)"
db2 force application all
fi
done

printf '%s %s\n' "BEFORE SLEEP MODE 15 SECONDS..."

#Snoozing for 5 seconds...
sleep 15

printf '%s %s\n' "SLEEP CYCLE IS COMPLETE. MOVING ON TO BACKUP..."

#Backing up DB...
printf '%s%s\n' "BACKUP PROCESS STARTED..."
db2 backup database campmor5 to /export/home/db2inst1/db2_backup
printf '%s %s\n' "BACKUP PROCESS COMPLETED..."

##SSH file to George..
rsync -a -e ssh -v /export/home/db2inst1/db2_backup db2inst1@enolagay:/export/home/db2inst1/db2_backup
#Checking for successful file transfer...
if ! rsync -a -e ssh -v /export/home/db2inst1/db2_backup db2inst1@enolagay:/export/home/db2inst1/db2_backup
then
echo "CAMPMOR DATABASE BACKUP FAILED" >> /db2_backup/timer.txt
print "CAMPMOR DATABASE BACK UP FAILED"
print "CHECK /EXPORT/HOME/DB2INST1/BACKUPCAMPMOR FOR BACKUP EXISTENCE."
exit 1
fi

#echo Back up completed on `date` >> /db2_backup/timer.txt
#echo --------------------------------------------------- >> /db2_backup/timer.txt
#exit

TIA,

T

 

[gregsimpson]

Tim,

it seesm to me that either your FORCE APPLICATION, still has work to do after your 15 seconds, or in the meantime something else has connected. Rather than specify each application you can issue

DB2 FORCE APPLICATION ALL

which should asynchrously kill all connections.

Issue another db2 list applications after your force and see what is still connected to the database.

You may be interested in the quiesce command.

http://publib.boulder.ibm.com/infoc...?topic=/com.ibm.db2.udb.doc/core/r0008635.htm

IT can also be a bit quirky if the session running has a connection so you may wish to issue a

DB2 TERMINATE

just in case

Cheers
Greg

 

[Tim2525]

Greg,

Thanks for the reply. I'll give your suggestions a go.

T

 

[rbod]

I see that you are doing a :
$listcmd | tail +5 | nawk '{print $3}' | while read applid
tail +5 -->skips the first 4 lines. So, there are still applications connected to the database. Thats why the error.
You can modify it as :
$listcmd | grep ${DBNAME} | nawk '{print $3}' | while read applid

Why you need the "sleep 15"?

the way we do it is as follows [slight modification from yours]:
count=0
set -A APPLIST `db2 "list applications for database ${DBNAME}" | grep ${DBNAME} | cut -c25-34`
echo "Forcing any existing ${DBNAME} applications off"
if [[ ${APPLIST[$count]} = "" ]]
then
continue
else
while [[ ${APPLIST[$count]} != "" ]]
do
db2 "force application(${APPLIST[$count]})"
(( count=count+1 ))
done
fi
echo "Performing OFFLINE backup of database ${DBNAME}"
db2 "deactivate database ${DBNAME}"
db2 "backup database ${DBNAME} to ${DBDUMPPATH}...

 

[gregsimpson]

Rbod,

good spot. Out of interest, is there a reason you figure out the applications connected to remove them individually, rather than issuing a DB2 FORCE APPLICATION ALL.

Cheers
Greg

 

[Tim2525]

Rbod,

Thanks for the information. Will give it a try.

T

 

[Tim2525]

Rbod,

I used the sleep 15 out of ignorance. I'm very new to scripting. Someone told me it was a good idea.

I took your code/idea and incorporated it into mine. Hope you don't mind. It looks much cleaner.

Thanks,

T

 

[Tim2525]

Rbob,

Can you tell me what cut -c25-34 does?

T

 

[gregsimpson]

I assumed you were doing the sleep 15 as the command to kill the db2 processes is asynchronous, therefore you we're attemtping to ensure it had done what you asked it to do before moving onto the next step of your script. I felt it made sense to have it in there.

Cheers
Greg

 

[rbod]

Greg,
the reason for not using FORCE APPLICATION ALL is that there might be applications that are connected to OTHER databases and we want to force only those applications that are connected to this one particular database. So, we first do a :
list applications for database ${DBNAME}
and then do a force application (appl_handle)

Tim2525,
cut is a command to split the lines of a file.
-c Specifies character positions.
cut -c25-34 returns the characters 25 through 34, which in this case is the application handle.

 

[gregsimpson]

rbod,

I'm with it now. Thanks for the info.

Greg