Determining the number of rows in the result table for a static scrollable cursor

You can determine how many rows are in the result table of an INSENSITIVE or SENSITIVE STATIC scrollable cursor.

Procedure

To determine the number of rows in the result table for a static scrollable cursor, follow these steps:

  1. Execute a FETCH statement, such as FETCH AFTER, that positions the cursor after the last row.
  2. Perform one of the following actions:
    • Retrieve the values of fields SQLERRD(1) and SQLERRD(2) in the SQLCA (fields sqlerrd[0] and sqlerrd[1] for C and C++). SQLERRD(1) and SQLERRD(2) together form a double-word value that contains the number of rows in the result table.
    • Issue a GET DIAGNOSTICS statement to retrieve the value of the DB2_NUMBER_ROWS item.

Example

The following C language code demonstrates how to obtain the number of rows in a result table of a sensitive static cursor.

EXEC SQL INCLUDE SQLCA;
long int rowcount; 
EXEC SQL                                            
 DECLARE SENSTAT SENSITIVE STATIC SCROLL CURSOR FOR  
 SELECT * FROM EMP;
EXEC SQL OPEN SENSTAT;
if (SQLCODE==0) {
 EXEC SQL FETCH AFTER SENSTAT; /* Position the cursor after the end */
                               /* of the result table               */ 
 if (SQLCODE==0) {
  /************************************/ 
  /* Get the row count from the SQLCA */
  /************************************/
  printf("%s \n","Row count from SQLCA: ");
  printf("%s %d\n","SQLERRD1: High-order word: ",sqlca.sqlerrd[0]);
                              /* Get the high-order word of the     */
                              /* result table size                  */
  printf("%s %d\n","SQLERRD2: Low-order word: ",sqlca.sqlerrd[1]);
                              /* Get the low-order word of the      */
                              /* result table size                  */
  /******************************************/ 
  /* Get the row count from GET DIAGNOSTICS */
  /******************************************/
  EXEC SQL GET DIAGNOSTICS :rowcount = DB2_NUMBER_ROWS;
  if (SQLCODE==0) {
   printf("%s %d\n","Row count from GET DIAGNOSTICS: ",rowcount);
  }
 }
}