IBM Support

In Db2 LUW if unknown hangs are being experienced better to check if DB2SLEEP registry is set or not

Technical Blog Post


Abstract

In Db2 LUW if unknown hangs are being experienced better to check if DB2SLEEP registry is set or not

Body

I am sharing this as we had a situation lately in a big 3 member 2 CF Purescale setup where there were hangs in the members being experienced without much notable reasons.

When checked the setup closely it was found out the DB2SLEEP registry was turned ON in this instance for some reason and it was left that way accidentally.

 

DB2SLEEP is an internal undocumented registry setting used only by Db2 Support team for advanced level of diagnostics collection.   The registry will put the instance in the sleep state so that more relevant data could be collected while the instance is frozen with the problem situation.

So, this is something which is used in very rare situations and with high degree of caution and never in a running production setup unless there is a planned data collection window.

And, most definitely it should not be left in the place after the data collection is done.

 

To identify whether DB2SLEEP caused the hang  look for following kind of message in the db2diag.log around the time of the hang started :

 

2017-07-28-15.03.36.561240-300 I517825A486          LEVEL: Severe

PID     : 3488877              TID : 369999         PROC : db2sysc 1

INSTANCE: myinst1             NODE : 001           DB   : MYDB

APPHDL  : 1-13808              APPID: <>

AUTHID  :  USER1             HOSTNAME: myhost.mycomp.com

EDUID   : 123456               EDUNAME: db2agent (MYDB) 1

FUNCTION: DB2 UDB, global services, sqlzeDumpFFDC, probe:20

MESSAGE : Putting instance to sleep!

 

 

This can be triggered against different kind of errors.

In this particular situation it was caused by an "AppErr"  with SQL0901

 

So, check with “db2set -all” command, to find if  following is set,

[i] DB2SLEEP=ON

If so, just unset it.

“db2set DB2SLEEP=”; db2stop; db2start

[{"Business Unit":{"code":"BU029","label":"Data and AI"}, "Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Component":"","Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"","Edition":""}]

UID

ibm11140376