 |
| 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
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