//BCHJOB JOB(JRTVDDL) JOBD(NERONI2/NERONI2) OUTQ(QPRINT) + ENDSEV(60) LOG(4 00 *SECLVL) MSGQ(*USRPRF) CCSID(280) /* Open source from www.neroni.it */ /* LA JOB DESCRIPTION "NERONI2/NERONI2" DEVE PREESISTERE. PUO' ESSERE */ /* IDENTICA A QBATCH E PUO' ESSERE SOSTITUITA DA QBATCH O SIMILE. */ /* From System: "S65D69DA" */ /* From Library: "NERONI2" */ /* Unload Time: 2016-07-11 17:23 */ /* To File : "JRTVDDL" */ /* To Library : "NERONI2" */ /* To Text : "Retrieve DDL. Src" */ /********* INIZIO ISTRUZIONI **************************************************/ /* LE SUCCESSIVE ISTRUZIONI PERMETTONO DI RICARICARE I SORGENTI. */ /* 1) DA UN VIDEO COMANDI DELL'AS400 RICEVENTE */ /* CREARE UN FILE SORGENTE DI LUNGHEZZA RECORD 112: */ /* CRTSRCPF FILE(NERONI2/STRINGHE) RCDLEN(112) */ /* 2) SPOSTARE IL FILE "JRTVDDL.txt" NELL'INDIRIZZARIO */ /* DI UN PC CONNESSO IN RETE CON L'AS400 RICEVENTE */ /* (AD ES.: "c:\"). */ /* 3) DAL VIDEO COMANDI DEL PC CHIAMARE FTP: */ /* ftp nomeas400 */ /* 4) DIGITARE UTENTE E PASSWORD. */ /* 5) ESEGUIRE IL COMANDO DI COPIA DA FILE PC A MEMBRO AS400: */ /* put "c:\JRTVDDL.txt" "/qsys.lib/NERONI2.lib/stringhe.file/JRTVDDL.mbr" */ /* 6) ABBANDONARE FTP: */ /* quit */ /* 7) DA UN VIDEO COMANDI DELL'AS400 RICEVENTE */ /* ESEGUIRE LA STRINGA COPIATA NEL MEMBRO SORGENTE: */ /* SBMDBJOB FILE(NERONI2/STRINGHE) MBR(JRTVDDL) JOBQ(QBATCH) */ /* LE SUCCESSIVE ISTRUZIONI PERMETTONO DI CREARE L'UTILITY. */ /* 8) DA UN VIDEO COMANDI DELL'AS400 RICEVENTE */ /* ESEGUIRE LA STRINGA O LE STRINGHE SORGENTE DI TIPO SEU "CL" */ /* (IL CUI NOME TERMINA SEMPRE CON ".") */ /* PRESENTI NEL FILE RICARICATO "NERONI2/JRTVDDL" */ /* FACENDO ATTENZIONE ALL'ORDINE DI ESECUZIONE INDICATO NEL */ /* MEMBRO FACOLTATIVO "A.LEGGIMI", AD ESEMPIO: */ /* SBMDBJOB FILE(NERONI2/JRTVDDL) MBR(JRTVDDL.) JOBQ(QBATCH) */ /********* FINE ISTRUZIONI ****************************************************/ /* Crea la libreria. */ MKDIR DIR('/qsys.lib/NERONI2.lib') CHGLIB LIB(NERONI2) TEXT('Claudio Neroni Utility') /* Imposta la lista librerie. */ CHGLIBL LIBL(QTEMP NERONI2 QGPL) /* Crea il file sorgente. */ DLTF FILE(NERONI2/JRTVDDL) CRTSRCPF FILE(NERONI2/JRTVDDL) RCDLEN(112) + TEXT('Retrieve DDL. Src') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDL) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDL) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDL) + SRCTYPE(CMD) + TEXT('Retrieve DDL. Cmd') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDL.) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDL.) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDL.) + SRCTYPE(CL) + TEXT('Retrieve DDL. CrtJs') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDLDS) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDLDS) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDLDS) + SRCTYPE(PF) + TEXT('*UNUSED* Format SQLR0100 DS parms for API QSQGNDDL') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDLP) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDLP) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDLP) + SRCTYPE(PNLGRP) + TEXT('Retrieve DDL. Help') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDLUF) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDLUF) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDLUF) + SRCTYPE(CMD) + TEXT('Retrieve DDL. Cmd UfVer') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDL1) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDL1) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDL1) + SRCTYPE(CLLE) + TEXT('Retrieve DDL. Cpp') /* Copia il sorgente dalla stringa al membro del file sorgente. */ /* Modifica testo e tipo seu del sorgente caricato. */ CPYF FROMFILE(JRTVDDL2) TOFILE(NERONI2/JRTVDDL) + TOMBR(JRTVDDL2) MBROPT(*REPLACE) SRCOPT(*SEQNBR) CHGPFM FILE(NERONI2/JRTVDDL) MBR(JRTVDDL2) + SRCTYPE(RPGLE) + TEXT('Retrieve DDL. ApiInterface v7r1') /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDL) FILETYPE(*SRC) ENDCHAR('//ENDSRC') /* Claudio Neroni 02-02-2015 Modificato. */ /* Aggiunti i campi usando la documentazione V7R2M0 al link */ /* http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/apis/qsqgnddl.htm?lang=en */ CMD PROMPT('Retrieve DDL') PARM KWD(OBJNAME) TYPE(*CHAR) LEN(258) MIN(1) + INLPMTLEN(25) PROMPT('Database Object Name') PARM KWD(OBJLIB) TYPE(*CHAR) LEN(258) + SPCVAL((*CURLIB) (*LIBL)) MIN(1) + INLPMTLEN(25) PROMPT('Database Object + Library') PARM KWD(OBJTYPE) TYPE(*CHAR) LEN(10) RSTD(*YES) + DFT(TABLE) VALUES(ALIAS CONSTRAINT + FUNCTION INDEX PERMISSION PROCEDURE + SCHEMA SEQUENCE TABLE TRIGGER TYPE + VARIABLE VIEW XSR) PROMPT('Database + Object Type') PARM KWD(SRCFILE) TYPE(*NAME) LEN(10) + DFT(QDDLSRC) SPCVAL((QDDLSRC) (QSQLSRC)) + PROMPT('Database Source File') PARM KWD(SRCLIB) TYPE(*NAME) LEN(10) DFT(*LIBL) + SPCVAL((*CURLIB) (*LIBL)) + PROMPT('Database Source File Library') PARM KWD(SRCMBR) TYPE(*NAME) LEN(10) + DFT(*OBJNAME) SPCVAL((*OBJNAME) (*FIRST) + (*LAST)) PROMPT('Database Source File + Member') PARM KWD(REPLACE) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*REPLACE) SPCVAL((*REPLACE 1) (*ADD + 0)) PROMPT('Replace (Member) Option') PARM KWD(STANDARDS) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(AS400) SPCVAL((AS400 0) (DB2 1) (ISO + 2)) PROMPT('Standards Option') PARM KWD(NAMING) TYPE(*CHAR) LEN(3) RSTD(*YES) + DFT(SYS) VALUES(SYS SQL) PROMPT('Naming + Option') PARM KWD(DATFMT) TYPE(*CHAR) LEN(3) RSTD(*YES) + DFT(*JOB) VALUES(ISO EUR JIS USA MDY DMY + YMD JUL) SPCVAL((*JOB JOB)) PROMPT('Date + Format') PARM KWD(DATSEP) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*JOB) SPCVAL((*JOB J) (*SLASH '/') + (*PERIOD '.') (*COMMA ',') (*DASH '-') + (*BLANK ' ')) PROMPT('Date Separator') PARM KWD(TIMFMT) TYPE(*CHAR) LEN(3) RSTD(*YES) + DFT(ISO) VALUES(ISO EUR JIS USA HMS) + PROMPT('Time Format') PARM KWD(TIMSEP) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*JOB) SPCVAL((*JOB J) (*COLON ':') + (*PERIOD '.') (*COMMA ',') (*BLANK ' ')) + PROMPT('Time Separator') PARM KWD(DECPOINT) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*COMMA) SPCVAL((*COMMA ',') (*PERIOD + '.')) VARY(*NO) PASSATR(*NO) + PROMPT('Decimal Point') PARM KWD(DROP) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Drop Option') PARM KWD(COMMENT) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Comment Option') PARM KWD(LABEL) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Label Option') PARM KWD(SYSNAM) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('System Name Option (RENAME)') PARM KWD(SEVLVL) TYPE(*INT4) DFT(0) RANGE(0 39) + SPCVAL((0) (10) (20) (30)) + PROMPT('Severity Level') PARM KWD(MSGLVL) TYPE(*INT4) DFT(0) RANGE(0 39) + PROMPT('Message Level') PARM KWD(STMFMT) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Statement Formatting Option') PARM KWD(HEADER) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Header Option') PARM KWD(TRIGGER) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Trigger Option') PARM KWD(CONSTRAINT) TYPE(*CHAR) LEN(1) + RSTD(*YES) DFT(*ALTER) SPCVAL((*ALTER 1) + (*CREATE 2) (*NO 0)) PROMPT('Constraint + Option') PARM KWD(PRIVILEGES) TYPE(*CHAR) LEN(1) + RSTD(*YES) DFT(*YES) SPCVAL((*YES 1) (*NO + 0)) PROMPT('Privileges Option') PARM KWD(CCSID) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('CCSID Option') PARM KWD(CRTORRPL) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Create or Replace Option') PARM KWD(OBFUSCATE) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Obfuscate Option') /** IL SEGUENTE PARAMETRO NON E' ANCORA GESTIBILE A V7R1 **/ PARM KWD(ACTIVATE) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*INACTIVE_IN_V7R1) SPCVAL((*NO 0) + (*YES 1) (*INACTIVE_IN_V7R1 1)) + PROMPT('Activate Row and Column Access') /** IL SEGUENTE PARAMETRO NON E' ANCORA GESTIBILE A V7R1 **/ PARM KWD(MASK) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*INACTIVE_IN_V7R1) SPCVAL((*NO 0) + (*YES 1) (*INACTIVE_IN_V7R1 1)) + PROMPT('Mask and Permission Option') PARM KWD(QUALIFIED) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*UNQUAL) SPCVAL((*UNQUAL 1) (*QUAL + 0)) PROMPT('Qualified Name Option') PARM KWD(ADDITIONAL) TYPE(*CHAR) LEN(1) + RSTD(*YES) DFT(*NO) SPCVAL((*NO 0) (*YES + 1)) PROMPT('Additional Index Option') PARM KWD(INDEXVIEW) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Index instead of View Option') //ENDSRC /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDL.) FILETYPE(*SRC) ENDCHAR('//ENDSRC') //BCHJOB JOB(JRTVDDL.) JOBD(NERONI2/NERONI2) OUTQ(QPRINTS) + ENDSEV(60) LOG(4 00 *SECLVL) MSGQ(*USRPRF) CCSID(280) jmy /* Claudio Neroni 02-02-2015 Creato. */ /* Link consultati per realizzare il tool. */ /* http://www.ibmsystemsmag.com/ibmi/developer/general/Generating-DDL-Source-Using-a-CL-Command/ */ /* http://www.itjungle.com/mgo/mgo060502-story01.html */ /* Documentazione dell'API chiamata. */ /* http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/apis/qsqgnddl.htm?lang=en */ /* JRTVDDL */ /* Retrieve DDL. */ /* Recupera DDL. */ /* Prerequisiti: nessuno */ /* Imposta la lista librerie. */ CHGLIBL LIBL(QTEMP QGPL) ADDLIBLE LIB(NERONI2) POSITION(*AFTER QTEMP) /* Cancella il file messaggi preesistente. */ DLTMSGF MSGF(NERONI2/JRTVDDL) /* Cancella i testi d'aiuto preesistenti. */ DLTPNLGRP PNLGRP(NERONI2/JRTVDDLP) /* Cancella i logici preesistenti. */ /* Cancella i fisici preesistenti. */ DLTF FILE(NERONI2/JRTVDDLDS) /* Cancella i comandi preesistenti. */ DLTCMD CMD(NERONI2/JRTVDDL) DLTCMD CMD(NERONI2/JRTVDDLUF) /* Cancella i programmi preesistenti. */ DLTPGM PGM(NERONI2/JRTVDDL1) DLTPGM PGM(NERONI2/JRTVDDL2) /* Crea i file fisici. */ CRTPF FILE(NERONI2/JRTVDDLDS) SRCFILE(JRTVDDL) MBR(*NONE) /* Crea i file logici. */ /* Crea i comandi. */ CRTCMD CMD(NERONI2/JRTVDDL) PGM(JRTVDDL1) SRCFILE(JRTVDDL) + HLPPNLGRP(JRTVDDLP) HLPID(CMD) PRDLIB(NERONI2) CRTCMD CMD(NERONI2/JRTVDDLUF) PGM(JRTVDDL1) SRCFILE(JRTVDDL) + HLPPNLGRP(JRTVDDLP) HLPID(CMD) PRDLIB(NERONI2) /* Duplica i comandi in QGPL. */ CRTPRXCMD CMD(QGPL/JRTVDDL) TGTCMD(NERONI2/JRTVDDL) AUT(*USE) + REPLACE(*YES) CRTPRXCMD CMD(QGPL/JRTVDDLUF) TGTCMD(NERONI2/JRTVDDLUF) AUT(*USE) + REPLACE(*YES) /* Crea i programmi. */ CRTBNDCL PGM(NERONI2/JRTVDDL1) SRCFILE(JRTVDDL) TGTRLS(*CURRENT) + DBGVIEW(*LIST) CRTBNDRPG PGM(NERONI2/JRTVDDL2) SRCFILE(JRTVDDL) DBGVIEW(*LIST) + TGTRLS(*CURRENT) /* Crea il file messaggi. */ CRTMSGF MSGF(NERONI2/JRTVDDL) TEXT('Retrieve DDL. Msgf') /* Fotografia comandi (xxxA001). */ ADDMSGD MSGID(JRDA001) MSGF(NERONI2/JRTVDDL) MSG('Vedi secondo + livello') SECLVL('JRTVDDL OBJNAME(''&1'') + OBJLIB(''&2'') OBJTYPE(&3) SRCFILE(&4) SRCLIB(&5) + SRCMBR(&6) REPLACE(&7) STANDARDS(&8) NAMING(&9) + DATFMT(&10) DATSEP(&11) TIMFMT(&12) TIMSEP(&13) + DECPOINT(&14) DROP(&15) COMMENT(&16) LABEL(&17) + SYSNAM(&18) SEVLVL(&19) MSGLVL(&20) STMFMT(&21) + HEADER(&22) TRIGGER(&23) CONSTRAINT(&24) + PRIVILEGES(&25) CCSID(&26) CRTORRPL(&27) + OBFUSCATE(&28) ACTIVATE(&29) MASK(&30) QUALIFIED(&31) + ADDITIONAL(&32) INDEXVIEW(&33)') FMT((*CHAR 10) (*CHAR + 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR + 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR + 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR + 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR + 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR + 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR 10) (*CHAR + 10) (*CHAR 10)) /* Messaggi comuni a pgm di comandi diversi (xxx0001). */ ADDMSGD MSGID(JRD81AA) MSGF(NERONI2/JRTVDDL) MSG('All CPF81xx + messages could be returned. xx is from 01 to FF.') ADDMSGD MSGID(JRD98AA) MSGF(NERONI2/JRTVDDL) MSG('All CPF98xx + messages could be signaled. xx is from 01 to FF.') /* Messaggi nei pgm del Cmd 1 (xxx0101). */ /* Messaggi nei pgm del Cmd 2 (xxx0201). */ /* Messaggi dei Cmd (xxx1001). */ /* Crea i testi d'aiuto. */ CRTPNLGRP PNLGRP(NERONI2/JRTVDDLP) SRCFILE(JRTVDDL) //ENDBCHJOB //ENDSRC /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDLDS) FILETYPE(*SRC) ENDCHAR('//ENDSRC') * Claudio Neroni 19-01-2015 Ricavato dal link: * http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/apis/qsqgnddl.htm?lang=en * che descrive la API V7R1 * "Generate Data Definition Language (QSQGNDDL) API" * Per i valori permessi, consultare il link. * * Format of the input template parameter for API QSQGNDDL. A R SQLR0100R A TEXT('DS parms for API QSQGNDDL') * SQLR0100 Format * 000 000 CHAR(258) Database object name A OBJ 258 A COLHDG('Object') * 258 102 CHAR(258) Database object library name A OBJLIB 258 A COLHDG('Object' + A 'library') * 516 204 CHAR(10) Database object type A OBJTYP 10 A COLHDG('Object' + A 'type') * 526 20E CHAR(10) Database source file name A SRCFIL 10 A COLHDG('Source' + A 'file') * 536 218 CHAR(10) Database source file library name A SRCLIB 10 A COLHDG('Source' + A 'library') * 546 222 CHAR(10) Database source file member name A SRCMBR 10 A COLHDG('Source' + A 'member') * 556 22C BINARY(4) Severity level A SEVLVL 9B A COLHDG('Severity' + A 'level') * 560 230 CHAR(1) Replace option A RPLOPT 1 A COLHDG('Replace' + A 'option') * 561 231 CHAR(1) Statement formatting option A STMFMTOPT 1 A COLHDG('Statement' + A 'formatting' + A 'option') * 562 232 CHAR(3) Date format A DATFMT 3 A COLHDG('Date' + A 'format') * 565 235 CHAR(1) Date separator A DATSEP 1 A COLHDG('Date' + A 'separator') * 566 236 CHAR(3) Time format A TIMFMT 3 A COLHDG('Time' + A 'format') * 569 239 CHAR(1) Time separator A TIMSEP 1 A COLHDG('Time' + A 'separator') * 570 23A CHAR(3) Naming option A NAMOPT 3 A COLHDG('Naming' + A 'option') * 573 23D CHAR(1) Decimal point A DECPNT 1 A COLHDG('Decimal' + A 'point') * 574 23E CHAR(1) Standards option A STDOPT 1 A COLHDG('Standards' + A 'option') * 575 23F CHAR(1) Drop option A DRPOPT 1 A COLHDG('Drop' + A 'option') * 576 240 BINARY(4) Message level A MSGLVL 9B A COLHDG('Message' + A 'level') * 580 244 CHAR(1) Comment option A CMTOPT 1 A COLHDG('Comment' + A 'option') * 581 245 CHAR(1) Label option A LBLOPT 1 A COLHDG('Label' + A 'option') * 582 246 CHAR(1) Header option A HDROPT 1 A COLHDG('Header' + A 'option') * 583 247 CHAR(1) Trigger option A TRGOPT 1 A COLHDG('Trigger' + A 'option') * 584 248 CHAR(1) Constraint option A CNSOPT 1 A COLHDG('Constraint' + A 'option') * 585 249 CHAR(1) System name option A SYSNAMOPT 1 A COLHDG('System' + A 'name' + A 'option') * 586 24A CHAR(1) Privileges option A PLGOPT 1 A COLHDG('Privileges' + A 'option') * 587 24B CHAR(1) CCSID option A CCSOPT 1 A COLHDG('CCSID' + A 'option') * 588 24C CHAR(1) Create or Replace option A CRTRPLOPT 1 A COLHDG('Create/' + A 'replace' + A 'option') * 589 24D CHAR(1) Obfuscate option A OBFOPT 1 A COLHDG('Obfuscate' + A 'option') * 590 24E CHAR(1) Activate row and column access control option A ACTROWOPT 1 A COLHDG('Activate' + A 'row col acc' + A 'ctl option') * 591 24F CHAR(1) Mask and permission option A MSKPEROPT 1 A COLHDG('Mask and' + A 'permission' + A 'option') * 592 250 CHAR(1) Qualified name option A QUANAMOPT 1 A COLHDG('Qualified' + A 'name' + A 'option') * 593 251 CHAR(1) Additional index option A ADDIDXOPT 1 A COLHDG('Additional' + A 'index' + A 'option') * 594 252 CHAR(1) Index instead of view option A IDXVIEOPT 1 A COLHDG('Index instead' + A 'of view' + A 'option') * 595 253 CHAR(*) Reserved (Variable length) //ENDSRC /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDLP) FILETYPE(*SRC) ENDCHAR('//ENDSRC') :PNLGRP. .*------------------------------------------------------------------------------ :HELP NAME=CMD. :H3.Comando JRTVDDL :H2.Retrieve DDL (Data Definition Language) from existing object :P.Recupera le specifiche DDL da oggetto esistente :P.Il comando permette di recuperare in un membro sorgente la definizione DDL di un oggetto gia' esistente, in qualunque modo generato. :P.In pratica, si recuperano le specifiche che permettono la ricreazione dell'oggetto stesso con istruzioni SQL. :P.La generazione delle specifiche viene eseguita chiamando l'api :HP2.QSQGNDDL:EHP2. "Generate Data Definition Language". :P.Il presente comando filtra semplicemente tutti i parametri richiesti dall'api rendendone piu' agevole l'uso. :P.Il presente help e' ricavato, senza traduzione ma con i necessari aggiustamenti, dallo spiegone IBM sull'api recuperato al link http://www.ibm.com/support/knowledgecenter/en/ssw_ibm_i_72/apis/qsqgnddl.htm :P.Retrieve Data Definition Language (JRTVDDL) generates the SQL data definition language statements required to recreate a database object. The results are returned in the specified database source file member. :P.Database physical files or logical files that were created using an interface other than SQL may be specified. For example, files created from DDS and the CRTPF or CRTLF commands may be specified. Even if the object was created using SQL, the Standards option may restrict what can be generated. In either of these cases: :PARML. :P.:HP2.o:EHP2. the operation may succeed with warnings that are generated in the SQL statement source, or :P.:HP2.o:EHP2. the operation may fail for certain non-relational files or objects not supported by the specified Standards option. :EPARML. :P.If a database object was created using an SQL interface, the resulting SQL statements may be slightly different than the SQL statements that created the object originally. For example: :PARML. :P.:HP2.o:EHP2. When there is more than one way to specify an attribute in SQL, the more standard syntax is generally chosen. For example, if a user creates a table with a FLOAT(52) column, DOUBLE PRECISION is generated. :P.:HP2.o:EHP2. When a clause is not specified in the original SQL statement and a default is taken instead, a clause may be generated to explicitly show the default. For example, if the default value for a nullable column is the null value, the clause DEFAULT NULL is generated. :P.:HP2.o:EHP2. When a Standards option is used to restrict the generated SQL to the ANS and ISO standard or the DB2® family, an attribute may be omitted. For example, if the ALLOCATE clause is specified on a VARCHAR column, the ALLOCATE clause is not generated unless the Standards option allows DB2 for IBM® i extensions. :EPARML. :P.For more information, see the Severity level field within the SQLR0100 Format. :P.You can use the JRTVDDL command with database objects only. DDM files (other than SQL aliases) are not supported. File overrides do not affect the specified object names. File overrides do affect the specified source file names. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/OBJNAME'. :H3.Database Object Name (OBJNAME) :P.Nome dell'oggetto di cui si vogliono recuperare le DDL. :P.The name of the database object for which DDL will be generated. Either the SQL name or the system name may be specified. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. For example, a file with a name of "abc" must be specified with the surrounding quotes. A file with a name of ABC must be specified in upper case. :P.If the object type is a FUNCTION or PROCEDURE, this name must be the specific name of the function or procedure. :P.If TABLE or VIEW is specified for the object type, the object name may identify an alias. In this case, the object that the alias points to will be generated. A CREATE ALIAS statement will be generated only if ALIAS is specified for the object type. :P.:HP3.Valore obbligatorio::EHP3. :PARML. :PT.nome-oggetto-database :PD.Nome dell'oggetto di cui recuperare le DDL. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/OBJLIB'. :H3.Database Object Library (OBJLIB) :P.Libreria dell'oggetto di cui si vogliono recuperare le DDL. :P.The name of the library containing the object for which DDL will be generated. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. This name is ignored if the specified object type is SCHEMA. You can use these special values for the library name: :P.:HP3.Valore obbligatorio::EHP3. :PARML. :PT.nome-libreria :PD.Nome della libreria o dello SCHEMA. :EPARML. :P.:HP3.Valori speciali::EHP3. :PARML. :PT.*CURLIB :PD.The job's current library :PT.*LIBL :PD.The library list :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/OBJTYPE'. :H3.Database Object Type (OBJTYPE) :P.Tipo dell'oggetto di cui si vogliono recuperare le DDL. :P.The type of the database object or object attribute for which DDL is generated. You can use these special values for the object type: :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.TABLE:EPK. :PD.The object is an SQL table or physical file. :PT.ALIAS :PD.The object is an SQL alias. If the Standards option is ISO, an ALIAS object type is not valid. :PT.CONSTRAINT :PD.The object attribute is a constraint. :PT.FUNCTION :PD.The object is an SQL function. :PT.INDEX :PD.The object is an SQL index. If the Standards option is ISO, an INDEX object type is not valid. :PT.MASK :PD.The object is an SQL column mask. If the Standards option is ISO, a MASK object type is not valid. :PT.PERMISSION :PD.The object is an SQL row permission. If the Standards option is ISO, a PERMISSION object type is not valid. :PT.PROCEDURE :PD.The object is an SQL procedure. :PT.SCHEMA :PD.The object is an SQL schema (collection) or library. :PT.SEQUENCE :PD.The object is an SQL sequence. :PT.TRIGGER :PD.The object attribute is a trigger. :PT.TYPE :PD.The object is an SQL type. :PT.VARIABLE :PD.The object is an SQL global variable. If the Standards option is ISO, an VARIABLE object type is not valid. :PT.VIEW :PD.The object is an SQL view or logical file. :PT.XSR :PD.The object is an XML schema repository object. If the Standards option is ISO, an XSR object type is not valid. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/SRCFILE'. :H3.Database Source File (SRCFILE) :P.File sorgente ricevente delle specifiche DDL recuperate. :P.The name of the source file that contains the SQL statements generated by the API. The name must be a valid system name. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. For example, a file with a name of "abc" must be specified with the surrounding quotes. A file with a name of ABC must be specified in upper case. :P.The record length of the specified source file must be greater than or equal to 92. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.nome-file-sorgente :PD.Nome del file sorgente. :EPARML. :P.:HP3.Valori speciali::EHP3. :PARML. :PT.:PK DEF.QDDLSRC:EPK. :PD.Nome suggerito per il file sorgente. :PT.QSQLSRC :PD.Altro nome suggerito per il file sorgente. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/SRCLIB'. :H3.Database Source File Library (SRCLIB) :P.Libreria del file sorgente ricevente delle specifiche DDL recuperate. :P.The name of the library containing the source file that contains the SQL statements generated by the API. The name must be a valid system name. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. You can use these special values for the library name: :P.:HP3.Valori permessi::EHP3. :PARML. :PT.nome-libreria :PD.Nome della libreria del file sorgente. :EPARML. :P.:HP3.Valori speciali::EHP3. :PARML. :PT.:PK DEF.*LIBL:EPK. :PD.The library list :PT.*CURLIB :PD.The job's current library :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/SRCMBR'. :H3.Database Source File Member (SRCMBR) :P.Membro del file sorgente ricevente delle specifiche DDL recuperate. :P.The name of the source file member that contains the SQL statements generated by the API. The name must be a valid system name. The name is case sensitive. If delimiters are required for the name to be valid, they must be specified. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.nome-membro :PD.Nome del membro del file sorgente. :EPARML. :P.:HP3.Valori speciali::EHP3. :PARML. :PT.:PK DEF.*OBJNAME:EPK. :PD.Lo stesso valore menzionato nel parametro OBJNAME. :PT.*FIRST :PD.The first database physical file member found. :PT.*LAST :PD.The last database physical file member found. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/REPLACE'. :H3.Replace (Member) Option (REPLACE) :P.Aggiunge o sostituisce le specifiche generate nel membro sorgente. :P.The replace option for the database source file member. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*REPLACE:EPK. :PD.The database source file member is cleared prior to adding the resulting SQL statements. If this option is chosen, the file may be cleared even if an error is returned from the API. :PT.*ADD :PD.The resulting SQL statements are appended to the end of the database source file member. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/STANDARDS'. :H3.Standards Option (STANDARDS) :P.Opzione standard. :P.The standards option specifies whether the generated SQL statements should contain DB2 for i extensions or whether the statements should conform to the DB2 family SQL or to the ANS and ISO SQL standards. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.AS400:EPK. :PD.DB2 for i extensions may be generated in SQL statements. :PT.DB2 :PD.The generated SQL statements must conform to SQL statements common to the DB2 family. :PT.ISO :PD.The generated SQL statements must conform to the following ANSI and ISO SQL standards: :PARML. :P.:HP2.o:EHP2.ISO (International Standards Organization) 9075-1: 2003, Database Language SQL :P.:HP2.o:EHP2.ANSI (American National Standards Institute) X3.135-1-2003, Database Language SQL :EPARML. :EPARML. :P.If option DB2 or ISO is chosen, the SQL statements generated may not completely represent the object in DB2 for i; however, the statements will be compatible with the specified DB2 Family or ANSI and ISO standards option. :P.If the object is an SQL function, SQL procedure, SQL trigger, or SQL view, the SQL statements in the body of the object are included in the generated SQL statement. Hence, if the option DB2 or ISO is chosen, the generated SQL statement may not conform to the specified standards option since the statements within the body of the SQL object may not conform to the specified standard. For example, if a CREATE INDEX statement exists in the body of an SQL procedure, the generated CREATE PROCEDURE statement will contain the CREATE INDEX statement even if option DB2 or ISO is chosen. :P.There is no attempt to take product specific limits into account. For example, a table name in DB2 for i can be 128 bytes, but other products may not support table names that are that long. Thus, even if the generated SQL statement is standard, it still may not work on other products if they have smaller limits than those on DB2 for i. There is no attempt to take product specific limits into account. For example, a table name in DB2 for i can be 128 bytes, but other products may not support table names that are that long. Thus, even if the generated SQL statement is standard, it still may not work on other products if they have smaller limits than those on DB2 for i. :XMP. :HP3.If option DB2 is specified,:EHP3. :HP2.o:EHP2.The naming option must be SQL. :HP2.o:EHP2.The date format must be ISO, USA, EUR, or JIS. :HP2.o:EHP2.The time format must be ISO, USA, EUR, or JIS. :HP2.o:EHP2.The decimal point must be the period. :HP2.o:EHP2.The LABEL option must be 0. :HP2.o:EHP2.The System name option must be 0. :HP2.o:EHP2.The Create or Replace option must be 0. :HP2.o:EHP2.The Obfuscate option must be 0. :EXMP. :XMP. :HP3.If option ISO is specified,:EHP3. :HP2.o:EHP2.The naming option must be SQL. :HP2.o:EHP2.The date format must be ISO. :HP2.o:EHP2.The time format must be ISO. :HP2.o:EHP2.The decimal point must be the period. :HP2.o:EHP2.The LABEL option must be 0. :HP2.o:EHP2.The COMMENT option must be 0. :HP2.o:EHP2.The System name option must be 0. :HP2.o:EHP2.The CCSID option must be 0. :HP2.o:EHP2.The Create or Replace option must be 0. :HP2.o:EHP2.The Obfuscate option must be 0. :HP2.o:EHP2.The Additional index option must be 0. :HP2.o:EHP2.The Index instead of view option must be 0. :HP2.o:EHP2.The Activate row and column access control option must be 0. :HP2.o:EHP2.The Mask and permission option must be 0. :HP2.o:EHP2.An ALIAS, VARIABLE, or XSR object type must not be specified. :EXMP. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/NAMING'. :H3.Naming Option (NAMING) :P.Opzione di denominazione :P.The naming convention used for qualified names in the generated SQL statements. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.SYS:EPK. :PD.library/file syntax :P.:HP2.o:EHP2.If the Standards option is DB2 or ISO, the SYS naming option is not valid. :P.:HP2.o:EHP2.If the object type is a FUNCTION, PROCEDURE, TRIGGER, or VIEW, and a column name is qualified by a qualified table name in the SQL body of the function, procedure, trigger, or view (that is, schema-name.table-name.column-name), the generated statement will not be valid because this type of column name qualification is not allowed in SYS naming. :PT.SQL :PD.collection.table syntax :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/DATFMT'. :H3.Date Format (DATFMT) :P.Formato data. :P.The date format used for date constants in a generated SQL CREATE TABLE statement. The date format may not apply to date constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*JOB:EPK. :PD.Formato data del lavoro corrente. :PT.ISO :PD.International Standards Organization (yyyy-mm-dd) :PT.EUR :PD.IBM European Standard (dd.mm.yyyy) :PC.If the Standards option is ISO, the EUR date format is not valid. :PT.JIS :PD.Japanese Industrial standard Christian Era (yyyy-mm-dd) :PC.If the Standards option is ISO, the JIS date format is not valid. :PT.USA :PD.IBM USA standard (mm/dd/yyyy) :PC.If the Standards option is ISO, the USA date format is not valid. :PT.MDY :PD.Month/day/year (mm/dd/yy) :PC.If the Standards option is DB2 or ISO, the MDY date format is not valid. :PT.DMY :PD.Day/month/year (dd/mm/yy) :PC.If the Standards option is DB2 or ISO, the DMY date format is not valid. :PT.YMD :PD.Year/month/day (yy/mm/dd) :PC.If the Standards option is DB2 or ISO, the YMD date format is not valid. :PT.JUL :PD.Julian (yy/ddd) :PC.If the Standards option is DB2 or ISO, the JUL date format is not valid. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/DATSEP'. :H3.Date Separator (DATSEP) :P.Carattere separatore della data. :P.The date separator used for date constants in a generated SQL CREATE TABLE statement. The date separator may not apply to date constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement. The date separator is only applicable if the date format is MDY, DMY, YMD, or JUL. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*JOB:EPK. :PD.Separatore data del lavoro corrente. :PT.*SLASH :PD."/" Slash separator :PT.*PERIOD :PD."." Period separator :PT.*COMMA :PD."," Comma separator :PT.*DASH :PD."-" Dash separator :PT.*BLANK :PD." " Blank separator :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/TIMFMT'. :H3.Time Format (TIMFMT) :P.Formato ora. :P.The format used for time constants in a generated SQL CREATE TABLE statement. The time format may not apply to time constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement in the generated SQL statements. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.ISO:EPK. :PD.International Standards Organization (hh.mm.ss) :PT.EUR :PD.IBM European Standard (hh.mm.ss) :PC.If the Standards option is ISO, the EUR time format is not valid. :PT.JIS :PD.Japanese Industrial standard Christian Era (hh:mm:ss) :PC.If the Standards option is ISO, the JIS time format is not valid. :PT.USA :PD.IBM USA standard (hh:mm AM, hh:mm PM) :PC.If the Standards option is ISO, the USA time format is not valid. :PT.HMS :PD.Hour/minute/second (hh:mm:ss) :PC.If the Standards option is DB2 or ISO, the HMS time format is not valid. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/TIMSEP'. :H3.Time Separator (TIMSEP) :P.Carattere separatore dell'ora. :P.The time separator used for time constants in a generated SQL CREATE TABLE statement. The time separator may not apply to time constants that are in ISO, EUR, USA, or JIS format in a CREATE VIEW, CREATE TRIGGER, CREATE FUNCTION, CREATE PROCEDURE, CREATE MASK, or CREATE PERMISSION statement. The time separator is only applicable if the time format is HMS in the generated SQL statements. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*JOB:EPK. :PD.Separatore ora del lavoro corrente. :PT.*COLON :PD.":" Colon separator :PT.*PERIOD :PD."." Period separator :PT.*COMMA :PD."," Comma separator :PT.*BLANK :PD." " Blank separator :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/DECPOINT'. :H3.Decimal Point (DECPOINT) :P.Punto decimale. :P.The decimal point used for numeric constants. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*COMMA:EPK. :PD."," Comma separator :PT.*PERIOD :PD."." Period separator :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/DROP'. :H3.Drop Option (DROP) :P.Opzione di cancellazione. :P.The drop option specifies whether DROP (or ALTER) SQL statements should be generated prior to the CREATE statement to drop the specified object. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.DROP statements should be generated. :PT.*NO :PD.DROP statements should not be generated. :EPARML. :P.Note that with the exception of DROP SCHEMA, the DROP statements generated will not include a CASCADE or RESTRICT option even if the standards option is ISO. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/COMMENT'. :H3.Comment Option (COMMENT) :P.Opzione di commento. :P.The comment option specifies whether COMMENT ON SQL statements should be generated if a comment exists on the specified database object. If comments are not supported by the specified database object, the comment option is ignored. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.COMMENT ON SQL statements should be generated. If the specified database object type is a table or view, COMMENT ON SQL statements will also be generated for columns of the table or view. :PT.*NO :PD.COMMENT ON SQL statements should not be generated. :EPARML. :P.If the Standards option is ISO, comment option *YES is not valid. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/LABEL'. :H3.Label Option (LABEL) :P.Opzione etichetta. :P.The label option specifies whether LABEL ON SQL statements should be generated if a label exists on the specified database object. If labels are not supported by the specified database object, the label option is ignored. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.LABEL ON SQL statements should be generated. If the specified database object type is a table or view, LABEL ON SQL statements will also be generated for columns of the table or view. :PT.*NO :PD.LABEL ON SQL statements should not be generated. :EPARML. :P.If the Standards option is DB2 or ISO, label option *YES is not valid. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/SYSNAM'. :H3.System Name Option (Rename) (SYSNAM) :P.Opzione nomi di sistema. :P.The system name option specifies whether a RENAME statement should be generated for the system name when it is different from the SQL name and the object type is an INDEX, TABLE, or VIEW. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*NO:EPK. :PD.A RENAME statement should not be generated. :PT.*YES :PD.A RENAME statement should be generated. :EPARML. :P.If the Standards option is DB2 or ISO, system name option *YES is not valid. The default is *NO. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/SEVLVL'. :H3.Severity Level (SEVLVL) :P.Livello di gravita'. :P.The severity level at which the operation fails. If errors occur that have a severity level greater than this value, the operation ends. The valid values are in the range 0 through 39 inclusive. Any severity 40 error will cause the API to fail. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.0:EPK. :PD.No errors or warnings. :PT.10 :PD.The following attributes will result in messages with this severity level: :PARML. :P.:HP2.o:EHP2. Schema ASP and WITH DATA DICTIONARY. :PC. If the Standards option is DB2 or ISO, these clauses will be ignored. :P.:HP2.o:EHP2. Test libraries. :PC. A CREATE SCHEMA statement will be generated to create the schema. Schemas are production libraries. :P.:HP2.o:EHP2. Libraries with a CRTAUT parameter value. :PC. Under SQL naming, schemas are always created with CRTAUT(*EXCLUDE). Under SYS naming, schemas are always created with CRTAUT(*SYSVAL). :P.:HP2.o:EHP2. NODEGROUPs. :PC. If the Standards option is DB2 or ISO, the NODEGROUP clause will be ignored. :P.:HP2.o:EHP2. COMMENT ON parameters. :PC. If the Standards option is DB2, the comment will be ignored. :P.:HP2.o:EHP2. System file names. :PC. If the Standards option is DB2 or ISO, only the SQL names are generated. Otherwise, FOR SYSTEM NAME clause is generated to assign the system file name. :P.:HP2.o:EHP2. System column names. :PC. If the Standards option is DB2 or ISO, only the SQL names are generated. Otherwise, a FOR COLUMN clause will be generated to assign each system column name. :P.:HP2.o:EHP2. BIGINT data types. :PC.If the Standards option is DB2 or ISO, a DECIMAL(19,0) will be generated. :P.:HP2.o:EHP2. DBCS-open data types. :PC. If the Standards option is DB2 or ISO, a character field will be generated. :PARML. :P.:HP2.o:EHP2. Binary with non-zero scale. :PC. A decimal data type will be generated. :P.:HP2.o:EHP2. Files whose format name is different from the file name. :PC. If the Standards option is DB2 or ISO, the format name will be the same as the file name. Otherwise, a RCDFMT clause will be generated to assign the format name. :P.:HP2.o:EHP2. Files with a REUSEDLT(*NO) attribute. :PC. REUSEDLT(*YES) will be used. :P.:HP2.o:EHP2. Physical or logical files that use any of the following keywords: CHECK, CHKMSGID, CMP, DATFMT, EDTCDE, EDTWRD, TIMFMT, RANGE, REFSHIFT, VALUES. :PC. These keywords will be ignored. :P.:HP2.o:EHP2. Logical files that use any of the following keywords: CCSID or TRNTBL. :PC. These keywords will be ignored. :P.:HP2.o:EHP2. Join logical files with JDFTVAL or JDUPSEQ. :PC. A LEFT OUTER JOIN clause will be generated, but the join default value will be the null value and the JDUPSEQ keyword will be ignored. :P.:HP2.o:EHP2. Logical files with SST function. :PC. If the Standards option is ISO, SUBSTRING is generated instead of SUBSTR. :P.:HP2.o:EHP2. COBOLLE and C++ languages in external functions and procedures. :PC. If the Standards option is DB2 or ISO, COBOL or C is generated. :P.:HP2.o:EHP2. RPGLE language in external functions and procedures. :PC. If the Standards option is DB2, RPG is generated. :EPARML. :EPARML. :PT.20 :PD.The following attributes will result in messages with this severity level: :PARML. :P.:HP2.o:EHP2. Multiple member files, files with no members, or files with MAXMBRS greater than one. :PC. The resulting file will contain one member. :P.:HP2.o:EHP2. Single format logical files with a member built over multiple physical file members. :PC. The resulting file will be based on the first physical file member. :P.:HP2.o:EHP2. Logical files that contain input/output fields that map an underlying physical file field to a different data type, length, precision or scale. :PC. A CAST scalar function will be generated to map the data to the correct attributes, but the resulting column is input-only. :P.:HP2.o:EHP2. Keyed logical files that do not share the based on physical file's format or have more than one based on file. :PC. If INDEX is specified, the format will be ignored. :P.:HP2.o:EHP2. Triggers with MODE DB2ROW. :PC. If the Standards option is DB2 or ISO, MODE DB2SQL will be used. :EPARML. :PT.30 :PD.The following attributes will result in messages with this severity level: :PARML. :P.:HP2.o:EHP2. CHAR or VARCHAR CCSID 65535. :PC. If the Standards option is ISO, a character field is generated. :P.:HP2.o:EHP2. GRAPHIC, VARGRAPHIC, or DBCLOB. :PC. If the Standards option is ISO, a character field is generated. :P.:HP2.o:EHP2. DataLinks or Row IDs. :PC. If the Standards option is DB2 or ISO, a character field is generated. :P.:HP2.o:EHP2. Open, Only, or Either fields. :PC. If the Standards option is AS400, the CCSID clause will result in an open field. Only and Either fields will result in a warning. If the Standards option is DB2, FOR MIXED DATA is generated. If the Standards option is ISO, character fields will be generated. :P.:HP2.o:EHP2. Keyed logical files. :PC. If VIEW is specified, the key specifications will be ignored unless the Index instead of view option 1 is specified, because all views are non-keyed. :P.:HP2.o:EHP2. Keyed physical files whose key is not a primary key. :PC. A CREATE TABLE will be generated without a primary key. The key specifications will be ignored, however, because only tables with a primary key are keyed. :P.:HP2.o:EHP2. Files that use any of the following keywords: ALTSEQ, DIGIT, FCFO, FIFO, LIFO, UNSIGNED, ZONE. :PC. These keywords will be ignored. :P.:HP2.o:EHP2. SRTSEQ. :PC. The sort sequence will be ignored. :P.:HP2.o:EHP2. Non-SQL triggers if TABLE object is specified. :PC. The triggers will be ignored. :P.:HP2.o:EHP2. NO EXTERNAL ACTION, SCRATCHPAD, FINAL CALL, ALLOW PARALLEL, or DBINFO, keywords in functions and procedures. :PC. If the standards option is ISO, these attributes will be ignored. :P.:HP2.o:EHP2. COMMIT ON RETURN YES, NOT FENCED, or NEW SAVEPOINT LEVEL clauses in functions and procedures. :PC. If the standards option is DB2 or ISO, these attributes will be ignored. :P.:HP2.o:EHP2. Functions and procedures with parameter style GENERAL WITH NULLS, DB2SQL, or DB2GENERAL. :PC. If the Standards option is ISO, PARAMETER STYLE SQL is used. :P.:HP2.o:EHP2. JAVA, REXX, RPG, and RPGLE language in functions and procedures. :PC. If the Standards option is ISO, the C language is used instead. :P.:HP2.o:EHP2. CL language in functions and procedures. :PC. If the Standards option is DB2 or ISO, the C language is used instead. :EPARML. :PT.40 :PD.The following attributes will result in messages with this severity level: :PARML. :P.:HP2.o:EHP2. Physical file if either VIEW or INDEX object type is specified. :P.:HP2.o:EHP2. Logical file if TABLE object type is specified. :P.:HP2.o:EHP2. Non-keyed file if INDEX object type is specified. :P.:HP2.o:EHP2. Non-alias file if ALIAS object type is specified. :P.:HP2.o:EHP2. Function if PROCEDURE object type is specified. :P.:HP2.o:EHP2. Procedure if FUNCTION object type is specified. :P.:HP2.o:EHP2. Device files :P.:HP2.o:EHP2. Program described physical files :P.:HP2.o:EHP2. Multiple format logical files :P.:HP2.o:EHP2. Indexes if the Standards option is ISO. :P.:HP2.o:EHP2. Aliases if the Standards option is ISO. :P.:HP2.o:EHP2. EVI Indexes if the Standards option is DB2. :P.:HP2.o:EHP2. Variables if the Standards option is ISO. :P.:HP2.o:EHP2. XSR Objects if the Standards option is ISO. :P.:HP2.o:EHP2. Masks if the Standards option is ISO. :P.:HP2.o:EHP2. Permissions if the Standards option is ISO. :P.:HP2.o:EHP2. UNIQUE WHERE NOT NULL if the Standards option is DB2. :P.:HP2.o:EHP2. Aliases that contain a member name if the Standards option is DB2. :P.:HP2.o:EHP2. System-generated UDFs :P.:HP2.o:EHP2. Built-in data types :P.:HP2.o:EHP2. SQL UDFs, if the Standards option is DB2. :P.:HP2.o:EHP2. Sourced UDFs, if the Standards option is ISO. :P.:HP2.o:EHP2. User-defined table functions, if the Standards option is ISO. :P.:HP2.o:EHP2. Non-SQL triggers if TRIGGER object is specified. :EPARML. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/MSGLVL'. :H3.Message Level (MSGLVL) :P.Livello messaggi. :P.The severity level at which the messages are generated. If errors occur that have a severity level greater than this value, a message is generated in the output. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.0:EPK. :PD.Tutti i messaggi vengono segnalati. :PT.0-39 :PD.The valid values are in the range 0 through 39 inclusive. :EPARML. :P.The message level must be less than or equal to the severity level. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/STMFMT'. :H3.Statement Formatting Option (STMFMT) :P.Opzione di formattazione delle istruzioni. :P.The formatting option used in the generated SQL statements. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*NO:EPK. :PD.No additional formatting characters are added to the generated SQL statements. :PT.*YES :PD.Additional end-of-line characters and tab characters are added to the generated SQL statements. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/HEADER'. :H3.Header Option (HEADER) :P.Opzione di intestazione. :P.The header option specifies whether a header should be generated prior to the CREATE statement. The header consists of comments that describe the version, date and time, the relational database, and some of the options used to generate the SQL statements. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*NO:EPK. :PD.A header should not be generated. :PT.*YES :PD.A header should be generated. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/TRIGGER'. :H3.Trigger Option (TRIGGER) :P.Opzione trigger. :P.The trigger option specifies whether triggers should be generated when the object type is a TABLE or VIEW. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.Triggers should be generated. :PT.*NO :PD.Triggers should not be generated. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/CONSTRAINT'. :H3.Constraint Option (CONSTRAINT) :P.Opzione di costrizione. :P.The constraint option specifies whether constraints should be generated when the object type is a TABLE. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*ALTER:EPK. :PD.Constraints should be generated using ALTER TABLE statements. :PC.Prima di V7R2, era *YES. :PT.:PK DEF.*CREATE:EPK. :PD.Constraints should be generated as part of the CREATE TABLE statement. :PC.Permesso da V7R2. :PT.*NO :PD.Constraints should not be generated. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/PRIVILEGES'. :H3.Privileges Option (PRIVILEGES) :P.Opzione privilegi. :P.The privileges option specifies whether GRANT SQL statements should be generated on the specified database object. If privileges are not supported by the specified database object, the privileges option is ignored. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.GRANT SQL statements should be generated. :PT.*NO :PD.GRANT SQL statements should not be generated. :EPARML. :P.To generate privileges for an external routine, the external routine must exist. :P.Only SQL privileges will be generated for the specified database object. Authorities acquired through a group user profile, authorization list, special authority (such as *ALLOBJ) or any authority granted through GRTOBJAUT that does not map directly to an SQL privilege are not generated. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/CCSID'. :H3.CCSID Option (CCSID) :P.Opzione CCSID. :P.The CCSID option specifies whether the CCSID attribute should be generated for column definitions when the object type is a TABLE. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.CCSID attribute should be generated. :PT.*NO :PD.CCSID atrribute should not be generated. :EPARML. :P.If the standards option is AS400 and *NO is specified, the CCSID clause, FOR MIXED DATA, FOR SBCS DATA, or FOR BIT DATA is not generated for the column definition. :P.If the standards option is DB2 and *NO is specified, FOR MIXED DATA, FOR SBCS DATA, or FOR BIT DATA is not generated for the column definition. :P.If the standards option is ISO the CCSID option is ignored. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/CRTORRPL'. :H3.Create or Replace Option (CRTORRPL) :P.Opzione crea o sostituisci. :P.The Create or Replace option specifies whether CREATE OR REPLACE should be generated for the specified database object on the CREATE statement. This option is ignored if the specified database object does not support CREATE OR REPLACE. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*YES:EPK. :PD.CREATE OR REPLACE should be generated. :PT.*NO :PD.CREATE OR REPLACE should not be generated. :EPARML. :P.If the Standards option is DB2 or ISO, the CREATE OR REPLACE option is not valid. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/OBFUSCATE'. :H3.Obfuscate Option (OBFUSCATE) :P.Opzione di offuscamento. :P.The obfuscate option specifies whether an obfuscated SQL statement should be returned for SQL functions, SQL procedures, or SQL triggers that were not created using obfuscated statements. This option is ignored if the standards option is not AS400. This option is also ignored if the object is not an SQL function, procedure, or trigger. This option is ignored if the object is already obfuscated. Setting Obfuscate option = *NO cannot be used as a means of obtaining the unobfuscated SQL statement for an obfuscated object. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*NO:EPK. :PD.An obfuscated statement should not be generated. :PT.*YES :PD.An obfuscated statement should be generated for SQL functions, SQL procedures, or SQL triggers. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/ACTIVATE'. :H3.Activate Row and Column Access (ACTIVATE) :P.Opzione di attivazione accesso riga colonna. :P.The activate row and column access control option specifies whether an ALTER TABLE to activate row and column access control should be generated when the object type is a TABLE. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*INACTIVE_IN_V7R1:EPK. :PD.Parametro valido da V7R2. :PT.*NO :PD.Activate row and column access control should not be generated. :PT.*YES :PD.Activate row and column access control should be generated. :EPARML. :P.If the Standards option is ISO, activate row and column access control option *YES is ignored. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/MASK'. :H3.Mask and Permission Option (MASK) :P.Opzione di maschera e permesso. :P.The mask and permission option specifies whether row permissions and column masks should be generated when the object type is a TABLE. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*INACTIVE_IN_V7R1:EPK. :PD.Parametro valido da V7R2. :PT.*NO :PD.Permissions and masks should not be generated. :PT.*YES :PD.Permissions and masks should be generated. :EPARML. :P.If the Standards option is ISO, mask and permission option *YES is ignored. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/QUALIFIED'. :H3.Qualified Name Option (QUALIFIED) :P.Opzione di generazione nomi qualificati. :P.The qualified name option specifies whether qualified or unqualified names should be generated for the specified database object. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*QUAL:EPK. :PD.Qualified object names should be generated. Unqualified names within the body of SQL routines will remain unqualified. :PT.*UNQUAL :PD.Unqualified object names should be generated when a library is found which matches the database object library name. Any SQL object or column reference that is RDB qualified will be generated in its fully qualified form. For example, rdb-name.schema-name.table-name and rdb-name.schema-name.table-name.column-name references will retain their full qualification. :EPARML. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/ADDITIONAL'. :H3.Additional Index Option (ADDITIONAL) :P.Opzione di indice addizionale. :P.The additional index option specifies whether additional CREATE INDEX statements will be generated for DDS-created keyed physical or logical files. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*NO:EPK. :PD.Additional CREATE INDEX statements will not be generated. :PT.*YES :PD.An additional CREATE INDEX statement will be generated that matches the index for a DDS-created keyed physical file. If the physical file has a PRIMARY KEY constraint, a CREATE INDEX statement is not generated. :P.An additional CREATE INDEX statement will be generated that matches the index for a DDS-created keyed logical file. If a value of *YES is specified for the "index instead of view option", an additional CREATE INDEX statement is not generated. Additional CREATE INDEX statements will also be generated that match the join indexes of a DDS-created join logical file. :EPARML. :P.If the Standards option is ISO, additional index option *YES is not valid. :EHELP. .*------------------------------------------------------------------------------ :HELP name='CMD/INDEXVIEW'. :H3.Index instead of View Option (INDEXVIEW) :P.Opzione di indice invece che vista. :P.The Index instead of view option specifies whether a CREATE INDEX or CREATE VIEW statement will be generated for a DDS-created keyed logical file. :P.:HP3.Valori permessi::EHP3. :PARML. :PT.:PK DEF.*NO:EPK. :PD.A CREATE VIEW statement will be generated. :PT.*YES :PD.A CREATE INDEX statement will be generated that matches the index for a DDS-created keyed logical file. :P.This option is ignored if VIEW is not specified for the Database object type or if the specified file is not keyed. :P.If the specified file is keyed, the CREATE INDEX statement will be generated as if a Database object type of INDEX was specified. :EPARML. :P.If the Standards option is ISO, index instead of view option *YES is not valid. :EHELP. .*------------------------------------------------------------------------------ :HELP NAME='CMD/COMMAND/EXAMPLES'. :XH3.Esempi per JRTVDDL :P.:HP2.Esempio 1: Da scrivere:EHP2. :XMP. xxxxxx :EXMP. :EHELP. .*------------------------------------------------------------------------------ :HELP NAME='CMD/ERROR/MESSAGES'. .*--------------------- :XH3.Messaggi di errore :P.:HP3.Messaggi vari:EHP3. :DL COMPACT. :DT.CPF24B4 :DD.&MSG(CPF24B4,QCPFMSG,*LIBL,nosub). :DT.CPF3C21 :DD.&MSG(CPF3C21,QCPFMSG,*LIBL,nosub). :DT.CPF3C23 :DD.&MSG(CPF3C23,QCPFMSG,*LIBL,nosub). :DT.CPF3C26 :DD.&MSG(CPF3C26,QCPFMSG,*LIBL,nosub). :DT.CPF3C39 :DD.&MSG(CPF3C39,QCPFMSG,*LIBL,nosub). :DT.CPF3C3A :DD.&MSG(CPF3C3A,QCPFMSG,*LIBL,nosub). :DT.CPF3C90 :DD.&MSG(CPF3C90,QCPFMSG,*LIBL,nosub). :DT.CPF3CF1 :DD.&MSG(CPF3CF1,QCPFMSG,*LIBL,nosub). :DT.JRD81AA :DD.&MSG(JRD81AA,JRTVDDL,*LIBL,nosub). :DT.JRD98AA :DD.&MSG(JRD98AA,JRTVDDL,*LIBL,nosub). :DT.SQL0113 :DD.&MSG(SQL0113,QSQLMSG,*LIBL,nosub). :DT.SQL7001 :DD.&MSG(SQL7001,QSQLMSG,*LIBL,nosub). :DT.SQL7003 :DD.&MSG(SQL7003,QSQLMSG,*LIBL,nosub). :DT.SQL7011 :DD.&MSG(SQL7011,QSQLMSG,*LIBL,nosub). :DT.SQL7039 :DD.&MSG(SQL7039,QSQLMSG,*LIBL,nosub). :DT.SQL7040 :DD.&MSG(SQL7040,QSQLMSG,*LIBL,nosub). :DT.SQL7041 :DD.&MSG(SQL7041,QSQLMSG,*LIBL,nosub). :DT.SQL7042 :DD.&MSG(SQL7042,QSQLMSG,*LIBL,nosub). :DT.SQL7043 :DD.&MSG(SQL7043,QSQLMSG,*LIBL,nosub). :DT.SQL7044 :DD.&MSG(SQL7044,QSQLMSG,*LIBL,nosub). :DT.SQL7045 :DD.&MSG(SQL7045,QSQLMSG,*LIBL,nosub). :DT.SQL7046 :DD.&MSG(SQL7046,QSQLMSG,*LIBL,nosub). :DT.SQL7047 :DD.&MSG(SQL7047,QSQLMSG,*LIBL,nosub). :EDL. .*--------------------- :EHELP. .*------------------------------------------------------------------------------ :EPNLGRP. //ENDSRC /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDLUF) FILETYPE(*SRC) ENDCHAR('//ENDSRC') /* Claudio Neroni 23-06-2016 Versione UF di JRTVDDL. */ CMD PROMPT('UF Retrieve DDL') PARM KWD(OBJNAME) TYPE(*CHAR) LEN(258) MIN(1) + INLPMTLEN(25) PROMPT('Database Object Name') PARM KWD(OBJLIB) TYPE(*CHAR) LEN(258) + SPCVAL((*CURLIB) (*LIBL)) MIN(1) + INLPMTLEN(25) PROMPT('Database Object + Library') PARM KWD(OBJTYPE) TYPE(*CHAR) LEN(10) RSTD(*YES) + DFT(TABLE) VALUES(ALIAS CONSTRAINT + FUNCTION INDEX PERMISSION PROCEDURE + SCHEMA SEQUENCE TABLE TRIGGER TYPE + VARIABLE VIEW XSR) PROMPT('Database + Object Type') PARM KWD(SRCFILE) TYPE(*NAME) LEN(10) + DFT(QSQLSRC) SPCVAL((QSQLSRC) (QDDLSRC)) + PROMPT('Database Source File') PARM KWD(SRCLIB) TYPE(*NAME) LEN(10) DFT(*LIBL) + SPCVAL((*CURLIB) (*LIBL)) + PROMPT('Database Source File Library') PARM KWD(SRCMBR) TYPE(*NAME) LEN(10) + DFT(*OBJNAME) SPCVAL((*OBJNAME) (*FIRST) + (*LAST)) PROMPT('Database Source File + Member') PARM KWD(REPLACE) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*REPLACE) SPCVAL((*REPLACE 1) (*ADD + 0)) PROMPT('Replace (Member) Option') PARM KWD(STANDARDS) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(AS400) SPCVAL((AS400 0) (DB2 1) (ISO + 2)) PROMPT('Standards Option') PARM KWD(NAMING) TYPE(*CHAR) LEN(3) RSTD(*YES) + DFT(SYS) VALUES(SYS SQL) PROMPT('Naming + Option') PARM KWD(DATFMT) TYPE(*CHAR) LEN(3) RSTD(*YES) + DFT(*JOB) VALUES(ISO EUR JIS USA MDY DMY + YMD JUL) SPCVAL((*JOB JOB)) PROMPT('Date + Format') PARM KWD(DATSEP) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*JOB) SPCVAL((*JOB J) (*SLASH '/') + (*PERIOD '.') (*COMMA ',') (*DASH '-') + (*BLANK ' ')) PROMPT('Date Separator') PARM KWD(TIMFMT) TYPE(*CHAR) LEN(3) RSTD(*YES) + DFT(ISO) VALUES(ISO EUR JIS USA HMS) + PROMPT('Time Format') PARM KWD(TIMSEP) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*JOB) SPCVAL((*JOB J) (*COLON ':') + (*PERIOD '.') (*COMMA ',') (*BLANK ' ')) + PROMPT('Time Separator') PARM KWD(DECPOINT) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*COMMA) SPCVAL((*COMMA ',') (*PERIOD + '.')) VARY(*NO) PASSATR(*NO) + PROMPT('Decimal Point') PARM KWD(DROP) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Drop Option') PARM KWD(COMMENT) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Comment Option') PARM KWD(LABEL) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Label Option') PARM KWD(SYSNAM) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('System Name Option (RENAME)') PARM KWD(SEVLVL) TYPE(*INT4) DFT(0) RANGE(0 39) + SPCVAL((0) (10) (20) (30)) + PROMPT('Severity Level') PARM KWD(MSGLVL) TYPE(*INT4) DFT(0) RANGE(0 39) + PROMPT('Message Level') PARM KWD(STMFMT) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Statement Formatting Option') PARM KWD(HEADER) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Header Option') PARM KWD(TRIGGER) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Trigger Option') PARM KWD(CONSTRAINT) TYPE(*CHAR) LEN(1) + RSTD(*YES) DFT(*ALTER) SPCVAL((*ALTER 1) + (*CREATE 2) (*NO 0)) PROMPT('Constraint + Option') PARM KWD(PRIVILEGES) TYPE(*CHAR) LEN(1) + RSTD(*YES) DFT(*NO) SPCVAL((*NO 0) (*YES + 1)) PROMPT('Privileges Option') PARM KWD(CCSID) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('CCSID Option') PARM KWD(CRTORRPL) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*YES) SPCVAL((*YES 1) (*NO 0)) + PROMPT('Create or Replace Option') PARM KWD(OBFUSCATE) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Obfuscate Option') /** IL SEGUENTE PARAMETRO NON E' ANCORA GESTIBILE A V7R1 **/ PARM KWD(ACTIVATE) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*INACTIVE_IN_V7R1) SPCVAL((*NO 0) + (*YES 1) (*INACTIVE_IN_V7R1 1)) + PROMPT('Activate Row and Column Access') /** IL SEGUENTE PARAMETRO NON E' ANCORA GESTIBILE A V7R1 **/ PARM KWD(MASK) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*INACTIVE_IN_V7R1) SPCVAL((*NO 0) + (*YES 1) (*INACTIVE_IN_V7R1 1)) + PROMPT('Mask and Permission Option') PARM KWD(QUALIFIED) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*UNQUAL) SPCVAL((*UNQUAL 1) (*QUAL + 0)) PROMPT('Qualified Name Option') PARM KWD(ADDITIONAL) TYPE(*CHAR) LEN(1) + RSTD(*YES) DFT(*NO) SPCVAL((*NO 0) (*YES + 1)) PROMPT('Additional Index Option') PARM KWD(INDEXVIEW) TYPE(*CHAR) LEN(1) RSTD(*YES) + DFT(*NO) SPCVAL((*NO 0) (*YES 1)) + PROMPT('Index instead of View Option') //ENDSRC /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDL1) FILETYPE(*SRC) ENDCHAR('//ENDSRC') /* Claudio Neroni 02-02-2015 Modificato. */ /* Genera DDL. */ /* Aggiunti i campi usando la documentazione V7R2M0 al link */ /* http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/apis/qsqgnddl.htm?lang=en */ /* Riceve parametri dal comando. */ PGM PARM(&OBJNAME + &OBJLIB + &OBJTYPE + &SRCFILE + &SRCLIB + &SRCMBR + &REPLACE + &STANDARDS + &NAMING + &DATFMT + &DATSEP + &TIMFMT + &TIMSEP + &DECPOINT + &DROP + &COMMENT + &LABEL + &SYSNAM + &SEVLVL + &MSGLVL + &STMFMT + &HEADER + &TRIGGER + &CONSTRAINT + &PRIVILEGES + &CCSID + &CRTORRPL + &OBFUSCATE + &ACTIVATE + &MASK + &QUALIFIED + &ADDITIONAL + &INDEXVIEW + ) /* Definisce i parametri ricevuti dal comando. */ DCL VAR(&OBJNAME ) TYPE(*CHAR) LEN(258) DCL VAR(&OBJLIB ) TYPE(*CHAR) LEN(258) DCL VAR(&OBJTYPE ) TYPE(*CHAR) LEN(10) DCL VAR(&SRCFILE ) TYPE(*CHAR) LEN(10) DCL VAR(&SRCLIB ) TYPE(*CHAR) LEN(10) DCL VAR(&SRCMBR ) TYPE(*CHAR) LEN(10) DCL VAR(&REPLACE ) TYPE(*CHAR) LEN(1) DCL VAR(&STANDARDS ) TYPE(*CHAR) LEN(1) DCL VAR(&NAMING ) TYPE(*CHAR) LEN(3) DCL VAR(&DATFMT ) TYPE(*CHAR) LEN(3) DCL VAR(&DATSEP ) TYPE(*CHAR) LEN(1) DCL VAR(&TIMFMT ) TYPE(*CHAR) LEN(3) DCL VAR(&TIMSEP ) TYPE(*CHAR) LEN(1) DCL VAR(&DECPOINT ) TYPE(*CHAR) LEN(1) DCL VAR(&DROP ) TYPE(*CHAR) LEN(1) DCL VAR(&COMMENT ) TYPE(*CHAR) LEN(1) DCL VAR(&LABEL ) TYPE(*CHAR) LEN(1) DCL VAR(&SYSNAM ) TYPE(*CHAR) LEN(1) DCL VAR(&SEVLVL ) TYPE(*INT) LEN(4) DCL VAR(&MSGLVL ) TYPE(*INT) LEN(4) DCL VAR(&STMFMT ) TYPE(*CHAR) LEN(1) DCL VAR(&HEADER ) TYPE(*CHAR) LEN(1) DCL VAR(&TRIGGER ) TYPE(*CHAR) LEN(1) DCL VAR(&CONSTRAINT ) TYPE(*CHAR) LEN(1) DCL VAR(&PRIVILEGES ) TYPE(*CHAR) LEN(1) DCL VAR(&CCSID ) TYPE(*CHAR) LEN(1) DCL VAR(&CRTORRPL ) TYPE(*CHAR) LEN(1) DCL VAR(&OBFUSCATE ) TYPE(*CHAR) LEN(1) DCL VAR(&ACTIVATE ) TYPE(*CHAR) LEN(1) DCL VAR(&MASK ) TYPE(*CHAR) LEN(1) DCL VAR(&QUALIFIED ) TYPE(*CHAR) LEN(1) DCL VAR(&ADDITIONAL ) TYPE(*CHAR) LEN(1) DCL VAR(&INDEXVIEW ) TYPE(*CHAR) LEN(1) /* Definisce i parametri dopo eventuale modifica */ DCL VAR(&OBJNAME2 ) TYPE(*CHAR) LEN(258) DCL VAR(&OBJLIB2 ) TYPE(*CHAR) LEN(258) DCL VAR(&OBJTYPE2 ) TYPE(*CHAR) LEN(10) DCL VAR(&SRCFILE2 ) TYPE(*CHAR) LEN(10) DCL VAR(&SRCLIB2 ) TYPE(*CHAR) LEN(10) DCL VAR(&SRCMBR2 ) TYPE(*CHAR) LEN(10) DCL VAR(&REPLACE2 ) TYPE(*CHAR) LEN(1) DCL VAR(&STANDARDS2 ) TYPE(*CHAR) LEN(1) DCL VAR(&NAMING2 ) TYPE(*CHAR) LEN(3) DCL VAR(&DATFMT2 ) TYPE(*CHAR) LEN(3) DCL VAR(&DATSEP2 ) TYPE(*CHAR) LEN(1) DCL VAR(&TIMFMT2 ) TYPE(*CHAR) LEN(3) DCL VAR(&TIMSEP2 ) TYPE(*CHAR) LEN(1) DCL VAR(&DECPOINT2 ) TYPE(*CHAR) LEN(1) DCL VAR(&DROP2 ) TYPE(*CHAR) LEN(1) DCL VAR(&COMMENT2 ) TYPE(*CHAR) LEN(1) DCL VAR(&LABEL2 ) TYPE(*CHAR) LEN(1) DCL VAR(&SYSNAM2 ) TYPE(*CHAR) LEN(1) DCL VAR(&SEVLVL2 ) TYPE(*INT) LEN(4) DCL VAR(&MSGLVL2 ) TYPE(*INT) LEN(4) DCL VAR(&STMFMT2 ) TYPE(*CHAR) LEN(1) DCL VAR(&HEADER2 ) TYPE(*CHAR) LEN(1) DCL VAR(&TRIGGER2 ) TYPE(*CHAR) LEN(1) DCL VAR(&CONSTRAIN2 ) TYPE(*CHAR) LEN(1) DCL VAR(&PRIVILEGE2 ) TYPE(*CHAR) LEN(1) DCL VAR(&CCSID2 ) TYPE(*CHAR) LEN(1) DCL VAR(&CRTORRPL2 ) TYPE(*CHAR) LEN(1) DCL VAR(&OBFUSCATE2 ) TYPE(*CHAR) LEN(1) DCL VAR(&ACTIVATE2 ) TYPE(*CHAR) LEN(1) DCL VAR(&MASK2 ) TYPE(*CHAR) LEN(1) DCL VAR(&QUALIFIED2 ) TYPE(*CHAR) LEN(1) DCL VAR(&ADDITIONA2 ) TYPE(*CHAR) LEN(1) DCL VAR(&INDEXVIEW2 ) TYPE(*CHAR) LEN(1) /* Definisce valori recuperati dal sistema. */ DCL VAR(&RTVDATFMT) TYPE(*CHAR) LEN(4) DCL VAR(&RTVDATSEP) TYPE(*CHAR) LEN(1) DCL VAR(&RTVTIMSEP) TYPE(*CHAR) LEN(1) DCL VAR(&RTVTEXT) TYPE(*CHAR) LEN(50) /* Definisce messaggio e dati messaggio. */ DCL VAR(&MSGID) TYPE(*CHAR) LEN(7) DCL VAR(&MSGDATA) TYPE(*CHAR) LEN(500) /* Intercetta tutti gli errori saltando a fine con segnalazione errore. */ MONMSG MSGID(CPF0000) EXEC(GOTO CMDLBL(ERROR)) /* Trascive i parametri ricevuti. */ CHGVAR VAR(&OBJNAME2 ) VALUE(&OBJNAME ) CHGVAR VAR(&OBJLIB2 ) VALUE(&OBJLIB ) CHGVAR VAR(&OBJTYPE2 ) VALUE(&OBJTYPE ) CHGVAR VAR(&SRCFILE2 ) VALUE(&SRCFILE ) CHGVAR VAR(&SRCLIB2 ) VALUE(&SRCLIB ) CHGVAR VAR(&SRCMBR2 ) VALUE(&SRCMBR ) CHGVAR VAR(&REPLACE2 ) VALUE(&REPLACE ) CHGVAR VAR(&STANDARDS2) VALUE(&STANDARDS ) CHGVAR VAR(&NAMING2 ) VALUE(&NAMING ) CHGVAR VAR(&DATFMT2 ) VALUE(&DATFMT ) CHGVAR VAR(&DATSEP2 ) VALUE(&DATSEP ) CHGVAR VAR(&TIMFMT2 ) VALUE(&TIMFMT ) CHGVAR VAR(&TIMSEP2 ) VALUE(&TIMSEP ) CHGVAR VAR(&DECPOINT2 ) VALUE(&DECPOINT ) CHGVAR VAR(&DROP2 ) VALUE(&DROP ) CHGVAR VAR(&COMMENT2 ) VALUE(&COMMENT ) CHGVAR VAR(&LABEL2 ) VALUE(&LABEL ) CHGVAR VAR(&SYSNAM2 ) VALUE(&SYSNAM ) CHGVAR VAR(&SEVLVL2 ) VALUE(&SEVLVL ) CHGVAR VAR(&MSGLVL2 ) VALUE(&MSGLVL ) CHGVAR VAR(&STMFMT2 ) VALUE(&STMFMT ) CHGVAR VAR(&HEADER2 ) VALUE(&HEADER ) CHGVAR VAR(&TRIGGER2 ) VALUE(&TRIGGER ) CHGVAR VAR(&CONSTRAIN2) VALUE(&CONSTRAINT) CHGVAR VAR(&PRIVILEGE2) VALUE(&PRIVILEGES) CHGVAR VAR(&CCSID2 ) VALUE(&CCSID ) CHGVAR VAR(&CRTORRPL2 ) VALUE(&CRTORRPL ) CHGVAR VAR(&OBFUSCATE2) VALUE(&OBFUSCATE ) CHGVAR VAR(&ACTIVATE2 ) VALUE(&ACTIVATE ) CHGVAR VAR(&MASK2 ) VALUE(&MASK ) CHGVAR VAR(&QUALIFIED2) VALUE(&QUALIFIED ) CHGVAR VAR(&ADDITIONA2) VALUE(&ADDITIONAL) CHGVAR VAR(&INDEXVIEW2) VALUE(&INDEXVIEW ) /* Se richiesto nome membro source uguale ad oggetto, lo assume. */ IF COND(&SRCMBR *EQ *OBJNAME) THEN(DO) CHGVAR VAR(&SRCMBR2) VALUE(&OBJNAME) ENDDO /* Recupera il testo del file esaminato. */ RTVOBJD OBJ(&OBJLIB/&OBJNAME) OBJTYPE(*FILE) + TEXT(&RTVTEXT) /* Se non trova, segnala e abbandona. */ MONMSG MSGID(CPF0000) EXEC(DO) SNDPGMMSG MSG('File' *BCAT &OBJLIB *TCAT '/' *TCAT + &OBJNAME *BCAT 'non esiste.') MSGTYPE(*DIAG) GOTO CMDLBL(ERROR) ENDDO /* Se richiesto il formato data del job, lo recupera e lo annota. */ IF COND(&DATFMT = JOB) THEN(DO) RTVJOBA DATFMT(&RTVDATFMT) CHGVAR VAR(&DATFMT2) VALUE(%SST(&RTVDATFMT 2 3)) ENDDO /* Se richiesto il separatore data del job, lo recupera e lo annota. */ IF COND(&DATSEP = J) THEN(DO) RTVJOBA DATSEP(&RTVDATSEP) CHGVAR VAR(&DATSEP2) VALUE(&RTVDATSEP) ENDDO /* Se richiesto il separatore ora del job, lo recupera e lo annota. */ IF COND(&TIMSEP = J) THEN(DO) RTVJOBA TIMSEP(&RTVTIMSEP) CHGVAR VAR(&TIMSEP2) VALUE(&RTVTIMSEP) ENDDO /* Se il file source non esiste, segnala e abbandona. */ CHKOBJ OBJ(&SRCLIB2/&SRCFILE2) OBJTYPE(*FILE) MONMSG MSGID(CPF0000) EXEC(DO) SNDPGMMSG MSG('File source' *BCAT &SRCLIB2 *TCAT '/' + *TCAT &SRCFILE2 *BCAT 'non esiste.') + MSGTYPE(*DIAG) GOTO CMDLBL(ERROR) ENDDO /* Se il membro source non esiste, lo aggiunge. */ CHKOBJ OBJ(&SRCLIB2/&SRCFILE2) OBJTYPE(*FILE) + MBR(&SRCMBR2) MONMSG MSGID(CPF0000) EXEC(DO) ADDPFM FILE(&SRCLIB2/&SRCFILE2) MBR(&SRCMBR2) ENDDO /* Modifica il membro source. */ CHGPFM FILE(&SRCLIB2/&SRCFILE2) MBR(&SRCMBR2) + SRCTYPE(&OBJTYPE2) TEXT(&RTVTEXT) /* Chiama l'interfaccia api passando i parametri modificati. */ /* Riceve di ritorno il messaggio di errore. */ CALL PGM(JRTVDDL2) + PARM(&OBJNAME2 + &OBJLIB2 + &OBJTYPE2 + &SRCFILE2 + &SRCLIB2 + &SRCMBR2 + &SEVLVL2 + &REPLACE2 + &STMFMT2 + &DATFMT2 + &DATSEP2 + &TIMFMT2 + &TIMSEP2 + &NAMING2 + &DECPOINT2 + &STANDARDS2 + &DROP2 + &MSGLVL2 + &COMMENT2 + &LABEL2 + &HEADER2 + &TRIGGER2 + &CONSTRAIN2 + &SYSNAM2 + &PRIVILEGE2 + &CCSID2 + &CRTORRPL2 + &OBFUSCATE2 + &ACTIVATE2 + &MASK2 + &QUALIFIED2 + &ADDITIONA2 + &INDEXVIEW2 + &MSGID + &MSGDATA + ) /* Se esito favorevole, messaggia e ritorna. */ IF COND(&MSGID = ' ') THEN(DO) SNDPGMMSG MSG('Generate DDL.') RETURN ENDDO /* Se messaggio di errore SQL, messaggia e salta a errore. */ IF COND(%SST(&MSGID 1 3) = SQL) THEN(DO) SNDPGMMSG MSGID(&MSGID) MSGF(QSQLMSG) MSGDTA(&MSGDATA) + MSGTYPE(*DIAG) GOTO CMDLBL(ERROR) ENDDO /* Messaggia. */ SNDPGMMSG MSGID(&MSGID) MSGF(QCPFMSG) MSGDTA(&MSGDATA) + MSGTYPE(*DIAG) /* Errore. */ ERROR: /* Messaggia errore. */ SNDPGMMSG MSG('Generazione DDL fallita.') MSGTYPE(*DIAG) RETURN ENDPGM //ENDSRC /*----------------------------------------------------------------------------*/ //DATA FILE(JRTVDDL2) FILETYPE(*SRC) ENDCHAR('//ENDSRC') * Claudio Neroni 02-02-2015 Modificato. * Aggiunti i campi usando la documentazione V7R2M0 al link * http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_72/apis/qsqgnddl.htm?lang=en * * Generate SQL DDL for a database object. * No warranty implied. Use at your own risk. * H dftactgrp(*no) actgrp(*caller) D Template ds D DBObjName 258 PIObjName D DBObjLib 258 PIObjLib D DBObjType 10 PIObjType D DBSrcFile 10 PISrcFile D DBSrcLib 10 PISrcLib D DBSrcMbr 10 PISrcMbr D Severity 10i 0 PISeverity D Replace 1 PIReplace D StmtFmtOpt 1 PIStmtFmtOpt D DateFmt 3 PIDateFmt D DateSep 1 PIDateSep D TimeFmt 3 PITimeFmt D TimeSep 1 PITimeSep D NamingOpt 3 PINamingOpt D DecimalPt 1 PIDecimalPt D StdsOpt 1 PIStdsOpt D DropOpt 1 PIDropOpt D MsgLvl 10i 0 PIMsgLvl D CommentOpt 1 PICommentOpt D LabelOpt 1 PILabelOpt D HdrOpt 1 PIHdrOpt D TrgOpt 1 PITrgOpt D CnsOpt 1 PICnsOpt D SysNamOpt 1 PISysNamOpt 61 D PlgOpt 1 PIPlgOpt 61 D CcsOpt 1 PICcsOpt 61 D CrtRplOpt 1 PICrtRplOpt 61 D ObfOpt 1 PIObfOpt 61 D ActRowOpt 1 PIActRowOpt 61 D MskPerOpt 1 PIMskPerOpt 61 D QuaNamOpt 1 PIQuaNamOpt 61 D AddIdxOpt 1 PIAddIdxOpt 61 D IdxVieOpt 1 PIIdxVieOpt D Reserved 100 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* msgdta 1 D ErrorDS ds D BytesProv 1 4b 0 inz(116) D BytesAvail 5 8b 0 D ExceptionID 9 15 D ErReserved 16 16 D ExceptionData 17 116 D D GenDDL pr extpgm('QSQGNDDL') D Template 695 D Length 10i 0 D Format 8 D ErrorDS 116 D * Entry plist D JRTVDDL2 pr extpgm('JRTVDDL2') D PIObjName 258 D PIObjLib 258 D PIObjType 10 D PISrcFile 10 D PISrcLib 10 D PISrcMbr 10 D PISeverity 10i 0 inz(10) D PIReplace 1 D PIStmtFmtOpt 1 inz('0') D PIDateFmt 3 inz('ISO') D PIDateSep 1 D PITimeFmt 3 inz('ISO') D PITimeSep 1 D PINamingOpt 3 inz('SYS') D PIDecimalPt 1 inz('.') D PIStdsOpt 1 inz('0') D PIDropOpt 1 inz('0') D PIMsgLvl 10i 0 inz(0) D PICommentOpt 1 inz('1') D PILabelOpt 1 inz('1') D PIHdrOpt 1 inz('1') D PITrgOpt 1 D PICnsOpt 1 D PISysNamOpt 1 61 D PIPlgOpt 1 61 D PICcsOpt 1 61 D PICrtRplOpt 1 61 D PIObfOpt 1 61 D PIActRowOpt 1 61 D PIMskPerOpt 1 61 D PIQuaNamOpt 1 61 D PIAddIdxOpt 1 61 D PIIdxVieOpt 1 D PIError 7 D PImsgdta 100 D D JRTVDDL2 pi D PIObjName 258 PIObjName D PIObjLib 258 PIObjLib D PIObjType 10 PIObjType D PISrcFile 10 PISrcFile D PISrcLib 10 PISrcLib D PISrcMbr 10 PISrcMbr D PISeverity 10i 0 PISeverity D PIReplace 1 PIReplace D PIStmtFmtOpt 1 PIStmtFmtOpt D PIDateFmt 3 PIDateFmt D PIDateSep 1 PIDateSep D PITimeFmt 3 PITimeFmt D PITimeSep 1 PITimeSep D PINamingOpt 3 PINamingOpt D PIDecimalPt 1 PIDecimalPt D PIStdsOpt 1 PIStdsOpt D PIDropOpt 1 PIDropOpt D PIMsgLvl 10i 0 PIMsgLvl D PICommentOpt 1 PICommentOpt D PILabelOpt 1 PILabelOpt D PIHdrOpt 1 PIHdrOpt D PITrgOpt 1 PITrgOpt D PICnsOpt 1 PICnsOpt D PISysNamOpt 1 PISysNamOpt 61 D PIPlgOpt 1 PIPlgOpt 61 D PICcsOpt 1 PICcsOpt 61 D PICrtRplOpt 1 PICrtRplOpt 61 D PIObfOpt 1 PIObfOpt 61 D PIActRowOpt 1 PIActRowOpt 61 D PIMskPerOpt 1 PIMskPerOpt 61 D PIQuaNamOpt 1 PIQuaNamOpt 61 D PIAddIdxOpt 1 PIAddIdxOpt 61 D PIIdxVieOpt 1 PIIdxVieOpt D PIError 7 PIError D PImsgdta 100 C z-add PISeverity Severity C z-add PIMsgLvl MsgLvl C move *allx'00' ErReserved C move *allx'00' Reserved /free DBObjName = PIObjName; DBObjLib = PIObjLib; DBObjType = PIObjType; DBSrcFile = PISrcFile; DBSrcLib = PISrcLib; DBSrcMbr = PISrcMbr; // Severity = PISeverity; Replace = PIReplace; StmtFmtOpt = PIStmtFmtOpt; DateFmt = PIDateFmt; DateSep = PIDateSep; TimeFmt = PITimeFmt; TimeSep = PITimeSep; NamingOpt = PINamingOpt; DecimalPt = PIDecimalPt; StdsOpt = PIStdsOpt; DropOpt = PIDropOpt; // MsgLvl = PIMsgLvl; CommentOpt = PICommentOpt; LabelOpt = PILabelOpt; HdrOpt = PIHdrOpt; TrgOpt = PITrgOpt; CnsOpt = PICnsOpt; SysNamOpt = PISysNamOpt; 61 PlgOpt = PIPlgOpt; 61 CcsOpt = PICcsOpt; 61 CrtRplOpt = PICrtRplOpt; 61 ObfOpt = PIObfOpt; 61 ActRowOpt = PIActRowOpt; 61 MskPerOpt = PIMskPerOpt; 61 QuaNamOpt = PIQuaNamOpt; 61 AddIdxOpt = PIAddIdxOpt; 61 IdxVieOpt = PIIdxVieOpt; //ErReserved = *allx'00'; /end-free C**** move *allx'00' CommentOpt C**** move *allx'00' LabelOpt C**** move *allx'00' HdrOpt C**** move *allx'00' TrgOpt C**** move *allx'00' CnsOpt C**** move *allx'00' SysNamOpt C**** move *allx'00' PlgOpt C**** move *allx'00' CcsOpt C**** move *allx'00' CrtRplOpt C**** move *allx'00' ObfOpt C move *allx'00' ActRowOpt C move *allx'00' MskPerOpt C**** move *allx'00' QuaNamOpt C**** move *allx'00' AddIdxOpt C**** move *allx'00' IdxVieOpt /free GenDDL (Template: TemplateLength: TemplateFormat: ErrorDS); PiError = ExceptionID; Pimsgdta= ExceptionData; *inlr = *on; /end-free //ENDSRC //ENDBCHJOB