Thanks again Birgitta, close CSR statements must/will be specified
and default compile opt CLOSQLCSR *ENDACTGRP will be used for SQLRPGLE
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Birgitta Hauser
Sent: Tuesday, February 04, 2014 7:39 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
Subject: AW: Activation Group design question
There is a difference between executing an CLOSE statement or using CLOSQLCSR.
For subsequent calls you need to close your cursors, so the data in the ODP can get refreshed.
But executing an close statement does NOT delete the ODP.
If CLOSQLCSR is specified the ODPs get deleted as soon as the module or activation group ends. (Consequently all left open cursors get closed).
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-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary Thompson
Gesendet: Tuesday, 04.2 2014 15:19
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: RE: Activation Group design question
Thank you Birgitta,
Taking suggestions from others and with your emphasis on CLOSQLCSR =
*ENDACTGRP:
clle_1 will use AG *NEW
sqlrpgle_1, sqlrpgle_2, sqlrpgle_3 will use AG *CALLER
and:
CLOSE CSR xx statements are removed/not run and
default compile opt CLOSQLCSR(*DFTACTGRP) will be used
It seems OPEN CSR xx statements remain unchanged and instead
are performed as more efficient "pseudeo opens" ??
(I always appreciate your closing quotes of LB and DB)
-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of Birgitta Hauser
Sent: Tuesday, February 04, 2014 12:55 AM
To: 'RPG programming on the IBM i (AS/400 and iSeries)'
Subject: AW: Activation Group design question
1. Run the CLLE in a named activation group. If it's an never ending program, that sleeps for some time but is not ended meanwhile, you may also use a *NEW activation group 2. The called SQLLE Programs should run in the *CALLER activation group.
3. Ending a program with *INLR or RETURN does not affect the SQL-Statements but RPG, i.e. files used with native I/O will be closed and reopened at the next time.
4. Avoid CLOSQLCSR = *ENDMOD, because the ODPs will be deleted as soon as the module ends. The next time the embedded (SQL) Program is called a FULL OPEN must be performed, i.e. among others the ODPs have to be created (which is the most time consuming process when executing an SQL Statement) and cannot be reused. With option CLOSQLCSR = *ENDACTGRP reusable ODPs remain opened until the activation group ends, only the data within the ODPs have to be updated (PSEUDO OPEN) the next time the program is called, which is much faster than executing a FULL OPEN. If your programs run in a *NEW activation group, it ends as soon as the program ends and all ODPs for this program get deleted at the end of a program. In this way you cannot profit from the CLOSQLCSR = *ENDACTGRP option.
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-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] Im Auftrag von Gary Thompson
Gesendet: Monday, 03.2 2014 21:45
An: RPG programming on the IBM i (AS/400 and iSeries)
Betreff: Activation Group design question
I have program clle_1 currently in pilot, and am reviewing activation group design to get correct function with good efficiency.
One concern is file updates made by program sqlrpgle_1 must be read by programs sqlrpgle_2 and sqlrpgle_3.
clle_1 runs as a (almost) never-ending program, meaning it runs in a loop for about 24 hours, ends and re-starts minutes later.
The clle_1 loop has two phases, separated by DLYJOB(75) :
1) Call sqlrpgle_1 to collect pallet item and quantity picked from
a 3rd-party app running on our network and update custom files on our system i. Data from the 3rd-party app is received from IFS directory 3p_1.
Call sqlrpgle_1 to collect pallet load status from the 3rd-party app and update custom files on the i to show pallets confirmed for shipment. Pallet confirmation is received from the same 3rd-party app, but the data is received from IFS directory 3p_2.
DLYJOB(75)
2) Call sqlrpgle_2 to check for customer deliveries where all pallets
are confirmed for shipment (no updates) and call sqlrpgle_3 to load local files with delivery data, print pallet labels and transfer an Advance Ship Notice to the customer.
DLYJOB(75)
My new activation group design:
Leave clle_1 set to *DFTACTGRP
Change sqlrpgle_1 from activation group QILE to *NEW
(to force updates to be "posted", assuming that with a *NEW
activation group, when sqlrpgle_1 returns with LR=*ON, file
updates "take effect" similar to FEOD ?)
Change sqlrpgle_2 from activation group *NEW to *CALLER,
Different than the *NEW activation group created by sqlrpgle_1,
for same reasons mentioned for sqlrpgle_1.
That is a combined total of some 1,400 activation group create/delete steps per day.
All suggestions/comments gratefully welcomed . . .
--
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 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
