Re: Problems with ODP in SQL cursor

[Vern Hamberg <hambergv@xxxxxxxxxxxxxx>]

Patrick

At 10:49 AM 11/3/97 +1100, you wrote:
>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).

I don't think anything is wrong here. The ODP is not the same as the SQL, rather it is used by SQL. An ODP, at the least, defines how data is accessed. The query optimizer determines, for each evocation of the SELECT, that the same access path can be used. That translates to the same ODP, if possible.

The result is actually better performance, if the ODP does not need to be rebuilt.

HTH


Vernon Hamberg
Systems Software Programmer
Old Republic National Title Insurance Company
400 Second Avenue South
Minneapolis, MN 55401
(612) 371-1111 x480


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