Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

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

    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
    Last edited by grofaty; 03-19-09 at 07:15.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    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
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    May 2007
    Location
    somewhere in dbforums
    Posts
    221
    how about using the good old do while loop in your shell script.....

    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
    IBM Certified Database Associate, DB2 9 for LUW

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    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

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    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

  6. #6
    Join Date
    Jan 2003
    Posts
    1,605
    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
    Last edited by grofaty; 03-20-09 at 04:17.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •