Template and Listdef

Introduction

DB2 table spaces and indexes require a certain amount of housekeeping to keep them recoverable and in good condition. This involves the creation of a large number of utility jobs, which is usually a large administrative task for the DBA. The problems are — building the jobs in the first place; maintaining the jobs - changing dataset sizes, etc., as amount of data increases; and failing to amend jobs when new objects are added to a database.

DB2 V7 adds facilities which address these problems — LISTDEF, which allows the generation of a list of DB2 table spaces or index spaces which can be used as input to a utility — and TEMPLATE, which is used to specify the characteristics for the datasets required by the utility.

LISTDEF

The function of LISTDEF is to provide a list of table spaces or index spaces which can be used as input to a utility. This means that you don’t have to generate a list of objects you want to process yourself — DB2 will do it for you. Here’s an example of a LISTDEF statement:

LISTDEF LIST01 INCLUDE TABLESPACES TABLESPACE PTDB.*
 

Explanation:

LISTDEF The keyword
LIST01 The name of the list I’m creating. I must name it so that the utility can refer to it later. It can be up to 18 characters long.
INCLUDE The start of the include clause, specifying that I’m going to include some objects. I could also EXCLUDE some objects. There can be several INCLUDE/EXCLUDE clauses within a LISTDEF.
TABLESPACES LIST01 will contain table spaces.
TABLESPACE

The type of the object in the pattern that follows. I could also specify DATABASE, INDEXSPACE, TABLE, or INDEX, or another list. Whichever is specified, the objects in the list will only be the related table spaces (in this example) or index spaces.

PTDB.*

The pattern which objects must match to be included.

Note that the wildcard used in the pattern is an unSQL-like asterisk. An asterisk or a percent sign (%) can be used to match zero or more characters, while underscore (_) or a question mark (?) can be used to match a single character. When matching table or index names, you must use a question mark instead of an underscore as underscore is a valid character in such names.

PARTLEVEL

For partitioned tablespaces, most utilities can act at the individual partition level or at the whole tablespace level. If PARTLEVEL is not specified in the LISTDEV clause, the table space is added to the list as a single object. If PARTLEVEL is specified, all partitions are added as separate objects to the list. If PARTLEVEL n is specified, the nth partition is added to the list. There is no way to specify a range of partitions.

In the release version of V7 there was a bug with PARTLEVEL - if PARTLEVEL is the last word in the input, it is ignored. This is fixed by PTF UQ68081.

Other Keywords

The keyword RI causes objects that are referentially related to objects in the list to be included.

There are several LOB keywords which specify whether LOB and/or BASE objects are to be added to the list.

The COPY keyword can be used for index spaces to include or exclude indexes which have the copy attribute set to YES or NO.

Examples

Include all table spaces in database DDBIVP except for SDBIVP and SDBACCT:

LISTDEF MYLIST INCLUDE TABLESPACES DATABASE DDBIVP
               EXCLUDE TABLESPACES TABLESPACE DDBIVP.SDBIVP
               EXCLUDE TABLESPACES TABLESPACE DDBIVP.SDBACCT

Include all table spaces which contain tables with a creator which starts with NUDB:

LISTDEF LISTA INCLUDE TABLESPACES TABLE NUDB*.*

Include all table spaces in database DDBIVP — for partitioned table spaces, each partition should be added as an individual element to the list:

LISTDEF LISTB INCLUDE TABLESPACES DATABASE DDBIVP PARTLEVEL

Include all table spaces in previously defined list LISTA except for tablespaces in database DDBIVP which start with an S:

LISTDEF LISTC INCLUDE TABLESPACES LIST LISTA
              EXCLUDE TABLESPACES TABLESPACE DDBIVP.S*

Catalog and Directory Objects

You cannot use pattern-matching for catalog and directory objects — each one must be added to the list individually and be fully specified.

LISTDEF and Utilities

LISTDEF can be used with the following utilities:


CHECK INDEX
COPY
COPYTOCOPY
MERGECOPY
MODIFY RECOVERY
MODIFY STATISTICS
QUIESCE
REBUILD INDEX
RECOVER
REORG INDEX
REORG TABLESPACE
REPORT
RUNSTATS
UNLOAD

Using LISTDEF involves defining the list (the LISTDEF statement itself) and then referring to the list in the utility. This is generally done by replacing TABLESPACE tsname or INDEX ixname in the utility statement with LIST listname. However syntax varies with the utility.

You specify the LISTDEF statements in the utility SYSIN or in a SYSLISTD DD name. Using SYSLISTD makes it easier to have standard lists.

Here is a full example of a utility step which uses LISTDEF to provide a list of table spaces as input to the RUNSTATS utility.

//SRUNST  EXEC DSNUPROC,
//             SYSTEM=DB2S
//SYSIN    DD  *
   LISTDEF LIST01 INCLUDE TABLESPACES DATABASE DDBIVP
   RUNSTATS TABLESPACE LIST LIST01 TABLE(ALL) INDEX(ALL)
   

The first line of the sysin is the definition of the list (called LIST01). The second line is the invocation of the RUNSTATS utility which uses the list.

The print output from the utility indicates that a list is being processed:

OUTPUT START FOR UTILITY, UTILID = SMITHAC.SMITHACL
LISTDEF LIST01 INCLUDE TABLESPACES DATABASE DDBIVP
LISTDEF STATEMENT PROCESSED SUCCESSFULLY
RUNSTATS TABLESPACE LIST LIST01 TABLE(ALL) INDEX(ALL)
PROCESSING LIST ITEM: TABLESPACE DDBIVP.PACKTEST

As does displaying the utility:

DSNUGDIS - USERID = SMITHAC
MEMBER =
UTILID = SMITHAC.SMITHACL
PROCESSING UTILITY STATEMENT 1
UTILITY = RUNSTATS
PHASE = UTILINIT COUNT = 0
NUMBER OF OBJECTS IN LIST = 15
LAST OBJECT STARTED = 3
STATUS = ACTIVE

The runstats for each process proceeds serially, just as if a number of runstats commands had been entered. No parallelism is conferred.

With QUIESCE, the list is treated as one quiesce statement containing a number of objects. For example,

LISTDEF LIST01 INCLUDE TABLESPACES DATABASE MYDB
QUIESCE LIST LIST01

Would be treated like:

QUIESCE TABLESPACE MYDB.TS1
        TABLESPACE MYDB.TS2
        TABLESPACE MYDB.TS3
		

rather than:

QUIESCE TABLESPACE MYDB.TS1
QUIESCE TABLESPACE MYDB.TS2
QUIESCE TABLESPACE MYDB.TS3

Copy works in the same way – items in the list are treated as if they are included in one copy statement. This means that the copies can precede in parallel if the PARALLEL keyword is used, and that certain directory and catalog objects which cannot appear in a COPY statement with other objects – DSNDB06.SYSCOPY for instance, cannot be in a list with anything else.

What LISTDEF doesn’t do

When LISTDEF chooses what objects to add to the list, it is only using name pattern matching and the relationships between objects to decide what to include. There is no way to use more complex criteria, such as choosing objects in copy pending or ones which haven’t been runstatted for a certain period, for instance.

TEMPLATE

LISTDEF gives us a means of running a utility against a dynamic list of objects which is likely to change with time as new objects are created. Some utilities, such as COPY, require additional datasets to be allocated as they process each object. If these datasets had to be hardcoded in the JCL, the flexibility and time-saving advantages conferred by LISTDEF would be lost. TEMPLATE allows the datasets to be allocated dynamically.

The most obvious example of this is the COPY utility, where an image copy dataset must be allocated as output from the utility. The objects in the list are likely to differ considerably in size, so the image copy datasets will need to differ in size as well. In addition they must be named differently. TEMPLATE provides two useful facilities to help us - automatic calculation of the size of the dataset required, and automatic name generation.

Automatic Size Calculation

TEMPLATE can calculate the size of the dataset required by the utility. It uses the formulae specified in the 'Output Dataset Size' section of the utilities manual for the relevant utility. For example for COPY, TEMPLATE uses either the NACTIVE column of SYSIBM.SYSTABLESPACE, or the VSAM statistics for the underlying VSAM dataset.

In general, this is done by default. The values calculated by DB2 can be overridden or have their characteristics altered.

The SPACE keyphrase can be used to set a static primary and secondary allocation in cylinders (CYL), tracks (TRK), or megabytes (MB). In general, this is of limited usage when processing a LISTDEF, as all the output datasets will have the same allocations.

The LOAD utility has a number of output datasets, depending on the keywords used — for instance, an inline image copy dataset, and SYSUT1 and SORTOUT datasets. LOAD is unable to calculate how big these datasets should be, because the data being loaded is outside DB2. You must therefore code the space keyword, otherwise you get a message such as:

DSNU1036I   DSNURELD - UNABLE TO ESTIMATE SPACE REQUIREMENTS FOR WORKDDN 2

If SORTKEYS with an estimate is specified in the load, and there is no referential integrity on the table, then SYSUT1, SORTOUT, SYSERR, SYSMAP, and SYSDISC can be small — you can use SPACE(1,1).

Other space-related keywords:

MAXPRIME is probably a necessity in a live system. Template by default will ask for a primary allocation of any size regardless of whether it can be allocated. For non-striped datasets, the maximum primary that can be allocated is 65,535 tracks - a request for any more will cause a JCL error. Striped datasets do not have that limit - typically 120,000 tracks is used as a primary allocation here. MAXPRIME can be used to put an upper bound on the primary allocation requested.

According to the syntax diagrams in the utilities manual, you should be able to say:

SPACE TRK MAXPRIME 500

i.e. be able to specify that the MAXPRIME is in tracks rather than cylinders (the default) - but this doesn't work. You can only specify TRK or MB if you also specify a primary and secondary amount, in which case you wouldn't want to specify MAXPRIME. Effectively, you can only specify MAXPRIME in cylinders.

PCTPRIME can be used to specify that n% of the allocation be requested as primary allocation, the rest as secondary.

NBRSECND specifies the number of secondary extents that the secondary allocation be divided between.

It would have been nice to have had some decision-making capability here – for instance, if a tablespace is bigger than a certain size, write the image copy to tape, otherwise write it to disk – however, there’s no such facility.

Automatic Name Generation

So that dataset names don't clash, the names generally have to be based on a combination of identifiers such as object name, subsystem name, or jobname. TEMPLATE allows you to specify a pattern name made up of literals and variables using the DSN keyword, e.g.:

DSN('&SS..IC.LDF.&DB..&TS..A001(+1)')

Here, each substitution variable is preceded by an ampersand (&) and followed by a full stop. The variables used in this example are SS (subsystem id), DB (database name), and TS (tablespace name). The rest of the pattern name is used as is. For each object processed, these variables are expanded.

The most useful substitution variables which can be specified are:

JOBNAME/JO The name of the job
STEPNAME/ST The stepname
UTILID/UT The utility id truncated to 8 characters
USERID/US The user id running the job
SSID/SS Subsystem name
ICTYPE/IC F for a full image copy, I for an incremental
DB Database name
TS Table space name
IS Index space name
PART/PA Partition number (five digits padded with leading zeroes)
DATE/DT YYYYDDD
TIME/TI HHMMSS

There are also variables for the various date and time components such as HOUR and YEAR.

Userid was added after the original V7 release.

Note that the partition number is five digits, so for instance, partition number one would be 00001. It is unlikely that existing GDG bases at any site use a five digit partition number. After the initial release, the ability to substring any of the variables was added. The format is &var(start) or &var(start,length), so to get a three-digit partition number, say &PART(3).

GDGs

If the dataset name used in the template is a GDG name, TEMPLATE detects it, and can attempt to allocate the GDG base if one does not already exist. This is controlled by the keyword GDGLIMIT. Specify GDGLIMIT(0) to disable this facility. For a number other than zero, TEMPLATE will attempt to allocate a GDG base with that limit if one does not already exist. The default is a limit of 99.

TEMPLATE and Tapes

STACK is an additional keyword when outputting to tapes which will cause the output datasets to be stacked to one tape volume instead of each dataset going to a separate volume. The biggest use of this is for the COPY utility.

For this to work, the objects have to be in the same COPY statement - i.e., they are either explicitly listed in the COPY statement itself, or qualify in a LISTDEF that the utility uses. This means that STACK is useless for copying the directory and catalog, because many of these objects cannot be put in a list of objects with anything else.

DISP

The default disposition of the TEMPLATE-allocated datasets differs with the utility and the DD name that the dataset is allocated to.

In general, datasets are allocated with a disposition of NEW, or MOD if the utility is restarted. For datasets which are permanent output from the utility (such as image copies), the disposition is CATLG for normal or abnormal end. The transient datasets SYSUT1 and SORTOUT (for reorgs, rebuilds and loads) have a normal disposition of CATLG, abnormal of DELETE. SYSREC in a reorg, although it is a transient dataset, has a normal disposition of CATLG, although it is not needed after the utility has finished.

These dispositions can be overriden with the DISP keyword.

Other TEMPLATE Options

The other TEMPLATE options map to the options in the JDL DD statement. Some examples are BUFNO, DATACLAS, RETPD, VOLCNT, TRTCH.

Allocation Time

If image copy datasets are allocated in JCL, they are all allocated at the beginning of the step, and so if there is a fail, they will all have been created. If these are (+1) GDGs, the ones which relate to items in the list which have not been processed will be empty. If the step is rerun several times, new generations will be created, many of them empty, and the GDG limit may cause the last valid image copy to roll off the end of the GDG.

Datasets allocated with TEMPLATE are created as each object in the list is processed. This greatly reduces the unused GDG problem.

In DB2 V6, the CHANGELIMIT keyword was added to the COPY utility. This gave the option of taking a full, an incremental, or no copy at all, depending on the percentage of changed pages in the table space. The problem was that if you coded an image copy GDG in the JCL and no copy was taken, this would create an empty GDG. It is very disappointing that even if TEMPLATE is used, the image copy dataset is allocated even if no image copy is to be taken. Obviously the decision to allocate a dataset is taken before the decision of whether to take a copy.

TEMPLATE and Utilities

TEMPLATE can be used with the following utilities:

CHECK DATA
CHECK INDEX
CHECK LOB
COPY
LOAD
MERGECOPY
REBUILD INDEX
REORG INDEX
REORG TABLESPACE
UNLOAD

To use a template, it must first be defined using the TEMPLATE statement, which names the template. This name can then be referred to in the utility statement where a DDname would usually be referred to.

TEMPLATE examples

Copy directory tablespace DSNDB01.SPT01. The image copy dataset will be called DB2T.IC.LDF.DSNDB01.SPT01.A001(+1). If the GDG base doesn't exist, one will be created with a limit of 9. The largest primary allocation that will be requested is 8,000 cylinders (120,000 tracks). COPYDDN(TCATCPY), rather than referring to a DDname of TCATCPY, now refers to the TEMPLATE called TCATCPY.

//SCOPY EXEC DSNUPROC, 
// SYSTEM=DB2T, 
// UID=HDBDC004 
//SYSTEMPL DD * 
TEMPLATE TCATCPY DSN('&SS..IC.LDF.&DB..&TS..A001(+1)') 
GDGLIMIT(9) MAXPRIME 8000 
//SYSIN DD * 
COPY TABLESPACE DSNDB01.SPT01 
COPYDDN(TCATCPY) 
SHRLEVEL CHANGE

Copy the table spaces specified in LISTDEF LIST01 (all tablespaces in database PTDB). For each one, use the template called TPLCPY to determine the names of the image copy dataset. If the relevant GDG base doen't exist, it will be created.

//SCPYUTIL EXEC DSNUPROC, 
// SYSTEM=DB2S, 
//* UTPROC=RESTART, 
// UID=HDBWC050 
//SYSTEMPL DD * 
TEMPLATE TPLCPY DSN('&SS..IC.LWF.&DB..&TS..A001(+1)') 
GDGLIMIT(9) 
//SYSIN DD * 
LISTDEF LIST01 INCLUDE TABLESPACES TABLESPACE PTDB.* 
COPY LIST LIST01 COPYDDN(TPLCPY) SHRLEVEL(CHANGE) PARALLEL 8

Copy the table spaces specified in LISTDEF LIST01 (all tablespaces in database DDBIVP). Partitions are included as separate items in the list. The copies are written to tape (UNIT AUTO) and are stacked on the same tape (STACK YES). Compression is used (TRTCH COMP).

//SCOPY EXEC DSNUPROC, 
//* UTPROC=RESTART, 
// SYSTEM=DGW1 
//SYSIN DD * 
LISTDEF LIST01 INCLUDE TABLESPACES TABLESPACE DDBIVP.* 
PARTLEVEL 
TEMPLATE TSYSCOP DSN('NUDBS.SECURE.&TS..P&PA..IC001') 
UNIT AUTO STACK YES TRTCH COMP 
COPY LIST LIST01 COPYDDN(TSYSCOP) 
SHRLEVEL CHANGE

Although DSNUM(n) was not specified in the COPY statement, because PARTLEVEL was specified in the LISTDEF, copying has taken place at the partition level, as shown by the utility output:

OUTPUT START FOR UTILITY, UTILID = SMITHAC.SMITHACL 
LISTDEF LIST01 INCLUDE TABLESPACES TABLESPACE DDBIVP.* PARTLEVEL
LISTDEF STATEMENT PROCESSED SUCCESSFULLY 
TEMPLATE TSYSCOP DSN('NUDBS.SECURE.&TS..P&PA..IC001') UNIT AUTO 
TEMPLATE STATEMENT PROCESSED SUCCESSFULLY 
COPY LIST LIST01 COPYDDN(TSYSCOP) SHRLEVEL CHANGE 
DATASET ALLOCATED. TEMPLATE=TSYSCOP 
DDNAME=SYS00001 
DSN=NUDBS.SECURE.SDBACCT.P00001.IC001 
COPY PROCESSED FOR TABLESPACE DDBIVP.SDBACCT DSNUM 1 
NUMBER OF PAGES=26 
AVERAGE PERCENT FREE SPACE PER PAGE = 3.03 
PERCENT OF CHANGED PAGES = 0.00 
ELAPSED TIME=00:00:03 
DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE DDBIVP.SDBACCT DSNUM 1 
COPY PROCESSED FOR TABLESPACE DDBIVP.SDBACCT DSNUM 2 
NUMBER OF PAGES=42 
AVERAGE PERCENT FREE SPACE PER PAGE = 1.09 
PERCENT OF CHANGED PAGES = 0.00 
ELAPSED TIME=00:00:01 
DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE DDBIVP.SDBACCT DSNUM 2 
COPY PROCESSED FOR TABLESPACE DDBIVP.SDBACCT DSNUM 3 
NUMBER OF PAGES=4 
AVERAGE PERCENT FREE SPACE PER PAGE = 12.75 
PERCENT OF CHANGED PAGES = 0.00 
ELAPSED TIME=00:00:01 

Strangely the dataset name is only output for the first item in the list.

Reorg the partitions of DDBIVP.SDBACCT separately. The unload dataset and image copy datasets are allocated using templates. One of each is allocated for each partition. The template name for the unload dataset cannot begin with SYSREC. Due to a bug in LISTDEF, PARTLEVEL was being ignored if it was at the end of the input, so it was moved forward in the LISTDEF statement.

//SREORG EXEC DSNUPROC, 
// SYSTEM=DGW1, 
// UID=SMITHAC@ 
//SYSLISTD DD * 
LISTDEF IVPLIST 
INCLUDE TABLESPACES PARTLEVEL TABLESPACE DDBIVP.SDBACCT 
//SYSTEMPL DD * 
TEMPLATE UNLD00 DSN('SMITHAC.&TS..P&PA..SYSREC') 
TEMPLATE SYSCOPY DSN('SMITHAC.&TS..P&PA..IC1') 
//SYSIN DD * 
REORG TABLESPACE LIST IVPLIST SORTDATA SORTKEYS LOG NO 
SORTDEVT SYSDA COPYDDN(SYSCOPY) UNLDDN(UNLD00)

The output looks like this:

OUTPUT START FOR UTILITY, UTILID = SMITHAC@ 
REORG TABLESPACE LIST IVPLIST SORTDATA SORTKEYS LOG NO SORTDEVT SY 
PROCESSING LIST ITEM: TABLESPACE DDBIVP.SDBACCT PARTITION 1 
DATASET ALLOCATED. TEMPLATE=UNLD00 
DDNAME=SYS00001 
DSN=SMITHAC.SDBACCT.P00001.SYSREC 
DATASET ALLOCATED. TEMPLATE=SYSCOPY 
DDNAME=SYS00002 
DSN=SMITHAC.SDBACCT.P00001.IC1 
UNLOAD PHASE STATISTICS - NUMBER OF RECORDS UNLOADED=1044 FOR TABLE
UNLOAD PHASE COMPLETE, ELAPSED TIME=00:00:01 
INDEXES WILL BE BUILT IN PARALLEL, NUMBER OF TASKS = 8 
COPY PROCESSED FOR TABLESPACE DDBIVP.SDBACCT PART 1 
NUMBER OF PAGES=27 
AVERAGE PERCENT FREE SPACE PER PAGE = 2.92 
PERCENT OF CHANGED PAGES =100.00 
ELAPSED TIME=00:00:03 
T - (RE)LOAD PHASE STATISTICS - NUMBER OF RECORDS=1044 FOR TABLE NUD
(RE)LOAD PHASE STATISTICS - NUMBER OF INPUT RECORDS PROCESSED=1044 
(RE)LOAD PHASE COMPLETE, ELAPSED TIME=00:00:03 
XA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=1044 FOR INDEX NUDB.X
XA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=1044 FOR INDEX NUDB.X
XA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=1044 FOR INDEX NUDB.X
XA - SORTBLD PHASE STATISTICS - NUMBER OF KEYS=1044 FOR INDEX NUDB.X
SORTBLD PHASE STATISTICS. NUMBER OF INDEXES = 4 
SORTBLD PHASE COMPLETE, ELAPSED TIME = 00:00:01 
DB2 IMAGE COPY SUCCESSFUL FOR TABLESPACE DDBIVP.SDBACCT PARTITION 1 
PROCESSING LIST ITEM: TABLESPACE DDBIVP.SDBACCT PARTITION 2 
DATASET ALLOCATED. TEMPLATE=UNLD00 
DDNAME=SYS00003 
DSN=SMITHAC.SDBACCT.P00002.SYSREC 
DATASET ALLOCATED. TEMPLATE=SYSCOPY 
DDNAME=SYS00004 
DSN=SMITHAC.SDBACCT.P00002.IC1 
.
.
.

Previewing

When you use LISTDEF and TEMPLATE, you could be processing hundreds of tablespaces and producing hundreds of datasets using variable substitution. It's quite easy to get the list of objects and the names of the datasets wrong, so there's a facility to preview what's going to happen. To invoke it, use the statement OPTIONS PREVIEW in the utility SYSIN input. Using one of our previous examples with an OPTIONS PREVIEW added to the SYSIN:

//SREORG EXEC DSNUPROC, 
// SYSTEM=DGW1, 
// UID=SMITHAC@ 
//SYSLISTD DD * 
LISTDEF IVPLIST 
INCLUDE TABLESPACES PARTLEVEL TABLESPACE DDBIVP.SDBACCT 
//SYSTEMPL DD * 
TEMPLATE UNLD00 DSN('SMITHAC.&TS..P&PA..SYSREC') 
TEMPLATE SYSCOPY DSN('SMITHAC.&TS..P&PA..IC1') 
//SYSIN DD * 
OPTIONS PREVIEW 
REORG TABLESPACE LIST IVPLIST SORTDATA SORTKEYS LOG NO 
SORTDEVT SYSDA COPYDDN(SYSCOPY) UNLDDN(UNLD00) 

The utility isn't actually invoked. Instead you're told what's going to be included and what datasets are going to be allocated:

DSNUGUTC - REORG TABLESPACE LIST IVPLIST SORTDATA SORTKEYS LOG NO 
DGW1 DSNUILSA - EXPANDING LISTDEF IVPLIST 
DGW1 DSNUILSA - PROCESSING INCLUDE CLAUSE TABLESPACE DDBIVP.SDBACCT
DGW1 DSNUILSA - CLAUSE IDENTIFIES 20 OBJECTS 
DGW1 DSNUILSA - LISTDEF IVPLIST CONTAINS 20 OBJECTS 
DSNUGULM - PROCESSING LIST ITEM: TABLESPACE DDBIVP.SDBACCT PARTITION 1 
DSNUGPVV - LISTDEF IVPLIST EXPANDS TO THE FOLLOWING OBJECTS: 
LISTDEF IVPLIST -- 00000020 OBJECTS 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00001) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00002) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00003) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00004) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00005) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00006) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00007) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00008) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00009) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00010) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00011) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00012) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00013) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00014) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00015) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00016) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00017) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00018) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00019) 
INCLUDE TABLESPACE DDBIVP.SDBACCT PARTLEVEL(00020) 
DSNUGPVV - TEMPLATE SYSCOPY DSN=SMITHAC.SDBACCT.P00001.IC1 
DSNUGPVV - TEMPLATE UNLD00 DSN=SMITHAC.SDBACCT.P00001.SYSREC 
DSNUGULM - PROCESSING LIST ITEM: TABLESPACE DDBIVP.SDBACCT PARTITION 2 
DSNUGPVV - TEMPLATE SYSCOPY DSN=SMITHAC.SDBACCT.P00002.IC1 
DSNUGPVV - TEMPLATE UNLD00 DSN=SMITHAC.SDBACCT.P00002.SYSREC 
DSNUGPVV - TEMPLATE UNLD00 DSN=SMITHAC.SDBACCT.P00002.SYSREC 
DSNUGULM - PROCESSING LIST ITEM: TABLESPACE DDBIVP.SDBACCT PARTITION 3 
DSNUGPVV - TEMPLATE SYSCOPY DSN=SMITHAC.SDBACCT.P00003.IC1 
DSNUGPVV - TEMPLATE UNLD00 DSN=SMITHAC.SDBACCT.P00003.SYSREC

Restarting Utilities

With LISTDEF, there's a possibility of processing hundreds of objects with one utility statement. It's important after an abend, therefore, to be able to restart the utility in the middle of a list. This is possible.

In testing, an abend was forced by specifying low space values in the template (these wouldn't normally be coded):

//SCPYE EXEC DSNUPROC, 
// SYSTEM=DB2T 
//SYSIN DD * 
LISTDEF LIST01 INCLUDE TABLESPACES TABLESPACE DDBIVP.SDBIVP 
INCLUDE TABLESPACES TABLESPACE DDBIVP.SDBUPD01
INCLUDE TABLESPACES TABLESPACE DDBIVP.SDBACCT 
TEMPLATE TSYSCOP DSN('SMITHAC.&TS..IC001') 
SPACE(1,1) TRK MAXPRIME 10 
COPY LIST LIST01 COPYDDN(TSYSCOP) 

The utility abended with a B37 on the image copy for the first item in the list. Interestingly enough, TEMPLATE values can be changed and the utility restarted. The SPACE values were increased, the image copy dataset deleted and the utility restarted:

//SCPYE EXEC DSNUPROC, 
// UTPROC=RESTART, 
// SYSTEM=DB2T 
//SYSIN DD * 
LISTDEF LIST01 INCLUDE TABLESPACES TABLESPACE DDBIVP.SDBIVP 
INCLUDE TABLESPACES TABLESPACE DDBIVP.SDBUPD01 
INCLUDE TABLESPACES TABLESPACE DDBIVP.SDBACCT 
TEMPLATE TSYSCOP DSN('SMITHAC.&TS..IC001') 
SPACE(5,5) TRK MAXPRIME 10 
COPY LIST LIST01 COPYDDN(TSYSCOP) 

The first member in the list was processed successfully. The second item then abended with a B37 as it is bigger. The same process was followed - the image copy dataset was deleted and the utility restarted with a larger space parameter. This time the utility abended with an I/O error as it expected the original dataset to be there. Using RESTART(PHASE) doesn't help. There is no way to restart at the beginning of the current item in the list. The only thing now is to terminate the whole utility.

This is not a problem with LISTDEF itself, but rather with processing items in lists. Jobs with large LISTDEFs will need to be restarted rather than terminated because you don't want to do the work you've already done, but they will be more difficult to restart for the operators.

Considerations for rolling out Template and Listdef

LISTDEF

Sites vary in the way that they run utilities in their production batch suites. One approach is to have one job for each utility — say a reorg or image copy. This allows a high degree of parallelism which is controlled by the operators using JES initiators or the batch scheduling software using control resources. Each job is very simple, allowing easy restarting by the operators after a fail. The other approach is to use smaller groups of fixed numbers of jobs, each processing a large number, say a hundred or more, utilities running serially. These jobs are probably easier to adminster, but considerably more difficult to restart, especially if tape stacking is used.

Listdef is a large departure from the former approach and would need some adjustment. It is very similar to the latter approach and would be fairly easy to implement.

Because of the large size of warehousing and image systems, many table spaces may be partitioned into more than 200 parts. Having a separate image copy job for each partition causes a huge proliferation of jobs. Using LISTDEF to copy each partition separately but within the same job step would greatly reduce the administrative workload of creating and maintaining those jobs. However the lack of a range parameter for the PARTLEVEL keyword means that you would have to copy all partitions in the step, or list each partition separately. The former would create a job of great duration; the latter is no improvement over what you can do at the moment without LISTDEF.

For third party products, such as those supported by systems areas, where JCL maintenance must be kept to a minimum, LISTDEF provides a way of achieving this. A product may consist of many small table spaces which LISTDEF can copy in one job step in a short space of time. When the product is upgraded and new objects are added, the job does not need to change.

TEMPLATE

TEMPLATE has two facilities which will make it useful immediately: automatic calculation of dataset size, and creation of GDG bases which don't exist. The former will reduce maintenance of project utility JCL which at the moment needs to change as objects increase in size. It also simplifies the usage of the same skeletal JCL across production and test systems which must somehow parameterise the difference between the dataset sizes in the two types of environment. The automatic creation of GDG bases could save a lot of job fails.

Summary

The LISTDEF statement is used to create a list of table spaces or index spaces which can be used as input to a utility.

Pattern matching characters can be used to add objects or exclude them from the list, but no other criteria (such as runstats) can be used. Because the list is built at utility invocation time, the list changes as new objects are added (as long as they match the list criteria).

Although LISTDEF allows the easy building of utilities which could take the place of hundreds of single jobs, this can affect job duration time and restarting after a fail.

TEMPLATE is used to provide a generic pattern for dynamic allocation of datasets by utilities. It is essential when LISTDEF is being used by utilities which create work or output datasets.

TEMPLATE's calculation of dataset size and creation of GDG bases will prevent many live fails and save many hours of administrative work. It provides large advantages even when Listdef is not used.

 

 

Alan C Smith, 2003