SQLRPGLE Delete Statement for db2 in iSeries (AS400)

In order to create an RPGLE source member that can use embedded SQL, you must use a source type of SQLRPGLE. To compile an SQLRPGLE source member, use the "Create SQL ILE RPG Object" (CRTSQLRPGI) command. If you are using PDM, option 14 will create a bound program, and option 15 will create a *MODULE object. If you are issuing the command manually, use the OBJTYPE parameter to indicate *PGM or *MODULE. Also, if your program updates a file that is not journaled, you must change the command option COMMIT to *NONE, otherwise the updates will not occur.

Its better to use the SET OPTION SQL statement. It allows you to enforce certain program options in the code itself rather than on the compile command which you might forget to do.

c/exec sql
c+ set option commit=*none,
c+ datfmt=*iso
c/end-exec

Sample SQLRPGLE program to Delete data in db2 using Delete SQL Statements

d $cmp            s              2s 0                            
d $empId          s              5s 0                            
d $empName        s             30a                              
d $empDate        s              8s 0                            
d $empSalary      s             11s 2                            
                                                                 
 /free                                                           
                                                                 
  //Set SQL options                                              
  exec sql                                                       
   SET OPTION                                                    
       commit=*none,                                             
       datfmt=*iso;                                              
                                                                 
  //Delete one or more records based on where clause             
  //If there is no where clause then all records will get deleted
                                                                 
  $cmp = 1;                                                      
  $empId = 102;                                                  
                                                                 
  exec sql                                                       
    Delete from qgpl/mydata                                       
    where d1cmp = :$cmp and d1id = :$empId;                      
                                 
    if sqlcod <> 0;              
    dsply sqlcod; //do something 
    endif;                       
                                 
  *inlr = *on;                   
                                 
 /end-free                            

Host Variables

Host variables are always preceded in SQL by a colon. Since your RPGLE program is the "host" of these SQL statements, the term host variable refers to any variable declared in the RPGLE program. We can use these variables when we execute SQL as parameters or as return values.