Reorg Utility Changes for DB2 6

Reorg Utility Changes for DB2 6.1
You can now use the reorg utility to unload your tables faster than using the DSNTIAUL sample program.

In my test, unloading using the reorg utility was 5 times faster

Test Condition

Columns 21

Record Length 74

Rows 1,262,361

Clock Time CPU Time DASD DCB

DSNTIAUL 5.95 min. 3 min 41.64 sec 1590 trk Recfm=FB,LRECL=72

Reorg 1.18 min. 0 min 44.20 sec 1800 trk Recfm=VB,LRECL=82

JCL for DSNTIAUL unload

//STEP17 EXEC PGM=IKJEFT01

//STEPLIB DD DSN=D0977.DB2.DBT1.DSNLOAD,DISP=SHR

//SYSTSPRT DD SYSOUT=*

//SYSTSIN DD *

DSN SYSTEM(DBT1)

RUN PROGRAM(DSNTIAUL) PLAN(DSNTIB61) -

LIB('D0977.DB2.DBT1.RUNLIB')

//SYSPRINT DD SYSOUT=*

//SYSUDUMP DD SYSOUT=*

//SYSREC00 DD DSN=D9747.DB2.UNLOAD.TCO88001,DISP=(,CATLG,DELETE),

// MGMTCLAS=BMIG00,SPACE=(4096,(10,10),RLSE)

//SYSPUNCH DD DSN=D9747.DB2.UNLOAD.SYSPUNCH,DISP=(,CATLG,DELETE),

// MGMTCLAS=BMIG00,SPACE=(800,(10,10),RLSE)

//SYSIN DD *

DBAADMIN.TCO88001

SYSPUNCH dataset

LOAD DATA LOG NO INDDN SYSREC00 INTO TABLE

DBAADMIN.TCO88001

(

BUSNSS_UNIT POSITION( 1 )

CHAR( 7) ,

CUST_NAME POSITION( 8 )

CHAR( 30) ,

SHORT_CUST POSITION( 38 )

CHAR( 12) ,

STREET POSITION( 50 )

CHAR( 30)

NULLIF( 80)='?',

CITY POSITION( 81 )

CHAR( 30)

NULLIF( 111)='?',

STATE POSITION( 112 )

CHAR( 2)

NULLIF( 114)='?',

ZIP POSITION( 115 )

CHAR( 10)

NULLIF( 125)='?',

T_TEST POSITION( 126 )

CHAR( 10)

NULLIF( 136)='?',

T_TESTX POSITION( 137 )

CHAR( 1)

)

JCL for Unload Under Reorg

//STEP12 EXEC DSNUPROC,SYSTEM='DBT1',

// LIB='D0977.DB2.DBT1.DSNLOAD'

//SORTLIB DD DISP=SHR,DSN=SYS1.SORTLIB

//SORTOUT DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK01 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK02 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK03 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK04 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//DSNTRACE DD SYSOUT=*

//SYSUT1 DD UNIT=WORK,SPACE=(4000,(50,50),,,ROUND)

//SYSREC00 DD DSN=D9747.DB2.REORG.TCO88001,

// MGMTCLAS=BMIG00,SPACE=(TRK,(2000,200),RLSE),DISP=(,CATLG,DELETE)

//SYSPUNCH DD DSN=D9747.DB2.UNLOAD.SYSPUNCH,DISP=(,CATLG,DELETE),

// MGMTCLAS=BMIG00,SPACE=(TRK,(1,1),RLSE)

//SYSIN DD *

REORG TABLESPACE DCO88001.SCO88001 SORTDEVT(WORK) UNLDDN(SYSREC00)

UNLOAD EXTERNAL NOPAD FROM TABLE DBAADMIN.TCO88001 PUNCHDDN(SYSPUNCH)

//*

Note:

The SYSREC00 dataset will contain all rows from table DBAADMIN.TCO88001 and all rows from

any other tables in the tablespace DCO88001.SCO88001

You may add additional condition onto the table selection by using the WHEN (condition)

Example UNLOAD EXTERNAL NOPAD FROM TABLE DBAADMIN.TCO88001 WHEN

(STATE = 'TX')

The SYSREC00 dataset will contains all rows in table DBAADMIN.TCO88001 where STATE = 'TX'

and all rows of any other tables in the same tablespace

Position 4-5 of each record will contain the table's OBID

SYSPRINT Output

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = XB3GDH.XB3DB2

DSNU050I DSNUGUTC - REORG TABLESPACE DCO88001.SCO88001 SORTDEVT(WORK) UNLDDN(SYSREC00) UNLOAD EXTERNAL NOPAD

DSNU650I # DSNUUGMS - FROM TABLE DBAADMIN.TCO88001

DSNU253I DSNUUMSG - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=1077 FOR TABLE DBAADMIN.TCO88001

DSNU253I DSNUUMSG - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=14 FOR TABLE XB3GDH.F2PLAN_TABLE

DSNU252I DSNURULD - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=1091 FOR TABLESPACE DCO88001.SCO88001

DSNU250I DSNURULD - UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00:00

DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

SYSPUNCH Dataset

LOAD DATA INDDN SYSREC00 LOG NO RESUME YES

EBCDIC CCSID(00000,00000,00000)

INTO TABLE "DBAADMIN"."TCO88001 "

WHEN(00004:00005 = X'0005')

( "BUSNSS_UNIT " POSITION(00007:00013) CHAR(007)

, "CUST_NAME " POSITION(00014:00043) CHAR(030)

, "SHORT_CUST " POSITION(00044:00055) CHAR(012)

, "STREET " POSITION(00057:00086) CHAR(030)

NULLIF(00056)=X'FF'

, "CITY " POSITION(00088:00117) CHAR(030)

NULLIF(00087)=X'FF'

, "STATE " POSITION(00119:00120) CHAR(002)

NULLIF(00118)=X'FF'

, "ZIP " POSITION(00122:00131) CHAR(010)

NULLIF(00121)=X'FF'

, "T_TEST " POSITION(00133:00142) CHAR(010)

NULLIF(00132)=X'FF'

, "T_TESTX " POSITION(00143:00143) CHAR(001)

)

INTO TABLE "XB3GDH "."F2PLAN_TABLE "

WHEN(00004:00005 = X'001D')

( "TIMESTAMP " POSITION(00007:00022) CHAR(016)

, "STMTNO " POSITION(00023:00026) INTEGER

, "SEQNO " POSITION(00027:00030) INTEGER

, "TEXT " POSITION(00031) VARCHAR

, "REMARKS " POSITION(*) CHAR(064)

)

Note:

The WHEN parameter tells it to only load records that contains the table's OBID in position 4-5

The unload dataset will contain records from all tables in the tablespace.

You can also use the reorg utility to remove rows as part of your reorg. In the past, this was done one of two ways 1) execute a SQL DELETE FROM TABLE WHERE condition, followed by an reorg or 2) unload table, delete rows from the unloaded dataset, load table.

JCL to Remove Rows with Reorg

//STEP12 EXEC DSNUPROC,SYSTEM='DBT1',

// LIB='D0977.DB2.DBT1.DSNLOAD'

//SORTLIB DD DISP=SHR,DSN=SYS1.SORTLIB

//SORTOUT DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK01 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK02 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK03 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK04 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//DSNTRACE DD SYSOUT=*

//SYSUT1 DD UNIT=WORK,SPACE=(4000,(50,50),,,ROUND)

//SYSREC DD DSN=D9747.DB2.REORG.TCO88001,DISP=(,DELETE,CATLG),

// MGMTCLAS=TESTMGM,SPACE=(TRK,(5,5),RLSE)

//DISC01 DD DSN=D9747.DB2.DISCARD.TCO88001,DISP=(,DELETE,CATLG),

// MGMTCLAS=TESTMGM,SPACE=(TRK,(5,5),RLSE)

//SYSPUNCH DD DSN=D9747.DB2.UNLOAD.SYSPUNCH,DISP=(,CATLG,DELETE),

// MGMTCLAS=BMIG00,SPACE=(TRK,(1,1),RLSE)

//SYSIN DD *

REORG TABLESPACE DCO88001.SCO88001 SORTDEVT(WORK)

DISCARDDN(DISC01) DISCARD FROM TABLE DBAADMIN.TCO88001

WHEN (STATE = 'TX')

Note:

As part of the reorg, the job will remove rows in table DBAADMIN.TCO88001 which contain

TX in the column STATE. All other data will be reloaded.

The discarded rows will be written to the dataset specified by the optional DISCARDDN parameter.

SYSPRINT Output

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = XB3GDH.XB3DB2

DSNU050I DSNUGUTC - REORG TABLESPACE DCO88001.SCO88001 SORTDEVT(WORK) DISCARDDN(DISC01) DISCARD

DSNU650I # DSNUUGMS - FROM TABLE DBAADMIN.TCO88001 WHEN

DSNU650I # DSNUUGMS - (STATE='TX')

DSNU253I DSNUUMSG - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=1017 FOR TABLE DBAADMIN.TCO88001

DSNU253I DSNUUMSG - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS DISCARDED=60 FOR TABLE DBAADMIN.TCO88001

DSNU253I DSNUUMSG - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=14 FOR TABLE XB3GDH.F2PLAN_TABLE

DSNU253I DSNUUMSG - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS DISCARDED=0 FOR TABLE XB3GDH.F2PLAN_TABLE

DSNU252I DSNURULD - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=1031 FOR TABLESPACE DCO88001.SCO880

DSNU250I DSNURULD - UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00:00

DSNU304I # DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=14 FOR TABLE XB3GDH.F2PLAN_TABLE

DSNU304I # DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=1017 FOR TABLE DBAADMIN.TCO88001

DSNU302I DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1031

DSNU300I DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:35

DSNU042I DSNUGSOR - SORT PHASE STATISTICS -

NUMBER OF RECORDS=2034

ELAPSED TIME=00:00:00

DSNU349I # DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=1017 FOR INDEX DBAADMIN.TCO88001I001

DSNU349I # DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=1017 FOR INDEX DBAADMIN.TCO88001I002

DSNU258I DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=2

DSNU259I DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:00:03

DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0

You can also gather statistics as part of your reorg now. A separate RUNSTATS would not be necessary after a reorg.

JCL to Gather Statistics

//STEP12 EXEC DSNUPROC,SYSTEM='DBT1',

// LIB='D0977.DB2.DBT1.DSNLOAD'

//SORTLIB DD DISP=SHR,DSN=SYS1.SORTLIB

//SORTOUT DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK01 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK02 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK03 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//SORTWK04 DD UNIT=WORK,SPACE=(4000,(20,20),,,ROUND)

//DSNTRACE DD SYSOUT=*

//SYSUT1 DD UNIT=WORK,SPACE=(4000,(50,50),,,ROUND)

//SYSREC DD DSN=D9747.DB2.REORG.TCO88001,DISP=(,DELETE,CATLG),

// MGMTCLAS=TESTMGM,SPACE=(TRK,(5,5),RLSE)

//SYSIN DD *

REORG TABLESPACE DCO88001.SCO88001 SORTDEVT(WORK)

STATISTICS

Note:

You may gather statistics on all tables in the tablespace as above or specify table name

(STATISTICS TABLE tablename) or specify tablename with sampling

(STATISTICS TABLE tablename SAMPLE nnnnn)

SYSPRINT Output

DSNU000I DSNUGUTC - OUTPUT START FOR UTILITY, UTILID = XB3GDH.XB3DB2

DSNU050I DSNUGUTC - REORG TABLESPACE DCO88001.SCO88001 SORTDEVT(WORK) STATISTICS

DSNU252I DSNURULD - UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=1031 FOR TABLESPACE DCO88001.SCO88001

DSNU250I DSNURULD - UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00:00

DSNU610I # DSNUSUTP - SYSTABLEPART CATALOG UPDATE FOR DCO88001.SCO88001 SUCCESSFUL

DSNU610I # DSNUSUTB - SYSTABLES CATALOG UPDATE FOR DBAADMIN.TCO88001 SUCCESSFUL

DSNU610I # DSNUSUTB - SYSTABLES CATALOG UPDATE FOR XB3GDH.F2PLAN_TABLE SUCCESSFUL

DSNU610I # DSNUSUTS - SYSTABLESPACE CATALOG UPDATE FOR DCO88001.SCO88001 SUCCESSFUL

DSNU620I # DSNURDRT - RUNSTATS CATALOG TIMESTAMP = 2002-02-07-11.30.17.305958

DSNU304I # DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=14 FOR TABLE XB3GDH.F2PLAN_TABLE

DSNU304I # DSNURWT - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=1017 FOR TABLE DBAADMIN.TCO88001

DSNU302I DSNURILD - (RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1031

DSNU300I DSNURILD - (RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:36

DSNU042I DSNUGSOR - SORT PHASE STATISTICS -

NUMBER OF RECORDS=2034

ELAPSED TIME=00:00:00

DSNU349I # DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=1017 FOR INDEX DBAADMIN.TCO88001I001

DSNU349I # DSNURBXA - BUILD PHASE STATISTICS - NUMBER OF KEYS=1017 FOR INDEX DBAADMIN.TCO88001I002

DSNU258I DSNURBXD - BUILD PHASE STATISTICS - NUMBER OF INDEXES=2

DSNU259I DSNURBXD - BUILD PHASE COMPLETE, ELAPSED TIME=00:00:03

DSNU010I DSNUGBAC - UTILITY EXECUTION COMPLETE, HIGHEST RETURN CODE=0