Did you tried using DISCONNECT CURRENT and CONNECT RESET.
With V7R1 we have seen the ODP's being left open by SQLRPGLE.
This worked for us well , please try.
Thanks,
Ashwani Singh
-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Peter Connell
Sent: Sunday, July 06, 2014 5:45 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: RE: embedded SQL loop leaves multiple files open
It appears that running the stack in a named activation group or *DFTACTGRP makes little difference.
I've found that I can eliminate the 300 duplicate opens by compiling an extra program just for the 'exec immediate' which specifies *ENDMOD and sets on LR.
But unfortunately performance suffers in this case.
It is more efficient to allow a new open to be automatically created by the system for each of the 300 iterations since this would appear to allow their reuse.
Interestingly, I notice that no matter what activation group is used, the 'execute immediate' appears to always run in *DFTACTGRP.
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.
--
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.
midrange.com
