AW: SQL Close Cursor Action

Hi,

you have to distinguish between hard cursor closes and soft closes or
implicitely and explicitely closes.
An implicitely close is performed as soon the SQL-Statement CLOSE will be
performed. An other reason for an implictely close is executing one of the
SQL-Statements COMMIT or ROLLBACK, if WITH HOLD is not specified in the
DECLARE statement.
With an implictely close an ODP can be reused, if it is reuseable. (CLOSE
and OPEN of serial cursor, will cause the cursor to be possitioned at the
beginning of the result set.)

An explicitely close is performed depending on the OPTION CLOSQLCSR in the
compile command or specified in an SET OPTION statement at either the end of
the activation group or at the end of the module. If you want to close a
cursor explicitely at the end of a program, use eithe activation group *NEW
or a named activation group combined with the execution of the CL-Command
RCLACTGRP at the end of the programm.
With an explictely close, all ODPs get deleted. If you execute the same SQL
statement again the complete OPEN and optiomization process must be executed
again.

May be I should explain what happens at an OPEN process:
1. First time an SQL statement gets executed:
   - If the SQL statement is executed by the new SQL query enginge (SQE)
access plans in the SQE plan cache get checked and validated.
   - If no access plan is available in the plan chache or the SQL statement
is executed by the old Classic Query Engine, access plans stored in the
(service-)programm objects (for static SQL) or in an SQL package for
(extended dynamic SQL) get validated.
   - If no access plan is available or dynamic SQL is used, an access plan
gets created from scratch.
     An access plan contains the information about the access methods used,
such as table scan or index access only or if temporary indexes or tables
must be generated.
     (Access plans for static SQL get stored in the (service-)program
objects, for extended SQL access plans get stored in the appropriate SQL
package. Access plans for dynamic SQL get NOT stored! If SQE is used to
execute the SQL statement, the access plan for all kinds of SQL-statements
additionally get stored in the SQE plan chache.)
   - When building or validating an access plan all indexes get checked to
determine the optimal one.

   - After creation or validation of the access plans, the data path gets
implemented.
     That means temporary tables or indexes are created, dynamic bitmaps get
generated and a lot more what is neccessary to get access to the data.
     Note: An access plan is the receipe, but it does no cooking.

   - After the implementation of the data path, the SQL statement can be
executed.

   - The first time the SQL-Statement gets executed the access path gets
deleted after execution.

2. Second time an SQL statement gets executed:
   - Access plans are checked and eventually updated again.
   - The data path must be built again.
   - The SQL statement gets executed.
   - If the data path is reusable, it won't get deleted anymore and can be
reused. (ODP=Open data path)

3. Beginning with the third execution, the ODP gets reused, and no
OPEN-process (access plan validation ...) gets executed.

Changing the library list or overriding/remove overrides affected
files/tables will cause an hard close of the cursors, i.e. delete open data
paths.

If you hard close your cursor at the end of a program/module, each time the
SQL statement gets executed again, the open process/optimization will be
executed again.

For performance issues, you should avoid hard closing your cursors whenever
possible. As far as I know up to 512 ODP can be hold in one job (by
default). If more data paths are needed the oldest ones get removed.

Each SQL statement gets its own ODP, that means executing the same SQL
statement in two different programs or subroutines in the same job, will
cause an optimization process and building an ODP for each statement.
Access plans can be shared, but not ODPs.

Mit freundlichen Gru?en / Best regards

Birgitta

"Shoot for the moon, even if you miss, you'll land among the stars."
(Les Brown)

-----Ursprungliche Nachricht-----
Von: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx]Im Auftrag von Jeff Young
Gesendet: Montag, 22. Mai 2006 21:54
An: rpg400-l@xxxxxxxxxxxx
Betreff: SQL Close Cursor Action


When creating an SQLRPGLE module, the only actions I can specify for closing
a cursor are *ENDMOD or *ENDACTGRP.
  What I would like to do is have all cursors closed when the program that
the module is a member of ends.
  Is there any way I can do this short of calling the module at program end
with a code to have it manualy close the cursor?

  TIA,



Jeff Young
Sr. Programmer Analyst
Dynax Solutions, Inc.
A wholly owned subsidiary of enherent Corp.
IBM -e(logo) server Certified Systems Exper - iSeries Technical Solutions
V5R2
IBM  Certified Specialist- e(logo) server i5Series Technical Solutions
Designer V5R3
IBM  Certified Specialist- e(logo)server i5Series Technical Solutions
Implementer V5R3












---------------------------------
Feel free to call! Free PC-to-PC calls. Low rates on PC-to-Phone.  Get
Yahoo! Messenger with Voice
--
This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.