Re: 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.


--
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.