Stuck when open cursor



Support for OS/VS COBOL, VS COBOL II, COBOL for OS/390 & VM and Enterprise COBOL for z/OS

Stuck when open cursor

Postby wsytx » Fri Aug 22, 2014 8:15 pm

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..
wsytx
 
Posts: 3
Joined: Fri Aug 22, 2014 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Stuck when open cursor

 

Re: Stuck when open cursor

Postby Terry Heinze » Fri Aug 22, 2014 11:01 pm

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?
.... Terry
Terry Heinze
 
Posts: 193
Joined: Wed Dec 04, 2013 11:08 pm
Location: Richfield, MN, USA
Has thanked: 10 times
Been thanked: 11 times

Re: Stuck when open cursor

Postby chaat » Sat Aug 23, 2014 1:03 am

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.
chaat
 
Posts: 14
Joined: Sun Aug 16, 2009 11:07 pm
Location: St. Cloud, Minnesota
Has thanked: 0 time
Been thanked: 1 time

Re: Stuck when open cursor

Postby wsytx » Sat Aug 23, 2014 7:02 am

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.
wsytx
 
Posts: 3
Joined: Fri Aug 22, 2014 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Stuck when open cursor

Postby wsytx » Sat Aug 23, 2014 7:48 am

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...
wsytx
 
Posts: 3
Joined: Fri Aug 22, 2014 7:59 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Stuck when open cursor

Postby NicC » Sun Aug 24, 2014 5:52 pm

Please show your open cursor code with the statements immediately prior and after.
The problem I have is that people can explain things quickly but I can only comprehend slowly.
Regards
Nic
NicC
Global moderator
 
Posts: 2690
Joined: Sun Jul 04, 2010 12:13 am
Location: Pushing up the daisys (almost)
Has thanked: 4 times
Been thanked: 105 times

Re: Stuck when open cursor

Postby abki » Fri Sep 12, 2014 7:00 pm

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
 
Posts: 3
Joined: Fri Sep 12, 2014 6:18 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Stuck when open cursor

Postby prino » Fri Sep 12, 2014 7:50 pm

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.
Robert AH Prins
robert.ah.prins @ the.17+Gb.Google thingy
User avatar
prino
 
Posts: 536
Joined: Wed Mar 11, 2009 12:22 am
Location: Oostende, Belgium
Has thanked: 3 times
Been thanked: 21 times

Re: Stuck when open cursor

Postby chaat » Fri Sep 19, 2014 11:04 am

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.
chaat
 
Posts: 14
Joined: Sun Aug 16, 2009 11:07 pm
Location: St. Cloud, Minnesota
Has thanked: 0 time
Been thanked: 1 time


Return to IBM Cobol

 


  • Related topics
    Replies
    Views
    Last post