RE: Problems with ODP in SQL cursor

[Bruce Guetzkow <bguetzkow@xxxxxxxxxxxxx>]

Is the SQL-Cursor program being called dynamically or is it a bound-call?  If 
dynamic, is the
calling program ILE or OPM?  What about activation groups...same for both 
programs?

We have experienced "interesting" results with program calls between OPM/ILE 
programs.

Have you tried walking through the program with a debug-tool?  Is the region 
code being
updated in the called program?

Just food for thought,
--Bruce

----------
From:   Patrick Fleming[SMTP:patrickf@real.com.au]
Sent:   Sunday, November 02, 1997 5:49 PM
To:     'MIDRANGE-L@midrange.com'
Subject:        Problems with ODP in SQL cursor

Hi

I have a problem which I just discovered, where an open data path is being 
reused by a SELECT clause, even though the SELECT clause is different prom the 
clause that created the ODP. Why did it reuse the ODP, and how can I specify 
that the ODP gets rebuilt each time I open the cursor.

I have written an ILE RPG program on our V3R7 AS/400 that contains a PREPARE 
the cursor, DECLARE , OPEN CURSOR and CLOSE CURSOR. This program is called in a 
loop from another program and is passed a region code. The region code is 
concatenated to a string containing a "SELECT field1, field2, field3..... WHERE 
xxxxxx GROUP BY field1, field2, field3"

The program runs interactively, reading the file which is overriden into QTEMP. 
The joblog tells me after the PREPARE that the ODP was reused, and doesn't 
rerun the SQL SELECT clause I just built, but reuses the SQL from the first 
invocation. I am setting on LR at the end of the program before returning to 
the previous program to get the next region code.

My question is, how do I get DB2 not to reuse the old query, but to rebuild the 
query each time I open the cursor (i.e. each time the program is invoked from 
its calling program).  I have other programs using the PREPARE  etc. and they 
work ok (though they are not called by anything).

Please help 

TIA

Patrick Fleming
(patrickf@real.com.au)
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MAJORDOMO@midrange.com
|    and specify 'unsubscribe MIDRANGE-L' in the body of your message.
| Questions should be directed to the list owner/operator: david@midrange.com
+---
umidr

+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to "MIDRANGE-L@midrange.com".
| To unsubscribe from this list send email to MAJORDOMO@midrange.com
|    and specify 'unsubscribe MIDRANGE-L' in the body of your message.
| Questions should be directed to the list owner/operator: david@midrange.com
+---