Thanks Rob,
Her original question was related to having over 200 ODPs and she was trying to reduce that number, she gave no details at first. So I said the only time a ODP should be created is when she opened her cursor. She just said imbedded SQL, that is how the 'should I be using a cursor question arose. Her quest is to reduce the number of data paths. Here I will give you her original missive:
Hi Danny,
Have you ever had an issue with a program that executes multiple embedded SQL's, multiple times, causing the number of Open Data Paths be excessive?
If so, how did you go about handling it?
Thanks
Thanks & Regards,
Danny Hayes
IT-Developer
Genuine Parts Company, APG IT
3100 Windy Hill Road
Atlanta, Georgia 30339
mailto:Danny_Hayes@xxxxxxxxx
(904) 502-2969
-----Original Message-----
From: MIDRANGE-L [mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Rob Berendt
Sent: Thursday, July 27, 2017 8:11 AM
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Subject: [EXT] Re: Should I be using cursors?
There are absolutists which state "if you are using cursors you are doing it wrong". Their belief is often that, if it takes any calculation between an input and an output, then you should limit yourself to an RPG read and RPG output operations. I kind of find that poppycock.
Another of their beliefs is that, if you can do "set at a time" updates then you should. Like
update mytable set payrate=payrate * :hostPayFactor where dept= :hostSelectedDepartment; This I have no problem with. Putting that in a cursor makes no sense.
Now, as to your particular situation...
While you did supply your sql statement I don't understand the recommendation for a cursor. I'm wondering if I do not have all the information. For example, Is your statement in a do loop in which some other operation updates one of the other files involved? For example
do loop...
read thisfile;
// do some calculations
open postFile;
write postFile;
close postFile;
execsql update thisfile set thisfile.colB=postfile.col27 where thisfile.colA = postfile.col01;
loop...
If this is the case then, yes, you should look at a cursor.
Rob Berendt
--
IBM Certified System Administrator - IBM i 6.1 Group Dekko Dept 1600 Mail to: 2505 Dekko Drive
Garrett, IN 46738
Ship to: Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com
From: Danny Hayes <Danny_Hayes@xxxxxxxxx>
To: Midrange Systems Technical Discussion <midrange-l@xxxxxxxxxxxx>
Date: 07/27/2017 07:21 AM
Subject: Should I be using cursors?
Sent by: "MIDRANGE-L" <midrange-l-bounces@xxxxxxxxxxxx>
I recently received the following email from a coworker, I am seeking
higher knowledge:
I am not using 'cursors'...
My statements are such as:
Exec Sql
INSERT into NSIRECDTA
WITH InScope (mldct, mlglpt, mlco, mlobj, mlsub)
AS
(select b.mldct, b.mlglpt, b.mlco, b.mlobj, b.mlsub
from nsiscope a
join F4095 b
on a.nsiobj = b.mlobj
and a.nsisub = b.mlsub
where b.mlCO = '00000'
group by b.mldct, b.mlglpt, b.mlco, b.mlobj, b.mlsub )
(select DISTINCT
sdDOCO as ndTDOC,
sdDCTO as ndTDCT,
sdKCOO as ndTCO,
cast((0) as numeric (8,0)) ndRDOC,
cast(' ' as char(2)) ndRDCT,
cast(' ' as char(5)) ndRCO,
glDOC as ndLDOC,
glDCT as ndLDCT,
glKCO as ndLCO,
cast((0) as numeric (7,1)) ndJELN,
cast((0) as numeric (8,0)) ndAA,
cast(' ' as char(12)) ndMCU,
cast(' ' as char(6)) ndOBJ,
cast(' ' as char(8)) ndSUB,
cast((0) as numeric (6,0)) ndDGJ,
cast(' ' as char(30)) nddesc,
cast(' ' as char(3)) ndRCD,
cast(' ' as char(1)) ndRCND,
:BatchNumber as ndICU,
(:counterNEW +1) as ndCNT,
User as ndUSER,
:ProgramID as ndPID,
cast('NSI0801-01' as char(10)) ndJOBN,
:$upmj as ndUPMJ,
current Date ,
current Time
from F0911
Join InScope e
on glDCTO = e.mlDCT
and glOBJ = e.mlOBJ
and glSUB = e.mlSUB
join F42119
on glKCO = sdKCO
and glDCT = sdDCT
and glDOC = sdDOC
Where e.mlCO = '00000'
and sdKCO = :inKCO
and sdDCT = :inDCT
and sdDOC = :inDOC
AND (sdDOCO, sdDCTO, sdKCOO, glDOC, glDCT, glKCO)
NOT IN(
Select ndTDOC, ndTDCT, ndTCO, ndLDOC, ndLDCT, ndLCO
from NSIRECDTA));
My source is :
MDMDEV/QRPGLESRC/NSI0801
MDMDEV/QRPGLESRC/NSI0801SQL - Tony's suggestion was to move all of the SQL
statements into another module, so that the calling module, NSI0801, would
execute the SQL, then NSI0801SQL turns on *inLR, so that the files would
be closed each time.
In her next email she asked:
Should I be using 'cursors' instead?
We are on V7R2.
Thanks In Advance,
Danny
"This e-mail and any attachments, contain Genuine Parts Company
confidential information that is proprietary, privileged, and protected by
applicable laws. If you have received this message in error and are not
the intended recipient, you should not retain, distribute, disclose or use
any of this information and you should destroy this e-mail, any
attachments or copies therein forthwith. Please notify the sender
immediately by e-mail if you have received this e-mail in error."
midrange.com
