Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

-904 DB2 error with DSNTEP2 utility

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Wed Jun 28, 2006 1:20 am    Post subject: -904 DB2 error with DSNTEP2 utility
Reply with quote

Hi All,

I am getting a SQL error -904 when running with the DSNTEP2 utility. Below is the description of my spool
Code:

SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000230, AND RESOURCE NAME 4K                                                 
SQLSTATE   = 57011 SQLSTATE RETURN CODE                                         
SQLERRP    = DSNXRSOR SQL PROCEDURE DETECTING ERROR                             
SQLERRD    = -115  13172746  0  13231826  -959250432  0 SQL DIAGNOSTIC INFORMATING ERROR 
SQLERRD    = X'FFFFFF8D'  X'00C9000A'  X'00000000'  X'00C9E6D2'  X'C6D30000'  X'959250432  0 SQL DIAGNOSTIC INFORMATION     



Here we see the page is getting filled due to the complicated query.

Could you please let me know the reason for the 904 error.
Back to top
View user's profile Send private message

vijayamadhuri

Active User


Joined: 06 Apr 2005
Posts: 180

PostPosted: Wed Jun 28, 2006 1:46 am    Post subject: hello
Reply with quote

The reason could be that someone is holding the table u r using . Give q
qw -904 on u r command line .it gives u a clear explanation of what the error is and a programnmer can do to resolve the error.

Happy Debugging icon_smile.gif
Back to top
View user's profile Send private message
priyesh.agrawal

Senior Member


Joined: 28 Mar 2005
Posts: 1452
Location: Chicago, IL

PostPosted: Wed Jun 28, 2006 6:50 am    Post subject: Re: -904 DB2 error
Reply with quote

-904 is an abend caused by Resource Deadlock.

You may want to check if Tablespace was available for ur SQL Statement.
Check to see, what mode this was at the time of abend, was that RW ???

Also you'll have to check if any other job was holding the concerned resource at that time. May be your DBA can help you better in Joblog Jargons.
Back to top
View user's profile Send private message
jagankallis
Warnings : 1

New User


Joined: 28 Jun 2006
Posts: 29
Location: DUISBURG

PostPosted: Wed Jun 28, 2006 12:58 pm    Post subject:
Reply with quote

Dear All,

Thanks for all your responses. But in my case no tables were held by any one. The reason could be the query is complicated. There are 4 views which is used to further process. The page 4K is getting filled, is there a way to increase the processing buffer size. Yes the deadlock has occured due to the reason the page is getting locked. How to realease the page ?

Please refer the query below

SELECT DISTINCT(C.KD_SYS_ID)
FROM ( SELECT DISTINCT(XMA.KD_ID_KN1)
FROM XMA
WHERE XMA.MA94_FLG <> 'J'
AND XMA.DELQ_FLG = 'J'
) A,
( SELECT DISTINCT(RIH.KD_SYS_ID)
FROM RIH
) B,
( SELECT DISTINCT(RIA.KD_SYS_ID)
FROM RIA,
RKT
WHERE RIA.KTO_SYS_ID = RKT.RKT_ID
AND RKT.RA_STATUS = 'E'
) C,
( SELECT DISTINCT(RIA.KD_SYS_ID)
FROM RIA,
RKT
WHERE RIA.KTO_SYS_ID = RKT.RKT_ID
AND RKT.RA_STATUS <> 'E'
) D
WHERE A.KD_ID_KN1 <> B.KD_SYS_ID
AND A.KD_ID_KN1 <> C.KD_SYS_ID
AND C.KD_SYS_ID = D.KD_SYS_ID
FETCH FIRST 5 ROWS ONLY
WITH UR;
Back to top
View user's profile Send private message
senthilssg

New User


Joined: 09 Dec 2005
Posts: 64
Location: USA

PostPosted: Thu Jun 29, 2006 6:17 pm    Post subject:
Reply with quote

Hi ,

From you error code ,
Code:

SQLCODE = -904, ERROR: UNSUCCESSFUL EXECUTION CAUSED BY AN UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE 00000230, AND RESOURCE NAME 4K



RESOURCE TYPE : 230 Temporary file


Reason code: 00C90084


Quote:
00C90084

Explanation: The temporary file with the page size shown in NAME is not available. An error was detected either during the opening of the page
set or during the formatting of the header and the space map pages of the page set.

This reason code is issued by the following CSECTs: DSNIWKFL, DSNITFFS

System Action: DB2 startup/restart continues if this reason code appears in a DSNT500I message issued during the startup process.

If the reason code appears in a DSNT500I message issued during the -START DATABASE command, the request fails. In either case, the
temporary file function does not allocate any temporary file on the page set.

User Response: Delete and redefine the temporary data set(s) in question, and then issue the -START DATABASE command to start the
temporary file.




So you will have to issue the following command
-start db (dsndb07)
suppose if u dont have suuffiect privilege ,please ask you SYSADM to do this


Regards ,
Senthil
Back to top
View user's profile Send private message
harry

New User


Joined: 27 Mar 2006
Posts: 31

PostPosted: Fri Jun 30, 2006 1:03 pm    Post subject: Re: -904 DB2 error
Reply with quote

Hi,

Many time you will get the sql return -904 if your data sets are got migrated.

Here in your case just try to running the query on table via spufi and then

you will get the DATA SET name and then recall it by IBM2(TSO2) recall will happen faster, then might be it will resolve.

Correct me if i am wrong.

Regards
Harry
Back to top
View user's profile Send private message
twissi

Active User


Joined: 01 Aug 2005
Posts: 105
Location: Somerset, NJ

PostPosted: Fri Jun 30, 2006 3:53 pm    Post subject:
Reply with quote

Harry,

There could be may reasons why you get a -904 for your sequel. It's basically because of an unavailable resource and I agree to your point that most of the times it happens becuase of the reason you said.

You get more information about the failure from the reason code; for the reason you said, the reason code will be 00C200F6.

Here Jaganathan has clearly given the reason code of 00C90084, so, it was not because of the reason you said.

Does this make any sense?
Twissi.
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 OO COBOL compile error Virendra Shambharkar COBOL Programming 3 Tue Jan 10, 2017 6:05 pm
No new posts TAPE VERIFY utility?? MrExtraordinare JCL & VSAM 3 Fri Dec 09, 2016 10:57 pm
No new posts S922 Error yuvan ABENDS & Debugging 3 Fri Dec 02, 2016 6:58 pm
No new posts Invoke Webservice Fails with DFHPI100... divated CICS 2 Thu Nov 24, 2016 5:57 pm
No new posts Error during restore rename archanamuthukrishnan All Other Mainframe Topics 2 Fri Oct 14, 2016 3:30 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us