WSL format

Work statement lists (WSLs) are stored in ISPF tables in a data set that you specify. WSLs can be accessed by other users and are protected by RACF®. By storing WSLs in ISPF tables, they can easily be moved to other systems or installations.

+---------------------NOT programming interface information----------------------+

Each row in the ISPF table contains a work statement, and the order of these rows is important.

Note: WSLs are intended to be created and maintained by Db2 Admin Tool, and the implementation is subject to change. Therefore, although you can manually edit a work statement list (WSL), doing so can produce unexpected results.

A WSL can include the following items. These items are passed as input to ADBTEP2.

SQL statements

The following SQL statements are supported:

Data definitions, such as CREATE, DROP, ALTER, and RENAME
Authorization changes, such as GRANT and REVOKE
Data manipulation changes, such as INSERT, UPDATE, and DELETE

Db2 commands

The format is -command.

Example: -DIS GROUP

DSN Commands

The following DSN commands are supported:

BIND
DCLGEN
FREE
REBIND
RUN

Db2 Utility statements

The following Db2 utilities are supported:

CHECK
COPY
COPYTOCOPY
DIAGNOSE
LOAD
MERGECOPY
MODIFY
QUIESCE
REBUILD
RECOVER
REORG
REPAIR
REPORT
RUNSTATS
STOSPACE
UNLOAD

UTILFROM statements

The Db2 UTILFROM utility is a pseudo utility that directs ADBTEP2 to execute the utility control statements that are contained in a data set. Only one utility can be contained within the data set. Therefore, RUNSTATS and LOAD cannot both be included in one UTILFROM statement. The purpose of this utility is to allow a ADBTEP2 to execute the LOAD control statements that are generated by UNLOAD, REORG UNLOAD, and Db2 HPU. Because UNLOAD does not produce all the options that are required (such as SORTNUM), they can be added by using the ADD keyword.

Use the following format of UTILFROM with the ADD parameter:

UTILFROM dsname ADD(additional options)

The following example shows the ADD parameter:

UTILFROM ROYC.ROYCDOC1.CNTLC.PPP2
   ADD(SORTNUM 8 SORTDEVT SYSDA 
     WORKDDN(UTLUT1,UTLOUT) ERRDDN(UTLERR) 
     DISCARDDN(UTLDISC) MAPDDN(UTLMAP));

Start of change To add DISCARDDN information at the partition level during a LOAD operation, use the ADDPART parameter, as shown in the following example: End of change

UTILFROM TS5794.DC1Q.C0000602.CNT.T0001 
ADDPART(DISCARDDN(EJDISC))
ADD(SORTNUM 8 SORTDEVT SYSDA 
WORKDDN(UTLUT1,UTLOUT) ERRDDN(UTLERR) 
MAPDDN(UTLMAP) ENFORCE NO DISCARDS 0);

Functional comments

The following functional comments are supported:

--#SET ROWS_FETCH n: where n is a non-negative integer that indicates the maximum number of rows to be fetched for each subsequent SELECT statement. Use -1 to indicate that all rows should be fetched.
--#SET ROWS_OUT n: where n is a non-negative integer that indicates the maximum number of rows to be output for each subsequent SELECT statement. Use -1 to indicate that all rows should be output.
--#SET TERMINATOR n: where n is a one-byte character that is to terminate the next SQL statement. Any character is valid, except blank, comma, single quotation, double quotation, underscore, and parentheses.
--#SET ACCEPT_RC (ON/OFF) m n: where mor n is the SQLCODE that can be accepted for the SQL statements before the program stops. The maximum number of SQLCODE that can be listed is 5. Using --#SET ACCEPT_RC m n can accept SQLCODE m or n for the following single SQL statement. Using --#SET ACCEPT_RC ON m n can accept SQLCODE m or n for the following multiple SQL statements until the next --#SET ACCEPT_RC OFF occurs. If no SQLCODE is provided after --#SET ACCEPT_RC (ON/OFF), it means all SQLCODEs can be accepted.
--#SET MAXERRORS n: where n is the number of DSN commands that can fail before the program stops. Use -1 to indicate that the program should tolerate an unlimited number of errors for DSN commands.
--#PROCESS name: where name is the name of a process, such as DROP or UNLOAD. This comment indicates that the WSL is split and these processes are to be run as parallel jobs.

Note: IBM® reserves the right to use additional parameters in these functional comment statements. These parameters might be present in the statements that Db2 Admin Tool generates for ADBTEP2. Do not modify these statements unless you are requested to do so by your IBM service representative.

REXX execs

The format is

REXX execname
parameters

execname can be the name of a CLIST. Programs are not supported. Db2 programs can be executed by using the DSN command RUN.

Db2 Admin Tool instructions

These commands follow a product-specific syntax for performing certain complex operations. The syntax of these instructions is subject to change.

WSLs can include the following Db2 Admin Tool commands:

RESTART

Defines a restart point for the WSL.

If you run a WSL and it fails before completing, you can restart it from any defined restart point or from the point where it failed. A WSL can have many restart points, but only one point can be used for the restart.

The syntax is

--#RESTART name

where name is the name of the restart point. This name can be anything except YES, NO, FORCE, or a pure numeric value.

ADM PARALLEL and ADM ENDPARALLEL

ADM JOB and ADM ENDJOB

Indicates that statements are to run in parallel.

Some of the input processes to the WSL (for example, from Object Comparison Tool) include this parallel specification.

The ADM PARALLEL and ADM ENDPARALLEL statements signify the start and end points for jobs to be run in parallel. Within the PARALLEL and ENPARALLEL pair, the ADM JOB and ADM ENDJOB statements signify the start and end points for a particular job.

Optionally, a name can be specified for the parallel process in the following format:

ADM PARALLEL name

WSL statements that are not included in a PARALLEL and ENDPARALLEL pair are placed in a separate job.

For example, the following specification results in three jobs. The first two jobs run concurrently and the third one runs when the first two are complete.

ADM PARALLEL UNLOAD

ADM JOB
tasks for job1
ADM ENDJOB
  
ADM JOB
tasks for job2
ADM ENDJOB

ADM ENDPARALLEL

serial tasks

A parallel specification might be used to unload multiple tables in parallel, then run DDL to drop and redefine the tables, and then load the tables in parallel. In this case, the loads and unloads can be run in parallel to increase performance. The DDL is done in one job to avoid Db2 locking or serialization problems.

ADMIN ACCELERATOR

Performs some function on the accelerator.

Examples:

ADMIN ACCELERATOR ADD: Defines a table on the accelerator.
ADMIN ACCELERATOR ARCHIVE: Archives parts of partitioned by range table or tables on the accelerator or accelerators.
ADMIN ACCELERATOR DELETE: Deletes a table from the accelerator.
ADMIN ACCELERATOR DISABLE: Disables acceleration for the table or tables defined on the accelerator or accelerators.
ADMIN ACCELERATOR ENABLE: Enables acceleration or replication for the table or tables defined on the accelerator or accelerators.
ADMIN ACCELERATOR LOAD: Loads data of the accelerated table or tables to the accelerator.

ADMIN IF and ADMIN ENDIF

Defines a conditional block of statements, which may or may not be processed.

ADMIN IF DEFINEYES

Checks whether a table space has the DEFINE YES attribute. Some utilities do not work if the table space has the DEFINE NO attribute.

ADMIN IF DSEXISTS

Checks whether UNLOAD created a LOAD statement.

ADMIN REGISTER TOKEN

Registers whether UNLOAD was run.

Db2 Admin Tool support commands

These commands are associated with (or support) primary commands that are listed later in the batch statement list. For example, the ALLOC command is used to allocate files for a program (the primary command). Support command processing is deferred until the primary command is encountered. Support commands must immediately precede their primary command.

The following commands are considered Db2 Admin Tool support commands:

ADBSYSIN

Identifies the location of the input.

Many programs, including ADBTEP2, use the filename (or DDNAME) SYSIN. ADBTEP2 uses SYSIN for the batch statement list; therefore, ADBSYSIN is used to identify the location of the input. The format is

ADBSYSIN COPYDD(ddname)

where ddname contains the SYSIN for the program following the ADBSYSIN.

ADBPAUSE

Pauses the current run of ADBTEP2 or ADBTEPA at a certain point. ADBTEP2 and ADBTEPA can be restarted at that point.

ALLOC

Issues a TSO ALLOCATE command with the provided parameters. ALLOC is intended to support programs only. It is not a valid support command for a Db2 utility.

Example: ALLOC DD(DATAI001) DS('ROYC.ROYCDOC1.UNLD.PPP1') SHR

CHECKBEGIN and CHECKEND

Delimits a block of CHECK DATA statements. When CHECKEND is reached, Db2 Admin Tool identifies the parent and child tables in RI relationships with the table spaces that are identified in the CHECK DATA statements within the block and generates CHECK DATA statements to clear these tables of any CHECK-pending status. Any TSODELETE commands before the CHECKEND statement are executed for all the generated CHECK DATA statements. Any TEMPLATE statements before the CHECKEND are supplied to the utility for all the generated CHECK DATA commands.

Example: In the following example, the second set of TSODELETE and TEMPLATE statements apply to the CHECK DATA statements that might be generated for the parent and descendent tables:

CHECKBEGIN;                                        
TSODELETE 'JIMWC.EB12.CSUT1.T0001';                
TSODELETE 'JIMWC.EB12.CSOUT.T0001';                
TSODELETE 'JIMWC.EB12.CSERR.T0001';                
TEMPLATE UTLUT1 DSN 'JIMWC.EB12.CSUT1.T0001'       
         UNIT SYSDA;                               
TEMPLATE UTLOUT DSN 'JIMWC.EB12.CSOUT.T0001'       
         UNIT SYSDA;                               
TEMPLATE UTLERR DSN 'JIMWC.EB12.CSERR.T0001'       
         UNIT SYSDA;                               
CHECK DATA TABLESPACE DB2144.TS2144                
  ERRDDN(UTLERR) WORKDDN(UTLUT1,UTLOUT)            
  SORTDEVT SYSDA SORTNUM 4;                        
TSODELETE 'JIMWC.EB12.CSUT1.T0001';                
TSODELETE 'JIMWC.EB12.CSOUT.T0001';                
TSODELETE 'JIMWC.EB12.CSERR.T0001';                
TEMPLATE UTLUT1 DSN 'JIMWC.EB12.CSUT1.T0001'       
         UNIT SYSDA;                               
TEMPLATE UTLOUT DSN 'JIMWC.EB12.CSOUT.T0001'       
         UNIT SYSDA;                               
TEMPLATE UTLERR DSN 'JIMWC.EB12.CSERR.T0001'       
         UNIT SYSDA;                               
CHECKEND;

TEMPLATE

Defines a template to support utilities. ADBTEP2 passes this command to the Db2 utility processor. ADBTEP2 performs a partial simulation of the Db2 TEMPLATE function for template names that are not supported by Db2 (for example, SYSREC). The main difference between Db2 allocation of templates and the simulation is at failure, as the failure disposition is not honored. ADBTEP2 does not support utility wild cards.

TSODELETE

Issues a TSO DELETE command for the data set provided. If the DELETE fails, a DELETE NOSCRATCH is attempted. Processing continues even if TSODELETE is unsuccessful.

Delimiters

Each statement must end with the current delimiter character. The delimiter character is a semicolon unless a -#SET TERMINATOR functional comment precedes the statement. (A semicolon is the delimiter for all ADBTEP2 commands, and a WSL calls ADBTEP2.)

Quotation marks can be used with delimited identifiers in a statement. If you clone a WSL that includes a statement containing delimited identifiers, Db2 Admin Tool removes the quotation marks from the identifier if it does not require delimiters.

Example: A WSL contains the following DDL:

DDL  CREATE SYNONYM "PROJSYN" FOR "DBA282"."PROJ"

The cloned result does not contain the quotation marks:

COM  -- Created by DBA282 on 2002/07/23 at 15:23 by cloning of 
COM  -- source work stmt list  RESULT  from library  WSL.DATA  
DDL  CREATE SYNONYM PROJSYN FOR DBA282.PROJ

+------------------End of NOT programming interface information------------------+