SQLRPGLE Update 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 Update data in db2 using Update 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;                                             
                                                                
  //Update one or more records based on where clause            
  //If there is no where clause then all records will get updates
                                                                
  $cmp = 1;                                                     
  $empId = 102;                                                 
                                                                
  exec sql                                                      
    Update qgpl/mydata set                                       
    d1saly = d1saly + 100                                        
    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.