RE: Table Definitions DDS or SQL?

["Scott Feddersen" <Scott.Feddersen@xxxxxxxxxx>]

I like to use embedded SQL to keep my scripts and use an SQLRPGLE source
type.  There may be a better way to accomplish this but here is a
possible solution.  It helps me keep other information about the table
in one
Source member.  

Thanks to Charles for the tip on INCLUDING COLUMN DEFAULTS.

F** MCP0220    POS Sales Transaction Detail

H DFTNAME(MCP0220)

D CMD1            S            160    INZ('CHGPF FILE(MYLIBR/MCP0220)
TEXT-
D                                     (''POS Sales Transaction Detail
-
D                                          '') SIZE(20000000 30000
3000)') 
D CMD2            S            160    INZ('GRTOBJAUT OBJ(MYLIBR/MCP0220)
O-
D                                     BJTYPE(*FILE) USER(BILLSM DAWNS)
AUT-
D                                     (*USE)')

D CMD3            S            160    INZ('ENDJRNPF
FILE(MYLIBR/MCP0220)') 
D CMD4            S            160    INZ('STRJRNPF FILE(MYLIBR/MCP0220)
J-
D                                     RN(MYLIBR/SAJRN) IMAGES(*BOTH)')

D* Define prototype for QCMDEXC                              
DQCMDEXC          PR                  EXTPGM('QCMDEXC')      
D COMMAND                     5000A   OPTIONS(*VARSIZE) CONST
D LEN                           15P 5 CONST

 /FREE

  // Create new version of table

     EXEC SQL

        CREATE TABLE MYLIBR/MCP0220 AS (SELECT

           TRDTN,

           STORE, 
           REGNO,
           TRNUM
           LNSEQ,
           LNTYP,
           ATEXT,
           VDFLG,
           RTNFL,
           TAXFL,
           TRNFL,
           SCNFL,
           SKU,  
           UPC14,
           DIMCD,
           SIZCD,
           VENDR,
           STYLE,
           COLCD,
           DDEPT,
           CLASS,
           AMT01,
           AMT02,    
           QNTY,     
           BKCRD,    
           DSRSN,    
           PRCNT,    
           TYPE,     
           NUMBR,    
           AUTHCD,   
           BCEXP,    
           ACCT#,    
           CODE,     
           DLSTA,    
           TRNID,    
           VLDCD,    
           ATEXT2,   
           ENCRKY,   
           LAST4,    
           MNTTMS    
        FROM MCP0000)
        WITH NO DATA 
        INCLUDING COLUMN DEFAULTS;                          
  // Commit changes                                         
     EXEC SQL COMMIT;                                       
  // Execute command to change text on file                 
     CALLP QCMDEXC(CMD1:%SIZE(CMD1));                       
  // Monitor                                                
     MONITOR;                                               
  //    Execute command to grant authority on file          
        CALLP QCMDEXC(CMD2:%SIZE(CMD2));                    
  // On-error                                               
     ON-ERROR;                                              
  // End Monitor                                            
     ENDMON;                                     
  // Monitor                                     
     MONITOR;                                    
  //    Execute command to end journaling on file if it was started
        CALLP QCMDEXC(CMD3:%SIZE(CMD3));         
  // On-error                                    
     ON-ERROR;                                   
  // End Monitor                                 
     ENDMON;                                     
  // Execute command to start journaling on file to correct journal 
     CALLP QCMDEXC(CMD4:%SIZE(CMD4));            
  // Set last record indicator on                
     *INLR = *ON;                                
 /END-FREE


-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx
[mailto:rpg400-l-bounces@xxxxxxxxxxxx] On Behalf Of rob@xxxxxxxxx
Sent: Friday, October 06, 2006 1:58 PM
To: RPG programming on the AS400 / iSeries
Subject: Re: Table Definitions DDS or SQL?


I forget.  It's not like I am using PDM or something to use a numbered 
option to "compile" them.  Sometimes I leave it blank.

Care to submit a DCR to give us a member type that will actually inspire

the editors to care?  Would be nice to be able to verify them in WDSC. 
These are all used, but plain SQL isn't:
SQLC 
SQLCBL 
SQLCBLLE
SQLFTN 
SQLPLI 
SQLRPG 
SQLRPGLE



Rob Berendt