DB2 Server for VSE. Operations Guide / Handbooks (2004-2007)

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0004 RECORD TIME AFTER UNLOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

2NN2 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0005 REPRO SAM DATA TO VSAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// DLBL SQMDAT,’D.SQLDS730.PUBLIC.SQMHELP’,0,VSAM,

RECORDS=000100,RECSIZE=8240,DISP=(NEW,KEEP),CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(SQMDAT1

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240))) -

OUTFILE(SQMDAT

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240)))

Figure 56. DBSPACE Reorg Option 2 (Unload DBSPACE) - Sample Jobstream (Part

106

Control Center Operations Guide for VSE

Reorganization Job Streams

* $$ JOB JNM=DBREORG,CLASS=0,DISP=D

* $$ LST PRI=3

// JOB DBREORG MUM REORG DBSPACE - DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0001 RECORD TIME BEFORE DDL GENERATION

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

3NN1 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0002 UNLOAD DDL

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMDDL,’L.SQLDS730.PUBLIC.SQMHELP’,0,VSAM,

RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB01,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

3NN SQLDBA

SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0003 UNLOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(SQLDS730)’

CONNECT SQLDBA IDENTIFIED BY SQLDBAPW

COMMENT ’***** UNLOAD DBSPACE "PUBLIC"."SQMHELP" *****’

SET UPDATE STATISTICS OFF;

UNLOAD DBSPACE ("PUBLIC"."SQMHELP")

OUTFILE(SQMDAT1 BLKSZ(02048) PDEV(DASD))

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0004 RECORD TIME AFTER UNLOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

3NN2 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 57. DBSPACE Reorg Option 3 (Reorganize DBSPACE) - Sample Jobstream (Part

Appendix A. Reorganization Job Streams

107

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0005 REPRO VSAM DDL TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL VSAMIN,’L.SQLDS730.PUBLIC.SQMHELP’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDDL1,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYS008,’VSEPK3’,1,0,0000445488,00600

// ASSGN SYS008,DISK,VOL=’VSEPK3’,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN ) -

OUTFILE(SQMDDL1 -

ENV(RECFM(F ) -

BLKSZ(0080) -

RECSZ(0080)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0006 RECORD TIME BEFORE RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

3NN3 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0007 RELOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// ASSGN SYS004,SYS005

// ASSGN SYS020,SYSIPT

// DLBL IJSYSIN,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYSIPT,’VSEPK3’,1,0,0000445488,00600

ASSGN SYSIPT,DISK,VOL=’VSEPK3’,SHR

// ON $ABEND GOTO CLOSEIPT

// ON $CANCEL GOTO CLOSEIPT

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(SQLDS730)’

// IF $RC > 0006 THEN

// GOTO CLOSEIPT

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0008 RECORD TIME AFTER RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

3NN4 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 57. DBSPACE Reorg Option 3 (Reorganize DBSPACE) - Sample Jobstream (Part

108

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0009 DELETE VSAM DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’L.SQLDS730.PUBLIC.SQMHELP’,,VSAM,

CAT=SQMCAT,DISP=(OLD,DELETE)

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// RESET SYSLST

// GOTO THEEND

/. CLOSEIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0010 CLOSE SYSIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0011 REPRO SAM DATA TO VSAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// DLBL SQMDAT,’D.SQLDS730.PUBLIC.SQMHELP’,0,VSAM,

RECORDS=000100,RECSIZE=8240,DISP=(NEW,KEEP),CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(SQMDAT1

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240))) -

OUTFILE(SQMDAT

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240)))

IF LASTCC > 0000 -

THEN CANCEL JOB

// GOTO $EOJ

/. THEEND

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0012 THE END

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Figure 57. DBSPACE Reorg Option 3 (Reorganize DBSPACE) - Sample Jobstream (Part

Appendix A. Reorganization Job Streams

109

Reorganization Job Streams

* $$ JOB JNM=RELOAD,CLASS=0,DISP=D,NTFY=YES

* $$ LST PRI=3

// JOB RELOAD MUM RELOAD DBSPACE FROM DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0001 CHECK FOR DATA FILE

* * * * * * * * * * * * * * * * * * * * * * * * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’D.SQLDS730.PUBLIC.SQMHELP’,,VSAM,

CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// IF $RC > 0000 THEN

// GOTO NODAT

// RESET SYSLST

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0002 CHECK FOR DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’L.SQLDS730.PUBLIC.SQMHELP’,,VSAM,

CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// IF $RC > 0000 THEN

// GOTO NODDL

// RESET SYSLST

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0003 REPRO VSAM DATA TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL VSAMIN,’D.SQLDS730.PUBLIC.SQMHELP’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,SYS302,1,0,02985,150

// ASSGN SYS007,DISK,VOL=SYS302,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240))) -

OUTFILE(SQMDAT1 -

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0004 RECORD TIME BEFORE RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

4NN4 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 58. DBSPACE Reorg Option 4 (Reload DBSPACE) - Sample Jobstream (Part

110

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0005 REPRO VSAM DDL TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL VSAMIN,’L.SQLDS730.PUBLIC.SQMHELP’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDDL1,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYS008,SYS302,1,0,03835,100

// ASSGN SYS008,DISK,VOL=SYS302,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN ) -

OUTFILE(SQMDDL1 -

ENV(RECFM(F ) -

BLKSZ(0080) -

RECSZ(0080)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0006 RELOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,SYS302,1,0,02985,150

// ASSGN SYS007,DISK,VOL=SYS302,SHR

// ASSGN SYS006,SYS007

// ASSGN SYS020,SYSIPT

// DLBL IJSYSIN,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYSIPT,SYS302,1,0,03835,100

ASSGN SYSIPT,DISK,VOL=SYS302,SHR

// ON $ABEND GOTO CLOSEIPT

// ON $CANCEL GOTO CLOSIPT

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(SQLDS730)’

// IF $RC > 0000 THEN

// GOTO CLOSEIPT

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0007 RECORD TIME AFTER RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDS730

PUBLIC SQMHELP

4NN4 SQLDBA

SQLDBAPW

// GOTO THEEND

/. CLOSEIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * *

* STEP0008 CLOSE SYSIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * *

CLOSE SYSIPT,SYS020

// GOTO $EOJ

/. NODAT

// RESET SYSLST

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * *

* STEP0009 NO DATA FILE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * *

* RELOAD ABORTED: MISSING FILE = D.SQLDS730.PUBLIC.SQMHELP

// GOTO $EOJ

/. NODDL

// RESET SYSLST

Figure 58. DBSPACE Reorg Option 4 (Reload DBSPACE) - Sample Jobstream (Part

Appendix A. Reorganization Job Streams

111

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0010 NO DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* RELOAD ABORTED: MISSING FILE = L.SQLDS730.PUBLIC.SQMHELP

/. THEEND

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0011 THE END

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Figure 58. DBSPACE Reorg Option 4 (Reload DBSPACE) - Sample Jobstream (Part 3 of 3)

* $$ JOB JNM=SUMREORG,CLASS=A,DISP=H,PRI=3

* $$ LST PRI=3

// JOB SUMREORG SUM REORG DBSPACE - DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0001 RECORD TIME BEFORE DDL GENERATION

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// ON $RC = 4095 CONTINUE

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,DBNAME=SQLDBA,PROGX

NAME=SQB02,NDIRBUF=000010,NPAGBUF=000010’

%%SQLDBA

PUBLIC SAMPLE

3NY1 SQLDBA SQLDBAPW

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0002 UNLOAD DDL

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMDDL,’L.SQLDBA.PUBLIC.SAMPLE’,0,VSAM,

RECORDS=000500,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// ON $RC = 4095 CONTINUE

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,DBNAME=SQLDBA,PROGX

NAME=SQB01,NDIRBUF=000010,NPAGBUF=000010’

%%SQLDBA

PUBLIC SAMPLE

3NY SQLDBA SQLDBAPW

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0003 UNLOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,DBNAME=SQLDBA,PROGX

NAME=ARIDBS,NDIRBUF=000010,NPAGBUF=000010’

CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;

Figure 59. Sample Single User Mode DBSPACE Reorganization Jobstream (Part 1

112

Control Center Operations Guide for VSE

Reorganization Job Streams

COMMENT ’***** UNLOAD DBSPACE "PUBLIC"."SAMPLE" *****’

SET UPDATE STATISTICS OFF;

UNLOAD DBSPACE ("PUBLIC"."SAMPLE")

OUTFILE(SQMDAT1 BLKSZ(02048) PDEV(DASD))

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0004 RECORD TIME AFTER UNLOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDBA

PUBLIC SAMPLE

3NY2 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0005 REPRO VSAM DDL TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL VSAMIN,’L.SQLDBA.PUBLIC.SAMPLE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDDL1,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYS008,’VSEPK3’,1,0,0000445488,00600

// ASSGN SYS008,DISK,VOL=’VSEPK3’,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN ) -

OUTFILE(SQMDDL1 -

ENV(RECFM(F ) -

BLKSZ(0080) -

RECSZ(0080)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0006 RECORD TIME BEFORE RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%SQLDBA

PUBLIC SAMPLE

3NY3 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 59. Sample Single User Mode DBSPACE Reorganization Jobstream (Part 2

Appendix A. Reorganization Job Streams

113

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0007 RELOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// ASSGN SYS004,SYS005

// ASSGN SYS020,SYSIPT

// DLBL IJSYSIN,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYSIPT,’VSEPK3’,1,0,0000445488,00600

ASSGN SYSIPT,DISK,VOL=’VSEPK3’,SHR

// ON $ABEND GOTO CLOSEIPT

// ON $CANCEL GOTO CLOSEIPT

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,DBNAME=SQLDBA,PROGX

NAME=ARIDBS,NDIRBUF=000010,NPAGBUF=000010’

// IF $RC > 0006 AND $RC < 4095 THEN

// GOTO CLOSEIPT

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0008 RECORD TIME AFTER RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// ON $RC = 4095 CONTINUE

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=N,DBNAME=SQLDBA,PROGX

NAME=SQB02,NDIRBUF=000010,NPAGBUF=000010’

%%SQLDBA

PUBLIC SAMPLE

3NY4 SQLDBA SQLDBAPW

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0009 DELETE VSAM DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’L.SQLDBA.PUBLIC.SAMPLE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,DELETE)

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// RESET SYSLST

// GOTO THEEND

/. CLOSEIPT

Figure 59. Sample Single User Mode DBSPACE Reorganization Jobstream (Part 3

114

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0010 CLOSE SYSIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0011 REPRO SAM DATA TO VSAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000423488,22000

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// DLBL SQMDAT,’D.SQLDBA.PUBLIC.SAMPLE’,0,VSAM,

RECORDS=000100,RECSIZE=8240,DISP=(NEW,KEEP),CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(SQMDAT1

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240))) -

OUTFILE(SQMDAT

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240)))

IF LASTCC > 0000 -

THEN CANCEL JOB

// GOTO $EOJ

/. THEEND

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0012 THE END

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* $$ EOJ

Figure 59. Sample Single User Mode DBSPACE Reorganization Jobstream (Part 4

Appendix A. Reorganization Job Streams

115

Reorganization Job Streams

$ $$ JOB JNM=TAPREORG,CLASS=0,DISP=D,PRI=9

* $$ LST PRI=3

// JOB TAPREORG SUM REORG DBSPACE - DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0001 RECORD TIME BEFORE DDL GENERATION

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// TLBL ARIARCH

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// ON $RC = 4095 CONTINUE

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=A,DBNAME=DB2710,PROGX

NAME=SQB02,NDIRBUF=000010,NPAGBUF=000015’

%%DB2710

PUBLIC SAMPLE

3NY1 SQLDBA SQLDBAPW

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0002 UNLOAD DDL

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// TLBL ARIARCH

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMDDL,’L.DB2710.PUBLIC.SAMPLE’,0,VSAM,

RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// ON $RC = 4095 CONTINUE

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=A,DBNAME=DB2710,PROGX

NAME=SQB01,NDIRBUF=000010,NPAGBUF=000015’

%%DB2710

PUBLIC SAMPLE

3NY SQLDBA SQLDBAPW

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0003 UNLOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// TLBL ARIARCH

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=A,DBNAME=DB2710,PROGX

NAME=ARIDBS,NDIRBUF=000010,NPAGBUF=000015’

CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;

COMMENT ’***** UNLOAD DBSPACE "PUBLIC"."SAMPLE" *****’

SET UPDATE STATISTICS OFF;

UNLOAD DBSPACE ("PUBLIC"."SAMPLE")

OUTFILE(SQMDAT1 BLKSZ(02048) PDEV(DASD))

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

Figure 60. Sample Single User Mode DBSPACE Reorganization using Tape (Part 1

116

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0004 RECORD TIME AFTER UNLOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%DB2710

PUBLIC SAMPLE

3NY2 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0005 REPRO VSAM DDL TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL VSAMIN,’L.DB2710.PUBLIC.SAMPLE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDDL1,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYS008,’VSEPK3’,1,0,0000239238,00900

// ASSGN SYS008,DISK,VOL=’VSEPK3’,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN ) -

OUTFILE(SQMDDL1 -

ENV(RECFM(F ) -

BLKSZ(0080) -

RECSZ(0080)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

* STEP0006 RECORD TIME BEFORE RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* * * * *

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB02,SIZE=AUTO

%%DB2710

PUBLIC SAMPLE

3NY3 SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 60. Sample Single User Mode DBSPACE Reorganization using Tape (Part 2

Appendix A. Reorganization Job Streams

117

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0007 RELOAD DBSPACE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// TLBL ARIARCH

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// ASSGN SYS004,SYS005

// ASSGN SYS020,SYSIPT

// DLBL IJSYSIN,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYSIPT,’VSEPK3’,1,0,0000239238,00900

ASSGN SYSIPT,DISK,VOL=’VSEPK3’,SHR

// ON $ABEND GOTO CLOSEIPT

// ON $CANCEL GOTO CLOSEIPT

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=A,DBNAME=DB2710,PROGX

NAME=ARIDBS,NDIRBUF=000010,NPAGBUF=000015’

// IF $RC > 0006 AND $RC < 4095 THEN

// GOTO CLOSEIPT

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0008 RECORD TIME AFTER RELOAD

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// TLBL ARIARCH

// DLBL SQMPARM,’SQLMSTR.REORG.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMRDAT,’SQLMSTR.REORG.DATA’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// ON $RC = 4095 CONTINUE

// EXEC ARISQLDS,SIZE=AUTO,PARM=’SYSMODE=S,LOGMODE=A,DBNAME=DB2710,PROGX

NAME=SQB02,NDIRBUF=000010,NPAGBUF=000015’

%%DB2710

PUBLIC SAMPLE

3NY4

// IF $RC > 0000 AND $RC < 4095 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0009 DELETE VSAM DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’L.DB2710.PUBLIC.SAMPLE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,DELETE)

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// RESET SYSLST

// GOTO THEEND

/. CLOSEIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0010 CLOSE SYSIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CLOSE SYSIPT,SYS020

Figure 60. Sample Single User Mode DBSPACE Reorganization using Tape (Part 3

118

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0011 REPRO SAM DATA TO VSAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// DLBL SQMDAT,’D.DB2710.PUBLIC.SAMPLE’,0,VSAM,

RECORDS=000100,RECSIZE=8240,DISP=(NEW,KEEP),CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(SQMDAT1

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240))) -

OUTFILE(SQMDAT

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240)))

IF LASTCC > 0000 -

THEN CANCEL JOB

GOTO $EOJ

THEEND

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

STEP0012 THE END

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Figure 60. Sample Single User Mode DBSPACE Reorganization using Tape (Part 4 of 4)

Appendix A. Reorganization Job Streams

119

Reorganization Job Streams

* $$ JOB JNM=TBUNLOAD,DISP=D

* $$ LST CLASS=A,PRI=3

// JOB TBUNLOAD UNLOAD TABLE "EXAMPLE"."ROUTINE" - DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0001 UNLOAD DDL

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMTPRM,’SQLMSTR.TABLE.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMDDL,’L.DB2710.EXAMPLE.ROUTINE’,0,VSAM,

RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB60,SIZE=AUTO

%%DB2710

EXAMPLE ROUTINE

2 N SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0002 UNLOAD TABLE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(DB2710)’

CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;

COMMENT ’***** UNLOAD TABLE "EXAMPLE"."ROUTINE" *****’

SET UPDATE STATISTICS OFF;

UNLOAD TABLE ("EXAMPLE"."ROUTINE")

OUTFILE(SQMDAT1 BLKSZ(02048) PDEV(DASD))

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0003 REPRO SAM DATA TO VSAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// DLBL SQMDAT,’D.DB2710.EXAMPLE.ROUTINE’,0,VSAM,

RECORDS=000100,RECSIZE=8240,DISP=(NEW,KEEP),CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(SQMDAT1

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240))) -

OUTFILE(SQMDAT

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240)))

Figure 61. TABLE Reorg Option 2 (Unload TABLE) - Sample Jobstream

120

Control Center Operations Guide for VSE

Reorganization Job Streams

* $$ JOB JNM=TBREORG,CLASS=0,DISP=D

* $$ LST CLASS=A,PRI=3

// JOB TBREORG REORG TABLE "EXAMPLE"."ROUTINE" - DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0001 UNLOAD DDL

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMTPRM,’SQLMSTR.TABLE.PARMS’,,VSAM,CAT=SQMCAT

// DLBL SQMDDL,’L.DB2710.EXAMPLE.ROUTINE’,0,VSAM,

RECORDS=001000,RECSIZE=80,DISP=(NEW,KEEP),CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB60,SIZE=AUTO

%%DB2710

EXAMPLE ROUTINE

3NN SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0002 UNLOAD TABLE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(DB2710)’

CONNECT SQLDBA IDENTIFIED BY SQLDBAPW;

COMMENT ’***** UNLOAD TABLE "EXAMPLE"."ROUTINE" *****’

SET UPDATE STATISTICS OFF;

UNLOAD TABLE ("EXAMPLE"."ROUTINE")

OUTFILE(SQMDAT1 BLKSZ(02048) PDEV(DASD))

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 62. TABLE Reorg Option 3 (Reorganize TABLE) - Sample Jobstream (Part

Appendix A. Reorganization Job Streams

121

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0003 REPRO VSAM DDL TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL VSAMIN,’L.DB2710.EXAMPLE.ROUTINE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDDL1,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYS008,’VSEPK3’,1,0,0000239238,00900

// ASSGN SYS008,DISK,VOL=’VSEPK3’,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN ) -

OUTFILE(SQMDDL1 -

ENV(RECFM(F ) -

BLKSZ(0080) -

RECSZ(0080)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * *

* STEP0004 RELOAD TABLE

* * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// ASSGN SYS004,SYS005

// ASSGN SYS020,SYSIPT

// DLBL IJSYSIN,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYSIPT,’VSEPK3’,1,0,0000239238,00900

ASSGN SYSIPT,DISK,VOL=’VSEPK3’,SHR

// ON $ABEND GOTO CLOSEIPT

// ON $CANCEL GOTO CLOSEIPT

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME()’

// IF $RC > 0006 THEN

// GOTO CLOSEIPT

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * *

* STEP0005 DELETE PARAMETER RECORD

* * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMTPRM,’SQLMSTR.TABLE.PARMS’,,VSAM,CAT=SQMCAT

// ASSGN SYS005,SYSRDR

// ASSGN SYS011,SYSLST

// EXEC SQB62,SIZE=AUTO

%%DB2710

EXAMPLE ROUTINE

NN SQLDBA SQLDBAPW

// IF $RC > 0000 THEN

// GOTO $EOJ

Figure 62. TABLE Reorg Option 3 (Reorganize TABLE) - Sample Jobstream (Part

122

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0006 DELETE VSAM DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’L.DB2710.EXAMPLE.ROUTINE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,DELETE)

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// RESET SYSLST

// GOTO THEEND

/. CLOSEIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0007 CLOSE SYSIPT

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

CLOSE SYSIPT,SYS020

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0008 REPRO SAM DATA TO VSAM

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// DLBL SQMDAT,’D.DB2710.EXAMPLE.ROUTINE’,0,VSAM,

RECORDS=000100,RECSIZE=8240,DISP=(NEW,KEEP),CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(SQMDAT1

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240))) -

OUTFILE(SQMDAT

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240)))

IF LASTCC > 0000 -

THEN CANCEL JOB

// GOTO $EOJ

/. THEEND

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0009 THE END

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *

Figure 62. TABLE Reorg Option 3 (Reorganize TABLE) - Sample Jobstream (Part

Appendix A. Reorganization Job Streams

123

Reorganization Job Streams

* $$ JOB JNM=TBREORG,CLASS=0,DISP=D

* $$ LST CLASS=A,PRI=3

// JOB TBREORG RELOAD TABLE "EXAMPLE"."ROUTINE"

DISK

// OPTION LOG

* * * * * * * * * * * * * * * * * * * * * * * * *

* * *

* STEP0001 CHECK FOR DATA FILE

* * * * * * * * * * * * * * * * * * * * * * * * *

* * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’D.DB2710.EXAMPLE.ROUTINE’,,VSAM,

CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// IF $RC > 0000 THEN

// GOTO NODAT

// RESET SYSLST

* * * * * * * * * * * * * * * * * * * * * * * * *

* * *

* STEP0002 CHECK FOR DDL FILE

* * * * * * * * * * * * * * * * * * * * * * * * *

* * *

// ASSGN SYSLST,IGN

// DLBL FILEIN,’L.DB2710.EXAMPLE.ROUTINE’,,VSAM,

CAT=SQMCAT

// EXEC IDCAMS,SIZE=AUTO

PRINT INFILE(FILEIN) -

COUNT(1)

// IF $RC > 0000 THEN

// GOTO NODDL

// RESET SYSLST

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0003 REPRO VSAM DATA TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL VSAMIN,’D.DB2710.EXAMPLE.ROUTINE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN

ENV(RECFM(VB) -

BLKSZ(8248) -

RECSZ(8240))) -

OUTFILE(SQMDAT1 -

ENV(RECFM(SB) -

BLKSZ(2048) -

RECSZ(8240)))

IF LASTCC > 0000 -

THEN CANCEL JOB

Figure 63. TABLE Reorg Option 4 (Reload TABLE) - Sample Jobstream (Part 1

124

Control Center Operations Guide for VSE

Reorganization Job Streams

* * * * * * * * * * * * * * * * * * * * * * * * *

* STEP0004 REPRO VSAM DDL TO SAM

* * * * * * * * * * * * * * * * * * * * * * * * *

// DLBL VSAMIN,’L.DB2710.EXAMPLE.ROUTINE’,,VSAM,

CAT=SQMCAT,DISP=(OLD,KEEP)

// DLBL SQMDDL1,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYS008,’VSEPK3’,1,0,0000239238,00900

// ASSGN SYS008,DISK,VOL=’VSEPK3’,SHR

// EXEC IDCAMS,SIZE=AUTO

REPRO INFILE(VSAMIN ) -

OUTFILE(SQMDDL1 -

ENV(RECFM(F ) -

BLKSZ(0080) -

RECSZ(0080)))

IF LASTCC > 0000 -

THEN CANCEL JOB

* * * * * * * * * * * * * * * * * * * * * * * *

* STEP0005 RELOAD TABLE

* * * * * * * * * * * * * * * * * * * * * * * *

// DLBL SQMDAT1,’SQLMSTR.DATA.FILE1’,0,SD

// EXTENT SYS007,’VSEPK3’,1,0,0000201438,06300

// ASSGN SYS007,DISK,VOL=’VSEPK3’,SHR

// ASSGN SYS006,SYS007

// ASSGN SYS020,SYSIPT

// DLBL IJSYSIN,’SQLMSTR.DDL.FILE1’,0,SD

// EXTENT SYSIPT,’VSEPK3’,1,0,0000239238,00900

ASSGN SYSIPT,DISK,VOL=’VSEPK3’,SHR

// ON $ABEND GOTO CLOSEIPT

// ON $CANCEL GOTO CLOSEIPT

// EXEC ARIDBS,SIZE=AUTO,PARM=’DBNAME(DB2710)’

// IF $RC > 0006 THEN

// GOTO CLOSEIPT

CLOSE SYSIPT,SYS020

// GOTO THEEND

/. CLOSEIPT

* * * * * * * * * * * * * * * * * * * * * * * *

* STEP0006 CLOSE SYSIPT

* * * * * * * * * * * * * * * * * * * * * * * *

CLOSE SYSIPT,SYS020

// GOTO $EOJ

/. NODAT

// RESET SYSLST

Figure 63. TABLE Reorg Option 4 (Reload TABLE) - Sample Jobstream (Part 2

Appendix A. Reorganization Job Streams

125

Appendix B. DBSPACE and Table Reorganization Tool Related

Files

A DBSPACE reorganization job involves several files. Depending on the options

chosen, some or all of the following files are used:

Filename

File ID & Description

SQMPARM

″SQLMSTR.REORG.PARMS″

This is the DBSPACE Reorganization parameter file. The job

submission programs write a record to this file whenever a job is

submitted. The SQMPARM record contains REORG parameters

and is read by the batch DDL generation and timekeeping

programs. SQMPARM is a VSAM KSDS file whose key is

composed of database name, owner, DBSPACE name, and option.

SQMPARM is defined during installation and resides on the

Control Center user catalog. The record is deleted when the

REORG completes.

SQMRDAT

″SQLMSTR.REORG.DATA″

This file holds statistical data relating to a DBSPACE

unload/reload. At job end, the SQLMAINT table is updated with

this data. SQMRDAT is used for Options 2, 3, and 4 of the

reorganization tool. It is a VSAM KSDS file whose key is composed

of database name, owner, and DBSPACENAME. The file is defined

during installation and resides on the Control Center user catalog.

SQMTPARM

″SQLMSTR.TABLE.PARMS″

This is the Table Reorganization parameter file. The job submission

programs write a record to this file whenever a job is submitted.

The SQMTPARM record contains REORG parameters and is read

by the batch DDL generation and timekeeping programs.

SQMTPARM is a VSAM KSDS file. It is defined during installation

and resides in the Control Center user catalog.

SQMDDL

File ID built dynamically during job submission.

SQMDDL is a VSAM-managed SAM file that is used to contain the

DDL created by SQB01, the batch DDL generation program. The

file id is composed of a concatenation of:

″L″ - (indicates DDL)

2. Database name

3. DBSPACE owner name

4. DBSPACE name

File size is defined by the DDL STMTS parameter on the DBSPACE

REORGANIZATION screen (defaults to 1000 80-byte records). This

file resides on the Control Center user catalog and remains there

until it is deleted by a successful REORGANIZE DBSPACE or

deleted specifically by the user.

SQMDATn

″SQLMSTR.DATA.FILEn″

127

DBSPACE Reorganization Tool Related Files

This SAM file is used to hold the output of the UNLOAD

DBSPACE step when DISK is selected as the unload media. ″n″

corresponds to the FILE # parameter that appears on the DBSPACE

REORGANIZATION UTILITY screen. Valid values are 1 to 3. This

file is defined from the WORK FILE LABEL DEFINITION facility

(Option 4 of the Main Menu). It is used in Options 2 and 3 of the

reorganization tool.

SQMDDLn

″SQLMSTR.DDL.FILEn″

This SAM file is required to contain the DDL extracted by SQB01,

the batch DDL generator. It is used as the DBSU command input

file in the RELOAD step. Because DBSU expects commands to

come from SYSIPT and a VSAM file cannot be assigned to SYSIPT,

the VSAM SQMDDL file is REPRO’d to SQMDDLn. ″n″

corresponds to the FILE # parameter on the DBSPACE

REORGANIZATION UTILITY screen. Valid values are 1 to 3. This

file is defined from the WORK FILE LABEL DEFINITION facility

and is used in Options 3 and 4 of the reorganization tool.

SQMDAT

Built dynamically by the submit program.

SQMDAT is a VSAM-managed SAM file that is used to contain the

DBSPACE data unloaded from DBSU in the UNLOAD DBSPACE

step. The file id is composed of a concatenation of:

″D″ - (indicates data)

2. Database name

3. DBSPACE owner name

4. DBSPACE name

File size is computed from DBSPACE catalog information. This file

is only created when a user selects UNLOAD DBSPACE (Option 2)

and does not specify tape. This file is also created if an error occurs

during the RELOAD step of a REORGANIZE DBSPACE from disk

(Option 3). In effect, this file is a backup copy of the unloaded

DBSPACE. It can be used as input to a RELOAD DBSPACE

(Option 4). SQMDAT resides on the Control Center user catalog

and remains there until the same DBSPACE is unloaded again or

the file is specifically deleted.

SQMTAPE

Specified by the user.

SQMTAPE is used when the user selects tape media by entering a

TAPE FILE NAME on the DBSPACE REORGANIZATION UTILITY

screen. This file must be defined to Control Center from the WORK

FILE LABEL DEFINITION facility (Option 4 of the Main Menu).

SQMMESG

″SQLMSTR.MESSAGES″

SQMMESG is a VSAM KSDS file that is used to contain Control

Center error message text. The key is a 4-digit number. SQMMESG

is defined during installation and resides on the Control Center

User catalog.

SQMWORK

″SQLMSTR.WORK.FILES″

SQMWORK is a VSAM KSDS file that holds the ASSGN, DLBL,

EXTENT, and TLBL statements that define your Control Center

work files. The key is composed of a 17-character file ID and a

128

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool Related Files

COMMENT ’**************** CONTROL CENTER DBSPACE REORG **************

COMMENT ’*

Database: SQLDBA

*’

COMMENT ’*

DBSPACE: "PUBLIC"."SAMPLE"

*’

COMMENT ’*

Date: 06/30/00 09:21:58

*’

COMMENT ’********************************************************’

CONNECT "SQLMSTR" IDENTIFIED BY ********;

COMMIT WORK;

GRANT DBA TO "SQLREORG" IDENTIFIED BY "PU24L5AR";

CONNECT "SQLREORG" IDENTIFIED BY "PU24L5AR";

COMMIT WORK;

COMMENT ’*************

Drop DBSPACE

*************’

SET ERRORMODE (CONTINUE)

DROP DBSPACE "PUBLIC"."SAMPLE"

;

COMMIT WORK;

SET ERRORMODE (OFF)

SET AUTOCOMMIT (ON)

COMMENT ’*************

Acquire DBSPACE

*************’

ACQUIRE PUBLIC DBSPACE NAMED "PUBLIC"."SAMPLE"

(PAGES =

512, PCTINDEX = 33,

PCTFREE = 10, NHEADER = 8,

STORPOOL =

1, LOCK = PAGE

);

COMMENT ’*************

Create

Tables

*************’

CREATE TABLE "SQLDBA"."ACTIVITY" (

"ACTNO" SMALLINT

NOT NULL

,"ACTKWD" CHAR(6)

NOT NULL

,"ACTDESC" VARCHAR(20)

NOT NULL

) IN "PUBLIC"."SAMPLE"

;

CREATE TABLE "SQLDBA"."DEPARTMENT" (

"DEPTNO" CHAR(3)

NOT NULL

,"DEPTNAME" VARCHAR(36)

NOT NULL

,"MGRNO" CHAR(6)

,"ADMRDEPT" CHAR(3)

NOT NULL

) IN "PUBLIC"."SAMPLE"

;

CREATE TABLE "SQLDBA"."EMP_ACT" (

"EMPNO" CHAR(6)

NOT NULL

,"PROJNO" CHAR(6)

NOT NULL

,"ACTNO" SMALLINT

NOT NULL

,"EMPTIME" DECIMAL(5,2)

,"EMSTDATE" DATE

,"EMENDATE" DATE

) IN "PUBLIC"."SAMPLE"

;

CREATE TABLE "SQLDBA"."EMPLOYEE" (

"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)

,"HIREDATE" DATE

,"JOB" CHAR(8)

,"EDLEVEL" SMALLINT

NOT NULL

,"SEX" CHAR(1)

,"BIRTHDATE" DATE

,"SALARY" DECIMAL(9,2)

,"BONUS" DECIMAL(9,2)

,"COMM" DECIMAL(9,2)

) IN "PUBLIC"."SAMPLE"

;

CREATE TABLE "SQLDBA"."PROJ_ACT" (

"PROJNO" CHAR(6)

NOT NULL

,"ACTNO" SMALLINT

NOT NULL

,"ACSTAFF" DECIMAL(5,2)

,"ACSTDATE" DATE

NOT NULL

Figure 64. Sample Generated DDL File (Part

130

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool Related Files

,"ACENDATE" DATE

) IN "PUBLIC"."SAMPLE"

;

CREATE TABLE "SQLDBA"."PROJECT" (

"PROJNO" CHAR(6)

NOT NULL

,"PROJNAME" VARCHAR(24)

NOT NULL

,"DEPTNO" CHAR(3)

,"RESPEMP" CHAR(6)

,"PRSTAFF" DECIMAL(5,2)

,"PRSTDATE" DATE

,"PRENDATE" DATE

,"MAJPROJ" CHAR(6)

) IN "PUBLIC"."SAMPLE"

;

COMMENT ’*************

Deactivated Primary Keys

*************’

COMMENT ’*************

Inactive Unique Constraints

*************’

COMMENT ’*************

Reload Tables

*************’

RELOAD TABLE("SQLDBA"."ACTIVITY")

PURGE

INTABLE("SQLDBA"."ACTIVITY")

INFILE(DBSFILE);

RELOAD TABLE("SQLDBA"."DEPARTMENT")

PURGE

INTABLE("SQLDBA"."DEPARTMENT")

INFILE(DBSFILE);

RELOAD TABLE("SQLDBA"."EMP_ACT")

PURGE

INTABLE("SQLDBA"."EMP_ACT")

INFILE(DBSFILE);

RELOAD TABLE("SQLDBA"."EMPLOYEE")

PURGE

INTABLE("SQLDBA"."EMPLOYEE")

INFILE(DBSFILE);

RELOAD TABLE("SQLDBA"."PROJ_ACT")

PURGE

INTABLE("SQLDBA"."PROJ_ACT")

INFILE(DBSFILE);

RELOAD TABLE("SQLDBA"."PROJECT")

PURGE

INTABLE("SQLDBA"."PROJECT")

INFILE(DBSFILE);

ALTER DBSPACE "PUBLIC"."SAMPLE"

(PCTFREE

0);

COMMENT ’*************

Comment Tables

*************’

COMMENT ’*************

Comment Columns

*************’

COMMENT ’*************

Primary Cluster Keys

*************’

ALTER TABLE "SQLDBA"."ACTIVITY"

ADD PRIMARY KEY

("ACTNO" )

PCTFREE =

10;

ALTER TABLE "SQLDBA"."DEPARTMENT"

ADD PRIMARY KEY

("DEPTNO" )

PCTFREE =

10;

ALTER TABLE "SQLDBA"."EMPLOYEE"

ADD PRIMARY KEY

("EMPNO" )

PCTFREE =

10;

ALTER TABLE "SQLDBA"."PROJ_ACT"

Figure 64. Sample Generated DDL File (Part 2 of 4)

Appendix B. DBSPACE and Table Reorganization Tool Related Files

131

DBSPACE Reorganization Tool Related Files

ADD PRIMARY KEY

("PROJNO" ,

"ACTNO" ,

"ACSTDATE" )

PCTFREE =

10;

ALTER TABLE "SQLDBA"."PROJECT"

ADD PRIMARY KEY

("PROJNO" )

PCTFREE =

10;

COMMENT ’************* Clustering Unique Constraints

*************’

COMMENT ’*************

Create Indexes

*************’

CREATE INDEX "SQLDBA"."PROJNOIN"

ON "SQLDBA"."EMP_ACT"

("PROJNO" )

PCTFREE =

10;

CREATE INDEX "SQLDBA"."DEPTNOI"

ON "SQLDBA"."PROJECT"

("DEPTNO" )

PCTFREE =

10;

CREATE INDEX "SQLDBA"."EMPNOIN"

ON "SQLDBA"."EMP_ACT"

("EMPNO" )

PCTFREE =

10;

CREATE INDEX "SQLDBA"."MGRNOI"

ON "SQLDBA"."DEPARTMENT"

("MGRNO" )

PCTFREE =

10;

CREATE INDEX "SQLDBA"."RESPEMPI"

ON "SQLDBA"."PROJECT"

("RESPEMP" )

PCTFREE =

10;

CREATE INDEX "SQLDBA"."WORKDEPTI"

ON "SQLDBA"."EMPLOYEE"

("WORKDEPT" )

PCTFREE =

10;

COMMENT ’*************

Primary Keys

*************’

COMMENT ’*************

Remaining Foreign

Keys

*************’

ALTER TABLE "SQLDBA"."PROJ_ACT"

ADD FOREIGN KEY "R_ACTIV"

("ACTNO"

) REFERENCES "SQLDBA"."ACTIVITY"

ON DELETE RESTRICT;

ALTER TABLE "SQLDBA"."EMPLOYEE"

ADD FOREIGN KEY "R_DEPT1"

("WORKDEPT"

) REFERENCES "SQLDBA"."DEPARTMENT"

ON DELETE SET NULL;

ALTER TABLE "SQLDBA"."PROJECT"

ADD FOREIGN KEY "R_DEPT2"

("DEPTNO"

) REFERENCES "SQLDBA"."DEPARTMENT"

ON DELETE RESTRICT;

ALTER TABLE "SQLDBA"."DEPARTMENT"

ADD FOREIGN KEY "R_EMPLY1"

("MGRNO"

) REFERENCES "SQLDBA"."EMPLOYEE"

ON DELETE SET NULL;

Figure 64. Sample Generated DDL File (Part 3 of 4)

132

Control Center Operations Guide for VSE

DBSPACE Reorganization Tool Related Files

ALTER TABLE "SQLDBA"."EMP_ACT"

ADD FOREIGN KEY "R_EMPLY3"

("EMPNO"

) REFERENCES "SQLDBA"."EMPLOYEE"

ON DELETE CASCADE;

ALTER TABLE "SQLDBA"."PROJECT"

ADD FOREIGN KEY "R_EMPLY2"

("RESPEMP"

) REFERENCES "SQLDBA"."EMPLOYEE"

ON DELETE SET NULL;

ALTER TABLE "SQLDBA"."EMP_ACT"

ADD FOREIGN KEY "R_PROACT"

("PROJNO"

, "ACTNO"

, "EMSTDATE"

) REFERENCES "SQLDBA"."PROJ_ACT"

ON DELETE RESTRICT;

ALTER TABLE "SQLDBA"."PROJ_ACT"

ADD FOREIGN KEY "R_PROJ2"

("PROJNO"

) REFERENCES "SQLDBA"."PROJECT"

ON DELETE RESTRICT;

COMMENT ’*************

Active Unique Constraints

*************’

COMMENT ’*************

Table Grants

*************’

CONNECT "SQLDBA" IDENTIFIED BY "BOOMER";

GRANT SELECT

ON "SQLDBA"."ACTIVITY"

TO "PUBLIC";

GRANT SELECT

ON "SQLDBA"."DEPARTMENT"

TO "PUBLIC";

GRANT SELECT

ON "SQLDBA"."EMP_ACT"

TO "PUBLIC";

GRANT SELECT

ON "SQLDBA"."EMPLOYEE"

TO "PUBLIC";

GRANT SELECT

ON "SQLDBA"."PROJ_ACT"

TO "PUBLIC";

GRANT SELECT

ON "SQLDBA"."PROJECT"

TO "PUBLIC";

COMMENT ’*************

Column Grants

*************’

COMMENT ’*************

Views with Grants

*************’

COMMENT ’******** SQLDBA.VPHONE

********’

CREATE VIEW VPHONE (LASTNAME, FIRSTNAME, MIDINITL, PHNUMBER, EMNUMBER,

DPNUMBER, DEPTNAME) AS SELECT LASTNAME, FIRSTNME, MIDINIT, PHONENO,

EMPNO, DEPTNO, DEPTNAME FROM EMPLOYEE,

DEPARTMENT WHERE WORKDEPT=DEPTNO;

GRANT SELECT

ON "SQLDBA"."VPHONE"

TO "PUBLIC";

COMMENT ’******** SQLDBA.VPROJ

********’

CREATE VIEW VPROJ (EMP_NO, AC_STAFF, DEPT_NO) AS SELECT EMPNO, ACSTAFF,

DEPTNO FROM EMP_ACT, PROJ_ACT,

PROJECT WHERE EMP_ACT.PROJNO = PROJ_ACT.PROJNO AND PROJ_ACT.PROJNO =

PROJECT.PROJNO;

COMMENT ’******************** PACKAGE REBIND ********************’

Figure 64. Sample Generated DDL File (Part 4 of 4)

Appendix B. DBSPACE and Table Reorganization Tool Related Files

133

Notices

IBM may not offer the products, services, or features discussed in this document in

all countries. Consult your local IBM representative for information on the

products and services currently available in your area. Any reference to an IBM

product, program, or service is not intended to state or imply that only that IBM

product, program, or service may be used. Any functionally equivalent product,

program, or service that does not infringe any IBM intellectual property right may

be used instead. However, it is the user’s responsibility to evaluate and verify the

operation of any non-IBM product, program, or service.

IBM may have patents or pending patent applications covering subject matter

described in this document. The furnishing of this document does not give you

any license to these patents. You can send license inquiries, in writing, to:

IBM Director of Licensing

IBM Corporation

North Castle Drive

Armonk, NY 10594-1785

U.S.A.

For license inquiries regarding double-byte (DBCS) information, contact the IBM

Intellectual Property Department in your country or send inquiries, in writing, to:

IBM World Trade Asia Corporation

Licensing

2-31 Roppongi 3-chome, Minato-ku

Tokyo 106, Japan

The following paragraph does not apply to the United Kingdom or any other

country where such provisions are inconsistent with local law:

INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS

PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER

EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED

WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY OR FITNESS

FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or

implied warranties in certain transactions, therefore, this statement may not apply

to you.

This information could include technical inaccuracies or typographical errors.

Changes are periodically made to the information herein; these changes will be

incorporated in new editions of the publication. IBM may make improvements

and/or changes in the product(s) and/or the program(s) described in this

publication at any time without notice.

Any references in this information to non-IBM Web sites are provided for

convenience only and do not in any manner serve as an endorsement of those Web

sites. The materials at those Web sites are not part of the materials for this IBM

product and use of those Web sites is at your own risk.

IBM may use or distribute any of the information you supply in any way it

believes appropriate without incurring any obligation to you.

135

Licensees of this program who wish to have information about it for the purpose

of enabling: (i) the exchange of information between independently created

programs and other programs (including this one) and (ii) the mutual use of the

information which has been exchanged, should contact:

IBM Corporation

Mail Station P300

522 South Road

Poughkeepsie, NY 12601-5400

U.S.A

Such information may be available, subject to appropriate terms and conditions,

including in some cases, payment of a fee.

The licensed program described in this information and all licensed material

available for it are provided by IBM under terms of the IBM Customer Agreement,

IBM International Program License Agreement, or any equivalent agreement

between us.

Any performance data contained herein was determined in a controlled

environment. Therefore, the results obtained in other operating environments may

vary significantly. Some measurements may have been made on development-level

systems and there is no guarantee that these measurements will be the same on

generally available systems. Furthermore, some measurement may have been

estimated through extrapolation. Actual results may vary. Users of this document

should verify the applicable data for their specific environment.

Information concerning non-IBM products was obtained from the suppliers of

those products, their published announcements, or other publicly available sources.

IBM has not tested those products and cannot confirm the accuracy of

performance, compatibility, or any other claims related to non-IBM products.

Questions on the capabilities of non-IBM products should be addressed to the

suppliers of those products.

All statements regarding IBM’s future direction or intent are subject to change or

withdrawal without notice, and represent goals and objectives only.

This information may contain examples of data and reports used in daily business

operations. To illustrate them as completely as possible, the examples include the

names of individuals, companies, brands, and products. All of these names are

fictitious and any similarity to the names and addresses used by an actual business

enterprise is entirely coincidental.

This information may contain sample application programs in source language,

which illustrates programming techniques on various operating platforms. You

may copy, modify, and distribute these sample programs in any form without

payment to IBM, for the purposes of developing, using, marketing, or distributing

application programs conforming to the application programming interface for the

operating platform for which the sample programs are written. These examples

have not been thoroughly tested under all conditions. IBM, therefore, cannot

guarantee or imply reliability, serviceability, or function of these programs.

136

Control Center Operations Guide for VSE

Glossary

access-path. The path used to get data specified in

database manager. A program product that processes

SQL statements. An access path can involve either an

SQL statements.

index, a sequential search, or a combination of both.

database switching. The facility that allows users and

applid. The name of a CICS system as known to

applications to connect from one database server to

VTAM^® .

another.

batch. Processing that involves little or no terminal

DBSPACE. A logical allocation of space in a storage

interaction.

pool contained in a database. Contains one or more

tables and their associated indexes.

catalog.

1. A set of tables maintained by the database

manager. 2. A directory of files and libraries, with

DBSU. Database Services Utility program used to run

reference to their locations. 3. To store a library member

the DDL needed to perform a function.

such as a phase, module, or book in a sublibrary.

DB2 (Database 2). Pertaining to the IBM licensed

CEDA. The resource definition online transaction.

program that is the version of DB2 Server for the VSE

and VM environments.

CICS. Customer Information Control System. IBM’s

teleprocessing monitor for VSE/ESA.

DB2 Optimizer. A component of a relational DBMS

that carries out the logic required to find data in a

CICS Report Controller Feature. A set of programs

database. The optimizer determines the access path.

and transactions that interface with VSE/POWER to

help users create reports and print them at distributed

DDL. Data Definition Language. The SQL statements

locations.

for deleting and defining objects such as tables and

indexes in an RDBMS.

CICS transaction. Computing and data-access tasks

grouped together as a unit of work.

distribution tape. A magnetic tape that contains, for

example, a preconfigured operating system such as

clustered index. An index whose sequence of key

VSE/ESA. This tape is shipped to the customer for

values closely corresponds to the sequence of rows

program installation.

stored in a table.

FCT. File Control Table. This CICS control table

clustering index. The first index created for a table.

contains entries that define files to CICS.

The DB2 database manager uses it to determine

placement of subsequent rows.

JCL (Job Control Language). A language that serves

to prepare a job or each job step of a job to be run.

CSD. CICS System Definition file.

job scheduling. The process of creating the JCL

Control Center. An IBM licensed program consisting

necessary to run a job, then to invoke VSE/POWER to

of menus and programs to assist a DBA in the on-going

actually submit the job to the system for execution.

administration of DB2 Server for VSE & VM databases.

locking. A mechanism that prevents concurrent users

database. An organized collection of stored

from accessing the same data, at the same time. This

operational data, used by the application systems of an

ensures data integrity.

organization.

LIBDEF. A VSE system control statement that defines

database administrator (DBA). An individual

what sublibraries are to be searched for members of a

responsible for the availability, development, design,

specified type or the sublibrary in which new phases

maintenance, operation, performance, recoverability,

are to be stored.

and security of the database.

menu. A screen that offers the user a choice of

database management system (DBMS). A software

execution options.

system that controls the logical and physical resources

and facilities of a database.

MUM (multiple user mode). A mode of operating the

DB2 database manager in which one or more users or

dbextent. The physical medium upon which data is

application programs can access the database at the

stored. One or more dbextents comprise a storage pool.

same time.

139

online processing. Processing by which the input data

areas of a VSE system and are thus available to all

enters the computer directly from a display station and

subsequent jobs that run in that partition or system.

the output data is transmitted directly to the display

station.

storage pool. A specific set of available storage areas.

These areas are used by the database administrator to

Online Resource Adapter. The DB2 code that

control storage of the database. A storage pool contains

provides the connection between DB2 databases and

one or more DBSPACES.

online (CICS) applications.

sublibrary. In VSE, a subdivision of a library.

operator command. A statement to a control program,

Members can only be accessed in a sublibrary.

issued using a console or terminal

SUM (single user mode). A mode of operation in

package. A control structure produced during

which the DB2 database manager and one application

program preparation that is used to execute SQL

run in the same virtual machine. No other application

statements.

programs or users can access the database at the same

time.

PCT. Program Control Table. The CICS control table

that contains entries that describe transactions.

time event scheduling. In VSE/POWER, the facility to

schedule jobs for processing in a partition at a

production library. The VSE/ESA library that contains

predefined time once or repetitively.

the DB2 and Control Center code (PRD2).

TLBL. The VSE Tape LaBeL system control statement

program preparation. The process of producing an

that defines a tape file to an application.

executable DB2 application program. The process

includes precompilation, compilation, and bind.

transaction. Execution of one or more programs that

function together as a unit in a CICS environment.

pseudo-conversational. A method of on-line

programming whereby a program is removed from

transaction identifier. The unique four-character code

storage when it is waiting for data from the terminal. A

that identifies a CICS transaction.

much more efficient technique than conversational

programming.

VSE (Virtual Storage Extended). A system that

consists of a basic operating system and any IBM

RDBMS. Relational Data Base Management System.

supplied and user-written programs required to meet

the data processing needs of a user. VSE and the

RDO. Resource Definition Online.

hardware it controls form a complete computing

system.

rebind. To recreate a package.

VSE/DITTO (VSE/Data Interfile Transfer, Testing, and

SAM. Sequential Access Method. Files processed

Operations). An IBM licensed program that provides

without an index.

file-to-file services for disk, tape, and card devices.

SNT. Signon Table. The CICS control table that

VSE/ESA (VSE/Enterprise Systems Architecture). The

contains an entry for each userid.

most advanced VSE system currently available.

spanned records. Records that are defined to span

VSE/ICCF (VSE/Interactive Computing and Control

multiple blocks.

Facility). An IBM licensed program that serves as

interface, on a time-slice basis, to authorized users of

spool file.

1. A file that contains output data saved for

terminals linked to the system’s processor.

later processing. 2. One of three VSE/POWER files on

disk: queue file, data file, and account file.

VSE/ICCF library. A file composed of smaller files

(libraries) including system and user data which can be

SQL. Structured Query Language. A data

accessed under the control of VSE/ICCF.

sub-language for defining and accessing data in an

RDBMS.

VSE/POWER. An IBM licensed program primarily

used to spool input and output. The program’s

static SQL. SQL statements that are embedded within

networking functions enable a VSE system to exchange

a program, and are prepared during the program

files with or run jobs on another remote processor.

preparation process before the program is executed.

Static SQL statements have a corresponding access plan

XCTL. CICS Transfer Program Control Command.

in the database.

standard labels. Disk file labels (DLBLS) that are

loaded into either the partition or system standard label

140

Control Center Operations Guide for VSE

Bibliography

This bibliography lists publications that are

v IBM VSE/ESA Messages & Codes, SC33-6507

referenced in this manual or that may be helpful.

v IBM VSE/ESA Networking Support, SC33-6508

v IBM VSE/ESA Operation, SC33-6506

DB2 Server for VSE Publications

v IBM VSE/ESA Planning, SC33-6503

v DB2 Server for VSE & VM Application

v IBM VSE/ESA System Control Statements,

Programming, SC09-2889

SC33-6513

v DB2 Server for VSE & VM Database

v IBM VSE/ESA System Macros User’s Guide,

Administration, SC09-2888

SC33-6515

v DB2 Server for VSE & VM Database Services

v IBM VSE/ESA System Macros Reference,

Utility, SC09-2983

SC33-6516

v DB2 Server for VSE & VM Diagnosis Guide and

v IBM VSE/ESA System Utilities, SC33-6517

Reference, LC09-2907

v IBM VSE/ESA Unattended Node Support,

v DB2 Server for VSE & VM Overivew, GC09-2995

SC33-6512

v DB2 Server for VSE & VM Interactive SQL Guide

v IBM VSE/ESA Using IBM Workstations,

and Reference, SC09-2990

SC33-6509

v DB2 Server for VSE & VM Master Index and

Glossary, SC09-2890

CICS/VSE Publications

v DB2 Server for VSE Messages and Codes,

CICS/VSE Application Programming Reference,

GC09-2985

SC33-0713

v DB2 Server for VSE & VM Operation, SC09-2986

CICS/VSE Application Programming Guide,

v DB2 Server for VSE System Administration,

SC33-0712

SC09-2981

CICS Application Programming Primer (VS

v DB2 Server for VSE & VM Performance Tuning

COBOL II), SC33-0674

Handbook, GC09-2987

CICS/VSE CICS-Supplied Transactions, SC33-0710

v DB2 Server for VSE & VM SQL Reference,

CICS/VSE Customization Guide, SC33-0707

SC09-2989

CICS/VSE Facilities and Planning Guide,

SC33-0718

Related Publications

CICS/VSE Intercommunication Guide, SC33-0701

v DB2 Server for VSE & VM Data Restore,

SC09-2991

CICS/VSE Performance Guide, SC33-0703

v DRDA: Every Manager's Guide, GC26-3195

CICS/VSE Problem Determination Guide,

SC33-0716

v IBM SQL Reference, Version 2, Volume 1,

SC26-8416

CICS/VSE Recovery and Restart Guide, SC33-0702

v IBM SQL Reference, SC26-8415

CICS/VSE Release Guide, GC33-1645

CICS/VSE Report Controller User’s Guide,

Virtual Storage Extended/Enterprise Systems

SC33-0705

Architecture (VSE/ESA) Publications

CICS/VSE Resource Definition (Macro), SC33-0709

v IBM VSE/ESA Administration, SC33-6505

CICS/VSE Resource Definition (Online),

v IBM VSE/ESA Diagnosis Tools, SC33-6514

SC33-0708

v IBM VSE/ESA General Information, GC33-6501

CICS/VSE System Definition and Operations

v IBM VSE/ESA Guide for Solving Problems,

Guide, SC33-0706

SC33-6510

CICS/VSE System Programming Reference,

v IBM VSE/ESA Guide to System Functions,

SC33-0711

SC33-6511

CICS/VSE User’s Handbook, SX33-6079

v IBM VSE/ESA Installation, SC33-6504

CICS/VSE XRF Guide, SC33-0704

141

CICS/ESA Publications

v IBM Distributed Data Management (DDM)

Architecture, Architecture Reference, Level 4,

v CICS/ESA General Information, GC33-0803

SC21-9526

VSE/Virtual Storage Access Method (VSE/VSAM)

v IBM Distributed Data Management (DDM)

Publications

Architecture, Implementation Programmer’s Guide,

SC21-9529

v VSE/VSAM Commands and Macros, SC33-6532

v VM/Directory Maintenance Licensed Program

v VSE/VSAM Introduction, GC33-6531

Specification, GC20-1836

v VSE/VSAM Messages and Codes, SC24-5146

v IBM Distributed Relational Database Architecture

v VSE/VSAM Programmer’s Reference, SC33-6535

Reference, SC26-4651

v IBM Systems Network Architecture, Format and

VSE/Interactive Computing and Control Facility

Protocol Reference, SC30-3112

(VSE/ICCF) Publications

v SNA LU 6.2 Reference: Peer Protocols, SC31-6808

v VSE/ICCF Administration and Operation,

SC33-6562

v Reference Manual: Architecture Logic for LU Type

6.2, SC30-3269

v VSE/ICCF Primer, SC33-6561

v IBM Systems Network Architecture, Logical Unit

v VSE/ICCF User’s Guide, SC33-6563

6.2 Reference: Peer Protocols, SC31-6808

VSE/POWER Publications

v Distributed Data Management (DDM) General

Information, GC21-9527

v VSE/POWER Administration and Operation,

SC33-6571

CCSID Publications

v VSE/POWER Application Programming,

v Character Data Representation Architecture,

SC33-6574

Executive Overview, GC09-2207

v VSE/POWER Networking, SC33-6573

v Character Data Representation Architecture

v VSE/POWER Remote Job Entry, SC33-6572

Reference and Registry, SC09-2190

Distributed Relational Database Architecture

C/370 Publications

(DRDA) Library

v IBM C/370 Installation and Customization Guide,

v Application Programming Guide, SC26-4773

GC09-1387

v Architecture Reference, SC26-4651

v IBM C/370 Programming Guide, SC09-1384

v Connectivity Guide, SC26-4783

v DRDA: Every Manager's Guide, GC26-3195

Communication Server for OS/2 Publications

v Planning for Distributed Relational Database,

v Up and Running!, GC31-8189

SC26-4650

v Network Administration and Subsystem

v Problem Determination Guide, SC26-4782

Management Guide, SC31-8181

v Command Reference, SC31-8183

C/370 for VSE Publications

v Message Reference, SC31-8185

v IBM C/370 General Information, GC09-1386

v Problem Determination Guide, SC31-8186

v IBM C/370 Programming Guide for VSE,

SC09-1399

Distributed Database Connection Services

v IBM C/370 Installation and Customization Guide

(DDCS) Publications

for VSE, GC09-1417

v DDCS User’s Guide for Common Servers,

v IBM C/370 Reference Summary for VSE,

S20H-4793

SX09-1246

v DDCS for OS/2 Installation and Configuration

v IBM C/370 Diagnosis Guide and Reference for

Guide, S20H-4795

VSE, LY09-1805

VTAM Publications

VSE/REXX Publication

v VTAM Messages and Codes, SC31-6493

v VSE/REXX Reference, SC33-6642

v VTAM Network Implementation Guide, SC31-6494

v VTAM Operation, SC31-6495

Other Distributed Data Publications

142

Control Center Operations Guide for VSE

v VTAM Programming, SC31-6496

v VS COBOL II Installation and Customization for

CMS, SC26-4213

v VTAM Programming for LU 6.2, SC31-6497

v VS COBOL II Installation and Customization for

v VTAM Resource Definition Reference, SC31-6498

VSE, SC26-4696

v VTAM Resource Definition Samples, SC31-6499

v VS COBOL II Application Programming Guide for

VSE, SC26-4697

CSP/AD and CSP/AE Publications

v Developing Applications, SH20-6435

Systems Network Architecture (SNA)

v CSP/AD and CSP/AE Installation Planning Guide,

Publications

GH20-6764

v SNA Transaction Programmer’s Reference Manual

v Administering CSP/AD and CSP/AE on VM,

for LU Type 6.2, GC30-3084

SH20-6766

v SNA Format and Protocol Reference: Architecture

v Administering CSP/AD and CSP/AE on VSE,

Logic for LU Type 6.2, SC30-3269

SH20-6767

v SNA LU 6.2 Reference: Peer Protocols, SC31-6808

v CSP/AD and CSP/AE Planning, SH20-6770

v SNA Synch Point Services Architecture Reference,

v Cross System Product General Information,

SC31-8134

GH23-0500

Miscellaneous Publications

Query Management Facility (QMF) Publications

v IBM 3990 Storage Control Planning, Installation,

v Introducing QMF, GC27-0714

and Storage Administration Guide, GA32-0100

v Installing and Managing QMF for VSE,

v Dictionary of Computing, ZC20-1699

GC27-0721

v APL2 Programming: Using Structured Query

v QMF Reference, SC27-0715

Language, SH21-1056

v Installing and Managing QMF for VM,

v ESA/390 Principles of Operation, SA22-7201

GC27-0720

v Developing QMF Applications, SC27-0718

Related Feature Publications

v QMF Messages and Codes, GC27-0717

DB2 for VSE Control Center Operations Guide,

GC09-2992

v Using QMF, SC27-0716

DB2

Replication Guide and Reference, SC26-9920

Query Management Facility (QMF) for Windows

Publications

v Getting Started with QMF for Windows,

SC27-0723

v Installing and Managing QMF for Windows,

GC27-0722

DL/I DOS/VS Publications

v DL/I DOS/VS Application Programming,

SH24-5009

COBOL Publications

v VS COBOL II Migration Guide for VSE,

GC26-3150

v VS COBOL II Migration Guide for MVS and

CMS, GC26-3151

v VS COBOL II General Information, GC26-4042

v VS COBOL II Language Reference, GC26-4047

v VS COBOL II Application Programming Guide,

SC26-4045

v VS COBOL II Application Programming

Debugging, SC26-4049

Bibliography

143

Contents

About This Manual

vii

Using CHARNAME and DBCS Options in VSE .

How to Use This Manual

vii

Components of the Relational Database Management

Chapter 2. Querying Tables

System

Selecting Columns

Prerequisite Publications

. x

Using Query Results

Corequisite Publications

Displaying Query Results

How to Send Your Comments

Results That Have Too Many Rows for One

Syntax Notation Conventions

Display

SQL Reserved Words

Results That Are Too Wide for One Display .

Authorization Names and Passwords

xvi

Ending a Query Display

Authorization Names without Quotation Marks . . xvi

Obtaining a Printed Report

Authorization Names in Double Quotation Marks xvii

Obtaining Multiple Copies of a Printed Report

Use of Highlighting in This Guide

xvii

Using More Than One Keyword with the Print

Command

Summary of Changes

xix

Summary of Changes for DB2 Version 7 Release 1

xix

Chapter 3. Managing Table Data

Enhancements, New Functions, and New

Controlling Changes to Table Data

Capabilities

. xix

Using the AUTOCOMMIT ON Setting

Reliability, Availability, and Serviceability

Using the AUTOCOMMIT OFF Setting

Improvements

xxi

Interpreting Messages While Making Changes .

Library Enhancements

xxi

Interpreting Errors While Making Changes . .

Chapter 1. Getting Started

Chapter 4. Using ISQL Commands to

Introducing the DB2 Server for VSE & VM Database

Save Time When Executing Statements

Manager

Reusing the Current SQL Statement

Introducing ISQL

Retrieving and Correcting SQL Lines

Introducing the ISQL Display Terminal

Altering and Reusing SQL Lines

Using the Program Function Keys

Changing the Current SQL Statement

Defining the ISQL Session

Correcting Typing Errors in the Statement . .

Using DBCS for DB2 Server for VM

Altering and Reusing the Statement

Before Starting ISQL

Deleting Portions of the Statement

DB2 Server for VSE

Ignoring an SQL Line

DB2 Server for VM

Preventing the Immediate Processing of an SQL

Starting ISQL for DB2 Server for VSE

Statement

Alternative Methods for Starting ISQL

Using Placeholders in SQL Statements

Starting ISQL for DB2 Server for VM

Controlling the Display

Chapter 5. Formatting Query Results

Interpreting DB2 Server for VSE & VM Messages .

Formatting Columns

Entering Commands

Creating a Report from Query Results

DB2 Server for VSE

Modifying the Separation between Columns .

DB2 Server for VM

Excluding Columns from the Display

Entering Commands While Viewing the Results of a

Including Columns in the Display

Query

Changing a Displayed Column Heading

Understanding ISQL Modes

Changing the Number of Decimal Places

Using the Continuation Character

Displayed

Correcting Typing Errors

Controlling the Display of Leading Zeros . .

Canceling Running Commands

Changing the Displayed Length Attribute of a

Obtaining Online HELP Information at a Terminal

Column

Selecting Online HELP Information

Formatting Reports

Typing While Viewing Online HELP Information

Obtaining an Outline Report Format

Using ISQL on a Non-DB2 Server for VM

Obtaining Totals for Reports

Application Server (VM Only)

Creating Titles for Printed Reports

Using ISQL on a Remote Application Server (VSE

Using More Than One Keyword in a FORMAT

Only)

Command

Stopping ISQL

iii

Displaying Null Values and Arithmetic Errors . . 58

Using a View to Restrict Privileges to Certain

Controlling Null-Field Displays

Rows

Controlling Query Format Characteristics

Using a View to Restrict Privileges to Certain

Setting the Format Characteristics by Using the

Columns

SET Command

Creating Tables That You Want to Share

Printing Reports on a Workstation Printer (DB2

Accessing Tables Belonging to Other Users . . . 93

Server for VSE)

Using Synonyms

Improving Query Performance

Chapter 6. Storing SQL Statements .

Indexing a Table

Maintaining Updated Statistics

Storing the Current Statement

Locking Data

Protecting a Stored Statement

Starting a Stored Statement

Starting a Stored Statement That Contains

Chapter 9. Using VM Functions

Placeholders

CMS-Subset Processing

Recalling a Stored Statement

Returning to ISQL from CMS Subset Mode . . . 99

Saving the Format Information

Entering CP Commands

Changing a Stored Statement

Obtaining Printed Reports on a Workstation Printer 100

Listing the Names of Stored Statements

Specifying the Number of Copies of Printed

Renaming a Stored Statement

Reports

100

Erasing a Stored Statement

Using EXEC Files

101

Stacking Commands in an EXEC File

101

Chapter 7. Creating and Using

Prompting by Using an EXEC File

101

Starting ISQL from a Terminal

103

Routines

Disconnecting after Starting ISQL .

103

Running Routines When ISQL Is Started

Profile Routines

Chapter 10. ISQL Commands

105

Routines to Which Parameters Can Be Passed

(DB2 Server for VM)

BACKOUT . .

106

Using the ISQL Transaction Identifier (DB2

BACKWARD .

107

Server for VSE)

CANCEL

108

Establishing Where Routines Are Stored

CHANGE . .

110

Storing a Routine

COLUMN . .

111

Managing a Routine

COUNTER . .

112

Running a Routine

DISPLAY

113

Running Shared Routines

END

114

Error Mode Processing in a Routine

ERASE

115

Using INPUT Commands in a Routine

EXIT

116

Using SELECT Statements in a Routine

FORMAT

117

FORWARD . .

125

HELP

126

Chapter 8. Creating and Managing

HOLD

127

Tables

IGNORE

128

Managing Your Own Tables

INPUT

129

Querying Information about Your Tables . . . 83

Interactive Select

131

Creating Your Own Tables

ISQLTRACE . .

136

Storing Your Tables

LEFT

137

Copying Data from Other Tables

LIST

138

Dropping a Table

140

Identifying the Minimum Contents of a Table . . 86

RECALL

144

Adding a Column to a Table

RENAME . .

145

Specifying Referential Constraints

RIGHT

146

Creating a Table That Contains a Primary Key. . 88

RUN

147

Adding a Primary Key to an Existing Table. . . 89

SAVE

148

Creating a Table That Contains a Foreign Key . . 89

SET

149

Adding a Foreign Key to an Existing Table . . . 90

SHOW

157

Activating and Deactivating Primary Keys,

START

159

Foreign Keys, or Unique Constraints

STORE

161

Determining Effects on Stored Format

TAB

163

Information

Example . .

163

Sharing Your Tables with Other Users

Granting Privileges to Multiple Users

Appendix A. Answers to the Exercises

165

iv Interactive SQL Guide and Reference

DB2 Server for VSE. Operations Guide / Handbooks (2004-2007) - page 15