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


This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

This mailing list archive is Copyright 1997-2019 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].