2002-06-25 如何將原有資料庫 Physical file DDS 格式轉成 SQL Script ?(Command GENDDL 利用 API QSQGNDDL)

如何將原有資料庫 Physical file DDS 格式轉成 SQL Script ?(Command GENDDL 利用 API QSQGNDDL)

利用 API  QSQGNDDL可將原有資料庫 Physical file DDS 格式轉成 SQL Script 。

File  : QRPGLESRC
Member: GENDDL01R
Type  : RPGLE
Usage : CRTBNDRPG GENDDL01R

      * 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

     C
     C                   Eval      DBObjName = PIObjName
     C                   Eval      DBObjLib  = PIObjLib
     C                   Eval      DBObjType = PIObjType
     C                   Eval      DBSrcFile = PISrcFile
     C                   Eval      DBSrcLib  = PISrcLib
     C                   Eval      DBSrcMbr  = PISrcMbr
     C                   if        (PIReplace = '1') or
     C                             (PIReplace = 'Y') or
     C                             (PIReplace = 'y')
     C                   Eval      Replace = '1'
     C                   else
     C                   Eval      Replace = '0'
     C                   EndIf
     C
     C                   CallP     GenDDL (Template: TemplateLength:
     C                                     TemplateFormat: ErrorDS)
     C                   Eval      PiError = ExceptionID
     C                   Eval      *inlr = *on



File  : QCLSRC
Member: GENDDL01C
Type  : CLP
Usage : CRTCLPGM GENDDL01C

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



File  : QCMDSRC
Member: GENDDL
Type  : CMD
Usage : CRTCMD CMD(GENDDL) PGM(GENDDL01C)

/******************************************************/
 /* 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?')