Re: SQL Closing Files

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