< Day Day Up > |
Using LISTDEF and TEMPLATE
Let's take a look at these new capabilities. Database Object Lists
For example, the following statement creates a list named CUSTALL that includes all of the database objects in the CUSTOMER database: LISTDEF CUSTALL INCLUDE TABLESPACES DATABASE CUSTOMER INCLUDE INDEXSPACES DATABASE CUSTOMER LISTDEF is not a utility; it is a control statement that can be used within other DB2 utilities. The LISTDEF statement can be used to assign a name to a list of DB2 database objects and previously defined lists of database objects ”so, a list can consist of other lists. After the list is defined, it can be used when executing other utilities. Each list must be named and that name can be up to 18 characters in length. To run a DB2 utility against a list, you have the option of putting the LISTDEF statements in a separate library data set or coding it directly before the DB2 utility control statement that refers to the list. The default DD name for a LISTDEF data set is SYSLISTD , but this can be changed using the OPTIONS LISTDEFDD control statement. To run LISTDEF as part of a utility execution, the process or user running the utility must have SELECT authority on: SYSIBM.SYSINDEXES , SYSIBM.SYSTABLES , and SYSIBM.SYSTABLESPACE . Of course, the process or user running the utility also must have the requisite authority to execute the utility being used to process the list. Because LISTDEF is a control statement, not an individual utility, LISTDEF conforms to the concurrency rules for the utility to which the list is being applied. The LISTDEF list is stored until it is referenced by a specific utility, at which time the list is expanded. At that time, the concurrency and compatibility restrictions of the utility being executed apply, with the additional restriction that the catalog tables necessary to expand the list must be available for read-only access. Creating Lists with LISTDEFThe INCLUDE and EXCLUDE keywords are used to build the list of database objects:
The LISTDEF statement can consist of multiple INCLUDE and EXCLUDE clauses. At least one INCLUDE clause must be specified. Furthermore, an INCLUDE clause must be coded before any subsequent EXCLUDE clauses. For most utilities, the INCLUDE and EXCLUDE clauses will be processed in the order they are coded in the LISTDEF statement. Certain specific utilities will modify the order of the list to optimize its processing:
The list will be built one clause at a time, by adding database objects to the list or removing database objects from the list. Any EXCLUDE statements that try to remove a database object from the list that has not yet been added to the list will cause DB2 to ignore that database object and proceed to the next INCLUDE or EXCLUDE clause. Be aware that a subsequent INCLUDE can return the excluded object to the list. Furthermore, INCLUDE and EXCLUDE statements can be typed ”meaning that they can be set to include or exclude only table spaces or only index spaces. Of course, the typing of INCLUDE and EXCLUDE statements is optional. If the type is not specified, the statement will default to a particular type of object based on the subsequent keywords that are coded. The available keywords for including and excluding database objects from a list include the following:
Additionally, the PARTLEVEL keyword can be used to specify only certain partitions for inclusion. The PARTLEVEL keyword applies to both table spaces and index spaces, and is ignored for non-partitioned database objects. The PARTLEVEL keyword takes an integer parameter that specifies the partition to be included. Failure to specify the partition number causes a list to be generated that contains one entry for every existing partition of the table space or index space. Be aware that the only way to exclude a partition is if it was first included explicitly as a partition. For example, the following LISTDEF statement will exclude partition 7 from the list because both statements are coded at the partition level: LISTDEF LST1 INCLUDE TABLESPACE DB.TS1 PARTLEVEL EXCLUDE TABLESPACE DB.TS1 PARTLEVEL(7) However, the next LISTDEF statement will not exclude partition 7 from the list, because the INLCUDE statement was specified at the table space level, but the EXCLUDE statement is specified at the partition level: LISTDEF LST2 INCLUDE TABLESPACE DB.TS1 EXCLUDE TABLESPACE DB.TS1 PARTLEVEL(7) Wildcarding
LISTDEF LST3 INCLUDE TABLESPACE DBXN.* EXCLUDE TABLESPACE DBXN.TS2 REORG LIST LST3 . . . This sequence of statements will reorganize all table spaces in the database named DBXN except for the one table space exempted, namely TS2 . Furthermore, if a table space is subsequently added to DBXN , the REORG job does not need to be changed. The next time it runs, REORG will query the DB2 Catalog to determine the table spaces that exist in the list name DB1 . Since it specifies all table spaces in DBXN , any new table space added to DBXN will automatically be picked up for processing. The valid wildcard options supported by LISTDEF are as follows :
These options were chosen by IBM to mimic the wildcarding capability of the SQL LIKE clause. However, the underscore and percent sign characters are commonly occurring characters within database object names , so additional wildcard character options were provided. CAUTION Be sure to use the question mark ( ? ) wildcard character instead of the underscore ( _ ) character for pattern-matching in table and index names. For table and index names, the underscore character represents a single occurrence of itself. The underscore can be used as an alternative to the question mark for database, table space, and index space names. Although wildcarding is a very powerful capability, DB2 does place some limits on its use. For example, it is not permissible to create all-inclusive lists, such as DATABASE * or TABLESPACE *.* . The bottom line, though, is that the ability to create lists using wildcards greatly eases the task of creating utility jobs. List ExpansionLists created using the LISTDEF statement are expanded each time the utility job in which the LISTDEF is included is executed. This is important, because it simplifies the task of creating utility jobs for new database objects. For example, suppose you have a utility job set up to make image copies for every table space within a given database, such as LISTDEF LSTX INCLUDE TABLESPACE DBGL00X1.* COPY LIST LSTX ... This sequence of commands is set up to copy every table space in the DBGL00X1 database. If you add a new table space to that database you do not need to create a new image copy job or modify the existing job. This is so because the next time the existing job runs the list will be expanded and will now include the new table space ”which will cause it to be copied . Lists (and templates), therefore, can greatly simplify the DBA task of creating utility jobs for new database objects. Referential Integrity and ListsThe RI parameter can be included on the INCLUDE statement to cause all tables that are referentially connected to tables specified in the list to also be included. This relieves the DBA of the burden of manually determining which tables are connected to which. Be advised, however, that the RI parameter cannot be used in conjunction with the PARTLEVEL parameter. NOTE
LOBs and ListsDatabase objects containing LOBs are special and might need to be treated differently than other database objects for utility processing. The LISTDEF statement provides options that enable the DBA to handle database objects that use LOBs differently. LISTDEF offers three options that can be specified on an INCLUDE or EXCLUDE specification, to indicate how to process the LOB-related objects. The auxiliary LOB relationship can be traversed in either direction:
LOB objects include LOB table spaces, auxiliary tables, indexes on auxiliary tables, and their associated index spaces. Failure to specify one of the following three keyword options will result in DB2 not following the auxiliary relationships, and therefore LOBs will not be filtered from BASE objects in the enumerated list:
Indexes, Lists, and the COPY UtilityAn additional consideration for lists built using the LISTDEF statement is the treatment of indexes with regard to the COPY utility. As of DB2 V6 it has been possible to take image copy backups of index spaces to be used for subsequent recovery; previously, indexes had to be rebuilt from table data after table spaces were recovered. LISTDEF offers the COPY keyword, which can be used to specify whether indexes that are defined as COPY YES (or COPY NO ) are to be included or excluded in the list. If the COPY keyword is not coded, all index spaces that satisfy the INCLUDE or EXCLUDE expression, regardless of their COPY attribute, will be included or excluded in the list. If specified, this keyword must immediately follow the INDEXSPACES keyword. If specified elsewhere, the keyword COPY is interpreted as the start of the COPY utility control statement. The COPY keyword can take on one of two values:
You can use INCLUDE with COPY YES to create a list of index spaces that can be processed by the COPY utility. Or, you can use EXCLUDE with COPY NO to remove index spaces that cannot be processed by the COPY utility from a list of index spaces. List Usage GuidelinesThe whole purpose for using LISTDEF to create lists of database objects is to use those lists when executing DB2 utilities. Once created, the LIST keyword is specified in the utility job in order to process just those objects on the list. The LIST keyword can be used with the following utilities:
Certain utilities, such as RECOVER and COPY , can process a LIST without a specified database object type. These utilities will derive the database object type to process based on the contents of the list. But other utilities, such as QUIESCE and REORG INDEX , must have the database object type specified before the utility can be executed. For these utilities you must specify an object type in addition to the LIST keyword, for example: QUIESCE TABLESPACE LIST list-name. Additionally, most utilities require output data sets in order to process. You can use the TEMPLATE statement to specify the naming convention and, optionally , the allocation parameters for each output data set type. Templates, like lists, can be reused as long as the naming convention prohibits the specification of duplicate data set names. Although it may be possible to use traditional JCL with certain LISTDEF lists it is not recommended because the JCL quickly becomes unwieldy and impractical to maintain (unless the list of database objects to be processed is quite small). When used together, LISTDEF and TEMPLATE statements make it easier to develop DB2 utility jobs. Job creation is faster, lists can be coded to capture and process new database objects automatically, and the resulting JCL is easier to maintain because fewer modifications are required when database objects are added to the list. Previewing the Contents of a ListYou can use the OPTIONS PREVIEW control statement to expand the list and see the database objects that will be included in the list before actual processing. The OPTIONS ITEMERROR control statement can be used to alter the handling of errors that might occur during list processing. The OPTIONS LISTDEFDD control statement can be used to switch LISTDEF library data sets between control statements within a job step. The default is LISTDEFDD SYSLISTD . LISTDEF ExampleThe example in Listing 30.1 shows the JCL and control statements used to build a list of table spaces to QUIESCE . The list created is named QLIST . This list includes all of the table spaces in database DB1 that begin with the characters " TSL " except for TSLY028A , TSLY066V , and TSLU071X . Also, one table space is included from database DB7 , namely TSLU077T . Listing 30.1. Image copy JCL//QUIESCE JOB 'USER=NAME',CLASS=A,... //******************************************************* //*QUIESCE LISTDEF DD LILSTDEF data sets //******************************************************* //STEP1 EXEC DSNUPROC,UID='DBAPSCM.QUIESCL', // UTPROC='',SYSTEM='DB2A' //LISTDSN DD DSN=JULTU103.TCASE.DATA2,DISP=SHR // DD DSN=JULTU103.TCASE.DATA3(MEM1),DISP=SHR //SYSUT1 DD DSN=JULTU103.QUIESC2.STEP1.SYSUT1, // DISP=(MOD,DELETE,CATLG),UNIT=SYSDA, // SPACE=(4000,(20,20),,,ROUND) //SORTOUT DD DSN=JULTU103.QUIESC2.STEP1.SORTOUT, // DISP=(MOD,DELETE,CATLG),UNIT=SYSDA, // SPACE=(4000,(20,20),,,ROUND) //SYSIN DD * LISTDEF QLIST INCLUDE TABLESPACE DB1.TSL* EXCLUDE TABLESPACE DB1.TSLY028A EXCLUDE TABLESPACE DB1.TSLY066V EXCLUDE TABLESPACE DB1.TSLU071X INCLUDE TABLESPACE DB7.TSLU077T QUIESCE LIST QLIST /* Templates
The purpose of the TEMPLATE statement is to provide DB2 utilities with the basic allocation information necessary to automatically generate and allocate valid data sets that are required for use as DB2 utilities are executed. Traditionally, a DB2 utility is run against a single database object and the JCL is manually prepared with each required data set hard-coded into the JCL job stream. However, with the advent of LISTDEF , DB2 utility jobs can be set up to operate on multiple database objects with a single run. Furthermore, the person developing the JCL has no way to know how many database objects will be processed, so it is not practical to manually allocate data sets to such utility jobs. And due to the nature of the LISTDEF statement, it is possible that a different number of database objects will be processed each time a utility is run using the same list. So, once again, allocating data sets to the JCL in such an environment is impractical, if it is even possible at all. Like LISTDEF , TEMPLATE is not a new utility, but is a new control statement. The TEMPLATE statement lets you allocate data sets, without using JCL DD cards, during the processing of a LISTDEF list. Using TEMPLATE the developer can define the data set naming convention, and specify further allocation parameters such as data set sizing, location, and attributes. The TEMPLATE statement is flexible enough to allow different characteristics for tape and disk data sets. Each TEMPLATE statement generates a named template to be referenced as needed. To perform the dynamic data set allocation during the utility execution, the TEMPLATE control statement deploys the MVS DYNALLOC macro ( SVC 99 ). To use a template with a DB2 utility, you have the option of putting the TEMPLATE statements in a separate library data set or directly before the DB2 utility control statements. The default DD name for a TEMPLATE data set is SYSTEMPL , but this can be changed using the OPTIONS TEMPLATEDD control statement. No additional privileges are required to run TEMPLATE as part of a utility execution. Of course, the process or user running the utility must have the requisite authority to execute the utility for which the template is being used. Using TEMPLATEEach TEMPLATE must be named. The TEMPLATE name is limited to eight alphanumeric characters, the first of which must be an alphabetic character (A through Z). After the template name you can specify keywords to control the allocation of tape and disk data sets. Each TEMPLATE statement can apply to either disk or tape, but not both. The UNIT keyword specifies a generic unit name that must be defined on your system. Additional keywords specified for the TEMPLATE statement must be consistent with the unit type specified; that is, valid for tape or disk. There are three grouping of options that can be specified for a TEMPLATE statement:
A single TEMPLATE statement can have a set of common options, and then either a set of disk options or a set of tape options. Common TEMPLATE OptionsThe following keywords are common options that can be applied to the definition of any TEMPLATE statement: The UNIT option is used to indicate the device-number, the generic device-type or the group -name to use for allocating the data set. The DSN option indicates the template for the data set name. The data set name can be created by using any combination of symbolic variables , alphanumeric constants, or national characters. When creating the DSN template follow the same basic rules for using symbolic variables within JCL. Special DB2 symbolic variables are provided that can be used to insert characteristics of the database object, utility, or job that is running. These symbolic variables are summarized in Table 30.1. Each symbolic variable is substituted by its related value at execution time to form an explicit data set name. When used in a DSN expression, the symbolic variable begins with the ampersand sign ( & ) and ends with a period ( . ). For example, DSN &DB..&TS..D&JDATE..COPY&ICTYPE. This DSN parameter creates a data set named using
The DISP option indicates the disposition of the data set using standard JCL parameters for (status, normal termination, and abnormal termination). The legal values for each are as follows:
The MODELDCB option indicates the DSN of a model data set that will be used to get the DCB information. The BUFNO option indicates the number of BSAM buffers to use for data set buffering. The DATACLAS option indicates the name of the SMS data class to use. If specified, this value must be a valid data class defined to SMS. The MGMTCLAS option indicates the name of the SMS management class to use. If specified, this value must be a valid management class defined to SMS. The STORCLAS option indicates the name of the SMS storage class to use. If specified, this value must be a valid storage class defined to SMS. The RETPD option indicates the retention period for the data set in days. The value must be between 1 and 9999 if specified. The EXPDL option indicates the expiration date for the data set. The date must be enclosed in single quotes and specified in Julian data format, that is, YYYYDDD . The VOLUMES option is used to provide a list of volume serial numbers for this allocation. The volume serial numbers are provided in a comma-delimited list and enclosed within parentheses. The first volume on the list must contain sufficient space for the primary allocation of space for the data set. The VOLCNT option indicates the maximum number of volumes that an output data set might require. The GDGLIMIT option indicates the number of entries to be created in a GDG base if the DSN specifies a GDG and the GDG base does not already exist. The value of GDGLIMIT can range from 0 to 255, where 0 is a special value to specify that a GDG base should be created if one does not already exist. Table 30.1. DB2 TEMPLATE Symbolics
Disk OptionsThe following keywords are disk options that can only be applied to the definition of a statement defining a disk TEMPLATE : The SPACE option indicates the primary and secondary disk space allocation values for the DSN template. The option takes two values, placed between parentheses and separated by a comma: the first is the primary allocation and the second is the secondary allocation.
The PCTPRIME option indicates the percentage of space to be obtained as the primary quantity. The MAXPRIME option specifies an upper limit on the primary quantity specified using the SPACE parameter ( expressed in the units specified on the SPACE parameter). The NBRSECOND option indicates the division of secondary space allocations. After the primary space is allocated, the remaining space is divided into the specified number of secondary allocations . The NBRSECOND value can range from 1 to 10. Tape OptionsThe following keywords are disk options that can only be applied to the definition of a statement defining a disk TEMPLATE : The UNCNT option indicates the number of devices to be allocated. If a specific device number is coded on the UNIT common option, then UNCNT must be 1 (or not coded). The STACK option indicates whether output data sets are to be stacked contiguously on tape. Valid values are YES or NO . The JES3DD option indicates the name of the JCL DD to be used at job initialization time for the tape unit. (JES3 requires that all necessary tape units are pre-allocated by DD statement.) The TRTCH option indicates the track recording technique (for tape drives with improved data recording capabilities). Valid values are
TEMPLATE ExamplesThe following statement shows a disk template with an explicit specification of space. The data set name will consist of the database name, the table space name, the name of the utility it was used with, and the time of allocation. Allocation is by cylinder with a primary space of 115 and a secondary of 15: TEMPLATE DISKTMP2 DSN(&DB..&TS..&UTILNAME..T&TIME.) SPACE(115,15) CYL This next example shows how to use TEMPLATE in conjunction with LISTDEF . The LISTDEF creates a list of table spaces to COPY . Two templates are specified, one for a disk local copy and one for a tape remote copy. The tape and disk template each specify different UNIT values, the disk template specifies the data set disposition, and the tape template specifies an expiration date of January 2, 2005 (in Julian date format): LISTDEF COPYLIST INCLUDE TABLESPACE DBGL01.* EXCLUDE TABLESPACE DBGL01.TSNAME TEMPLATE DISKTMPL UNIT SYSDA DSN(&DB..&TS..COPY&IC.&LR.&PB..D&DATE..T&TIME.) DISP (MOD,CATLG,CATLG) TEMPLATE TAPETMPL UNIT T35901 DSN(&DB..&TS..COPY&IC.&LR.&PB..D&DATE..T&TIME.) EXPDL '2005002' COPY LIST COPYLIST COPYDDN (DISKTMPL) RECOVERYDDN (TAPETMPL) SHRLEVEL REFERENCE Testing Options for Lists and TemplateIt is a good idea to test lists and templates before using them with a utility. Doing so can help to validate the accuracy of the list contents and template specification. The OPTIONS utility control statement can be used to indicate processing options that apply across many utility executions. By specifying various options you can
The processing options set using the OPTIONS statement remain in effect for the duration of the job step, or until they are overridden by another OPTIONS statement in the same job step. The OPTIONS control statement requires no specific privileges to be specified. Of course, the process or user running the utility also must have the requisite authority to execute the actual utility that is being executed with the OPTIONS statement. OPTIONS is not a utility itself, but a control statement used to set up the environment for other utilities to run. The OPTIONS statement is simply stored until it is referenced by a DB2 utility. When the OPTIONS statement is referenced, the list specified for that utility will be expanded and the concurrency and compatibility restrictions of that utility will apply. In addition, the catalog tables necessary to expand the list also must be available for read-only access. Using OPTIONSTwo keywords can be used in conjunction with OPTIONS : PREVIEW and OFF . The PREVIEW keyword is similar to the JCL PREVIEW parameter. It is used to check for syntax errors in subsequent utility control cards and will not execute the utilities. For example, PREVIEW can be used in conjunction with LISTDEF to expand a list in order to verify that the proper database objects have been included in the list. The OFF keyword can be used to restore to the default options. Using OPTIONS OFF will not override the PREVIEW JCL parameter, which, if specified, remains in effect for the entire job step. When specifying OPTIONS OFF , no other OPTIONS keywords may be used. |
< Day Day Up > |