 |
| 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
| 1 | Input template | Input | Char(*) |
| 2 | Length of input template | Input | Binary(4) |
| 3 | Input template format name | Input | Char(8) |
| 4 | Error code | I/O | Char(*) |
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') - Database Object Name: The name of the database file for which the DDL table will be generated.
- Database Object Library Name: The name of the library containing the object for which the DDL table will be generated.
- Database Object Type: The type of DDL table object that needs to be generated. Like: Table, View, Index
- 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.
- 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.
- 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.
- Severity Level: The severity level at which the API processing fails.
- Replace Option: It will clear the source file member before the execution of the API.
- Statement formatting option: To format the generated SQL in the proper format.
- Date format: To set the format of the date in the generated SQL table.
- Date separator: The Date separator that will be used while generating the SQL table.
- Time format: To set the format of the time in the generated SQL table.
- Time separator: Colon separator, Period separator, Comma separator, Blank separator
- Name option: SQL or SYS will be used
- Decimal point: Period separator or Comma separator.
- Standards option: To include DB2 for i extensions or other DB2 family SQL.
- Drop option: Drop or Alter is used before generating the SQL table.
- Message level: Severity level on which messages are generated.
- Comment option: Comment should be generated or not on generated SQL table.
- Label option: To generate the label in the generate SQL table.
- 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
Read also :
- CRTPF and Create Table in IBM i
- Primary Key in DDS and DDL tables
- Data Validation in DDS and DDL tables
- Column Heading in DDS Files and DDL tables
- Column Text in DDS and DDL tables
- Attributes of DDL table
- Auto-update Timestamp Column using ROW CHANGE TIMESTAMP in DDL table
- ADD Constraint in DDL tables
- Create Index and LF keyed in IBM i
- Create View and LF Non-Keyed in IBM i
- DDS to DDL Modernization : Level Check Error
- Identity column in DDL table