Build SQL to Define Your Existing Files
June 5, 2002 Timothy Prickett Morgan
Note: The code accompanying this article is available for download here.
Hey, Ted:
We have always defined our files with DDS, but now we’re ready to use SQL for data definition.
 |
Do you have a good way to convert our DDS to SQL Data Definition Language (DDL) commands?
— Paul
IBM included just the tool you need in V5R1.
It’s the Generate Data Definition Language (QSQGNDDL) API.
You can read about it on the Web at http://publib.boulder.ibm.com/html/as400/v5r1/ic2924/index.htm?info/apis/qsqgnddl.htm .
I’ll give you an example of what it can do. Then I’ll give you some code you can use to generate DDL.
Here are DDS members for a physical file called CUSTOMER and a logical file called CUSTOMER2:
* Physical file CUSTOMER
A UNIQUE
A R CUSTREC
A COMPANY 3P 0 DFT(1)
A TEXT('Company')
A COLHDG(' ' ' '
'Comp')
A CUSTNBR 5P 0
A TEXT('Customer
account number')
A COLHDG('Cust'
'acct' 'nbr')
A CUSTNAME 20
A TEXT('Customer name')
A COLHDG(' ' 'Cust'
'name')
A CREDITLIM 7S 2
A TEXT('Credit limit')
A COLHDG(' ' 'Credit'
'limit')
A K COMPANY
A K CUSTNBR
* Logical file CUSTOMER2 A R CUSTREC PFILE(CUSTOMER) A COMPANY A CUSTNBR A CUSTNAME A K CUSTNAME A S COMPANY CMP(EQ 2)
Here is the SQL source member that QSQGNDDL created from the object definitions:
-- Generate SQL
-- Version: V5R1M0 010525
-- Generated on: 05/31/02 14:19:55
-- Relational Database:
-- Standards Option: DB2 UDB AS/400
CREATE TABLE ABCLIB/CUSTOMER (
-- SQL150B 10 REUSEDLT(*NO) in table CUSTOMER
in ABCLIB ignored.
-- SQL1509 10 Format name CUSTREC for CUSTOMER
in ABCLIB ignored.
COMPANY DECIMAL(3, 0) NOT NULL DEFAULT +1 ,
CUSTNBR DECIMAL(5, 0) NOT NULL DEFAULT 0 ,
CUSTNAME CHAR(20) CCSID 37 NOT NULL DEFAULT '' ,
CREDITLIM NUMERIC(7, 2) NOT NULL DEFAULT 0 ,
PRIMARY KEY( COMPANY , CUSTNBR ) ) ;
-- SQL150A 30
-- System trigger QSYS_TRIG_ABCLIB_____
CUSTOMER___000002 in ABCLIB ignored.
-- SQL150A 30
-- System trigger QSYS_TRIG_ABCLIB_____
CUSTOMER___000001 in ABCLIB ignored.
LABEL ON COLUMN ABCLIB/CUSTOMER
( COMPANY IS ' Comp' ,
CUSTNBR IS 'Cust acct nbr' ,
CUSTNAME IS ' Cust name' ,
CREDITLIM IS ' Credit limit' ) ;
LABEL ON COLUMN ABCLIB/CUSTOMER
( COMPANY TEXT IS 'Company' ,
CUSTNBR TEXT IS 'Customer account number' ,
CUSTNAME TEXT IS 'Customer name' ,
CREDITLIM TEXT IS 'Credit limit' ) ;
-- Generate SQL
-- Version: V5R1M0 010525
-- Generated on: 05/31/02 14:21:14
-- Relational Database:
-- Standards Option: DB2 UDB AS/400
CREATE VIEW ABCLIB/CUSTOMER2 (
-- SQL1509 10 Format name CUSTREC for
CUSTOMER2 in ABCLIB ignored.
-- SQL1506 30 Key or attribute for
CUSTOMER2 in ABCLIB ignored.
COMPANY ,
CUSTNBR ,
CUSTNAME )
AS
SELECT
COMPANY ,
CUSTNBR ,
CUSTNAME
FROM ABCLIB/CUSTOMER
WHERE
COMPANY = +2 ;
I wrote a quick utility to run the API. I call it GENDDL. It consists of a command object, an ILE CL program called GENDDL01C, and an ILE RPG program called GENDDL01R. It’s not meant to be a general-purpose utility–I hard-wired a lot of the options in the template parameter, the first parameter that is passed to the API. I have used it to generate SQL code for tables, views, indexes, and functions and it has worked for me, but I don’t make any guarantees. You will need to change it to suit your purposes.
These are the commands I used to generate the SQL for the CUSTOMER and CUSTOMER2 files in the examples above.
GENDDL OBJECT(CUSTOMER) + OBJECTLIB(ABCLIB) OBJECTTYPE(TABLE) + SRCFILE(SQLGEN) SRCLIB(QTEMP) SRCMBR(CUS) + REPLACE(Y) GENDDL OBJECT(CUSTOMER2) + OBJECTLIB(ABCLIB) OBJECTTYPE(VIEW) + SRCFILE(SQLGEN) SRCLIB(QTEMP) SRCMBR(CUS) + REPLACE(N)
I hope this helps.
— Ted
/******************************************************/
/* Generate SQL DDL for a database object. */
/* No warranty implied. Use at your own risk. */
/* */
/* To compile: */
/* CRTCMD CMD(XXX/GENDDL) PGM(*LIBL/GENDDL01C) + */
/* SRCFILE(XXX/QCMDSRC) SRCMBR(GENDDL) */
/*****************************************************/
CMD PROMPT('Generate SQL DDL')
PARM KWD(OBJECT) TYPE(*CHAR) LEN(258) MIN(1) +
EXPR(*YES) PROMPT('Object name')
PARM KWD(OBJECTLIB) TYPE(*CHAR) LEN(258) MIN(1) +
EXPR(*YES) PROMPT('Object library')
PARM KWD(OBJECTTYPE) TYPE(*CHAR) LEN(10) +
RSTD(*YES) VALUES(TABLE VIEW ALIAS +
CONSTRAINT FUNCTION INDEX SCHEMA TRIGGER +
TYPE) MIN(1) EXPR(*YES) PROMPT('Object type')
PARM KWD(SRCFILE) TYPE(*NAME) LEN(10) MIN(1) +
EXPR(*YES) PROMPT('Source physical file')
PARM KWD(SRCLIB) TYPE(*NAME) LEN(10) +
SPCVAL((*CURLIB) (*LIBL)) MIN(1) +
EXPR(*YES) PROMPT('Source library')
PARM KWD(SRCMBR) TYPE(*NAME) LEN(10) +
SPCVAL((*FIRST) (*LAST)) MIN(1) +
EXPR(*YES) PROMPT('Source member')
PARM KWD(CRTSRC) TYPE(*CHAR) LEN(4) RSTD(*YES) +
DFT(*NO) VALUES(*YES *NO) SPCVAL((*YES +
'1') (*NO '0')) EXPR(*YES) CHOICE('*YES, +
*NO') PROMPT('Create file and/or member?')
PARM KWD(REPLACE) TYPE(*CHAR) LEN(8) RSTD(*YES) +
DFT(*APPEND) VALUES(*REPLACE *APPEND) +
SPCVAL((*REPLACE '1') (*APPEND '0')) +
EXPR(*YES) CHOICE('*REPLACE, *APPEND') +
PROMPT('Replace or append to source?')
/******************************************************/
/* Generate SQL DDL for a database object. */
/* No warranty implied. Use at your own risk. */
/* */
/* To compile: */
/* CRTBNDCL PGM(XXX/GENDDL01C) SRCFILE(XXX/QCLSRC) + */
/*SRCMBR(GENDDL01C) DFTACTGRP(*NO) ACTGRP(*NEW) */
/*****************************************************/
pgm (&obj &objlib &objtype +
&srcfile &srclib &srcmbr &crtsrc &replace)
dcl &obj *char 258
dcl &objlib *char 258
dcl &objtype *char 10
dcl &srcfile *char 10
dcl &srclib *char 10
dcl &srcmbr *char 10
dcl &replace *lgl 1
dcl &crtsrc *lgl 1
dcl &error *char 7
monmsg cpf0000 exec(goto error)
chkobj obj(&srclib/&srcfile) objtype(*file) +
aut(*objexist)
monmsg cpf9801 exec(do)
if &crtsrc +
then( crtsrcpf (&srclib/&srcfile))
enddo
chkobj obj(&srclib/&srcfile) objtype(*file) +
mbr(&srcmbr) aut(*objexist)
monmsg cpf9815 exec(do)
if &crtsrc +
then( addpfm &srclib/&srcfile &srcmbr)
enddo
call genddl01r (&obj &objlib &objtype +
&srcfile &srclib &srcmbr &replace &error)
if (&error *eq ' ') do
sndpgmmsg msgid(cpf9898) msgf(qcpfmsg) +
msgdta('Generation of DDL was successful') +
msgtype(*comp)
enddo
else do
sndpgmmsg msgid(cpf9898) msgf(qcpfmsg) +
msgdta('Generation of DDL failed. See +
source member for errors') msgtype(*escape)
enddo
return
error:
sndpgmmsg msgid(cpf9898) msgf(qcpfmsg) +
msgdta('Generation of DDL failed with +
an unexpected error') msgtype(*escape)
monmsg cpf0000
endpgm
* Member GENDDL01R, type RPGLE
*
* Generate SQL DDL for a database object.
* No warranty implied. Use at your own risk.
*
* To compile:
* CRTBNDRPG PGM(XXX/GENDDL01R) +
* SRCFILE(XXX/QRPGLESRC) SRCMBR(GENDDL01R)
H dftactgrp(*no) actgrp(*caller)
D Template ds 583
D DBObjName 258
D DBObjLib 258
D DBObjType 10
D DBSrcFile 10
D DBSrcLib 10
D DBSrcMbr 10
D Severity 10i 0 inz(30)
D Replace 1
D StmtFmtOpt 1 inz('0')
D DateFmt 3 inz('ISO')
D DateSep 1
D TimeFmt 3 inz('ISO')
D TimeSep 1
D NamingOpt 3 inz('SYS')
D DecimalPt 1 inz('.')
D StdsOpt 1 inz('0')
D DropOpt 1 inz('0')
D MsgLvl 10i 0 inz(0)
D CommentOpt 1 inz('1')
D LabelOpt 1 inz('1')
D HdrOpt 1 inz('1')
D TemplateLength s 10i 0 inz(%size(Template))
D TemplateFormat s 8 inz('SQLR0100')
D
D ErrorDS ds 16
D BytesProv 10i 0 inz(15)
D BytesAvail 10i 0
D ExceptionID 7
D
D GenDDL pr extpgm('QSQGNDDL')
D Template 583
D Length 10i 0
D Format 8
D ErrorDS 12
D
D*entry plist
D GenDDL01R pr extpgm('GENDDL01R')
D PIObjName 258
D PIObjLib 258
D PIObjType 10
D PISrcFile 10
D PISrcLib 10
D PISrcMbr 10
D PIReplace 1
D PIError 7
D
D GenDDL01R pi
D PIObjName 258
D PIObjLib 258
D PIObjType 10
D PISrcFile 10
D PISrcLib 10
D PISrcMbr 10
D PIReplace 1
D PIError 7
/free
DBObjName = PIObjName;
DBObjLib = PIObjLib;
DBObjType = PIObjType;
DBSrcFile = PISrcFile;
DBSrcLib = PISrcLib;
DBSrcMbr = PISrcMbr;
if (PIReplace = '1') or
(PIReplace = 'Y') or
(PIReplace = 'y');
Replace = '1';
else;
Replace = '0';
endif;
GenDDL (Template: TemplateLength:
TemplateFormat: ErrorDS);
PiError = ExceptionID;
*inlr = *on;
/end-free
Sponsored By looksoftware |
|
Application Modernization made Easy!
newlook allows you to rapidly re-face, web-enable, and extend your applications. And, newlook will integrate your iSeries applications with desktop applications like Notes, Excel, Add file transfer, DDM, display and printer emulation using secure browser-based deployment and move to Download your FREE evaluation at www.looksoftware.com/down_main.htm
|
