RE: External RE: Count records in SQL -- RPG400-L

Hi Jerry,

I am surprised you can just delete records from the files to be purged.

We did spend quite some time building purge processes to ensure we did not
break any referential integrity as part of removing records and this
process needs some regular checking and updating.

We have some clients with over 25 years of history and now trying to
convince them to actually purge some of that data is the difficult bit ...
if you know what I mean!

Cheers
Don

Â

Don Brown

Senior Consultant
Â
[1]OneTeam IT Pty Ltd
P: 1300 088 400

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jerry
Forss
Sent: Tuesday, 17 February 2026 11:57 PM
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: RE: External RE: Count records in SQL

Thank you ALL for your great explanations!

What I am doing is setting up a purge process for our main packages.
This has NEVER been done as they always wanted to have all history for
ever and ever.

This has caused several issues, not to say the least Invoice Numbers being
reused some 15 years later.

1 - Get Purge through date (I have convinced them at 8 years)
2 - List all files to be purged and use SQL to determine number of records
that are going to be purged using SQL.
This will also verify that there are no locks on the file as well.
3 - Display list file files/records as a verification.
4 - If No locks found and continue is selected.
Create Purge Library
Loop until done
Allocate file
CrtDupObj of each File in Purge Library
CPYF records from Live File to Duplicate file
Delete records from Live File using SQL
Reorg file
UnAllocate file

We have plenty of space on our box so no need to remove them from the
system and want them somewhere incase They MIGHT be needed in an inquiry.

Again, thank you all!

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Birgitta
Hauser
Sent: Thursday, February 12, 2026 10:51 PM
To: 'RPG programming on IBM i' <rpg400-l@xxxxxxxxxxxxxxxxxx>
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.

GET DIAGNOSTICS ... will only return information AFTER an SQL Statement is
run.

ROW_COUNT: (Except from the SQL Reference) Identifies the number of rows
associated with the previous SQL statement that was executed. If the
previous SQL statement is a DELETE, INSERT, REFRESH, or UPDATE statement,
ROW_COUNT identifies the number of rows deleted, inserted, or updated by
that statement, excluding rows affected by either triggers or referential
integrity constraints. If the previous SQL statement is a MERGE statement,
ROW_COUNT identifies the total number of rows deleted, inserted, and
updated by that statement, excluding rows affected by either triggers or
referential integrity constraints. If the previous SQL statement is a
multiple-row-fetch, ROW_COUNT identifies the number of rows fetched.
Otherwise, the value zero is returned.

May be DB2_NUMBER_ROWS would be the better option: (Except from the SQL
Reference)
If the previous SQL statement was an OPEN or a FETCH which caused the size
of the result table to be known, returns the number of rows in the result
table. For SENSITIVE cursors, this value can be thought of as an
approximation since rows inserted and deleted will affect the next
retrieval of this value. Otherwise, the value zero is returned.

Mit freundlichen Grï¿½ï¿½en / Best regards

Birgitta Hauser
Modernization - Education - Consulting on IBM i Database and Software
Architect IBM Champion since 2020

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok) "What is
worse than training your staff and losing them? Not training them and
keeping them!"
"Train people well enough so they can leave, treat them well enough so
they don't want to. " (Richard Branson) "Learning is experience ...
everything else is only information!" (Albert
Einstein)

-----Original Message-----
From: RPG400-L <rpg400-l-bounces@xxxxxxxxxxxxxxxxxx> On Behalf Of Jimmy
Sansi
Sent: Thursday, 12 February 2026 21:44
To: RPG programming on IBM i <rpg400-l@xxxxxxxxxxxxxxxxxx>
Subject: Re: Count records in SQL

What about ...

exec sql GET DIAGNOSTICS :Rows = ROW_COUNT;

[2]https://www.ibm.com/docs/en/i/7.5.0?topic=statements-get-diagnostics

On 2026-02-12 12:15, Jerry Forss wrote:

> 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 To subscribe, unsubscribe, or
change list options,
visit: [3]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
[4]https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
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: [5]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
[6]https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
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: [7]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
[8]https://archive.midrange.com/rpg400-l.

Please contact support@xxxxxxxxxxxxxxxxxxxx for any subscription related
questions.

--
Message protected by MailGuard: e-mail anti-virus, anti-spam and content
filtering.
[9]https://www.mailguard.com.au

References

Visible links
1. https://www.oneteamit.com.au/
2. https://www.ibm.com/docs/en/i/7.5.0?topic=statements-get-diagnostics
3. https://lists.midrange.com/mailman/listinfo/rpg400-l
4. https://archive.midrange.com/rpg400-l.
5. https://lists.midrange.com/mailman/listinfo/rpg400-l
6. https://archive.midrange.com/rpg400-l.
7. https://lists.midrange.com/mailman/listinfo/rpg400-l
8. https://archive.midrange.com/rpg400-l.
9. https://www.mailguard.com.au/