QSQGNDDL API - IBM to convert DDS file into DDL table

QSQGNDDL API - IBM to convert DDS file into DDL table
QSQGNDDL API - IBM to convert DDS file into DDL table, DDS to DDL, ddl in ibmi as400, dds to sql db2, sql
QSQGNDDL API - IBM to convert DDS file into DDL table

In the previous articles, on DDS to DDL conversion, it is explained how we can convert a DDS file into a DDL table step by step manually. 

But, the same can be achieved without manual work using an API QSQGNDDL.

QSQGNDDL API - IBM

QSQGNDDL API stands for Generate Data Definition Language.

QSQGNDDL API is used to convert the DDS files into the SQL DDL table or we can that a new DDL table object is created with the same DDS file structure in SQL.

QSQGNDDL API is a program in the IBMi server, so we need to call this API from the program with some parameters.


QSQGNDDL API Parameters

Required Parameters

Input Template

Input template is a data structure that contains the input parameters which are required by the API to generate the DDL table structure.

SQLR0100 is DS which will be having the structure containing the input data.

SQLR0100

 
 * 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')   

  1. Database Object Name: The name of the database file for which the DDL table will be generated.
  2. Database Object Library Name: The name of the library containing the object for which the DDL table will be generated. 
  3. Database Object Type: The type of DDL table object that needs to be generated. Like: Table, View, Index  
  4. Database Source File Name: The name of the source file that will contain the generated SQL DDL table code generated by API. The name should be a valid name and present on the system. It is case-sensitive.
  5. Database Source File Library Name:  The name of the library having the source file which will contain the SQL DDL table code generated by API.
  6. Database Source File Member Name: The name of the source file member that will contain the generated SQL DDL table code generated by API. The name should be a valid name and present on the system. It is case-sensitive.
  7. Severity Level: The severity level at which the API processing fails.
  8. Replace Option: It will clear the source file member before the execution of the API. 
  9. Statement formatting option: To format the generated SQL in the proper format.
  10. Date format: To set the format of the date in the generated SQL table.
  11. Date separator: The Date separator that will be used while generating the SQL table.
  12. Time format: To set the format of the time in the generated SQL table.
  13. Time separator: Colon separator, Period separator, Comma separator, Blank separator
  14. Name option: SQL or SYS will be used 
  15. Decimal point: Period separator or Comma separator.
  16. Standards option: To include DB2 for i extensions or other DB2 family SQL.
  17. Drop option: Drop or Alter is used before generating the SQL table.
  18. Message level: Severity level on which messages are generated.
  19. Comment option: Comment should be generated or not on generated SQL table.
  20. Label option: To generate the label in the generate SQL table.
  21. Header option: To generate the header in the generate SQL table.

Length Of Input Templet

It is the size of the SQLR0100 data structure in which input data is passed.

Input Template Format Name

It is the format name that needs to be used. Here we are using SQLR0100 so the same name will be passed.

Error Code

It is an output parameter where the error that occurred during the execution of the API is logged. 
It's in the form of data structure as shown below:
 *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              

Prototype of QSQGNDDL API

 * 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                          

Working Example of QSQGNDDL API

To understand QSQGNDDL API more, Kindly click on QSQGNDDL API working Example.

Related Post

Post a Comment

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