SQLRPGLE leaving QTEMP table OPEN

[stewartp]

Hi,
I have a .net function calling a Stored Procedure that calls and SQLRPGLE pgm.
I create a table in qtemp (using SQL CREATE TABLE)
write records to the qtemp table (using SQL INSERT)
return a cursor for all records in qtemp.

the problem that I have is that the QTEMP file is remaining Open after the SQLRPGLE pgm ends(*INLR on)
I dont want to close the cursor(SQL CLOSE), because if i do, the results sets are not returned.

Why is the QTEMP file remaining open?
How can I clean up?

Also, when I compile the SQLRPGLE pgm I needed to specify COMMIT *NONE, otherwise the QTEMP file would be locked giving me grief next time I call this function. But what would I do if I wanted commit on for some files but not say the QTEMP file.

thankyou in advance.

[chrisr]

Re: SQLRPGLE leaving QTEMP table OPEN

Setting on LR is not going to have any impact on a table opened thru embedded sql afaik. The job has an open data path to the table until you issue the close cursor directive.
This discussion covers similar ground

http://www.code400.com/forum/showthread.php/8051-Problem-with-cursors-and-temporary-tables

[B.Hauser]

Re: SQLRPGLE leaving QTEMP table OPEN

1. *LR is part of the RPG cycle and has nothing to do with embedded SQL.
2. Since ILE was introduced setting *LR to *ON does not end a program.
A program is not ended (removed from memory) before the activation group in which the program runs is ended (RCLACTGRP).
3. Embedded SQL works differently. The first time an SQL statement is executed within the same activation group (or module) a FULL OPEN must be performed. A full open includes, creating or at least validating an access plan, interviewing the statistics and estimating all access paths (in either an SQL index or a DDS described logical file). After having created the ODP (Open data path) is build, that means the temporary objects described in the access plan are generated and filled with data by using the access methods (index access or table scan or probe) also described in the access plan. Opening the data path is the most time consuming process when executing an SQL statement.
After the first execution of the SQL statement with ODP will be deleted. If the SQL statement is executed the next time within the same activation group, the access plan is validated and the ODP opened again.
After the second execution the ODP stays open (at least if the SQL statement is reuseable). For all subsequent exection only a PSEUDO OPEN must be performed, i.e. the ODP is reused and only the data within the temporary objects are updated.
To gain the best performance the default is to keep the ODPs open.
If a SQL statement must be performed only once and you do not want to keep the ODPs open, you may close the activation group in which your program runs. An other way to delete the ODPs as soon as the module is ended, is to set option CLOSQLCSR in the compile command to *ENDMOD.
I'd not suggest to change this default value.

BTW when returning a result set you CANNOT close the cursor and ODP in anyway, otherwise your result set is gone.

2. Using Commitment Control is (SQL) Standard, that's why the default value for option COMMIT is set to *CHG in the compile command.
When creating a table SQL tries to register this table in an journal, either QSQJRN in the data library or the journal specified in the data area QDFTJRN located in the data library. If a journal is not found the table is not registered. When executing an embedded SQL statement compiled with COMMIT other than *NONE, SQL automatically starts commitment control and all SQL manipulation statements (INSERT, UPDATE, DELETE) run under commitment control.
If a table is not registered in a journal (and tables in the QTEMP will never be!), the manipulation commands executed under commitment control will fail except NO Commit is explictly specified, just add WITH NC (= with no commit) at the end of your manipulation command:

Exec SQL  Insert into QTEMP/MyFile ....
        [B]with NC;[/B]

Last but not least ...
is it really necessary to create a temporary table?
For more information read the following article written by Kent Milligan:
A Sensible Approach to Multi-Step DB2 for i Query Solutions

Birgitta