Last Updated on 14 October 2019 by Roberto De Pedrini
Sometimes we may need to know in advance the number of records you are about to fetch from the database and show to the user. Just think of applications built for the Web, where a list is shown in groups of elements and buttons allow jumping from one page to another, like this:
Here we see how the total number of elements already appears, while the rows are displayed 10 at a time.
To fetch the data, we need to define an SQL cursor and load all the fetched rows in a DS array, then managing the positioning page by page.
The count can be obtained by setting the result of a COUNT(*) in a variable against a statement with the same query as the cursor defined above.
But a more effective solution exists, and that saves us from writing two statements that are almost equal…
The GET DIAGNOSTICS statement returns a lot of information about the last executed SQL statement. Here the detailed documentation from IBM. In our case, the information we need is DB2_NUMBER_ROWS which, after the OPEN of a cursor, holds the number of rows of the result set.
Exec SQL
GET DIAGNOSTICS :rows= DB2_NUMBER_ROWS ;Here a simple RPG program which declares an SQL query and retrieves the number of rows:
//
// Row count with GET DIAGNOSTICS - DB2_NUMBER_ROWS
//
// *ENTRY PLIST
dcl-pi FAQ40001 ;
end-pi;
dcl-s rows packed(5:0);
*inLR=*on;
Exec SQL
SET OPTION Commit = *none,
CloSqlCsr=*EndMod,
DlyPrp=*YES
;
Exec SQL
DECLARE c1 CURSOR FOR
SELECT
m.tipoArt, m.articolo, descrizione,
codMag, dataMovimento, segno, m.um, quantita
FROM mduca1.movmag00f m
LEFT JOIN mduca1.anaart00f a ON
(m.tipoArt, m.Articolo)=(a.tipoArt, a.Articolo)
WHERE quantita > 50
;
Exec SQL
OPEN c1;
Exec SQL
GET DIAGNOSTICS :rows = DB2_NUMBER_ROWS;
dsply ('ROWS = ' + %char(rows)) ;
return; Running the program against a test database, this is the result:
DSPLY ROWS = 409
Press Enter to continue. Let’s check it running the same query from an SQL session, selecting only the record count:
But… there’s something wrong ! For the same query, the program returns 408 lines, and why does SQL return 448 ?
The answer to this weirdness is found in the IBM manual: for so-called “dynamic” cursors (like that seen above) the value returned in DB2_NUMBER_ROWS is to be considered an approximation, as any inserted or deleted lines may have an impact on the final results set.
To get the exact number of lines it is therefore necessary to explicitly define the cursor as “INSENSITIVE”; in this way the result set is “frozen” when the cursor is opened and any changes to the tables referenced in the SQL query will have no effect. INSENSITIVE cursors cannot be used for update or delete operations.
Exec SQL
DECLARE c1 INSENSITIVE CURSOR FOR
ecc...We run again the program after the change… and now everything finally fits !
DSPLY ROWS = 448
Press Enter to continue. Warning: if paging is directly managed into the SQL cursor (with the keywords LIMIT or FETCH FIRST n ROWS ONLY and OFFSET), the GET DIAGNOSTICS will not return the total number of rows of the result set, but only those specified in the LIMIT clause. To get the total it will therefore be necessary to define a second cursor without the clause limiting the number of lines fetched.
Introduction Often, in our applications, we need to work with text strings, and DB2 SQL can come in very useful Read more
DB2 Database and SQL ... maybe the most important things on IBM i platform: here's a collection of FAQs, tips Read more
Comes directly with the Easter egg this IBM announcement for the news of the IBM i 7.4 version, iNext version Read more
Introduction "Generated Always Column": are columns, table fields, filled by DB2 engine: something like columns with a default value but Read more
