Thanks Venkata for you time and explanation.
No, the program has not EXCLUSIVE MODE.
Right now I am reviewing CLAIMDTA parameter of DSNZPARM that IBM books suggest:
# Subsystem parameter CLAIMDTA, in macro DSN6SPRM, allows you to specify
# whether agents get a claim on data before they get a claim on an index.
# For partitioned table spaces, agents always get a claim at the table space
# level before they claim partitions or indexes. Specify YES if you have
# problems with utility jobs that terminate with a return code of 8 and a
# reason code of 00C200EA. The default value is NO. See Part 5 (Volume 2) of
# DB2 Administration Guide, SC26-9931-05 for more information about claims
# and drains.
My installation has CLAIMDTA=NO; any way If you know something about this parameter, please share it.
What are the "couple of interesting bind parameters" that you saw?
-----Mensaje original-----
De: Kosaraju Venkata
[mailto:db2udbdba-ezmlmshield-x6872915.[Email address protected]
Enviado el: Martes, 30 de Mayo de 2006 02:01 p.m.
Para: LazyDBA Discussion
Asunto: RE: Help SQL Code
I am sorry, that was my fat finger:
Looks like you have the lock drain issue:
Here is some explanation about your error:
00C200EA
Explanation: DB2 is unable to perform the drain function on an object (a
table space, an index space, or a partition) because the object was held
by other claimers and the drain request timed out waiting for the claim
count to reach zero.
System Action: If the problem was encountered by DB2 when trying to
automatically recover a group buffer pool recovery pending (GRECP) object
following the failure of a group buffer pool (GBP), then DB2 issues
message DSNB350I or DSNB351I to the console with this reason code
indicating that the object cannot be automatically recovered.
Otherwise, a 'resource not available' return and reason code are sent back
to the requesting function. DB2 message DSNT500I may be sent to the
console. The reason code and the object name are recorded in the cursor
table (CT) and made available to the user in the SQLCA or in the message.
System Programmer Response: Take one of these actions:
o Reevaluate the utility timeout factor on installation panel DSNTIPI
and increase it, if necessary.
o Find the threads holding the object through the -DISPLAY DB CLAIMERS
command on the object. Either terminate those threads or wait until no
one holds the object. Then invoke the application again.
If DSNB350I or DSNB351I messages were issued, then after you take one of
the actions above, recover the GRECP objects as indicated in the "System
Programmer Response" for DSNB350I or DSNB351I.
Are you locking the table in EXCLUSIVE MODE before update the table in your COBOL program.
Also I have noticed couple of interesting bind parameters in your bind card. Please check with your systems programmer about lock size z-parms parameter.
Please give me a call if you need any further help,
Thanks,
Venkat.
614-222-6638
-----Original Message-----
From: Kosaraju, Venkata
Sent: Tuesday, May 30, 2006 2:52 PM
To: 'Lopez Garcia Ramiro '; LazyDBA Discussion
Subject: RE: Help SQL Code
Looks like you are running out of
-----Original Message-----
From: Lopez Garcia Ramiro
[mailto:db2udbdba-ezmlmshield-x71164090.[Email address protected]
Sent: Tuesday, May 30, 2006 1:39 PM
To: LazyDBA Discussion
Subject: RE: Help SQL Code
Thanks Cruickshank!
You are rigth, yesterday afternoon the job had a contention with a QMF online user; but at night
the job were running again, alone, and it abends also with the same sql code/reason code, nothing was
running at the same time, can you believe it? Another way to try fix it?
Rgds!
-----Mensaje original-----
De: Cruickshank Rohan
[mailto:db2udbdba-ezmlmshield-x35075964.[Email address protected]
Enviado el: Martes, 30 de Mayo de 2006 12:17 p.m.
Para: LazyDBA Discussion
Asunto: RE: Help SQL Code
Seems like you have a contention issue with someone(s) else accessing
the table.
You may want to lock the table exclusive before doing the update or wait
until there is no one else accessing the table.
Here is a link that might be helpful...
http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=/
com.ibm.db2.doc.mc/listomsgs.html
Thanks..
Rohan Cruickshank
Systems Engineer
RTS
770 433 8211 ext 84942
Purity of Heart, Clarity of Mind
and a Soul Devout
These three things I hope to find
Within and not Without
Mr Ramiro,
The error you are getting is with the LOCK drain issue;
Here is the some description of your error;
00C200EA
Explanation: DB2 is unable to perform the drain function on an object (a
table space, an index space, or a partition) because the object was held
by other claimers and the drain request timed out waiting for the claim
count to reach zero.
System Action: If the problem was encountered by DB2 when trying to
automatically recover a group buffer pool recovery pending (GRECP) object
following the failure of a group buffer pool (GBP), then DB2 issues
message DSNB350I or DSNB351I to the console with this reason code
indicating that the object cannot be automatically recovered.
Otherwise, a 'resource not available' return and reason code are sent back
to the requesting function. DB2 message DSNT500I may be sent to the
console. The reason code and the object name are recorded in the cursor
table (CT) and made available to the user in the SQLCA or in the message.
System Programmer Response: Take one of these actions:
o Reevaluate the utility timeout factor on installation panel DSNTIPI
and increase it, if necessary.
o Find the threads holding the object through the -DISPLAY DB CLAIMERS
command on the object. Either terminate those threads or wait until no
one holds the object. Then invoke the application again.
If DSNB350I or DSNB351I messages were issued, then after you take one of
the actions above, recover the GRECP objects as indicated in the "System
Programmer Response" for DSNB350I or DSNB351I.
Are you locking the table in exclusive mode before running update and also check with your systems programmer about the lock size in the z-parms.
Thanks,
Venkat.
-----Original Message-----
From: Lopez Garcia Ramiro
[mailto:db2udbdba-ezmlmshield-x42553993.[Email address protected]
Sent: Tuesday, May 30, 2006 12:32 PM
To: LazyDBA Discussion
Subject: Help SQL Code
Hi guys, there is a program COBOL/DB2, runs an massive UPDATE in a jcl
with statict sql, but suddenly abends with:
SQL Code -904, Reason Code 00C200EA
The table has 27 millions of rows,12 indexes and 64 partitions; the
frecuency of commits for the update is 1000 rows. DB2 v7 for z/OS. The
BIND parameters are:
BIND PLAN (myplan) OWNER(X) QUALIFIER(X)+
NODEFER(PREPARE) VALIDATE(BIND) FLAG(I)+
ISOLATION(CS) CACHESIZE(1024) CURRENTDATA(YES)+
DEGREE(ANY) SQLRULES(DB2) ACQUIRE(ALLOCATE)+
RELEASE(DEALLOCATE) EXPLAIN(NO) DYNAMICRULES(RUN)+
NOREOPT(VARS) KEEPDYNAMIC(NO) IMMEDWRITE(NO)+
DBPROTOCOL(PRIVATE) ENCODING(500)+
DISCONNECT(EXPLICIT)+
MEMBER(mine)+
LIBRARY('mylib.prgrms.DBRM')+
ENABLE(*)+
ACTION(REPLACE) RETAIN
Can any one have a suggestion?
Thanks in advanced for all your help!
DB2 & UDB email list listserv db2-l LazyDBA home page