Re: SQL Closing Files -- RPG400-L

There's your real problem...

You've got a process designed back in the 80's that expects to have the
system all to itself...

Rather than handicap the new functionality, consider updating the old
process and/or system systems.

Consider one or more of the following
- adding an ALCOBJ CONFLICT(*RQSRLS) to your month-end process
- changing the WAITFILE parm of the PF/LF to something other than *IMMED
- Use non-exclusive operations, for example instead of CLRPFM use SQL
DELETE

HTH,
Charles

On Thu, Dec 26, 2013 at 3:23 PM, <tegger@xxxxxxxxxxx> wrote:

The close is important as part of the month-end won't run if one of the
files is allocated by anything.

----- Original Message -----

From: "Eric DeLong" <EDeLong@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <
rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, December 26, 2013 2:55:02 PM
Subject: RE: SQL Closing Files

I hesitate to enter this conversation again, but I'm really unsure why
closing cursors is so important to you... IMO, CGI applications are
expected to offer high performance, which in SQLRPGLE applications is
enhanced with the implementation of "lazy close". In SQL, creating a new
cursor is EXTREMELY time consuming, and you would want to defer this cost I
would think...

Is there a specific issue you're trying to avoid? Locked records? File
locks interfering with backups?

I noted this performance impact in my earlier post, but only indirectly.
There are many considerations in regard to CGI performance, like avoiding
AG(*NEW) and what-not...

-Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of tegger@xxxxxxxxxxx
Sent: Thursday, December 26, 2013 1:31 PM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL Closing Files

I'm fiddling around with the settings now.

I have the programs in a shadow production with different compile settings
and a RCLRSC by the calling CL.
So far, there doesn't appear to be difference in performance. But, there's
only 5 people using the code right now. In another month or so, that'll
jump to around 200...

----- Original Message -----

From: "Gary Monnier" <Gary.Monnier@xxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <
rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, December 26, 2013 1:59:40 PM
Subject: RE: SQL Closing Files

Oh yes. I've been bitten by it. It has to do with IBM performing a
pseudo-close on the table.

For performance sake they don't actually completely close the table until
the CLOSQLCSR condition is encountered. As Mr. Thompson mentioned if
CLOSQLCSR is *ENDACTGRP it is a wise idea for the program containing the
embedded SQL run in a named activation group and reclaim the activation
group when the program ends.

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of tegger@xxxxxxxxxxx
Sent: Thursday, December 26, 2013 9:18 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: Re: SQL Closing Files

Looks like the CLOSQLCSR with *ENDMOD worked.
The *ENDPGM works with non-ILE programs.

----- Original Message -----

From: "Eric DeLong" <EDeLong@xxxxxxxxxxxxxxx>
To: "RPG programming on the IBM i (AS/400 and iSeries)" <
rpg400-l@xxxxxxxxxxxx>
Sent: Thursday, December 26, 2013 11:04:44 AM
Subject: RE: SQL Closing Files

Hi Jay,

SQL has specific requirements on how cursors are managed. There are
compiler options that control this, but I would recommend using an SQL SET
OPTIONS statement to define this behavior. By embedding these option into
source, you don't have to remember the magic mix of compile commands each
time you build the program. The Options block is like the H-spec in that
way... Here's one that's fairly standard (for me, anyway)...

EXEC SQL
Set Option
DatFmt=*Iso, Naming=*Sys, Commit=*None, UsrPrf=*User, DynUsrPrf=*User,
CloSqlCsr=*EndMod ;

Note the last option (CloSqlCsr)...

You need to search the online references (SQL Programming Reference) for
details on this option. To mark a starting point, please be sure to review
*ENDPGM as a possible solution. Please be aware that for CGI, you could
adversely affect performance by changing these behaviors.

You should probably use PRTSQLINF command on your CGI program, so that you
can see which SQL options were specified on your existing program. Review
those options in the SQL reference to understand why your program was NOT
closing cursors.

If you get stumped along the way, ask here and we'll help you understand.

Hth,
-Eric DeLong

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx]
On Behalf Of tegger@xxxxxxxxxxx
Sent: Thursday, December 26, 2013 9:23 AM
To: RPG programming on the IBM i (AS/400 and iSeries)
Subject: SQL Closing Files

I have a couple of programs that are RPG/CGI with embedded SQL using
CGIDEV2 that are not closing files. The *INLR is on and each SQL block has
the exec sql close statements. But the files are still being left open.

Anyone else seeing this??

Jay B
Delaware

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

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