Re: SQLRPGLE SQL help needed 2

Likely true..

Unless you need/want to run the update under commitment control.

If the file is journaled, then performance will suffer if you don't use
commitment control (or have the 5770SS1 Opt. 42 HA Journal Performance
LICPGM installed)

But 1 millions updates is too much for a single commit cycle.

In which case you can use a cursor and commit every 1000 records or so.
That will be better performing than a set based UPDATE WITH NC.

Again, unless you have 5770SS1 Opt. 42 HA Journal Performance and the
journal set JRNCACHE(*YES)...in which case the set UPDATE WITH NC would be
the best performing.

Charles


On Mon, Oct 19, 2015 at 1:06 PM, Mike Jones <mike.jones.sysdev@xxxxxxxxx>
wrote:

date: Sun, 18 Oct 2015 12:52:47 +0300
from: Gad Miron <gadmiron@xxxxxxxxx>
subject: SQLRPGLE SQL help needed 2

Hello guys

A problem I'm wrestling with for some time now but to no avail
The sample is a bit long so bear with me

SQLRPGLE program selects some records (those with updnbr = 0)
from a file and process each one using a cursor and a fetch loop

like this:

// Set SQL options
Exec Sql Set Option Datfmt=*Iso, Commit=*None, Closqlcsr=*Endmod;

// Declare open SQL cursor
Exec Sql Declare Cursor1 Cursor For Select * From URPS/NMMLIMPF

where

updnbr = 0
For Update of CHGTIMSTMP,CHGUSER,ACUM_TIT,ACUM_ISWC,
ACUM_ID,ACUM_PRF,SENTDGL,UPDNBR;

Exec Sql Open Cursor1 ;

Dow xSQLState2 = Success ; // Begin SQL Loop
Exec Sql Fetch Cursor1 Into :DATA ;
If xSQLState2 = Success; // Fatch

OK

start
EVAL W_COMPNY = D_COMPNY
More code
.
.
.
Exec Sql Update NMMLIMPF
set (CHGTIMSTMP,CHGUSER,ACUM_TIT,ACUM_ISWC,ACUM_ID,
ACUM_PRF,SENTDGL,UPDNBR) =
(:CHGTIMSTMP@,:CHGUSER,:ACUM_TIT,:ACUM_ISWC,:ACUM_ID,
:ACUM_PRF,:SENTDGL,:UPDNBR)
Where Current of Cursor1 ;

ITER

...

Running the same select
(Select * From URPS/NMMLIMPF where updnbr = 0 )
from STRSQL
does retrieve *quite a few* records (1 million +)

Hi Gad,

If your code is going to update 1 million + rows, you're going to pay a
huge performance price by structuring this code to update 1 million rows
one row at a time. If you provide more detail on how you're populating
host variables :CHGTIMSTMP@,:CHGUSER,:ACUM_TIT,:ACUM_ISWC,:ACUM_ID,
:ACUM_PRF,:SENTDGL,:UPDNBR, chances are good I can show you how to
structure this to be a SET based UPDATE that will dramatically outperform
single row processing, with a greatly reduced code volume, without having
to use an OPEN, FETCH, CLOSE, etc.. Something like this:

update URPS/NMMLIMPF
set (CHGTIMSTMP,CHGUSER,ACUM_TIT,ACUM_ISWC,ACUM_ID,
ACUM_PRF,SENTDGL,UPDNBR) =
( select CURRENT_TIMESTAMP, USER, etc..
from
where
)
where updnbr = 0

Mike
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.