How Many Records Did SQL Delete?

February 4, 2004 Hey, Ted

I have an RPG IV program with an embedded SQL DELETE statement. Is it possible for my program to determine how many records are deleted when the statement executes?

–Tom

DB2/400 returns this information to you through a subfield of the SQL communications area (SQLCA), a data area that is automatically included in your RPG program when you compile. You can refer to this subfield by either of two names: SQLERRD(3) or SQLER3. When a DELETE, UPDATE, or INSERT operation completes normally, DB2 updates the subfield with the number of rows that qualified for the operation.

C/exec sql                              
C+             delete from somefile   
C+              where company = :company
C/end-exec                              
 /free                                  
            if SQLER3 > *zero;          

I understand that RPG is the only compiler that includes the SQLCA by default. According to the SQL Reference, the COBOL, C, PL/I, and FORTRAN programs must use the SQL INCLUDE command to reference the SQLDA. In the following short COBOL program, the SQLCA is included in the working-storage section.

Identification division.                   
Program-ID.    SQL005.                     
                                           
Environment division.                      
                                           
Data division.                             
Working-storage section.                   
01  Company     pic s999   packed-decimal. 
01  EmptySet    pic x.                     
                                           
    Exec SQL include SQLCA end-exec.       
                                           
Procedure division.                        
Main-logic.                                
    Exec SQL                               
         set option commit=*none           
    end-exec.                              
    Exec SQL                               
         delete from qtemp/cuxt
          where company = 99   
    end-exec.                  
    if SQLERRD(3) > zero       
       move "N" to EmptySet    
    else                       
       move "Y" to EmptySet    
    end-if.                    
    goback.                    

For more information about the SQLCA, consult the DB2 reference on IBM’s Web site.

–Ted