Re: External Re: Count records in SQL

[Niels Liisberg <nli@xxxxxxxxxxxxxxxxx>]

The reason is the difference between insentive and sensitive cursors in the
different environments. SQLCLI, JDBC, Native ( RPG) and how you are opening
the cursor.

So i suggest you always use an OLAP function that removes the environment
and the way you are opening the cursor out of the equation

Use:

Select count(*) over() as counter  -- this is OLAP "looking once more at
the resultset before classic SQL gives you the resultset".










On Thu, Feb 19, 2026 at 4:08 PM Jerry Forss <JForss@xxxxxxxxxxxxx> wrote:

> I added the FOR READ ONLY.
>
>    SQLFile = File(FileIdx);
>     SQLLibrary = FileLibrary(FileIdx);
>
>     // Declare Sql Statement
>     Exec SQL  Declare LockCursor Cursor For
>        SELECT Object_Schema, Object_Name, Lock_Scope, Job_Name
>        FROM QSYS2.OBJECT_LOCK_INFO
>        WHERE SYSTEM_OBJECT_SCHEMA = :SQLLibrary
>          And SYSTEM_OBJECT_NAME = :SQLFile
>        FOR READ ONLY;
>
> Why it was getting a lock on a DtaQ in XA is baffling but is working well
> now.
>
> From: Daniel Gross <daniel@xxxxxxxx>
> Sent: Thursday, February 19, 2026 8:41 AM
> To: Jerry Forss <JForss@xxxxxxxxxxxxx>
> Cc: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: External Re: Count records in SQL
>
> And what was the solution?
>
> Regards,
> Daniel
>
>
>
> Am 19.02.2026 um 14:42 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:
> JForss@xxxxxxxxxxxxx>>:
> 
> I got it to work!
> Thank you!
>
> From: Jerry Forss
> Sent: Thursday, February 19, 2026 7:17 AM
> To: 'Daniel Gross' <daniel@xxxxxxxx<mailto:daniel@xxxxxxxx>>
> Cc: rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: RE: External Re: Count records in SQL
>
> I am also trying this but returns nothing.
>
>        SQLFile = File(FileIdx);
>         SQLLibrary = FileLibrary(FileIdx);
>
>         // Declare Sql Statement
>         Exec SQL  Declare LockCursor Cursor For
>            SELECT Object_Schema, Object_Name, Lock_Scope, Job_Name
>            FROM QSYS2.OBJECT_LOCK_INFO
>            WHERE SYSTEM_OBJECT_SCHEMA = :SQLLibrary
>              And SYSTEM_OBJECT_NAME = :SQLFile;
>
>
> Now, when I do a WRKOBJLCK it says there are no locks, but if I press F6
> for member locks it shows many.
> Am I missing something?
>
> From: Daniel Gross <daniel@xxxxxxxx<mailto:daniel@xxxxxxxx>>
> Sent: Thursday, February 19, 2026 7:09 AM
> To: Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:JForss@xxxxxxxxxxxxx>>
> Cc: rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
> Subject: Re: External Re: Count records in SQL
>
> Most probably the environment differs - like the transaction isolation
> level (between eSQL and STRSQL).
>
> You can try different clauses to solve the problem.
>
> Add "FOR READ ONLY" to your SQL statement
>
> Add "WITH NC" to your SQL statement
>
> Add "USE CURRENTLY COMMITTED" to your SQL statement
>
> Read about all 3 clauses here:
>
> -> https://www.ibm.com/docs/en/i/7.6.0?topic=queries-select-statement
>
> HTH
> Daniel
>
>
> Am 19.02.2026 um 13:45 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:
> JForss@xxxxxxxxxxxxx>>:
> 
> Hi Daniel
>
> I am checking to see if there are any locks on the files using
> QSys2.Objecy_Lock_Info and getting a lock wait on a DtaQ in the target
> library, SqlSTT of 01687.
>
> Do you have any idea why using that would be locking?
> When I run it under strsql there are no issues.
>
>     // Check If File Is Locked
>     SqlSelect = 'SELECT Object_Schema, ' +
>                       'Object_Name, '+
>                       'Lock_Scope, ' +
>                       'Job_Name ' +
>                'From QSYS2.Object_Lock_Info ' +
>                'Where OBJECT_SCHEMA = ' + SQ + %Trim(FileLibrary(FileIdx))
> + SQ + ' And ' +
>                'OBJECT_NAME = ' + SQ + %Trim(PmKey3) + SQ;
>
>     // Prepare Sql Statement
>     Exec SQL Prepare LockCmd From :SqlSelect;
>
>     // Declare Sql Cursor
>     Exec SQL Declare LockCursor Cursor For LockCmd;
>
>     // Run Sql Statement
>     Exec SQL Open LockCursor;
>
>     // Process Data Set
>     DoU Done;
>
>       // Get Employee Data
>       Exec Sql Fetch Next From LockCursor Into : Object_Schema,
>                                                : Object_Name,
>                                                : Lock_Scope,
>                                                : Job_Name;
>
>       //  If Eof Of Data or Unknown Error get out
>       If   (SQLSTT <> SQLOK)
>        And (SQLSTT <> SQLCmpError);
>         Leave;
>       EndIf;
>
>
>
> From: Daniel Gross <daniel@xxxxxxxx<mailto:daniel@xxxxxxxx>>
> Sent: Friday, February 13, 2026 12:25 AM
> To: rpg400-l@xxxxxxxxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxxxxxxxx>
> Cc: Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:JForss@xxxxxxxxxxxxx>>
> Subject: External Re: Count records in SQL
>
> CAUTION: This email originated from outside of the organization. Do not
> click links or open attachments unless you recognize the sender and know
> the content is safe.
> Hi Jerry,
>
> of course, the easiest way to count records is a static SQL like:
>
> exec sql select count(*)
>          into :nCountVar
>          from ...
>
> But as I have seen, you have a dynamic SQL to tables in different schemas.
> And that's not possible in static SQL without creating aliases first.
>
> But with dynamic SQL you can't have an INTO clause in your SELECT
> statement - so a possible solution would be:
>
> SqlSelect = 'select count(*) ' +
>       'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
>       'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
> SqlSelect = 'values ('+SqlSelect+') into ?';
> exec sql prepare stmSelectCount from :SqlSelect;
> exec sql execute stmSelectCount using :nCountVar;
>
> You have to wrap your SELECT in a VALUES/INTO statement with a dynamic
> parameter marker. Then prepare and execute it.
>
> The solutions using GET DIAGNOSTICS after the OPEN doesn't help, as the
> database does not always return the exact number of rows in the cursor - it
> can either be estimated or (as this might be an ASENSITIVE or SENSITIVE
> cursor) it can change during the fetch loop.
>
> But if you want to read all the records anyway, you can do the following:
>
> SqlSelect = 'SELECT count(*) over(), ' +
>                      'C6DcCd, '+
>                      'C6CvNb, '+
>                      'C6AcDt, ' +
>                      'C6FnSt,' +
>                      'C6B9Cd  ' +
>               'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
>               'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
> exec sql prepare stmSqlSelect from :SqlSelect;
> exec sql declare csrSqlSelect INSENSITIVE cursor for stmSqlSelect;
> exec sql open...
>
> exec sql fetch csrSqlSelect into :nCountVar, ...;
>
> This way you have to actual number of rows with every fetch in the
> variable. As the cursor is declared as INSENSITIVE, the rows won't change,
> even when new rows matching the criteria are inserted during runtime.
>
>     COUNT(*) OVER()
>
> is the simpliest form of an OLAP expression (aka window function). It's a
> very powerful feature of modern SQL and massively underrated and
> underutilized.
>
> ->
> https://www.ibm.com/docs/en/i/7.6.0?topic=expressions-olap-specifications
>
> In essence, you can calculate typical aggregates on every level of your
> select statement without using GROUP BY. Like having a grouped counter and
> row number over groups of rows or creating running totals, ranks and a lot
> more.
>
> If you need the row count and the records, I would do it with OLAP. If you
> only need the count, the VALUES INTO solution is good.
>
> What is the purpose of the program? Or better what doesn't do with the
> fetched rows?
>
> If you need more help, feel free to contact me via the list or in private.
>
> HTH
> Daniel
>
>
>
>
>
> Am 12.02.2026 um 21:16 schrieb Jerry Forss <JForss@xxxxxxxxxxxxx<mailto:
> JForss@xxxxxxxxxxxxx>>:
> I have a SQL
>  SqlSelect = 'SELECT C6DcCd, ' +
>                      'C6CvNb, '+
>                      'C6AcDt, ' +
>                      'C6FnSt,' +
>                      'C6B9Cd  ' +
>               'From ' + %Trim(PurgeXALib) + '/MBC6REP ' +
>               'Where C6ACDT <= ' + %EditC(PurgeDateCYMD : 'X');
>
> Instead of reading through the cursor, I want the number of records found.
>
> How do I do that?
> --
> This is the RPG programming on IBM i (RPG400-L) mailing list
> To post a message email: RPG400-L@xxxxxxxxxxxxxxxxxx<mailto:
> 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<mailto:
> 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<mailto:
> support@xxxxxxxxxxxxxxxxxxxx> for any subscription related questions.
>
> Subject to Change Notice:
>
> WalzCraft reserves the right to improve designs, and to change
> specifications without notice.
>
> Confidentiality Notice:
>
> This message and any attachments may contain confidential and privileged
> information that is protected by law. The information contained herein is
> transmitted for the sole use of the intended recipient(s) and should "only"
> pertain to "WalzCraft" company matters. If you are not the intended
> recipient or designated agent of the recipient of such information, you are
> hereby notified that any use, dissemination, copying or retention of this
> email or the information contained herein is strictly prohibited and may
> subject you to penalties under federal and/or state law. If you received
> this email in error, please notify the sender immediately and permanently
> delete this email. Thank You
>
> WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
> www.walzcraft.com<https://www.walzcraft.com> Phone: 1-800-237-1326
>
>
> Subject to Change Notice:
>
> WalzCraft reserves the right to improve designs, and to change
> specifications without notice.
>
> Confidentiality Notice:
>
> This message and any attachments may contain confidential and privileged
> information that is protected by law. The information contained herein is
> transmitted for the sole use of the intended recipient(s) and should "only"
> pertain to "WalzCraft" company matters. If you are not the intended
> recipient or designated agent of the recipient of such information, you are
> hereby notified that any use, dissemination, copying or retention of this
> email or the information contained herein is strictly prohibited and may
> subject you to penalties under federal and/or state law. If you received
> this email in error, please notify the sender immediately and permanently
> delete this email. Thank You
>
> WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
> www.walzcraft.com<https://www.walzcraft.com> Phone: 1-800-237-1326
>
>
>
> Subject to Change Notice:
>
> WalzCraft reserves the right to improve designs, and to change
> specifications without notice.
>
> Confidentiality Notice:
>
> This message and any attachments may contain confidential and privileged
> information that is protected by law. The information contained herein is
> transmitted for the sole use of the intended recipient(s) and should "only"
> pertain to "WalzCraft" company matters. If you are not the intended
> recipient or designated agent of the recipient of such information, you are
> hereby notified that any use, dissemination, copying or retention of this
> email or the information contained herein is strictly prohibited and may
> subject you to penalties under federal and/or state law. If you received
> this email in error, please notify the sender immediately and permanently
> delete this email. Thank You
>
> WalzCraft PO Box 1748 La Crosse, WI, 54602-1748
> www.walzcraft.com<https://www.walzcraft.com> Phone: 1-800-237-1326
> --
> 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.