IBM Support

Offline database backup failing with SQL1035N

Troubleshooting


Problem

An offline database backup after the database was quiesced and deactivated might fail with SQL1035N.

Symptom

The following output demonstrates the issue:

db2 connect to DBXX

Database Connection Information

Database server = DB2/AIX64 10.1.2
SQL authorization ID = DB2INST1
Local database alias = DBXX

db2 quiesce database immediate force connections
DB20000I The QUIESCE DATABASE command completed successfully.
db2 connect reset
DB20000I The SQL command completed successfully.
db2 terminate
DB20000I The TERMINATE command completed successfully.
db2 deactivate database DBXX
DB20000I The DEACTIVATE DATABASE command completed successfully.
db2 terminate
DB20000I The TERMINATE command completed successfully.
db2 "backup database DBXX use tsm open 1 sessions ..."
SQL1035N The operation failed because the specified database cannot be connected to in the mode requested. SQLSTATE=57019

Cause

The problem is caused by a timing issue. There is a design limitation with quiesced databases being activated by users without quiesce_connect (QUIESCECONNECTAUTH) privileges.
You must start the database in order to check the database authorizations in the catalogs.
Once the database is activated and authorizations are checked, the user fails to advance (fails with -20157) and the database is brought back down.

When the user connection goes through FirstConnect to connect to the database and it does not have the proper authorization to connect to a quiesced database, the backup agent attempts to connect. It receives -1035 because the offline backup requires an exclusive connection. This behavior is expected given the design limitation.

When you have users trying to connect in a loop, the timing issue is exposed. If there was no connection attempt, the backup would be successful.

Diagnosing The Problem

You would see messages similar to the following in your db2diag.log at the time offline backup failed:

A user (DB2XXXX) that does not have QUIESCECONNECTAUTH privilege activates the database with FirstConnect:

2013-04-12-12.50.09.001201+120 E232186A506 LEVEL: Event
PID : 21364974 TID : 4885 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DBXX
APPHDL : 0-234 APPID: xx.xx.xx.xx.12345
AUTHID : DB2XXXX HOSTNAME: host1
EDUID : 4885 EDUNAME: db2agent (DBC2) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000
START : DATABASE: DBC2 : ACTIVATED: NO

DB2INST1 fails to do the backup:

2013-04-12-12.50.09.412200+120 I232693A623 LEVEL: Error
PID : 21364974 TID : 6455 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DBXX
APPHDL : 0-242 APPID: *LOCAL.db2inst1
AUTHID : DB2INST1 HOSTNAME: host1
EDUID : 6455 EDUNAME: db2agent (DBC2) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::SubsequentConnect, probe:9782
MESSAGE : ZRC=0xFFFFFBF5=-1035
SQL1035N The operation failed because the specified database cannot
be connected to in the mode requested.

User DB2XXXX receives -20157 and the database is deactivated again:

2013-04-12-12.50.10.865760+120 I235107A893 LEVEL: Error
PID : 21364974 TID : 4885 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : DBXX
APPHDL : 0-234 APPID: xx.xx.xx.xx.12345
AUTHID : DB2XXXX HOSTNAME: host1
EDUID : 4885 EDUNAME: db2agent (DBC2) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_appl_init, probe:180
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -20157 sqlerrml: 27
sqlerrmc: DB2XXXX QUIESCE DATABASE
sqlerrp : SQLRR003
sqlerrd : (1) 0x801A006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0xFFFFFE70 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:

Resolving The Problem

The issue can be addressed by using the "restricted access" option in the QUIESCE INSTANCE command. This allows you to do an offline backup by quiesce the instance in restricted access mode, instead of quiescing the database individually. You can also start the DB2 instance in restricted access mode, with the db2start admin mode restricted access command.
These two methods are the recommended ways to quiesce the database before taking offline backups.

The following is an example of this method:

db2 quiesce instance <inst_name> restricted access immediate force connections


db2 deactivate db <dbname>
db2 backup db <dbname>
db2 activate db <dbname>
db2 unquiesce instance <inst_name>

If you have many databases in your instance and you only want to do an offline backup of one database while the others remain online, you can see to use an alias for the applications connecting to the database. Before the backup, uncatalog that alias and catalog it in the database again with that alias after the offline backup is complete.

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Routines (SP \u0026 UDF) - SQL","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF010","label":"HP-UX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"9.8;9.7;9.5;9.1;10.1","Edition":"Advanced Enterprise Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
08 December 2022

UID

swg21636292

Page Feedback