Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2007
    Posts
    4

    Unanswered: Better way to DROP Database ??

    Friends,

    [ DB2 v9.5.5 on Win2K3 ]

    While restoring backups to Mirrors, I have been dropping the target DB before restoring new copy overnight. Our Mirror servers host 2 DBs in one instance. So I need to force applications selectively to keep other DB intact while restorin the one.

    I use following logic to Drop DB

    Code:
    CONNECT TO GAMING
    
    
       Database Connection Information
    
    
    
     Database server        = DB2/NT64 9.5.4
    
     SQL authorization ID   = RESTORE
    
     Local database alias   = GAMING
    
    
    
    
    Sat 12/18/2010- 2:15:29.66-0
    QUIESCE DB IMMEDIATE FORCE CONNECTIONS
    
    DB20000I  The QUIESCE DATABASE command completed successfully.
    
    
    
    Sat 12/18/2010- 2:15:30.00-0
    UNQUIESCE DB
    
    DB20000I  The UNQUIESCE DATABASE command completed successfully.
    
    
    
    Sat 12/18/2010- 2:15:30.23-0
    CONNECT RESET
    
    DB20000I  The SQL command completed successfully.
    
    
    
    Sat 12/18/2010- 2:15:30.47-0
    DEACTIVATE DB GAMING
    DB20000I  The DEACTIVATE DATABASE command completed successfully.
    
    
    
    Sat 12/18/2010- 2:15:39.33-0
    DROP DB GAMING
    SQL1137W  The database manager was unable to remove the database path or some 
    
    of the containers when dropping database "GAMING".  Cleanup is required.
    
    
    
    Sat 12/18/2010- 2:15:41.50-4
    ===================================
    Sat 12/18/2010- 2:15:41.50-4---Drop DB Fail
    Sat 12/18/2010- 2:16:00.51-0---ReTry Drop DB
    ===================================
    CONNECT TO GAMING
    SQL1031N  The database directory cannot be found on the indicated file system. 
    
    SQLSTATE=58031
    But recently the I have come across the issue above. Seems that some application is holding some files related to GAMING at system level.

    Can some one suggest me a better way to DROP DB before RESTOREing.

    Or Can I do REPLACE EXISTING without dropping GAMING ??

    Regards

    Sarbjit Singh Gill
    Last edited by sarbjitsinghgill; 12-18-10 at 16:27.

  2. #2
    Join Date
    Oct 2004
    Location
    DELHI INDIA
    Posts
    338
    you should practically use " REPLACE EXISTING" only while restoring. Thats simple and straight and DB2 provides this feature.

    Jayanta Datta
    DB2 UDB DBA
    IBM India, Global Delivery
    New Delhi

  3. #3
    Join Date
    Dec 2007
    Posts
    4
    Well, I will try that.


    Logically, what does DB2 do with connected applications while restoring without dropping.
    Further if DB2 fails to DROPping DB; doesn't this imply that DB2 will fail while REPLACEing.

    I will try tonight skipping the DROP DB command in the script and let you know.

    Thanks

    Sarbjit Singh Gill
    Last edited by sarbjitsinghgill; 12-18-10 at 16:27.

  4. #4
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,368
    Why don't you do something similar to the following:

    deactivate db ...
    connect to ...
    quiesce db immediate force connections
    connect reset
    <you can add equivalent of "sleep <seconds>" on windows to give force enough time to terminate existing connections>
    restore db ... without prompting
    connect to ...
    unquiesce db
    connect reset

  5. #5
    Join Date
    Dec 2007
    Posts
    4
    Alright, and thanks.

    I will do this. I may have to double check if any one can connect to quiesced database.

    Regards

  6. #6
    Join Date
    Dec 2007
    Posts
    4
    Can someon suggest me how to suppress this warning ??

    Code:
    RESTORE DATABASE GAMING INCREMENTAL AUTOMATIC FROM 'H:\daily_backup\Image' TAKEN AT 20101218013005 TO D: LOGTARGET H:\daily_backup\logs WITH 20 BUFFERS BUFFER 512 replace existing WITHOUT PROMPTING 
    SQL2540W  Restore is successful, however a warning "2539" was encountered during Database Restore while processing in No Interrupt mode.
    This warning causes return code of value 1 which causes my script to send a warning e-mail to myself and my manager.

    Warning "2539" is
    SQL2539W Warning! Restoring to an existing database that is the same as
    the backup image database. The database files will be deleted.

    Explanation:

    The database aliases, names and seeds of the target database and
    database image are the same, indicating these are the same databases.
    The current database will be overwritten by the backup version.

    User response:

    Return to the utility with the callerac parameter indicating processing
    to continue or end.
    I just want to avoid this warning. REPLACE EXISTING and WITHOUT PROMPTING are already there to indicate that I want to replace an existing database.

    Any idea or work around ??

    thanks

Posting Permissions

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