Did you get this DB2 SQL1035N error if you were trying to take offline backup? The error is obvious as someone is connected to the database and you cannot take offline database backup.
There are several solution that you can take to do offline backup when you get SQL -1035 error. They are described as below:
Method – Brute Force
C:\>db2_kill C:\>db2start C:\>db2 backup database sample to C:\db2backup
The above method is a brute force method and is not recommended. You may get unexpected results at the expense of even corrupting the database. It is not guaranteed that above will work always. You may still get SQL1035N error even after db2_kill. You try to now kill every process with “kill -9” command and still it is not guaranteed to work. It is not at all recommended to use this brute force method.
Method – Mild Force
C:\>db2 force applications all C:\>db2stop force C:\>db2start C:\>db2 backup database sample to C:\db2backup
The above method is less brute force but you may not be able to use this if there are other databases in your instance. Stopping instance to just take a database backup is not acceptable if there are other databases in the instance.
Method – Correct way
Scenario – Application server has retry logic
Let us say, you want to take offline backup of your database and there is an application server which is designed to connect to the database as soon as it becomes available. The application server has retry logic and it will keep on trying making a connection to a database but your intent is to take an offline database backup. You are stuck as you have to ask your application server administrator to shut down the application server so that you can take up the backup. This may not be sometime possible to get your application server down. The above brute force methods also do not work in this circumstance. The following is the correct way to take offline backup of the database under this scenerio.
db2 CONNECT TO SAMPLE db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS db2 TERMINATE db2 DEACTIVATE DB SAMPLE db2 BACKUP DATABASE SAMPLE TO "C:\DB2Backup" WITHOUT PROMPTING db2 CONNECT TO SAMPLE db2 UNQUIESCE DATABASE db2 TERMINATE
Let us examine what we did in above example:
1. We did not stop instance.
2. We did not kill db2
3. We quiesced the database and forced connections out if we had to kill the sessions connected to SAMPLE database.
4. We terminated the connection so that db2bp process goes away.
5. We deactivated the database
6. We took the backup.
7. Since we quiesced the database, the application server’s retry logic will not be able to make connections to the database.
8. After successful database backup, do not forget to unquiesce the database.
Scenario – I just need to be able to take database backup
db2 CONNECT TO SAMPLE db2 QUIESCE DATABASE IMMEDIATE FORCE CONNECTIONS db2 UNQUIESCE DATABASE db2 TERMINATE db2 DEACTIVATE DB SAMPLE db2 BACKUP DATABASE SAMPLE TO "C:\DB2Backup" WITHOUT PROMPTING
The above method is a graceful method to take offline backup on a system that gets used heavily. I have seen DB2 DBAs using brute force methods as they get frustrated due to following:
1. There is no connection to the database
2. No one is trying to connect
3. Why am I still getting SQL1035N error?
The answer to above question is – If you continue to get SQL1035N error even if there are no connections, the ONLY way you can take offline backup is to deactivate the database and take the backup. For offline backup, the only condition that you need to meet is : The instance should be up and running and still you can take offline backup if you are the only one connected.
C:\>db2start C:\>db2 connect to sample C:\>db2 backup db sample TO C:\DB2Backup
If there is no one connected to the database except you and still you were able to take offline backup. Open up another db2 window and make a connection to sample database and try to take a offline backup, you now get SQL1035N error.
Hope this solves your issue of SQL1035N error in your db2 database.