Appendix A: Schema JCL

//MFIXDBA  JOB (DB2SQL),'DB2 BATCH SQL',REGION=0M,CLASS=A,MSGCLASS=X
//*
//SCHEMA   EXEC PGM=IKJEFT01,DYNAMNBR=20 
//SYSTSPRT DD SYSOUT=*
//SYSOUT   DD SYSOUT=* 
//SYSPRINT DD SYSOUT=* 
//SYSUDUMP DD SYSOUT=* 
//SYSTSIN  DD *
 DSN SYSTEM(DB9R)
 RUN  PROGRAM(DSNTEP2) -
      PLAN(DSNTEP91) -
      LIB('DSN910.RUNLIB.LOAD')
 END
/*
//SYSIN    DD *

SELECT CURRENT SERVER FROM SYSIBM.SYSDUMMY1;

DROP DATABASE DBURSVIL;
COMMIT WORK;

CREATE DATABASE DBURSVIL BUFFERPOOL BP2 CCSID EBCDIC;
COMMIT WORK;

CREATE TABLESPACE DETESTLG IN DBURSVIL 
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.ACCOUNT
(
     COD_CUS                        CHAR(10) NOT NULL WITH DEFAULT,
     OFF_NUM                        DECIMAL(5,0) NOT NULL WITH DEFAULT,
     ACC_NUM                        DECIMAL(10,0) NOT NULL WITH DEFAULT,
     AMOUNT                         DECIMAL(12,0) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE TABLESPACE DETESTLG IN DBURSVIL 
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.CCARD
(
     OFF_NUM                        DECIMAL(5,0) NOT NULL WITH DEFAULT,
     ACC_NUM                        DECIMAL(10,0) NOT NULL WITH DEFAULT,
     CARD_TYPE                      CHAR(1) NOT NULL WITH DEFAULT,
     CARD_NUM                       CHAR(16) NOT NULL WITH DEFAULT,
     AVAILABILITY                   DECIMAL(25,0) NOT NULL WITH DEFAULT,
     EXP_DATE                       CHAR(8) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE TABLESPACE DETESTLG IN DBURSVIL 
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.CUSTOMER
(
     COD_CUS                        CHAR(10) NOT NULL WITH DEFAULT,
     NAME                           CHAR(40) NOT NULL WITH DEFAULT,
     SURNAME                        CHAR(40) NOT NULL WITH DEFAULT,
     DATE_OF_BIRTH                  CHAR(8) NOT NULL WITH DEFAULT,
     PLACE_OF_BIRTH                 CHAR(50) NOT NULL WITH DEFAULT,
     ADDRESS                        CHAR(50) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE TABLESPACE DETESTLG IN DBURSVIL 
    BUFFERPOOL BP2
    CCSID EBCDIC;

CREATE TABLE DELGT.OPERAT
(
     CARD_TYPE                      CHAR(1) NOT NULL WITH DEFAULT,
     CARD_NUM                       CHAR(16) NOT NULL WITH DEFAULT,
     ACCOUNT_OPER                   DECIMAL(25,0) NOT NULL WITH DEFAULT,
     DATE_OPRT                      CHAR(8) NOT NULL WITH DEFAULT
)  IN  DBURSVIL.DETESTLG AUDIT NONE  DATA CAPTURE NONE ;

CREATE INDEX DELGT.ACCOUNTX1
    ON DELGT.ACCOUNT(
        COD_CUS
 ) 
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

CREATE INDEX DELGT.CCARDX1
    ON DELGT.CCARD(
        OFF_NUM,ACC_NUM
 ) 
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

CREATE UNIQUE INDEX DELGT.CUSTOMERX1
    ON DELGT.CUSTOMER(
        COD_CUS
 ) 
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

CREATE INDEX DELGT.OPERATX1
    ON DELGT.OPERAT(
        CARD_TYPE,CARD_NUM
 ) 
 FREEPAGE 0
 PCTFREE 10
 BUFFERPOOL BP0
 CLOSE YES ;

/*
//