Re: GET DIAGNOSTICS DB2_NUMBER_ROWS does not return exact row number

Simpler way to handle this. Declare an array of records for what you are
loading. Dim the array to be the page size plus one.

dcl-ds TD_RecordIn Qualified Template;

ClientLibrary VarChar(10);

ClientName VarChar(30);

ClientIdentity Int(20);

NumberOfClientTables Int(5);

end-ds;



dcl-ds gRecordsIn LikeDs(TD_RecordIn) Dim(RECORDS_TO_LOAD);

Do a Select into the array. and check to find out how many records you
loaded. If greater than subfile page size, then you have at least one more
record that can be loaded.

If True, do a fetch prior to move cursor back one record.

Anyway, you get the idea. Better performance because you are loading a page
of records and you can simply see if you have more records.

NumberToLoad = RECORDS_TO_LOAD;
Exec Sql Fetch Next
From CursorCTRLH
For :NumberToLoad Rows
Into :gRecordsIn;
SqlErrorCheck(SqlState:
'LoadOnePage':
'Fetch From C1 failed!':
EndOfFile);
If EndOfFile;
RecordsRead = 0;
Else;
RecordsRead = SqlErrD(3);
EndIf;

If RecordsRead > RECORDS_PER_PAGE;
NumberToWrite = RECORDS_PER_PAGE;
DisplaySubfileEnd = FALSE;
Exec Sql Fetch Prior
From CursorCTRLH;
SqlErrorCheck(SqlState:
'LoadOnePage':
'Fetch Prior From C1 failed!':
EndOfFile);
Else;
NumberToWrite = RecordsRead;
DisplaySubfileEnd = TRUE;
EndIf;

For x = 1 To NumberToWrite;
WriteSubfileRecord(x);
EndFor;

If gRecordsLoadedInSubfile = 0;
If NumberToWrite = 0;
Msg = 'No records found to load';
XVMSGF_SendProgramMessage('CPF9898':
CPF_MESSAGE_FILE_NAME:
Msg:
MAIN_PROCEDURE_NAME:
gdsSystemDataStructure.ModuleName:
PROGRAM_NAME);
EndIf;
Else;
If NumberToWrite = 0;
Msg = 'No More Records Found to Load';
XVMSGF_SendProgramMessage('CPF9898':
CPF_MESSAGE_FILE_NAME:
Msg:
MAIN_PROCEDURE_NAME:
gdsSystemDataStructure.ModuleName:
PROGRAM_NAME);
EndIf;
EndIf;

H_SFPOS = gSubfileRecordNumber;

Return;



On Sat, Jul 18, 2020, 7:11 PM Javier Sanchez <
javiersanchezbarquero@xxxxxxxxx> wrote:

I have this particular query. I am filling a subfile with records from my
customer's master. The desired access path is customer number, which is
already ordered, but these numbers are not necessarily adjacent, as there
is a verifying digit as the last one.

I was trying to read by blocks the same size as my subfile's page size,
trying also to make sure in every read of blocks I'll have to turn
indicator SLFEND or not.

I used a very simple scroll cursor with something similar to this:

EXEC SQL DECLARE CSR001 CURSOR FOR
SELECT * FROM CUSTOMER_MASTER WHERE CUSTOMER_NUMBER >
:myHostCustomerNumberVariable FETCH FIRST 13 ROWS ONLY;

As the subfile page size is the same as the subfile size (SFLPAG = SFLSIZ),
what I actually do is OPEN and CLOSE the cursor and change accordingly, the
value for myHostCustomerNumberVariable.

It's very curious that when reading the last page, which is less than my
page size, when I open my cursor and the do:

EXEC SQL GET DIAGNOSTICS :cursor_rows = DB2_NUMBER_ROWS;

cursor_rows is loaded with 13!!! and NOT with the remaining *5* rows in
the table.

Am I missing something, or is there a BUG here?

I understand that this GET DIAGNOSTICS tool is precisely to look ahead the
counted rows that match the criteria of the SELECT within the cursor.

Is it right that you may not always have the exact row number?

Thanks in advance.

JS
--
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxxxxxxxx
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

Help support midrange.com by shopping at amazon.com with our affiliate
link: https://amazon.midrange.com