Re: embedded SQL loop leaves multiple files open

[Nathan Andelin <nandelin@xxxxxxxxx>]

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.