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

The problem,
for reasons unclear, sometimes the open cursor does not return any records
and the fetch loop exits after 1 iteration
*BUT*
Running the same select
(Select * From URPS/NMMLIMPF where updnbr = 0 )
from STRSQL
does retrieve *quite a few* records (1 million +)

I suspect that this problem occurs after some kind of undo is run
(updating updnbr and setting it to 0 )
but I'm not sure.

Some of the fields in this file are CCSID(1208)
and many are varlen and ALWNULL


Any help will be greatly appreciated

Gad


------------------------------

message: 3
date: Sun, 18 Oct 2015 09:07:23 -0500
from: CRPence <crpbottle@xxxxxxxxx>
subject: Re: SQLRPGLE SQL help needed 2

On 18-Oct-2015 04:52 -0500, Gad Miron wrote:

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

The problem,
for reasons unclear, sometimes the open cursor does not return
any records and the fetch loop exits after 1 iteration
*BUT*
Running the same select
(Select * From URPS/NMMLIMPF where updnbr = 0 )
from STRSQL
does retrieve *quite a few* records (1 million +)

Not sure what xSQLState2 is and how that gets initialized and reset,
but that reference seems to suggest an understanding for the need to
check SQL return codes. However there is not any test of the SQLSTATE
after the OPEN. That would seem the most likely place to look.

Nor is there any indication that any logging or notification is done
when SQLSTATE^=Success for the FETCH? The next likely place to look
then, is the FETCH. The effect of "no more rows" probably should be
tested-for by itself, rather than merely a test for Success, to decide
if there are any more rows to process; i.e. Not(Success) is any litany
of potential issues, other than "no more rows left to process". Thus
any other result than "no more rows" should probably log an error
[showing the unexpected state] and possibly effect an inquiry asking the
programmer to diagnose whatever SQLSTATE being received for which the
program was not coded as explicitly being handled; i.e. "handled" being
anything that the program recognizes as an innocuous condition that was
explicitly ignored, or that the program recognizes as an effective error
but for which recovery can be performed without user\programmer
intervention.

The logic for effecting CLOSE is not included in the snippet. Is
there any? A CLOSE ensures the ability to reuse the ODP for repeated
calls, thus position is obtained from an existing ODP rather than the
query running again and a new ODP created.
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/rzajq/retrs.htm
]

I suspect that this problem occurs after some kind of undo is run
(updating updnbr and setting it to 0 )
but I'm not sure.

If by "undo", ROLLBACK is meant, thus implying a prior UPDATE run
under isolation to SET updnbr=0, then a conflicting record lock could
easily effect what is described.

Some of the fields in this file are CCSID(1208)
and many are varlen and ALWNULL

If a job ever runs with CCSID(65535) [aka CCSID(*HEX)] while running
the query, then that would result in an error such that the test for
Success would be flawed in the manner previously alluded; as would the
aforementioned record lock, but in that case, pervasive, as in every
row, such that only the /first/ iteration would be processed, because
the msg SQL0332 or SQLCODE=-332 and SQLSTATE='57017',

Any help will be greatly appreciated

Sometimes I will precede my OPEN with a CLOSE, irrespective the
EndMod setting for the Close SQL Cursor OPTION\attribute, and the error
is recorded in logging. As such, the lack of an error on the close in a
failing scenario such as the described, might suggest that the *ENDMOD
did not function as expected.

The following _Listing of SQLSTATE values_ should include some
/messages/ with "cursor", "open", or "close", for things that might
possibly be expected to have gone wrong or may be worth logging as
informational on the OPEN or the FETCH.
[
http://www.ibm.com/support/knowledgecenter/api/content/ssw_ibm_i_72/rzala/rzalaccl.htm
]
"... Class Code 24: Invalid Cursor State ..."

The following may be helpful for use of the optimizer debug messages
to assist, if changes to the coding to effect logging or react to
unexpected conditions does not do so directly.
[
http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/rzajq/pimesodp.htm
]
"Query optimization performance information messages and open data paths
..."

--
Regards, Chuck



------------------------------

Subject: Digest Footer

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) digest 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.



------------------------------

End of MIDRANGE-L Digest, Vol 14, Issue 1476
********************************************