SQLRPGLE Create Table in db2 - Add column heading, column text, drop table

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 Create Table on iSeries(AS400)

 /free                                                                          
                                                                                
  //Set SQL options                                                             
  exec sql                                                                      
   SET OPTION                                                                   
       commit=*none,                                                            
       datfmt=*iso;                                                             
                                                                                
  //Create the SQL table                                                        
  exec sql                                                                      
    CREATE TABLE QGPL/MYDATA (                                                   
       COMPANY FOR COLUMN D1CMP DEC ( 2, 0) NOT NULL WITH DEFAULT 0,            
       EMPLOYEE_ID FOR COLUMN D1ID DEC ( 5, 0) NOT NULL WITH DEFAULT 0,         
       EMPLOYEE_NAME FOR COLUMN D1NAME CHAR ( 30) NOT NULL WITH DEFAULT '',     
       START_DATE FOR COLUMN D1SDAT DEC ( 8, 0) NOT NULL WITH DEFAULT 0,        
       SALARY FOR COLUMN D1SALY DEC ( 11, 2)  NOT NULL WITH DEFAULT 0,          
       PRIMARY KEY (COMPANY, EMPLOYEE_ID)) RCDFMT MYDATAR;                      
                                                                                
  //Update description for SQL table                                            
  exec sql                                                                      
    LABEL on TABLE QGPL/MYDATA is                                                
       'My Employee Master Data';                                               
                                                                               
  //Update Column Heading for SQL table               
  exec sql                                            
    LABEL ON COLUMN QGPL/MYDATA (                      
       COMPANY IS 'Company Number',                   
       EMPLOYEE_ID IS 'Employee Id',                  
       EMPLOYEE_NAME IS 'Employee Name',              
       START_DATE IS 'Employee Start Date',           
       SALARY IS 'Employee Salary');                  
                                                      
  //Update Column Text for SQL table                  
  exec sql                                            
    LABEL ON COLUMN QGPL/MYDATA (                      
       COMPANY TEXT IS 'Company Number',              
       EMPLOYEE_ID TEXT IS 'Employee Id',             
       EMPLOYEE_NAME TEXT IS 'Employee Name',         
       START_DATE TEXT IS 'Employee Start Date',      
       SALARY TEXT IS 'Employee Salary');             
                                                      
  *inlr = *on;                                        
                                                      
 /end-free             

Sample SQLRPGLE Program to Drop Table on iSeries(AS400)

 /free                         
                               
  //Set SQL options            
  exec sql                     
   SET OPTION                  
       commit=*none,           
       datfmt=*iso;            
                               
  //Create the SQL table       
  exec sql                     
    DROP TABLE QGPL/MYDATA ;    
                               
  *inlr = *on;                 
                               
 /end-free  

CREATE TABLE statement

Create - To make a new database, table, index, or stored query. A CREATE statement in SQL creates an object inside of a relational database management system (RDBMS). The types of objects that can be created depends on which RDBMS is being used, but most support the creation of tables, indexes, users, synonyms and databases. The most common CREATE command is the CREATE TABLE command. The typical usage is:

CREATE [TEMPORARY] TABLE [table name] ( [column definitions] ) [table parameters].

Column Definitions: A comma-separated list consisting of any of the following

• Column definition: [column name] [data type] {NULL | NOT NULL} {column options}
• Primary key definition: PRIMARY KEY ( [comma separated column list] )
• CONSTRAINTS: {CONSTRAINT} [constraint definition]
• RDBMS specific functionality

DROP TABLE statement

Drop - To destroy an existing database, table, index, or view.

A DROP statement in SQL removes an object from a relational database management system (RDBMS). The types of objects that can be dropped depends on which RDBMS is being used, but most support the dropping of tables, users, and databases. The typical usage is simply:

DROP objecttype objectname.

ALTER TABLE statement

Alter - To modify an existing database object.

An ALTER statement in SQL changes the properties of an object inside of a relational database management system (RDBMS). The types of objects that can be altered depends on which RDBMS is being used. The typical usage is:

ALTER objecttype objectname parameters.