Topic
IC4NOTICE: developerWorks Community will be offline May 29-30, 2015 while we upgrade to the latest version of IBM Connections. For more information, read our upgrade FAQ.
6 replies Latest Post - ‏2012-11-18T06:55:56Z by bluey01
bluey01
bluey01
50 Posts
ACCEPTED ANSWER

Pinned topic DB2 restore fails on a regular basis

‏2012-11-11T23:41:47Z |
DB2 9.7 Windows 64.

A nightly job drops and restores 2 test databases to refresh the data. Over a period of a week, we get 2 or 3 failures on the process, while the other days the process works without errors.

The errors are either:
Drop database TESTDB
SQL1137W The database manager was unable to remove the database path or some of the containers when dropping database "TESTDB". Cleanup is required.
or
SQL1035N The database is currently in use. SQLSTATE=57019

The restore job does the following steps
CONNECT TO TESTDB;
QUIESCE DATABASE immediate force connections;
UNQUIESCE DATABASE;
connect reset;
deactivate db TESTDB;
drop database TESTDB;
RESTORE DATABASE NEWDB FROM "D:\backups" INTO TESTDB WITHOUT PROMPTING;
activate db TESTDB;

The aim is to have a job that will drop and create these databases without the continual failure rate.

So I have 2 questions:
1. Why does the DROP fail to remove some items on such a regular basis - and what can I do to stop this error.
2. How do you kill/drop connections to a database to allow for a successful drop and restore? I understand that the Quiesce may want to rollback transactions. Is there another command (on WIndows) that will let me terminate the connections. As the database is being restored, there is no reason to save data.
Updated on 2012-11-18T06:55:56Z at 2012-11-18T06:55:56Z by bluey01
  • mwandishi
    mwandishi
    47 Posts
    ACCEPTED ANSWER

    Re: DB2 restore fails on a regular basis

    ‏2012-11-13T07:00:21Z  in response to bluey01
    Hi,

    Are these databases created in a MSCS cluster? I have seen cases where the DB path is still somehow in the cluster manager?
    Also are you in the DB path when you issue the drop? It may be DB2 / Win can't remove certain files / directories because you are in that path and there are open file handles?

    It would help if you could post the following:

    db2 list db directory - and provide just the entry from TESTDB
    Any related db2diag.log messages

    Lastly, if you are intent on dropping, why not run: 'db2 uncatalog db testdb' after the drop, this should clean up any files in the db directory, which is where I suspect the problem may lie. Or alternatively specify the 'REPLACE EXISTING' clause to avoid the drop altogether.

    Best regards,
    Stephen.
  • SystemAdmin
    SystemAdmin
    17917 Posts
    ACCEPTED ANSWER

    Re: DB2 restore fails on a regular basis

    ‏2012-11-13T19:27:09Z  in response to bluey01
    Exactly. There isn't usually a need to physically drop the database just to refresh it's contents. A full database RESTORE with REPLACE EXISTING and WITHOUT PROMPTING specified should everything you need.

    The SQL1137 could be caused by a number of things as already mentioned, and the SQL1035 almost always means that someone or some application is still connected to the DB, and given the steps that you've shown it is still possible for any application which automatically reconnects to the DB to sneak in before your DROP statement is actually executed. One tactic I've seen people use to address this sort of problem is to create an alias on the server (CATALOG DB <internalName> AS <sharedName>), keeping the actual DB name secret, for use only by administrative operations such as this. Then, after you've forced everyone off you can simply remove the alias until you're ready to make the DB visible again by re-cataloging the alias.
    • bluey01
      bluey01
      50 Posts
      ACCEPTED ANSWER

      Re: DB2 restore fails on a regular basis

      ‏2012-11-15T03:00:03Z  in response to SystemAdmin
      Thanks for the responses - I will try either the uncatalog or Restore with REPLACE options.

      Just on the Restore with Replace - if there is something automatically connecting to the DB - then does that mean the Restore with Replace will still fail?

      As you say, it is possible for an application to connect - so how can you take the database 'offline' so that all existing connections are killed and no new connections can be made? Being a DEV environment, there is possibility that the restore may happen a couple of times a day.
      • mwandishi
        mwandishi
        47 Posts
        ACCEPTED ANSWER

        Re: DB2 restore fails on a regular basis

        ‏2012-11-15T09:26:24Z  in response to bluey01
        Hi,

        You are doing an offline backup which will require exclusivity so yes, a connection will cause it to fail ( SQL1035 ).
        There are a couple of options, the best in this case, is probably to quiesce the database:

        db2 "quiesce db testdb immediate" - This will quiesce it and not wait for transactions to commit so be careful. A gentler option is:
        db2 "quiesce db testdb defer with timeout <value in mins> - if no value is specified the default is 10 mins.

        Take a look at:
        http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.cmd.doc/doc/r0008635.html

        I should point out that if your apps connect with a SYSADM UID then quiesce won't work as SYSADM authority connections are permitted to connect while it is quiesced. It has to be so otherwise a quiesced database will remain so indefinitely.

        If this is the case then you will have to get a little more creative and prevent the apps from making connections, either by: recataloging temporarily and using a different alias or: stopping tcpip, maybe: db2set DB2COMM= ( NULLing DB2COMM ). But this assume the apps are remote and not local using IPCs. Or by just stopping the app while the backup is running. Once DB2 backup has exclusivity then the apps will be prevented from connecting, it's 1st come 1st served.

        Lastly, consider online backups which makes all this moot to a degree. However, online backups come with additional considerations, like archive log management. Enabling DB2 for archival logging isn't appropriate for every situation but it is worth mentioning here.

        I hope this helps?

        Best regards,
        Stephen Levett
        • mwandishi
          mwandishi
          47 Posts
          ACCEPTED ANSWER

          Re: DB2 restore fails on a regular basis

          ‏2012-11-15T09:41:32Z  in response to mwandishi
          Apologies, my previous post should have read restore not backup. Same advise stands, a quiesce will be the best option providing the apps don't connect as SYSADM during the restore.
          • bluey01
            bluey01
            50 Posts
            ACCEPTED ANSWER

            Re: DB2 restore fails on a regular basis

            ‏2012-11-18T06:55:56Z  in response to mwandishi
            Thanks guys - some good feedback for me to work with