02a - SQL (EN)02c - Miscellanea DB2 for i (EN)

SQL: How many rows in my select statement?

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

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 ;                                          
        dcl-s  rows       packed(5:0);                            
        Exec SQL                                                   
          SET OPTION Commit = *none,                               
         Exec SQL                                                  
           DECLARE c1 CURSOR FOR                                   
           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)) ;                    

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                                                  

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.

About author

Senior IBM i Analyst/Developer and technical writer. Former collaborator of the Italian edition of "System i News" magazine and author of several publications about tools and development practices for the IBM i platform.

Leave a Reply

%d bloggers like this: