You must declare the cursor INSENSITIVE.
-----Messaggio originale-----
Da: RPG400-L [mailto:rpg400-l-bounces@xxxxxxxxxxxxxxxxxx] Per conto di Javier Sanchez
Inviato: domenica 19 luglio 2020 04:11
A: rpg400-l@xxxxxxxxxxxxxxxxxx
Oggetto: GET DIAGNOSTICS DB2_NUMBER_ROWS does not return exact row number
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
midrange.com
