IBM Mainframe Forum

Hi,

I'm facing follow problem... Please advise if any. Many thanks.

When running a job to execute a DB2 program, my program is stucked in the paragraph 'OPEN CURSOR'. Job keeps running for long time without IO, not error message displayed..
I've try to debug as:
1. Display message before 'OPEN CURSOR', the message displays normally.
2. Display message after 'OPEN CURSOR', the message never shows up.
3. Tried to use JCL to run the SQL statement that declare in the cursor. The job ended and result showed normallly.
4. Tables are REORG, RUNSTAT, REBIND.

Please advise any hint..

If your "open cursor" statement fails, you should be getting an SQL error telling you why. Does your program accumulate CPU time at the time it's not accumulating I/Os?

i would suggest putting a display before the FETCH. It could be that you are performing the fetch logic until SQLCODE = 100 and you may have encountered another error condition from DB2 and all of a sudden, you are stuck in an infinite loop.

perhaps a display of the SQLCODE after the FETCH would also be helpful.

Also, does your cursor have an ORDER BY clause. If so DB2 could be gathering all the data and attempting to sort it. If your result set is very large or it's doing tablescan's in a nested join that could be very expensive.

I would check the the PLAN_TABLE (assuming that your BIND uses EXPLAIN YES) and check the access paths to look for performance issues.

if you used EXPLAIN NO, then do your bind again with EXPLAIN YES.

Chuck H.

Terry Heinze wrote:If your "open cursor" statement fails, you should be getting an SQL error telling you why. Does your program accumulate CPU time at the time it's not accumulating I/Os?

The 'open cursor' statement did not fail, so not any SQL error message could be find. And yes, the program accumulate CPU time but without I/O.

chaat wrote:i would suggest putting a display before the FETCH. It could be that you are performing the fetch logic until SQLCODE = 100 and you may have encountered another error condition from DB2 and all of a sudden, you are stuck in an infinite loop.

perhaps a display of the SQLCODE after the FETCH would also be helpful.

Also, does your cursor have an ORDER BY clause. If so DB2 could be gathering all the data and attempting to sort it. If your result set is very large or it's doing tablescan's in a nested join that could be very expensive.

I would check the the PLAN_TABLE (assuming that your BIND uses EXPLAIN YES) and check the access paths to look for performance issues.

if you used EXPLAIN NO, then do your bind again with EXPLAIN YES.

Chuck H.

The program had not went to 'FETCH CURSOR' statement yet, it stuck in 'OPEN CURSOR' statement.
The cursor was test via JCL and also SPUFI, it works well...

Please show your open cursor code with the statements immediately prior and after.

Open can be very havy if where-clause isnt good.
Check tables in declare and the joins. Any wrong can takes days to execute.
U can try to take only 1 table and add the oter 1 at time.

abki wrote:Open can be very havy if where-clause isnt good.
Check tables in declare and the joins. Any wrong can takes days to execute.
U can try to take only 1 table and add the oter 1 at time.

You have joined this forum today, and rather than lurking a bit to see what kind of behaviour is expected, you have been posting willy-nilly, seemingly without paying any attention to the replies already given in the various threads, in essence wasting the time of those of us who subscribe to get new replies to threads.

In this case the TS stated that he tested the cursor in SPUFI where it worked without problems.

quick question for TS, in your program do you use any host variables in the predicate ?

if so then I would guess that in SPUFI you coded the SQL with literals in the predicate.

This can make a significant difference in the access path that DB2 chooses.

for example assuming that an index exists on column COL_A and DB2 frequency stats show that value 2 has only one occurrence out of 1M rows with a cardinality of 2. Also assume that the other columns do NOT have frequency stats...

assuming that there are other indexes on COL_B and COL_C


WHERE COL_B = :VALUE-3
AND COL_C = :VALUE-5
AND COL_A = :VALUE-2

the access path can be quite different because at bind time DB2 doesn't know what the value of the host variables.

where as in the following example (SPUFI would do a 'prepare' of the dynamic SQL) at which time DB2 would know the values of the literals and should choose the index with COL_A based on the frequency stats. When binding your program with host variables DB2 would not know that.

WHERE COL_B = 3
AND COL_C = 5
AND COL_A = 2

so it is quite possible that queries with literals in the predicate would run much differently than the same query with host variables.

ps.. I've been doing DB2 performance tuning full time for 10+ years and I can assure you that the famous IBM phrase "it depends" is very true...

that is why i suggested doing two EXPLAIN's of the SQL statements, one with literals and the second with parameter markers. Then look at the PLAN_TABLE rows to see the access path for each query.

If you have access to the free tool that IBM provides, DATA STUDIO, then the explain process is easier to do and you will see a graphical representation of the access plath.

Chuck H.