Cat/Dir REORG JCLs Part 0 - Intro

Michael Ebert

Cat/Dir REORG JCLs Part 0 - Intro
Hello all -

I give up. I have until now sent my Cat/Dir REORG JCL to 70 people, but another
15 have accumulated in the meantime (today). So I'm finally going to cut&paste
the original documents into several mails (to prevent size problems) and post
them to the list in general.

I have also received two resposes concerning problems with this PIPE statement
in the CATLIST REXX:

address TSO "PIPE SQL SSID" ssid ,
"SELECT A.DBNAME, A.TSNAME, B.INDEXSPACE",
" FROM SYSIBM.SYSTABLES A," ,
" SYSIBM.SYSINDEXES B" ,
" WHERE A.CREATOR=B.TBCREATOR" ,
" AND A.NAME=B.TBNAME" ,
" AND A.TYPE='T'" ,
" AND B.DBNAME='DSNDB06'" ,
" ORDER BY 1, 2, 3" ,
"|stem rec."

Unfortunately I cannot help you much there... this PIPE command is our way of
running SQL from REXX, and I don't have any documentation at all... I extracted
the syntax from existing REXXs (the command puts the result set returned into
the stem variable REC., with columns separated by x'00' bytes, plus a binary
length for VARCHAR fields). You have to replace this by your shop's way of
accessing DB2 from REXX (there certainly is one), or you can convert it to SAS
or your favourite programming language... this REXX is really just a
quick-and-dirty way of relieving me of the tedious job of checking the space
parameters of 90 VSAM files per SSID several times.

First of all, here is some general information:

Please note that while the JCLs provided have been thoroughly tested and run
successfully, we cannot take any responsibility if run outside our control. If
you do not agree to these conditions, please return the product immediately for
a full refund...

There's 4 text files that will follow:

DB2PCAT: the DB2 Cat/Dir Reorg job
DB2CATCK: A check job which you should run before/after the Reorg
RCVRPCAT: A DB2 Cat/Dir Recovery job
CATLIST: a REXX exec that provides a listing of DB2 Cat/Dir VSAM files space
parameters: tracks allocated/used, Cylinders allocated/used, allocation unit,
number of extents (a really quick-and-dirty auxiliary tool); this will need some
customising (at least).

Some more comments:

1. I have decided to do all space allocations on our Production DB2 (DB2P) in
cylinders. The JCLs are set up accordingly. Secondary quantities are about 20%
of the primary allocation (also in Cyls, of course).
2. In our JCL, the VSAM files are put onto two disks (MSY003 and MSY005). They
are distributed as described in the header of DB2PCAT. The CATLIST REXX also has
this distribution built in. You may have to change this to suit your
environment.
3. The Cat/Dir in our case were about 3 GB. Processing them took about 90
minutes without the Check job (this took maybe another 30 minutes).
4. Use the CATLIST Pgm to determine the required space (you must edit the JCLs
accordingly). Use the Used Cylinders as a guideline. The Image copy datasets
should have about the same amount of space.
5. The unload Dataset (DB2P.UNLD.SYSREC) should have the size of the largest TS
(in our case, DSNDB01.SPT01). You may want to manually pre-allocate it to make
sure it has room to grow. Note that this dataset is re-used by all Reorg Steps.
6. I did not run the Reorg JCL as provided but split it into smaller steps:
first the Imagecopy, then all Reorg steps except the ones for SPT01 - these were
run in a parallel job using a different SYSREC. However, due to I/O contention,
I do not think that this saved a noticeable amount of time so I do NOT recommend
it.
The second Imagecopy was again in a separate job, so I had to change the
Relative Generation number as described in the RESTART instructions (these are
very rudimentary!)
7. If your system has been in operation for some time, make sure that the SMS
definitions are still ok (i.e. that you are allowed to DEFINE datasets on the
disks you are trying to use). You can test this by creating a VSAM file with a
name like a Cat/Dir TS but with .S0001. replacing the .I0001. , for example.
8. The "DB2 Developers Guide" mentioned in some places is a book by Craig S.
Mullins.
9. Note that Imagecopy information for DSNDB06.SYSCOPY, DSNDB01.SYSUTILX,
DSNDB01.DBD01 is recorded in the Log, not in SYSCOPY.

>>>10. Make sure you are Install SYSADM when you run this job, as this is
required to start a stopped DSNDB01 TS (according to the Admin Guide)!!!

11. I have listed all indexes separately in the DSN commands because we have
user-defined indexes. These are DB2-managed, so they are not part of the
DELETE/DEFINE steps. You may have to adapt this to your environment.
12. It's highly recommended to test everything first in a test or non-prod
environment! It has taken me WEEKS to develop them, so you should spend some
days familiarizing yourself with the steps (the reason for developing them was
that one of the disks the Cat/Dir was on was completely full, and also some of
the TS were into dozens of extents).

Have fun & tell me how it went. You can contact me in case of problems.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany
Tel. ++49 -8122-433962
Fax ++49-8122-433260