× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.


  1.  Home
  2. RPG400-L
  3. December 2013

RE: SQL Closing Files

I second Charles' comment on SQL DELETE in place of CLRPFM.

This was so effective for our nightly operations we made a
local command to have an easy alternative.

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

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.


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


As an Amazon Associate we earn from qualifying purchases.

This thread ...
Follow-Ups:
Replies:
Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].

Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.