QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)

QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)
QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...), QSQLGNDDl, DDS to DDL conversion api, dds to ddl, dd s to sql, dds file to ddl table, dds, ddl, as400, ibmi, iseries, api in ibmi for files
QSQGNDDL API - IBM to convert DDS file into DDL table (Continuing...)

This is the continuation of the article QSQGNDLL API - IBM (Part:1) before moving forward kindly go through the link for better understanding.

In this article, a working example of the QSQGNDDL API will be shown

QSQGNDDL API Program

For executing the QSQGNDDL API we will use the below program and will keep on updating the input parameters to get the required SQL DDL tables. 
RPG Code in Fixed format using QSQGNDDL API to generate DDS source in RPG AS400
      * format of Input Tempalate parameter of SQLR0100 format    
     D SQLR0100        DS                                         
     D databaseobjectname...                                      
     D                              258a                          
     D databaseobjectlibraryname...                               
     D                              258a                          
     D databaseobjecttype...                                      
     D                               10a                          
     D databasesourcefilename...                                  
     D                               10a                          
     D databasesourcefilelibraryname...                           
     D                               10a                          
     D databasesourcefilemembername...                            
     D                               10a                          
     D severitylevel...                                           
     D                               10i 0 INZ(30)                
     D replaceoption...                                           
     D                                1a   INZ('0')               
     D statementformattingoption...                               
     D                                1a   INZ('0')     
     D dateformat...                                    
     D                                3a   INZ('ISO')   
     D dateseparator...                                 
     D                                1a   INZ('/')     
     D timeformat...                                    
     D                                3a   INZ('ISO')   
     D timeseparator...                                 
     D                                1a   INZ(':')     
     D namingoption...                                  
     D                                3a   INZ('SYS')   
     D decimalpoint...                                  
     D                                1a   INZ('.')     
     D standardsoption...                               
     D                                1a   INZ('0')     
     D dropoption...                                    
     D                                1a   INZ('0')     
     D messagelevel...                                  
     D                               10i 0 INZ(0)       
     D commentoption...                                 
     D                                1a   INZ('1')                 
     D labeloption...                                               
     D                                1a   INZ('1')                 
     D headeroption...                                              
     D                                1a   INZ('1')                 
                                                                    
      *error code parameter format                                  
     D errc0100        DS                                           
     D bytesprovided...                                             
     D                               10i 0                          
     D bytesavailable...                                            
     D                               10i 0                          
     D exceptionid...                                               
     D                                7a                            
     D reserved...                                                  
     D                                1a   INZ(x'00')               
     D exceptiondata...                                             
     D                              500a                            
      * qsqgnddl prototype (generate data definition language API)  
     D QSQGNDDL        pr                  extpgm('QSQGNDDL')       
     D inputtemplate                583a                                         
     D leninptemplate                10i 0                                       
     D inptemplfmtnam                 8a                                         
     D ecrorcode                    516a                                         
      *                                                                          
     D inptemplfmtnam  s              8a   INZ('SQLR0100')                       
     D leninptemplate  s             10i 0 INZ(%SIZE(SQLR0100))                  
     C                   EVAL      databaseobjectname ='DDS1'                    
     C                   EVAL      databaseobjectlibraryname = 'EASYCLASS1'  
      * // pass value as INDEX or VIEW for index or view source     
     C                   EVAL      databaseobjecttype  =  'TABLE'                
     C                   EVAL      databasesourcefilename =   'DDSTODDL2'        
     C                   EVAL      databasesourcefilelibraryname =  'EASYCLASS1' 
     C                   EVAL      databasesourcefilemembername =  'DDLG1'       
     C                   CALL      'QSQGNDDL'                                    
     C                   PARM                    SQLR0100                        
     C                   PARM                    leninptemplate                  
     C                   PARM                    inptemplfmtnam                  
     C                   PARM                    errc0100                        
     C                   EVAL      *INLR =  *ON                                                                                                                     
RPG Code in /Free format using QSQGNDDL API to generate DDS source in RPG AS400
                           
      * format of Input Tempalate parameter of SQLR0100 format        
     D SQLR0100        DS                                             
     D databaseobjectname...                                          
     D                              258a                              
     D databaseobjectlibraryname...                                   
     D                              258a                              
     D databaseobjecttype...                                          
     D                               10a                              
     D databasesourcefilename...                                      
     D                               10a                              
     D databasesourcefilelibraryname...                               
     D                               10a                              
     D databasesourcefilemembername...                                
     D                               10a                              
     D severitylevel...                                               
     D                               10i 0 INZ(30)                    
     D replaceoption...                                               
     D                                1a   INZ('0')                   
     D statementformattingoption...                                   
     D                                1a   INZ('0')     
     D dateformat...                                    
     D                                3a   INZ('ISO')   
     D dateseparator...                                 
     D                                1a   INZ('/')     
     D timeformat...                                    
     D                                3a   INZ('ISO')   
     D timeseparator...                                 
     D                                1a   INZ(':')     
     D namingoption...                                  
     D                                3a   INZ('SYS')   
     D decimalpoint...                                  
     D                                1a   INZ('.')     
     D standardsoption...                               
     D                                1a   INZ('0')     
     D dropoption...                                    
     D                                1a   INZ('0')     
     D messagelevel...                                  
     D                               10i 0 INZ(0)       
     D commentoption...                                 
     D                                1a   INZ('1')                  
     D labeloption...                                                
     D                                1a   INZ('1')                  
     D headeroption...                                               
     D                                1a   INZ('1')                  
                                                                     
      *error code parameter format                                   
     D errc0100        DS                                            
     D bytesprovided...                                              
     D                               10i 0                           
     D bytesavailable...                                             
     D                               10i 0                           
     D exceptionid...                                                
     D                                7a                             
     D reserved...                                                   
     D                                1a   INZ(x'00')                
     D exceptiondata...                                              
     D                              500a                             
      * qsqgnddl prototype (generate data definition language API)   
     D QSQGNDDL        pr                  extpgm('QSQGNDDL')        
     D inputtemplate                583a                                                             
     D lengthofinputtemplate...                                                                      
     D                               10i 0                                                           
     D inputtemplateformatname...                                                                    
     D                                8a                                                             
     D ecrorcode                    516a                                                             
      *                                                                                              
     D inputtemplateformatname...                                                                    
     D                 s              8a   INZ('SQLR0100')                                           
     D lengthofinputtemplate...                                                                      
     D                 s             10i 0 INZ(%SIZE(SQLR0100))                                      
      /Free                                                                                          
          databaseobjectname  = 'DDS1';                                                              
          databaseobjectlibraryname = 'EASYCLASS1';                                                  
          databaseobjecttype  =  'TABLE';    // pass value as INDEX or VIEW for index or view source 
          databasesourcefilename =   'DDSTODDL2';                                                    
          databasesourcefilelibraryname =  'EASYCLASS1';                                             
          databasesourcefilemembername =  'DDLG1';                                                   
                                                                                                     
          QSQGNDDL(SQLR0100:                                                                         
                   lengthofinputtemplate:                                     
                   inputtemplateformatname:                                   
                   errc0100);                                                 
                                                                              
          *INLR = *ON;                                                        
      /End-Free                                                                                                                                                            
RPG Code in Fully Free format using QSQGNDDL API to generate DDS source in RPG AS400
**FREE                                       
dcl-ds SQLR0100;                             
 databaseobjectname char(258);               
 databaseobjectlibraryname char(258);        
 databaseobjecttype char(10);                
 databasesourcefilename char(10);            
 databasesourcefilelibraryname char(10);     
 databasesourcefilemembername char(10);      
 severitylevel uns(10) inz(30);              
 replaceoption  char(1) inz('0');            
 statementformattingoption char(1) inz('0'); 
 dateformat char(3) inz('ISO');              
 dateseparator  char(1) inz('/');            
 timeformat char(3) inz('ISO');              
 timeseparator char(1) inz(':');             
 namingoption char(3) inz('SYS');            
 decimalpoint char(1) inz('.');              
 standardsoption char(1) inz('0');           
 dropoption  char(1) inz('0');               
 messagelevel uns(10) inz(0);        
 commentoption char(1) inz('1');     
 labeloption char(1) inz('1');       
 headeroption char(1) inz('1');      
end-ds;                              
                                     
dcl-ds errc0100;                     
  bytesprovided uns(10);             
  bytesavailable uns(10);            
  exceptionid char(7);               
  reserved char(1) inz(x'00');       
  exceptiondata char(500);           
end-ds;                              
                                     
dcl-pr QSQGNDDL extpgm('QSQGNDDL');  
  inputtemplate char(583);           
  lengthofinputtemplate int(10);     
  inputtemplateformatname char(8);   
  errorcode char(516);               
end-pr;                              
                                                          
dcl-s inputtemplateformatname char(8) inz('SQLR0100');    
dcl-s lengthofinputtemplate int(10) inz(%SIZE(SQLR0100));
 
databaseobjectname  = 'DDS1';                   
databaseobjectlibraryname = 'EASYCLASS1';       
databaseobjecttype  =  'TABLE'; // pass value as INDEX or VIEW for index or view source                
databasesourcefilename =   'DDSTODDL2';         
databasesourcefilelibraryname =  'EASYCLASS1';  
databasesourcefilemembername =  'DDLG1';        
                                                          
QSQGNDDL(SQLR0100:                              
         lengthofinputtemplate:                 
         inputtemplateformatname:               
         errc0100);                             
                                                          
*INLR = *ON;                                                                                       

Generating the DDL table from QSQGNDDL API

Example: 1

Sample DDS file  which will be converted to SQL DDL table through QSQGNDDL API

DDS File1

A                                      UNIQUE     
A          R RDDS1                                
A            FLD1          10A                    
A            FLD2          20A         VARLEN     
A            FLD3          15G         CCSID(1200)
A            FLD4          30A         ALWNULL    
A            FLD5           2P 0                  
A            FLD6           2S 0                  
A          K FLD1                                 
SQL DDL Table after conversion of DDL File1

SQL DDL Table1

--  Generate SQL                                                     
--  Version:                   V7R4M0 190621                         
--  Generated on:              12/10/21 20:50:40                     
--  Relational Database:                                             
--  Standards Option:          Db2 for i                             
                                                                     
CREATE TABLE EASYCLASS1/DDS1 (                                       
--  SQL150B   10   REUSEDLT(*NO) in table DDS1 in EASYCLASS1 ignored.
  FLD1 CHAR(10) CCSID 273 NOT NULL DEFAULT '' ,                      
  FLD2 VARCHAR(20) CCSID 273 NOT NULL DEFAULT '' ,                   
  FLD3 GRAPHIC(15) CCSID 1200 NOT NULL DEFAULT '' ,                  
  FLD4 CHAR(30) CCSID 273 DEFAULT NULL ,                             
  FLD5 DECIMAL(2, 0) NOT NULL DEFAULT 0 ,                            
  FLD6 NUMERIC(2, 0) NOT NULL DEFAULT 0 ,                            
  PRIMARY KEY( FLD1 ) )                                              
                                
  RCDFMT RDDS1      ;         
                              
LABEL ON TABLE EASYCLASS1/DDS1
  IS 'DDS PF' ;               
SQL150B is just a warning as the REUSEDLT option for the newly generated table is set as *NO.

Example: 2

Sample DDS file with column text and column heading which will be converted to SQL DDL table through QSQGNDDL API

DDS File2

A                                      UNIQUE                     
A          R RDDS6                                                
A            FLD1          10A         TEXT('FIRST TEXT FLD')     
A                                      COLHDG('FIRST' 'TEXT' 'FLD'
A            FLD2           2P 0       TEXT('FIELD2')             
A                                      COLHDG('FIELD2')           
A          K FLD1                                                 
SQL DDL Table after conversion of DDL File2

SQL DDL Table2

--  Generate SQL                                                     
--  Version:                   V7R4M0 190621                         
--  Generated on:              12/10/21 21:00:21                     
--  Relational Database:                                             
--  Standards Option:          Db2 for i                             
                                                                     
CREATE TABLE EASYCLASS1/DDS6 (                                       
--  SQL150B   10   REUSEDLT(*NO) in table DDS6 in EASYCLASS1 ignored.
  FLD1 CHAR(10) CCSID 273 NOT NULL DEFAULT '' ,                      
  FLD2 DECIMAL(2, 0) NOT NULL DEFAULT 0 ,                            
  PRIMARY KEY( FLD1 ) )                                              
                                                                     
  RCDFMT RDDS6      ;                                                
                                                             
LABEL ON TABLE EASYCLASS1/DDS6                             
  IS 'DDS PF' ;                                            
                                                           
LABEL ON COLUMN EASYCLASS1/DDS6                            
( FLD1 IS 'FIRST               TEXT                FLD' ,  
  FLD2 IS 'FIELD2' ) ;                                     
                                                           
LABEL ON COLUMN EASYCLASS1/DDS6                            
( FLD1 TEXT IS 'FIRST TEXT FLD' ,                          
  FLD2 TEXT IS 'FIELD2' ) ;                                
SQL150B is just a warning as the REUSEDLT option for the newly generated table is set as *NO.

Generating Index through QSQGNDDL API

Example : 3

Sample DDS logical file  which will be converted to SQL DDL Index through QSQGNDDL API

DDS Logical File3

A          R RDDS8                     PFILE(DDS8) 
A            FLD1                                  
A            FLD2                                  
A          K FLD1                                  
SQL DDL Index after conversion of DDL Logical File3

SQL DDL Index

--  Generate SQL                                 
--  Version:                   V7R4M0 190621     
--  Generated on:              12/10/21 21:11:08 
--  Relational Database:                         
--  Standards Option:          Db2 for i         
                                                 
CREATE INDEX EASYCLASS1/DDS8LF1                  
  ON EASYCLASS1/DDS8 ( FLD1 ASC ) 
                                  
  RCDFMT RDDS8 ;                  
                                  
LABEL ON INDEX EASYCLASS1/DDS8LF1 
  IS 'DDS PF' ;                   

Generating View through QSQGNDDL API

Example: 4

Sample DDS logical file  which will be converted to SQL DDL View through QSQGNDDL API

DDS Logical File4

A                                      UNIQUE      
A          R RDDS8                     PFILE(DDS8) 
A            FLD1                                  
A            FLD2                                  
A          K FLD1                                  
SQL DDL View after conversion of DDL Logical File4

SQL DDL View

--  Generate SQL                                 
--  Version:                   V7R4M0 190621     
--  Generated on:              12/10/21 21:20:36 
--  Relational Database:                         
--  Standards Option:          Db2 for i         
                                                 
CREATE VIEW EASYCLASS1/DDS8LF2 (                 
  FLD1 ,               
  FLD2 )               
  AS                   
  SELECT               
  FLD1 ,               
  FLD2                 
  FROM EASYCLASS1/DDS8 
                                                           
  RCDFMT RDDS8      ;                                    
                                                         
LABEL ON TABLE EASYCLASS1/DDS8LF2                        
  IS 'DDS PF' ;                                          
                                                         
LABEL ON COLUMN EASYCLASS1/DDS8LF2                       
( FLD1 IS 'FIRST               TEXT                FLD' ,
  FLD2 IS 'FIELD2' ) ;                                   
                                                         
LABEL ON COLUMN EASYCLASS1/DDS8LF2                       
( FLD1 TEXT IS 'FIRST TEXT FLD' ,                        
  FLD2 TEXT IS 'FIELD2' ) ;                              

Related Post

Post a Comment

© AS400 and SQL Tricks. All rights reserved. Developed by Jago Desain