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
UID
ibm11140376