View previous topic :: View next topic
|
Author |
Message |
jagankallis Warnings : 1 New User
Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG
|
|
|
|
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 |
|
|
vijayamadhuri
Active User
Joined: 06 Apr 2005 Posts: 180
|
|
|
|
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 |
|
Back to top |
|
|
priyesh.agrawal
Senior Member
Joined: 28 Mar 2005 Posts: 1448 Location: Chicago, IL
|
|
|
|
-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 |
|
|
jagankallis Warnings : 1 New User
Joined: 28 Jun 2006 Posts: 29 Location: DUISBURG
|
|
|
|
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 |
|
|
senthilssg
New User
Joined: 09 Dec 2005 Posts: 64 Location: USA
|
|
|
|
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 |
|
|
harry
New User
Joined: 27 Mar 2006 Posts: 31
|
|
|
|
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 |
|
|
twissi
Active User
Joined: 01 Aug 2005 Posts: 105 Location: Somerset, NJ
|
|
|
|
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 |
|
|
|