How to Use GET DIAGNOSTICS in SQLRPGLE to Retrieve Row Count on IBM i

In this post, I’ll walk you through a simple and effective way to retrieve the number of rows affected by SQL statements in your SQLRPGLE programs using the GET DIAGNOSTICS statement.

Whether you're inserting, updating, or deleting rows, GET DIAGNOSTICS allows you to programmatically capture how many records were impacted.

---

What You'll Learn

• How to use GET DIAGNOSTICS after an SQL operation
• How to check the number of rows inserted, updated, or deleted
• A practical example using two test tables

---

Prerequisites

Before running the example, create two tables using these SQL statements:

CREATE TABLE TESTFILE (
    TFTYPE CHAR(2) NOT NULL WITH DEFAULT,
    TFNUMB NUMERIC(7, 0) NOT NULL WITH DEFAULT
);

CREATE TABLE TESTFILE2 (
    TFTYPE CHAR(2) NOT NULL WITH DEFAULT,
    TFNUMB NUMERIC(7, 0) NOT NULL WITH DEFAULT
);

Then populate TESTFILE with the following sample data:

INSERT INTO TESTFILE (TFTYPE, TFNUMB)
VALUES 
    ('01', 1000),
    ('02', 2000),
    ('03', 3000),
    ('04', 4000),
    ('05', 5000);

---

The SQLRPGLE Source Code

Here is the full free-format RPGLE source code that demonstrates the use of GET DIAGNOSTICS:

 

  

  
**free
// ****************************************************************
// * Author.......: ALDO SUCCI                                    *
// * Description..: Retrieve number of rows affected by           *
// * SQL statements                                               *
// ****************************************************************
// * Purpose: Demonstrate how to use GET DIAGNOSTICS to retrieve  *
// *          the number of rows affected by SQL statements.      *
// *                                                              *
// * Pre-requisite: Create the tables TESTFILE and TESTFILE2      *
// *                                                              *
// * Run these SQL statements to create the tables:               *
// *                                                              *
// * create table TESTFILE (                                      *
// *     TFTYPE char(02) NOT NULL WITH DEFAULT,                   *
// *     TFNUMB numeric(07, 0) NOT NULL WITH DEFAULT              *
// * );                                                           *
// *                                                              *
// * create table TESTFILE2 (                                     *
// *     TFTYPE char(02) NOT NULL WITH DEFAULT,                   *
// *     TFNUMB numeric(07, 0) NOT NULL WITH DEFAULT              *
// * );                                                           *
// *                                                              *
// * Fill TESTFILE with sample data:                              *
// *                                                              *
// * INSERT INTO TESTFILE (TFTYPE, TFNUMB)                        *
// * VALUES ('01', 1000),                                         *
// *        ('02', 2000),                                         *
// *        ('03', 3000),                                         *
// *        ('04', 4000),                                         *
// *        ('05', 5000);                                         *
// ****************************************************************
 
ctl-opt option(*nodebugio:*srcstmt:*nounref)
        dftactgrp(*no);
 
// Declare a variable to hold the number of affected rows
dcl-s Rows int(10);
 
// Disable commitment control for this example
exec sql SET OPTION COMMIT = *NONE;
 
// ---------------------------------------------------------------
// Insert all rows from TESTFILE into TESTFILE2
// Use GET DIAGNOSTICS to retrieve the number of rows inserted
// ---------------------------------------------------------------
exec sql INSERT INTO TESTFILE2 (TFTYPE, TFNUMB)
         SELECT TFTYPE, TFNUMB FROM TESTFILE;
 
exec sql GET DIAGNOSTICS :Rows = ROW_COUNT;
 
dsply ('No. of rows inserted = ' + %char(Rows));
// The No. of rows inserted should be 5 if TESTFILE has 5 rows
 
// ---------------------------------------------------------------
// Update rows in TESTFILE2 where TFTYPE = '04'
// Set TFNUMB to 4444 and get the number of rows updated
// ---------------------------------------------------------------
exec sql UPDATE TESTFILE2
         SET TFNUMB = 4444
         WHERE TFTYPE = '04';
 
exec sql GET DIAGNOSTICS :Rows = ROW_COUNT;
 
dsply ('No. of rows updated = ' + %char(Rows));
// The No. of rows updated should be 1 if TFTYPE '04' exists
 
// ---------------------------------------------------------------
// Delete rows from TESTFILE2 where TFTYPE = '05'
// Use GET DIAGNOSTICS to retrieve number of rows deleted
// ---------------------------------------------------------------
exec sql DELETE FROM TESTFILE2
         WHERE TFTYPE = '05';
 
exec sql GET DIAGNOSTICS :Rows = ROW_COUNT;
 
dsply ('No. of rows deleted = ' + %char(Rows));
// The No. of rows deleted should be 1 if TFTYPE '05' exists
 
// Set last record indicator to end the program
*inlr = *on;

  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
  
 

---

Why Use GET DIAGNOSTICS?

• It gives you precise feedback on the SQL operation you just ran.
• It replaces the need to guess or re-query the table.
• It improves error handling and makes your programs smarter.

---

Output Example

If everything runs correctly, you should see:

No. of rows inserted = 5  
No. of rows updated = 1  
No. of rows deleted = 1  

---

Final Thoughts

Using GET DIAGNOSTICS is a simple way to make your SQLRPGLE applications more dynamic and aware of what's happening under the hood. It's particularly useful when working with large datasets or conditional logic based on SQL outcomes.

Have you used GET DIAGNOSTICS in your own programs? Share your experience in the comments below!

---

Tags: SQLRPGLE IBM i RPG Free GET DIAGNOSTICS DB2 for i

Written by Aldo Succi — Visit RPGFreeIBM Blog