IBM Mainframe Forum Index
 
Log In
 
IBM Mainframe Forum Index Mainframe: Search IBM Mainframe Forum: FAQ Register
 

-904 for updatation in a single Table


IBM Mainframe Forums -> DB2
Post new topic   Reply to topic
View previous topic :: View next topic  
Author Message
kingkunlao

New User


Joined: 23 Apr 2008
Posts: 6
Location: mumbai

PostPosted: Fri Oct 03, 2008 12:45 pm
Reply with quote

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
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Oct 03, 2008 1:41 pm
Reply with quote

The number of locks exceeded the maximum locks.

Could give you give a try by using

WITH UR;

at the end of your query
Back to top
View user's profile Send private message
kingkunlao

New User


Joined: 23 Apr 2008
Posts: 6
Location: mumbai

PostPosted: Fri Oct 03, 2008 1:45 pm
Reply with quote

I have given with UR for each query
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 1:54 pm
Reply with quote

Quote:

I am getting a -904 for updatation in a single Table .


you are trying to update a table, and receive a -904.
(by the way, I don't think you can update more than one table with a single query)

Quote:

I have given with UR for each query


don't think you can use WITH UR on an update query.

what actually are you doing? Is this batch or online; are you issuing COMMITs at all?
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Oct 03, 2008 1:57 pm
Reply with quote

If you are using updation you cannot use WITH UR; I missed the update part in your query.
Back to top
View user's profile Send private message
Prasanthhere

Active User


Joined: 03 Aug 2005
Posts: 306

PostPosted: Fri Oct 03, 2008 2:07 pm
Reply with quote

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
Back to top
View user's profile Send private message
anandinmainframe

Active User


Joined: 31 May 2007
Posts: 171
Location: India

PostPosted: Fri Oct 03, 2008 2:31 pm
Reply with quote

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.
Back to top
View user's profile Send private message
kingkunlao

New User


Joined: 23 Apr 2008
Posts: 6
Location: mumbai

PostPosted: Fri Oct 03, 2008 3:06 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 3:12 pm
Reply with quote

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.
Back to top
View user's profile Send private message
kingkunlao

New User


Joined: 23 Apr 2008
Posts: 6
Location: mumbai

PostPosted: Fri Oct 03, 2008 3:19 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 3:37 pm
Reply with quote

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?)
Back to top
View user's profile Send private message
kingkunlao

New User


Joined: 23 Apr 2008
Posts: 6
Location: mumbai

PostPosted: Fri Oct 03, 2008 3:47 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 4:33 pm
Reply with quote

Kunal,

Quote:

but in each of the jobs i have separated the ranges such that none of them are overlapping


I tend to think that you have 2 or more tasks on the same page; but this may also be due to the total number of page locks and have nothing to do with tasks overlapping.

spent a couple minutes going thru the documentation. Try reducing your commit frequency. Though you will use up cpu commiting so often, the number of locks will be reduced.

I assume you have 10 jobs because you have a lot of activity. Any easy test would be to reduce the commit frequency which may allow 10 concurrent tasks to run. How to balance commit frequency and the number of concurrent tasks? trial and error.

If your commit freqency is controlled by a one time input parm, you are limited. I normally have a commit freqency in the RESTART table, that can be modified on the fly, as each time i commit, I refresh my commit freqency.

hitting -904's means you are forced to rollback, which is not fun. I would start with a balance that has no -904's and then increase parms until the 904's occur, then throttle back. yeah, I know, lot of work, lot of time.
Back to top
View user's profile Send private message
kingkunlao

New User


Joined: 23 Apr 2008
Posts: 6
Location: mumbai

PostPosted: Fri Oct 03, 2008 4:49 pm
Reply with quote

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.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Fri Oct 03, 2008 6:26 pm
Reply with quote

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.

Quote:

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.


This is not my a..h... hat speaking. I have no idea what your application is doing, why, etc... That is why I suggested that you analyze your process. I don't know what the mix is to your separate jobs, what your table looks like, etc... If you have observed behavior that suggests fewer input results in less or no -904s you have something to go on. That is all that you can do: observe behavior and make educated guesses about what is going on. but, be careful, do not make assumptions. an educated guess is knowing precisely what is going on, why the system returns a code - and for what reason.

keep experimenting with your mix of input until you have observed enough behavior to accurately know what is going on and why.

Quote:

cause in the prior case there are many input records which are being (sic) errored out could this be one of the reason of getting the abend.


not sure here, don't know what you mean by an error. not looking for a response. At this point I am afraid that you must continue to experiment until you observe enough to determine what/how your process must be controlled to avoid the negative SQLcode returns.

you appear to be innovative enough to solve your problem. If something specific occurs and you want to know why, I or someone else here will be glad to provide you answers or starting directions.

apologize for donning my talk-a-lot hat.
Back to top
View user's profile Send private message
dbzTHEdinosauer

Global Moderator


Joined: 20 Oct 2006
Posts: 6966
Location: porcelain throne

PostPosted: Sat Oct 04, 2008 4:04 pm
Reply with quote

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.
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic View Bookmarks
All times are GMT + 6 Hours
Forum Index -> DB2

 


Similar Topics
Topic Forum Replies
No new posts Load new table with Old unload - DB2 DB2 6
No new posts Pulling a fixed number of records fro... DB2 2
No new posts Multiple table unload using INZUTILB DB2 2
No new posts Check data with Exception Table DB2 0
No new posts Dynamically pass table name to a sele... DB2 2
Search our Forums:

Back to Top