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.
SQL1035N The database is currently in use. SQLSTATE=57019
The restore job does the following steps
CONNECT TO TESTDB;
QUIESCE DATABASE immediate force connections;
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.
NOTICE: 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.
This topic has been locked.
6 replies Latest Post - 2012-11-18T06:55:56Z by bluey01
Pinned topic DB2 restore fails on a regular basis
Answered question This question has been answered.
Unanswered question This question has not been answered yet.
Updated on 2012-11-18T06:55:56Z at 2012-11-18T06:55:56Z by bluey01
Re: DB2 restore fails on a regular basis2012-11-13T07:00:21Z in response to bluey01Hi,
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.
SystemAdmin 110000D4XK17917 PostsACCEPTED ANSWER
Re: DB2 restore fails on a regular basis2012-11-13T19:27:09Z in response to bluey01Exactly. 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.
Re: DB2 restore fails on a regular basis2012-11-15T03:00:03Z in response to SystemAdminThanks 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.
Re: DB2 restore fails on a regular basis2012-11-15T09:26:24Z in response to bluey01Hi,
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:
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?
Re: DB2 restore fails on a regular basis2012-11-15T09:41:32Z in response to mwandishiApologies, 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.