RE: embedded SQL loop leaves multiple files open -- RPG400-L

Nathan,
I found that when I moved the 'execute immediate' operation to a separate module that closed the cursor on completion then I had only 4 open data paths.
But this performed p to 10 times worse so 1200 open data paths is the best option at present.

Thanks, Peter

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of Nathan Andelin
Sent: Tuesday, 8 July 2014 9:14 a.m.
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: embedded SQL loop leaves multiple files open

Peter,

I don't know if this will help. But I posted an example of a program that generates an SQL result set based on row filter criteria within a loop of
300 iterations, using a our high-level "cursor" API.

I don't fully understand your SQL select criteria, but it seems like you might be able to use a high-level SQL CLI API like this that would only create 4 open data paths for the 4 files in question, rather than 1,200 open data paths.

http://code.midrange.com/0783add3f1.html

Nathan.

On Mon, Jul 7, 2014 at 2:00 PM, Peter Connell <Peter.Connell@xxxxxxxxxx>
wrote:

Nathan,
I had not considered an SQL CLI approach. I too am a little wary of
SQLRPGLE. I hate stepping into the pre-compiled code.
Had not considered the view approach either. The process is very simple.
For index=1 To 300
Chain (index) SQLStmtFile
Exec sql execute immediate: SQLStmt // each stmt joins 4 files
in a different way
EndFor

The system performs a new open for each of the 4 files and every
iteration. But since the entire iteration completes in 4 secs then I
guess the system knows what it's about.

Peter

-----Original Message-----
From: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of
Nathan Andelin
Sent: Tuesday, 8 July 2014 6:49 a.m.
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: embedded SQL loop leaves multiple files open

Peter,

Years ago, I wrote high-level wrappers around IBM's SQL CLI
procedures, which today supports 99.9% of our SQL interface
requirements. I never really got into using SQLRPGLE interfaces much,
which seem somewhat clunky to me in comparison; That might just be
personal preference. One nice thing about the SQL CLI approach is that
you have full control over the open, navigation, and close of SQL
result sets via program logic. Have you ever considered something like that?

I don't know what you mean by a program running 300 SQL statements and
generating 300 values. You say the program joins only 4 DB tables.
Couldn't you just create an SQL View joining the 4 tables, then
refresh the View 300 times using different "where" clause criteria?
Seems like SQL CLI could handle that very easily with very readable code.

We always run SQL CLI interfaces using "server" mode, where the
statements (open, navigation, close, etc.) run in an instance of one
of the QSQLSVR prestart jobs, rather than the Job which uses the
result sets. SQL CLI appears to use shared memory for communicating
with Jobs which use the result sets.

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