Portal | Manuals | References | Downloads | Info | Programs | JCLs | Mainframe wiki | Quick Ref
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Profile Log in to check your private messages Log in
 
I am getting sqlcode -904 while using the select query

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
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    Post subject: I am getting sqlcode -904 while using the select query
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

Site Director


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

PostPosted: Wed Dec 31, 2008 1:17 am    Post subject:
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    Post subject:
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

Site Director


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

PostPosted: Wed Dec 31, 2008 5:23 am    Post subject:
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    Post subject: Reply to: I am getting sqlcode -904 while using the select q
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: 1013
Location: India

PostPosted: Wed Dec 31, 2008 10:24 am    Post subject:
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    Post subject: Hi Bala
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    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts HEX value search in a DB2 query maxsubrat DB2 2 Wed Oct 04, 2017 3:04 pm
No new posts Create procedure issues -628 when add... chandraBE DB2 1 Mon Sep 18, 2017 12:16 pm
No new posts Can we limit length in concatenation ... balaji81_k DB2 7 Tue Aug 22, 2017 2:50 am
No new posts Select numeric portion from CHAR data... balaji81_k DB2 6 Sat Aug 19, 2017 1:51 am
No new posts SQL - select data available in index Nileshkul DB2 3 Mon Jun 26, 2017 1:30 am

Facebook
Back to Top
 
Job Vacancies | Forum Rules | Bookmarks | Subscriptions | FAQ | Polls | Contact Us