SEO

Does anyone have any idea what the following entries in the DB2 diag
could indicate? Here are the details...

Second night in a row that DB2 has hung. All DB2 commands related to
databases hang. Platform is AIX 5.3 and DB2 8.2 ESE. Here are the
diag entries. Take notice of teh PID value. When I saw that I went
and executed a KILL -9 on the PID and immediately all hanging commands
executed...

2005-11-10-00.00.14.777345-360 I49361C414 LEVEL: Severe
PID : 426236 TID : 1 PROC : db2agent
(WCMPRDD1) 0
INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
FUNCTION: DB2 UDB, database monitor, sqm___start_evmon, probe:55
RETCODE : ZRC=0x850D0083=-2062745469=SQLM_RC_EVRSRCE "resource
shortage"

2005-11-10-00.00.14.792176-360 I49776C464 LEVEL: Severe
PID : 426236 TID : 1 PROC : db2agent
(WCMPRDD1) 0
INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
MESSAGE : event monitor name
DATA #1 : Hexdump, 17 bytes
0x200527F8 : 4442 3244 4554 4149 4C44 4541 444C 4F43
DB2DETAILDEADLOC
0x20052808 : 4B K

2005-11-10-00.00.14.792463-360 I50241C453 LEVEL: Severe
PID : 426236 TID : 1 PROC : db2agent
(WCMPRDD1) 0
INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
FUNCTION: DB2 UDB, database monitor, sqm___evmgr::log_ev_err, probe:1
MESSAGE : DiagData
DATA #1 : Hexdump, 4 bytes
0x2FF14F30 : 850D 0083

Any help would be greatly appreciated.

Thanks!!

Martin

"mghale" <martinghale@yahoo.com> wrote in message
news:1131632220.129974.219770@g43g2000cwa.googlegr oups.com...
> Does anyone have any idea what the following entries in the DB2 diag
> could indicate? Here are the details...
>
> Second night in a row that DB2 has hung. All DB2 commands related to
> databases hang. Platform is AIX 5.3 and DB2 8.2 ESE. Here are the
> diag entries. Take notice of teh PID value. When I saw that I went
> and executed a KILL -9 on the PID and immediately all hanging commands
> executed...
>
> 2005-11-10-00.00.14.777345-360 I49361C414 LEVEL: Severe
> PID : 426236 TID : 1 PROC : db2agent
> (WCMPRDD1) 0
> INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
> APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
> FUNCTION: DB2 UDB, database monitor, sqm___start_evmon, probe:55
> RETCODE : ZRC=0x850D0083=-2062745469=SQLM_RC_EVRSRCE "resource
> shortage"
>
> 2005-11-10-00.00.14.792176-360 I49776C464 LEVEL: Severe
> PID : 426236 TID : 1 PROC : db2agent
> (WCMPRDD1) 0
> INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
> APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
> MESSAGE : event monitor name
> DATA #1 : Hexdump, 17 bytes
> 0x200527F8 : 4442 3244 4554 4149 4C44 4541 444C 4F43
> DB2DETAILDEADLOC
> 0x20052808 : 4B K
>
> 2005-11-10-00.00.14.792463-360 I50241C453 LEVEL: Severe
> PID : 426236 TID : 1 PROC : db2agent
> (WCMPRDD1) 0
> INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
> APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
> FUNCTION: DB2 UDB, database monitor, sqm___evmgr::log_ev_err, probe:1
> MESSAGE : DiagData
> DATA #1 : Hexdump, 4 bytes
> 0x2FF14F30 : 850D 0083
>
> Any help would be greatly appreciated.
>
> Thanks!!

In the first message a "resource shortage" is encountered, which has to do
with monitors (specificly, the DB2DETAILDEADLOCK event monitor.)

You should increase MON_HEAP_SZ so that you don't run out of monitor memory.

I'd also suspect that your workload is encountering a lot of deadlocks
(hence firing the DB2DETAILDEADLOCK even monitor frequently), so you may
want to look at your queries and see what you can do to reduce deadlocks, as
this will improve your overall performance.

--
Matt Emmerton

Matt Emmerton wrote:

>
> "mghale" <martinghale@yahoo.com> wrote in message
> news:1131632220.129974.219770@g43g2000cwa.googlegr oups.com...
>> Does anyone have any idea what the following entries in the DB2 diag
>> could indicate? Here are the details...
>>
>> Second night in a row that DB2 has hung. All DB2 commands related to
>> databases hang. Platform is AIX 5.3 and DB2 8.2 ESE. Here are the
>> diag entries. Take notice of teh PID value. When I saw that I went
>> and executed a KILL -9 on the PID and immediately all hanging commands
>> executed...
>>
>> 2005-11-10-00.00.14.777345-360 I49361C414 LEVEL: Severe
>> PID : 426236 TID : 1 PROC : db2agent
>> (WCMPRDD1) 0
>> INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
>> APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
>> FUNCTION: DB2 UDB, database monitor, sqm___start_evmon, probe:55
>> RETCODE : ZRC=0x850D0083=-2062745469=SQLM_RC_EVRSRCE "resource
>> shortage"
>>
>> 2005-11-10-00.00.14.792176-360 I49776C464 LEVEL: Severe
>> PID : 426236 TID : 1 PROC : db2agent
>> (WCMPRDD1) 0
>> INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
>> APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
>> MESSAGE : event monitor name
>> DATA #1 : Hexdump, 17 bytes
>> 0x200527F8 : 4442 3244 4554 4149 4C44 4541 444C 4F43
>> DB2DETAILDEADLOC
>> 0x20052808 : 4B K
>>
>> 2005-11-10-00.00.14.792463-360 I50241C453 LEVEL: Severe
>> PID : 426236 TID : 1 PROC : db2agent
>> (WCMPRDD1) 0
>> INSTANCE: wcmprdi1 NODE : 000 DB : WCMPRDD1
>> APPHDL : 0-872 APPID: *LOCAL.wcmprdi1.051110060013
>> FUNCTION: DB2 UDB, database monitor, sqm___evmgr::log_ev_err, probe:1
>> MESSAGE : DiagData
>> DATA #1 : Hexdump, 4 bytes
>> 0x2FF14F30 : 850D 0083
>>
>> Any help would be greatly appreciated.
>>
>> Thanks!!
>
> In the first message a "resource shortage" is encountered, which has to do
> with monitors (specificly, the DB2DETAILDEADLOCK event monitor.)
>
> You should increase MON_HEAP_SZ so that you don't run out of monitor
> memory.
>
> I'd also suspect that your workload is encountering a lot of deadlocks
> (hence firing the DB2DETAILDEADLOCK even monitor frequently), so you may
> want to look at your queries and see what you can do to reduce deadlocks,
> as this will improve your overall performance.
>
> --
> Matt Emmerton

Also, if you are on DB2 ESE V8.2 base (i.e. at Fixpack 7 level without any
fixpacks applied), I would strongly advise you to get to Fixpack 9a at
least. There were lots of problems with monitoring code in the base V8.2
release : either they didn't work properly or they consumed too many
resources.

As a last resort you could turn off / drop the event monitor in question
(which is turned on by default). However you then lose this important
indication. As Matt says, you need to look at what is causing deadlocks,
as this probably is the root cause.

Phil

"Philip Nelson" <teamdba@scotdb.com> wrote in message
news:vhRcf.277044$uo2.180046@fe04.news.easynews.co m...
>
> Also, if you are on DB2 ESE V8.2 base (i.e. at Fixpack 7 level without any
> fixpacks applied), I would strongly advise you to get to Fixpack 9a at
> least. There were lots of problems with monitoring code in the base V8.2
> release : either they didn't work properly or they consumed too many
> resources.
>
> Phil

I would recommend FP10, which seems fairly stable.

Thanks for the replies.

We are on FixPak 9a at this time. The weird thing is that the
MON_HEAP_SZ is set to 5000 and the workload during the night (this
problem occurs between midnight and 1:00 AM is non-existent. All app
users go home at 6:00 PM and the only thing running against the
database at that time is our backups, which usually only take a few
minutes, and the runstats. There are connections trying to come
through from 3 connection pools on Websphere servers but no activity is
being passed in....

Very confusing as we should not be experiencing DeadLocks at this time
of the day as no should be on the system. I'll try to verify the
system activity tonight....

Thanks again for all the replies. Your help is greatly appreciated.

Martin

Just to give one more feedback...
We migrated recently from v7.2.3 (FP9) to 8.2.3 (FP10) on AIX 5.3 (p5
hw) including more than 300 SP converted as-is to the new interpreted
SQL PL runtime (no more C compiler required on dev platform).
FP10 seems definitivly more stable compared to previous levels and SP
performance seems comparable to the old 'C' ones...

Bye

Dario