IBMi (AS400) fans only - QSQGNDDL API: How to get SQL SOURCE specification from a database object
#IBMiSample
QSQGNDDL API generates the SQL data definition language statements from a database object.
QSQGNDDL API generates the SQL data definition language statements from a database object.
Here's how to use it.
1. Create a clp like this:
ASDD01CL.clp - From DDS to SQL
/*--------------------------------------------------------------*/
/* FROM DDS TO SQL */
/*--------------------------------------------------------------*/
PGM PARM( +
&DDS_LIBR +
&DDS_FILE +
&DDS_TYPE +
&SQL_LIBR +
&SQL_SRCF +
&SQL_MEMB +
)
DCL VAR(&DDS_LIBR) TYPE(*CHAR) LEN(10)
DCL VAR(&DDS_FILE) TYPE(*CHAR) LEN(10)
DCL VAR(&DDS_TYPE) TYPE(*CHAR) LEN(10)
DCL VAR(&SQL_LIBR) TYPE(*CHAR) LEN(10)
DCL VAR(&SQL_SRCF) TYPE(*CHAR) LEN(10)
DCL VAR(&SQL_MEMB) TYPE(*CHAR) LEN(10)
ADDPFM FILE(&SQL_LIBR/&SQL_SRCF) MBR(&SQL_MEMB) +
TEXT(&DDS_LIBR *TCAT '/' *TCAT &DDS_FILE +
*TCAT '/' *TCAT &DDS_TYPE) SRCTYPE(SQL)
MONMSG MSGID(CPF0000) EXEC(CALLSUBR SUBR(DONTDOIT))
CALLSUBR SUBR(DOIT)
/*--------------------------------------*/
/* DON'T DO IT */
/*--------------------------------------*/
SUBR SUBR(DONTDOIT)
SNDPGMMSG MSG('MEMBER ALREADY EXIST. PROCEDURA NON +
ESEGUITA')
RETURN
ENDSUBR
/*--------------------------------------*/
/* DO IT */
/*--------------------------------------*/
SUBR SUBR(DOIT)
CALL PGM(ASDD01A) +
PARM( +
&DDS_LIBR +
&DDS_FILE +
&DDS_TYPE +
&SQL_LIBR +
&SQL_SRCF +
&SQL_MEMB +
)
ENDSUBR
ENDPGM
Now we need a command like this:
FROM DDS TO SQL (ASDDSTOSQL)
Type choices, press Enter.
DDS LIBRARY NAME . . . . . . . . DDS_LIBR VDOTEST1 DDS FILE NAME . . . . . . . . . DDS_FILE CLANA00F DDS TYPE (PF INDEX VIEW) . . . . DDS_TYPE PF SQL SOURCE MEMBER LIBRARY NAME SQL_LIBR vdotest1 SQL SOURCE MEMBER FILE NAME . . SQL_SRCF QSQLSRC SQL SOURCE MEMBER NAME . . . . . SQL_MEMB CLANA00Fsq
Bottom
F3=Exit F4=Prompt F5=Refresh F12=Cancel F13=How to use this display
F24=More keys
to obtain the command object
2. Create a cmd like this:
ASDDSTOSQL.cmd - From DDS to SQL
ASDDSTOSQL: CMD PROMPT('FROM DDS TO SQL')
/* COMMAND PROCESSING PROGRAM IS: ASDD01CL */
PARM KWD(DDS_LIBR) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('DDS LIBRARY NAME')
PARM KWD(DDS_FILE) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('DDS FILE NAME')
PARM KWD(DDS_TYPE) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('DDS TYPE (PF INDEX VIEW)')
PARM KWD(SQL_LIBR) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('SQL SOURCE MEMBER LIBRARY NAME')
PARM KWD(SQL_SRCF) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('SQL SOURCE MEMBER FILE NAME')
PARM KWD(SQL_MEMB) TYPE(*CHAR) LEN(10) MIN(1) +
PROMPT('SQL SOURCE MEMBER NAME')
Compile ASDDSTOSQL.cmd with this parameters:
CMD > ASDDSTOSQL
> yourSourceLibrary
PGM > ASDD01CL
> *LIBL
SRCFILE > yourSourceFile
> yourSourceLibrary
SRCMBR > ASDDSTOSQL
THDSAFE *NO
3. Create a rpgle like this:
ASDD01A.rpgle - From DDS to SQL
**free
ctl-opt actgrp(*new) dftactgrp(*no)
main(Main) ;
dcl-pr Main extpgm('ASDD01A') ;
*n char(10) ;
*n char(10) ;
*n char(10) ;
*n char(10) ;
*n char(10) ;
*n char(10) ;
end-pr ;
dcl-proc Main ;
dcl-pi *n ;
DDS_Libr char(10) ;
DDS_File char(10) ;
DDS_Type char(10) ;
SQL_Libr char(10) ;
SQL_SrcF char(10) ;
SQL_Memb char(10) ;
end-pi ;
dcl-pr CreateSqlSource extpgm('QSQGNDDL') ;
*n char(583) const ;
*n int(10) const ;
*n char(8) const ;
*n char(256) ;
end-pr ;
dcl-ds InputTemplate qualified ;
InFile char(258) ;
InLibrary char(258) ;
InFileType char(10) inz('TABLE') ;
OutSourceFile char(10) ;
OutLibrary char(10) ;
OutMember char(10) ;
SeverityLevel uns(10) inz(30) ;
*n char(16) inz('10ISO ISO SYS.00') ;
MessageLevel uns(10) inz(0) ;
*n char(3) inz('111') ;
end-ds ;
dcl-ds ErrorDs qualified ;
Size uns(10) inz(%size(ErrorDs)) ;
ReturnedSize uns(10) ;
Id char(7) ;
*n char(1) inz(x'00') ;
Data char(240) ;
end-ds ;
InputTemplate.InFile = DDS_File ;
InputTemplate.InLibrary = DDS_Libr ;
InputTemplate.OutSourceFile = SQL_SrcF ;
InputTemplate.OutLibrary = SQL_Libr ;
InputTemplate.OutMember = SQL_Memb ;
if ((DDS_Type = 'INDEX') or (DDS_Type = 'VIEW')) ;
InputTemplate.InFileType = DDS_Type ;
endif ;
CreateSqlSource(InputTemplate:%len(InputTemplate):
'SQLR0100':ErrorDs) ;
return ;
end-proc ;
4. Now try to create CLANA00F SQL specification:
4. Now try to create CLANA00F SQL specification:
This is the COMMAND to create CLANA00F SQL specification
This is the COMMAND to create CLANA01L (logica file) SQL specification
Now we have the SQL script to create the TABLE CLANA00F and che LF CLANA01L.
Use the command RUNSQLSTM if you really wont to create the TABLE:
RUNSQLSTM SRCFILE(yourSourceLibrary/yourSrcFile) SRCMBR(yourSourceMember)
I appreciate all the comments made on this blog.
Comments
Post a Comment