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

I am getting sqlcode -904 while using the select query


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

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Tue Dec 30, 2008 10:29 pm
Reply with quote

Hi,

We have a cobol Db2 program, this program read the records from the input file and move the key value to host variable and then it is firing the select sql .If the record not found in the table then it simply insert the record in to the table using insert query else if record found then it simply write the record in to another file with the error description.

I have submitted this job will one million records in the input file but my job is getting abend with the sql code -904. As the per the Ceedump this program reads around 1 lac 10 thousand records and all the records were found in the table. So this program only fired the select sql.

And also I have checked the index, tablespace and database all are in the
RW mode. And I am sure that no one else using this table.

can you please help me to find the reason for this.

Thanks
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Dec 31, 2008 1:17 am
Reply with quote

Hello,

What resource is unavailable? What is the reason code?

You need to post more complete info for someone to be able to help.
Back to top
View user's profile Send private message
banand

New User


Joined: 05 Jun 2007
Posts: 28
Location: Mumbai

PostPosted: Wed Dec 31, 2008 3:53 am
Reply with quote

HI ,

Below I have given the abend details . Please let me know if you need any more information .


Below Information I got from SYSOUT
-----------------------------------------------
0000302.D15548A72.S1448 .X'004E27'
SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABL
RESOURCE 00000302, AND RESOURCE NAME D1558A72.S1448 .X'004E27'
SQLSTATE = 57011 SQLSTATE RETURN CODE
SQLERRP = DSNXRRC SQL PROCEDURE DETECTING ERROR
SQLERRD = 102 13172746 0 13813475 -640086009 0 SQL DIAGNOSTIC
SQLERRD = X'00000066' X'00C9000A' X'00000000' X'00D2C6E3' X'D9D

This Information I got from CEEDUMP:
-------------------------------------------

' DSNT408I SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CA
USED BY AN UNAVAILABLE RESOURCE. REASON 00C90096, TYPE OF
'
' RESOURCE 00000302, AND RESOURCE NAME D1558A72.S14
48 .X'004E27'
Back to top
View user's profile Send private message
dick scherrer

Moderator Emeritus


Joined: 23 Nov 2006
Posts: 19244
Location: Inside the Matrix

PostPosted: Wed Dec 31, 2008 5:23 am
Reply with quote

Hello Bala,

What happens if you run the same select query in SPUFI?

Also, please post the result of a -dis for the databawse/tablespace.

You might change to code to begin selecting rows after the last key used in the run that returns the -904. This would be to see if the same problem occurs after some new/different 10k rows are processed.
Back to top
View user's profile Send private message
saranya_sn

New User


Joined: 03 Jul 2005
Posts: 12
Location: India

PostPosted: Wed Dec 31, 2008 9:39 am
Reply with quote

Hi Bala,

From the Ceedump you can see the reason code and this tells:
00C90096

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



As your program is also doing Inserts, and there is no frequent commmits, because of which the job is abending with -904 and there is a lock escalation.

Set some commit frequeny and commit when ever thet limit is reached. and initialise the comit count.

I guess this will resolve your porblem.

-Saranya S
Back to top
View user's profile Send private message
sushanth bobby

Senior Member


Joined: 29 Jul 2008
Posts: 1020
Location: India

PostPosted: Wed Dec 31, 2008 10:24 am
Reply with quote

Banand,

Reduce the number of input records in the input file.
Try it out.

Thanks,
Sushanth
Back to top
View user's profile Send private message
ameenansari

New User


Joined: 18 Sep 2006
Posts: 11
Location: chennai

PostPosted: Wed Dec 31, 2008 10:25 am
Reply with quote

Hi Bala,

Use WITH UR command in your cursor. This might resolve your problem

-Ameen
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 RC query -Time column CA Products 3
No new posts Dynamically pass table name to a sele... DB2 2
No new posts Query on edit primary command CLIST & REXX 5
No new posts Query on edit primary command CLIST & REXX 1
No new posts SELECT from data change table DB2 5
Search our Forums:

Back to Top