IBM Support

Preventing application database connections from automatically activating a database after db2start.

Question & Answer


Question

Is it possible to prevent incoming database-connection attempts from application layer/driver from automatically activating the database.

Cause

Often, during maintenance events, a database is deactivated and the database-instance is stopped but the application is not stopped and will continue to attempt to establish a connection to the database while the database is under maintenance, this is not a problem in itself.
Later after maintenance completes and the database-instance is restarted and the database is activated, the application's attempt to establish a database connection will be successful.

However, there is a timing window where, after maintenance completes and the database-instance is started but before the database is activated, an application's database connection attempt can come in and automatically start database activation before a DBA has explicitly activated it. Since database activation can sometimes take several minutes, the application might timeout before the activation completes, and rollback the database activation. If the number of application threads attempting to connect to the database is very large, they may repeatedly start database activation ahead of the DBA's request to explicitly activate the database, and repeatedly timeout and rollback the activation.
This can cause a repeated loop of automatic database activation and timeout/rollback, while the DBA's explicit request to activate the database appears to hang.

Answer

In order to prevent this kind of phenomenon, we can temporarily pause application database connection attempts from reaching the database server and automatically starting database activation, so that the DBA's request to explicitly activate the database can complete.

There are two methods that can be used:

Method #1: use the db2trc -suspend option to pause incoming database connections from reaching the database server, and then un-pause them after the DBA has completed database activation. (note that this method does not actually perform a trace, so it does not have any performance impact).

1) db2trc on -debug DB2.SQLCC.sqlcctcpconnmgr_child.115 -suspend
2) db2start
3) db2 activate database <dbname>
4) db2trc off


Method #2: use the 'db2start admin mode restricted access' command to quiesce the database in restricted mode during db2start, and then unquiesce after the database activation is complete.

1) db2start admin mode restricted access
2) db2 activate database <dbname>
3) db2 unquiesce instance <instance name>

[{"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Component":"Database Objects\/Config - Instance","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;10.1;10.5","Edition":"Advanced Enterprise Server;Advanced Workgroup Server;Enterprise Server;Express;Express-C;Personal;Workgroup Server","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 June 2018

UID

swg21673436