Using IBM Application Development Tools for z/OS and OS/390

558 Using IBM Application Development Tools for z/OS and OS/390

File Manager DB2 IVP databases installation

The IVP sample uses the classic DB2 database definition and load, as shown in

Example B-1.

Example: B-1 FMN2VER in FMN310.SFMNSAM1

//********************************************************************

//* *

//* FMN2VER JOB *

//* *

//* THIS JOB WILL DEFINE OBJECTS FOR VERIFICATION FILE MANAGER V3R1 *

//* DB2 FEATURE. *

//* *

//********************************************************************

//*

//* STEP 0: DROP THE SAMPLE DATABASE

//PH01S00 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(8,LT)

//STEPLIB DD DSN=DSN610.SDSNLOAD,DISP=SHR

//SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DBA1)

RUN PROGRAM(DSNTIAD) PLAN(DSNTIA61) -

LIB('DSN610.RUNLIB.LOAD')

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//SYSIN DD *

DROP DATABASE FMNDB2SD;

COMMIT ;

/*

//* STEP 1: CREATE SAMPLE STORAGE GROUPS, TABLESPACES

//PH01S01 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(8,LT)

//STEPLIB DD DSN=DSN610.SDSNLOAD,DISP=SHR

//SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DBA1)

RUN PROGRAM(DSNTIAD) PLAN(DSNTIA61) -

LIB('DSN610.RUNLIB.LOAD')

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//SYSIN DD *

CREATE DATABASE FMNDB2SD

STOGROUP SYSDEFLT

BUFFERPOOL BP0

CCSID EBCDIC;

CREATE TABLESPACE FMNDB2SD

IN FMNDB2SD

USING STOGROUP SYSDEFLT

PRIQTY 20

SECQTY 20

ERASE NO

LOCKSIZE PAGE LOCKMAX SYSTEM

BUFFERPOOL BP0

CLOSE NO

CCSID EBCDIC;

Appendix B. Database generation samples 559

COMMIT ;

CREATE TABLESPACE FMNDB2SE

IN FMNDB2SD

USING STOGROUP SYSDEFLT

PRIQTY 20

SECQTY 20

ERASE NO

NUMPARTS 4

(PART 1 USING STOGROUP SYSDEFLT

PRIQTY 12

SECQTY 12,

PART 3 USING STOGROUP SYSDEFLT

PRIQTY 12

SECQTY 12)

LOCKSIZE PAGE LOCKMAX SYSTEM

BUFFERPOOL BP0

CLOSE NO

COMPRESS YES

CCSID EBCDIC;

COMMIT ;

CREATE TABLESPACE FMNDB2SR

IN FMNDB2SD

USING STOGROUP SYSDEFLT

PRIQTY 20

SECQTY 20

ERASE NO

LOCKSIZE PAGE LOCKMAX SYSTEM

BUFFERPOOL BP0

CLOSE NO

CCSID EBCDIC;

COMMIT ;

//*

//*

//* STEP 2: CREATE SAMPLE TABLES, VIEWS

//PH01S02 EXEC PGM=IKJEFT01,DYNAMNBR=20,COND=(8,LT)

//STEPLIB DD DSN=DSN610.SDSNLOAD,DISP=SHR

//SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DBA1)

RUN PROGRAM(DSNTIAD) PLAN(DSNTIA61) -

LIB('DSN610.RUNLIB.LOAD')

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//SYSIN DD *

CREATE TABLE DAVINR1.DEPT

(DEPTNO CHAR(3) NOT NULL,

DEPTNAME vaRCHAR(36) NOT NULL,

MGRNO CHAR(6) ,

ADMRDEPT CHAR(3) NOT NULL,

LOCATION CHAR(16) ,

PRIMARY KEY(DEPTNO))

IN FMNDB2SD.FMNDB2SD

CCSID EBCDIC;

560 Using IBM Application Development Tools for z/OS and OS/390

CREATE UNIQUE INDEX DAVINR1.XDEPT1

ON DAVINR1.DEPT

(DEPTNO ASC)

USING STOGROUP SYSDEFLT

PRIQTY 12

ERASE NO

BUFFERPOOL BP0

CLOSE NO;

CREATE INDEX DAVINR1.XDEPT2

ON DAVINR1.DEPT

(MGRNO ASC)

USING STOGROUP SYSDEFLT

PRIQTY 12

ERASE NO

BUFFERPOOL BP0

CLOSE NO;

CREATE TYPE 2 INDEX DAVINR1.XDEPT3

ON DAVINR1.DEPT

(ADMRDEPT ASC)

USING STOGROUP SYSDEFLT

PRIQTY 12

ERASE NO

BUFFERPOOL BP0

CLOSE NO;

CREATE VIEW DAVINR1.VDEPT

AS SELECT ALL DEPTNO ,

DEPTNAME,

MGRNO ,

ADMRDEPT

FROM DAVINR1.DEPT;

CREATE VIEW DAVINR1.VHDEPT

AS SELECT ALL DEPTNO ,

DEPTNAME,

MGRNO ,

ADMRDEPT,

LOCATION

FROM DAVINR1.DEPT;

COMMIT ;

CREATE TABLE DAVINR1.EMP

(EMPNO CHAR(6) NOT NULL,

FIRSTNME vaRCHAR(12) NOT NULL,

MIDINIT CHAR(1) NOT NULL,

LASTNAME vaRCHAR(15) NOT NULL,

WORKDEPT CHAR(3) ,

PHONENO CHAR(4) CONSTRAINT NUMBER CHECK

(PHONENO >= '0000' AND PHONENO <= '9999'),

HIREDATE DATE ,

JOB CHAR(8) ,

EDLEVEL SMALLINT ,

SEX CHAR(1) ,

BIRTHDATE DATE ,

SALARY DECIMAL(9, 2) ,

BONUS DECIMAL(9, 2) ,

COMM DECIMAL(9, 2) ,

Appendix B. Database generation samples 561

PRIMARY KEY(EMPNO),

FOREIGN KEY RED (WORKDEPT) REFERENCES DAVINR1.DEPT

ON DELETE SET NULL)

EDITPROC DSN8EAE1

IN FMNDB2SD.FMNDB2SE

CCSID EBCDIC;

CREATE TYPE 2 UNIQUE INDEX DAVINR1.XEMP1

ON DAVINR1.EMP

(EMPNO ASC)

USING STOGROUP SYSDEFLT

PRIQTY 12

ERASE NO

CLUSTER

(PART 1 vaLUES('099999'),

PART 2 vaLUES('199999'),

PART 3 vaLUES('299999'),

PART 4 vaLUES('999999'))

BUFFERPOOL BP0

CLOSE NO;

CREATE INDEX DAVINR1.XEMP2

ON DAVINR1.EMP

(WORKDEPT ASC)

USING STOGROUP SYSDEFLT

PRIQTY 12

ERASE NO

BUFFERPOOL BP0

CLOSE NO;

CREATE VIEW DAVINR1.VEMP

AS SELECT ALL EMPNO ,

FIRSTNME,

MIDINIT ,

LASTNAME,

WORKDEPT

FROM DAVINR1.EMP;

COMMIT ;

ALTER TABLESPACE FMNDB2SD.FMNDB2SE

PART 3 COMPRESS NO;

ALTER TABLE DAVINR1.DEPT

FOREIGN KEY RDD (ADMRDEPT) REFERENCES DAVINR1.DEPT

ON DELETE CASCADE;

ALTER TABLE DAVINR1.DEPT

FOREIGN KEY RDE (MGRNO) REFERENCES DAVINR1.EMP

ON DELETE SET NULL;

CREATE TABLE DAVINR1.EDEPT LIKE DAVINR1.DEPT

IN FMNDB2SD.FMNDB2SR ;

CREATE TABLE DAVINR1.EEMP LIKE DAVINR1.EMP

IN FMNDB2SD.FMNDB2SR ;

ALTER TABLE DAVINR1.EDEPT

ADD RID CHAR(4);

ALTER TABLE DAVINR1.EDEPT

ADD TSTAMP TIMESTAMP;

ALTER TABLE DAVINR1.EEMP

ADD RID CHAR(4);