-904 DB2 error with DSNTEP2 utility

jagankallis · 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

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

priyesh.agrawal · Posted: Wed Jun 28, 2006 6:50 am

-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.

jagankallis · 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;

senthilssg · New User Joined: 09 Dec 2005 Posts: 64 Location: USA

Hi ,

From you error code ,

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

twissi · Posted: Fri Jun 30, 2006 3:53 pm

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.