-904 for updatation in a single Table

kingkunlao · New User Joined: 23 Apr 2008 Posts: 6 Location: mumbai

hi all,

I am getting a -904 for updatation in a single Table ...
the details of the Spool are..
SQLCODE -904

An unavailable resource caused unsuccessful execution of this SQL
statement.

Reason code: 00C90096
Resource Name: DKY4U0B0.RKY4C00 .X'0000E04B'
Resource Type: TABLE SPACE PAGE

The page lock on the page or subpage identified by 'NAME' in message
DSNT500I or DSNT501I caused the total number of page locks
concurrently held to reach the installation maximum number of page
locks (NUMLKUS) allowed for a single agent in the system.

Isolation: CURSOR STABILITY
SQLCAID SQLCA
SQLCABC 136
SQLCODE -904
SQLERRML 47
SQLERRMC
A B E N D - A I D

00C90096 00000302 DKY4U0B0.RKY4C00 .X'0000E04B'
SQLERRP DSNXRRC
SQLERRD(1) 102 X'00000066'
SQLERRD(2) 13,172,746 X'00C9000A'
SQLERRD(3) 0 X'00000000'
SQLERRD(4) 13,227,746 X'00C9D6E2'
SQLERRD(5) -470,675,452 X'E3F21004'
SQLERRD(6) 1,073,741,824 X'40000000'
SQLSTATE 57011

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

The number of locks exceeded the maximum locks.

Could give you give a try by using

WITH UR;

at the end of your query

kingkunlao · New User Joined: 23 Apr 2008 Posts: 6 Location: mumbai

I have given with UR for each query

dbzTHEdinosauer · Posted: Fri Oct 03, 2008 1:54 pm

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

If you are using updation you cannot use WITH UR; I missed the update part in your query.

Prasanthhere · Active User Joined: 03 Aug 2005 Posts: 306

Check this link

http://www-01.ibm.com/support/docview.wss?rs=338&context=SSEK24&dc=DB520&uid=swg21220631&loc=en_US&cs=UTF-8&lang=en

anandinmainframe · Active User Joined: 31 May 2007 Posts: 171 Location: India

Hi kingkunlao,
You can run the same after sometime or else if you have any High prevelege id then you can try from that. As dbzTHEdinosauer said you will have to use COMMIT to do updation.

kingkunlao · New User Joined: 23 Apr 2008 Posts: 6 Location: mumbai

hi guys thanks for the tip..
here i am running 10 parallel jobs.

but here i am using With Ur for all the select statements..
and not in the update query i guess we cannot use with ur in update there..
also i am commiting the records still it is giving the -904 error.
i am not able to get what the issue is exactly.

dbzTHEdinosauer · Posted: Fri Oct 03, 2008 3:12 pm

10 parallel jobs. Why the parallelism in batch?
Apparently you have a lot of activity going on. If parallelism is so important,
I would look into partitioned tables.
If that is to hard, reduce the number of parallel jobs.

Modifiying the num page locks allowed is a global parameter
I don't think your dba's would buy into that.

kingkunlao · New User Joined: 23 Apr 2008 Posts: 6 Location: mumbai

yes thats true Dick i guess that is what is happening here ..
actually when i am running 10 jobs few of them are running sucessfully while others are abending with -904.

dbzTHEdinosauer · Posted: Fri Oct 03, 2008 3:37 pm

what is your commit frequency?

you are having pagelocks because so many of the jobs are beating on the same page.

each of the 10 jobs has some criteria to determine what to read and what to update.

try to analyze your activity and determine how to separate the activity being performed on the table into 10 general zones delineated by your key. (your update is not modifying the key is it. is your index unique?)

kingkunlao · New User Joined: 23 Apr 2008 Posts: 6 Location: mumbai

I am using Commit frequency of 100.
but in each of the jobs i have separated the ranges such that none of them are overlapping.
no my update is not modifying the key.

dbzTHEdinosauer · Posted: Fri Oct 03, 2008 4:33 pm

Kunal,

kingkunlao · New User Joined: 23 Apr 2008 Posts: 6 Location: mumbai

Dick,

what i tried is reducing the range which is being supplied in the input file.
then the job run is successful.
what could be the reason here.
cause in the prior case there are many input records which are being errored out could this be one of the reason of getting the abend.

dbzTHEdinosauer · Posted: Fri Oct 03, 2008 6:26 pm

kunal,

First a couple of words about a completely different subject:
I realize that most on this board are rather flexible about terms and word usage. I am sort-of-an-asshole and insist on proper terminology. An ABEND is an Abnormal Termination/End of a task/rununit. What you meant was a DB2 negative SQLCODE. More and more, IBM has separated the service routines from the application code and supplies a return-code from the service. If the application decides to terminate, that is a decision made by the application, not the service routine. In your case, you could have performed a rollback and then proceeded in your application to attempt to re-update. Your application decided to terminate based on the -904. Not a problem. normally, that is accepted practice. You don't want an application to re-attempt something that will continually have unsuccessful results.

Now, back to your questions.

dbzTHEdinosauer · Posted: Sat Oct 04, 2008 4:04 pm

You realize you need to release locks that are created by reads?

the fact that you are successful with fewer input items, leads me to think that you should reduce your commit to 20 or so, and use the full amount of input items. reducing input items is not a solution. You said you commit after 100 somethings. I don't think you are counting all that should be considered before releasing resources with a COMMIT. Reads will also create locks, not exclusive, but nevertheless locks are involved. I would commit after 100 reads (db2 selects) and or 100 updates. in your case you need to experiment and start commit more frequently.