Re: Embeded SQL ODP problem

[Mark Anderson <mja@xxxxxxxxxx>]

Schadd, if your concern is simply the resources consumed by the number of 
pseudo-closed cursors that are in the job (the ones that SQL keeps open 
for performance reasons), I would suggest using the QAQQINI option 
OPEN_CURSOR_THRESHOLD. This allows you to specify how many cursors SQL 
should keep open in the job. It closes them in a least recently used order 
so the ones that actually get reused the most stay open. Here is an 
excerpt from the Database Performance and Query Optimization book.

OPEN_CURSOR_THRESHOLD

OPEN_CURSOR_THRESHOLD is used in conjunction with OPEN_CURSOR_CLOSE_COUNT 
to manage the number of open cursors within a job. If the number of open 
cursors, which includes open cursors and pseudo-closed cursors, reaches 
this threshold value, pseudo-closed cursors are hard (fully) closed with 
the least recently used cursors being closed first. The number of cursors 
to be closed is
determined by OPEN_CURSOR_CLOSE_COUNT. The valid user-entered values for 
this parameter are 1 - 65536. Having a value of 0 (default value) would 
indicate that there is no threshold and hard closes will not be forced on 
the basis of the number of open cursors within a job.


IBM Distinguished Engineer
Chief Architect for DB2 UDB for iSeries 
IS2A /015-3 B109
507-253-2651 Tie 553-2651
mja@xxxxxxxxxx