Re: SQLRPGLE SQL help needed 2

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