RE: External Re: Count records in SQL

I got it to work!
Thank you!

From: Jerry Forss
Sent: Thursday, February 19, 2026 7:17 AM
To: 'Daniel Gross' <[email protected]>
Cc: [email protected]
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 <[email protected]<mailto:[email protected]>>
Sent: Thursday, February 19, 2026 7:09 AM
To: Jerry Forss <[email protected]<mailto:[email protected]>>
Cc: [email protected]<mailto:[email protected]>
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 <[email protected]<mailto:[email protected]>>:

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 <[email protected]<mailto:[email protected]>>
Sent: Friday, February 13, 2026 12:25 AM
To: [email protected]<mailto:[email protected]>
Cc: Jerry Forss <[email protected]<mailto:[email protected]>>
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 <[email protected]<mailto:[email protected]>>:
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: [email protected]<mailto:[email protected]>
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]<mailto:[email protected]>
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected]<mailto:[email protected]> 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
-- 
This is the RPG programming on IBM i (RPG400-L) mailing list
To post a message email: [email protected]
To subscribe, unsubscribe, or change list options,
visit: https://lists.midrange.com/mailman/listinfo/rpg400-l
or email: [email protected]
Before posting, please take a moment to review the archives
at https://archive.midrange.com/rpg400-l.

Please contact [email protected] for any subscription related questions.