RE: embedded SQL loop leaves multiple files open

Birgitta,

Thank you, although I am familiar with these open/close explanations, the significant thing here is that all these are handled automatically during the 300 "execute immediate" operations between the start and end of a single SQLRPGLE module. The add remove library list suggestion is the sort of thing I was looking for.

Peter

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Sunday, 6 July 2014 2:55 a.m.
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
Subject: AW: embedded SQL loop leaves multiple files open

Each SQL-Statement gets its own ODP (Open Data Path).
Because the FULL OPEN (the first execution is a time consuming process, i.e.
syntax checking, index estimation, questioning the statistics manager, building an access plan and finally opening the data path, creating the temporary objects described in the access plan and populating them with data, SQL tries to keep the ODPs open as long as possible.
If the ODP is reusable, it stays open until a hard close is performed.
A hard close can be forced by setting the compile option CLOSQLCSR to *ENDMOD or running the program in an activation group that can be reclaimed after the program was finished. (Option *ENDPGM is only allowed for OPM/RPGIII programs) You may also try to remove the data library from the library list and re-add it after (it may work assumed you are working with system naming conventions and not hardcoding the schema).

Mit freundlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is worse than training your staff and losing them? Not training them and keeping them!"


-----Ursprüngliche Nachricht-----
Von: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Luis Rodriguez
Gesendet: Saturday, 05.7 2014 13:40
An: RPG programming on the IBM i / System, i
Betreff: RE: embedded SQL loop leaves multiple files open

Peter,

Out of ideas for this one but, have you tried any others or the CLOSQLCSR options, like *endpgm?

Regards,
Luis

Sent from my Nexus 7 tablet. Please excuse my brevity.
On Jul 5, 2014 5:44 AM, "Peter Connell" <Peter.Connell@xxxxxxxxxx> wrote:

I have an SQLRPGLE program that has the loop that does 300 different
SQL execute immediate statements each of which performs a join on the
same 4 tables .
I've yet to determine why, after the program completes, DSPJOB
OPTION(*OPNF) shows 300 open file entries for each of the 4 files
(1200 open file entries) I figure this is not really a good thing.

The program uses *CALLER but the top level program was developed as an
OPM CLP which means that everything run in *DFTACTGRP (which I hate to
see)

Peter


-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of D*B
Sent: Saturday, 5 July 2014 5:22 p.m.
To: RPG programming on the IBM i / System i
Subject: Re: embedded SQL loop leaves multiple files open

... normally those open data pathes don't hurt if the database keeps
them open for performance reasons. CLOSQLCSR does lazy closes as the
SQL close operations too (BTW: best practice is to issue a close when
no longer needed and CLOSQLCSR should have no work to do, neither at
endmod, nor at endactgrp). A disconnect should close them hard, this
would implicit happen, if you specify ACTGRP(*NEW).

D*B

--
This is the RPG programming on the IBM i (AS/400 and 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.


######################################################################
###############

This correspondence is for the named person's use only. It may contain
confidential or legally privileged information, or both. No
confidentiality or privilege is waived or lost by any mistransmission.
If you receive this correspondence in error, please immediately delete
it from your system and notify the sender. You must not disclose, copy
or rely on any part of this correspondence if you are not the intended
recipient.
Any views expressed in this message are those of the individual
sender, except where the sender expressly, and with authority, states
them to be the views of Veda.
If you need assistance, please contact Veda on either :- Australia
customerassistance@xxxxxxxxxxx or New Zealand +64 9 367 6200
--
This is the RPG programming on the IBM i (AS/400 and 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.

--
This is the RPG programming on the IBM i (AS/400 and 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.