Re: AW: Closing files opened by SQL

Hi Birgitta,
  I have a RPGLE program consisting of 3 modules.
  Module 1 is the main driver
  Module 2 uses SQL to build a list of items to process - Module 2 uses the 
default for 
  CloSqlCsr
  Module 3 uses SQL & Native I/O to Update files and uses 
  CloSqlCsr = *EndMod .
  In addition, when I am finished processing the data retreived for the cursor, 
I am issuing an explicit SQL CLOSE  for it.
   
  When I am done with the program, I want to close all files that were open.
  I am using a named activation group to allow subprocedures in my modules and 
do not want to use *NEW unless I must.
   
  Thanks,


HauserSSS <Hauser@xxxxxxxxxxxxxxx> wrote:
  Hi Jeff,

you have to distinguish between implicit and explicit closes.
A cursor gets explicitely closed as soon as one of the following
SQL-statements is performed:
CLOSE, COMMIT or ROLLBACK (without hold)

A cursor gets implicitely (or hard) closed at either the end of the
activation group or at the end of a module (in ILE), depending on what is
specified in the CLOSQLSRC option in the compile command (CRTSQLRPGI) or in
an SET OPTION statement embedded in your source code.
*ENDACTGRP is the default value and hard closes the cursor at the end of the
activation group.
*ENDMOD will hard close the cursor at the end of the module.
(For RPGIII options *ENDJOB, *ENDPGM and *ENDSQL are available)

For performance issues, you should prefer *ENDACTGRP.
The first time an SQL statement gets executed the access plans stored in the
program object (if static SQL, otherwise the access plan gets built from
scratch) gets validated and the optimal access path gets determined and
used. The second time the same SQL statement gets executed, the access plans
are checked again and either confirmed or an other access path gets
determined. If the access path is reusable, the Opend Data Path stays open
after the second execution and gets used each time the SQL statement gets
executed (no overhead of access plan validation and optimization any more)

If you hard close the cursor at the end of the module, the complete
procedure of validating access plans, determining the optimal access path
and opening the access path must be performed each time the SQL-statement
gets executed.

By the way up to 512 ODPs per job can be opened, if additionals are used,
the ODP that was not used for the longest time gets closed.

Mit freundlichen Gruessen / 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: Mittwoch, 22. Marz 2006 19:50
An: midrange-l@xxxxxxxxxxxx; rpg400-l@xxxxxxxxxxxx
Betreff: Closing files opened by SQL


I have an SQLRPGLE program running in a named activation group.
The program uses SQL to select data using a cursor, update data based on
host variables and insert data using host variables.
Before returning at the end of the program, I issue a CLOSE for the SQL
Cursor.
When the program completes, I check my list of open files and I see all
the tables used in the program are still open.
In order to close them, I must do a RCLACTGRP.
Is there another method to close these files when I am finished with them?

Thanks,



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












---------------------------------
Yahoo! Mail
Use Photomail to share photos without annoying attachments.
--
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.