DB2 administrative task scheduler
In Chapter 4, “DB2 infrastructure setup” on page 99, which describes the DB2 infrastructure of the scenario for this book, we used the administrative task scheduler (ADMT) to trigger batch jobs in the event any of the DB2 members started or stopped. We furthermore used ADMT for autonomic statistics monitoring to trigger RUNSTATS utility executions on objects that have no or outdated statistics. Autonomic statistics monitoring tasks are executable on any member of the data sharing group.
This appendix provides information about the implementation tasks that we performed to put the initial infrastructure in place and then describes the steps that we took to add batch jobs and regular autonomic statistic monitoring tasks to the ADMT task list.
This appendix describes the installation and use of the DB2 administrative task scheduler by detailing these activities in the following sections:
A.1 Implementation
The ADMT infrastructure that we implemented in our DB2 data sharing environment is illustrated in Figure A-1.
Figure A-1 ADMT data sharing overview
The illustration that is shown in Figure A-2 on page 485 provides an overview of the administration scheduler installation jobs and outlines the implementation tasks.
Figure A-2 Overview admin scheduler installation
A.1.1 Installing the DSNTIJMV job
DSNTIJMV creates a template of the administrative task scheduler (ADMT) JCL. You use this template to customize the JCL that you need to run ADMT in your environment. In our example, we installed the JCL procedure that is shown in Example A-1, once for each
DB2 member.
Example A-1 ADMT STC JCL
//*********************************************************************
//* JCL FOR PROCEDURE FOR THE STARTUP OF
//* THE DB2 ADMINISTRATIVE SCHEDULER ADDRESS SPACE.
//*
//* INSTALLATION MAY CHANGE PROGRAM LIBRARY
//* NAMES IN STEPLIB DD STATEMENT TO THE
//* LIBRARY IN WHICH DB2 MODULES ARE
//* LOADED USING THE PROCEDURE VARIABLE:
//* LIB
//*
//* Before using this proc
//* - Locate and review the settings for the following
//* parameters:
//* - DB2SSID: The name of this DB2 subsystem
//* - DFLTUID: The default ID used by Administrative Scheduler
//* to execute its tasks. Must differ from
//* the ID used to start this address space
//* - TRACE : Whether to activate tracing for the Admin-
//* istrative Scheduler (OFF or ON, default is OFF)
//*
//* Following optional parameters of DSNADMT0 may be added:
//* - MAXTHD: The maximum number of threads that can execute
//* scheduled tasks concurrently. Default is 99
//* - ERRFREQ: Interval in minutes between the display of
//* two successive identical error messages to
//* the console. Default is 1
//* - STOPONDB2STOP: stops the Administrative Scheduler when
//* DB2 comes down. No value needed
//*
//*********************************************************************
//D0Z1ADMT PROC LIB='DB0ZT.SDSNLOAD',
// DB2SSID=D0Z1,
// DFLTUID=D0ZGADMT,
// TRACE=ON,
// MAXTHD=10,
// MAXHIST=10
//STARTADM EXEC PGM=DSNADMT0,DYNAMNBR=100,REGION=0K,
// PARM=('DB2SSID=&DB2SSID',
// ' DFLTUID=&DFLTUID',
// ' TRACE=&TRACE',
// ' MAXTHD=&MAXTHD',
// ' MAXHIST=&MAXHIST')
//STEPLIB DD DISP=SHR,DSN=DB0ZT.SDSNEXIT
// DD DISP=SHR,DSN=&LIB
//ADMTDD1 DD DISP=SHR,DSN=DB0ZD.
We configured the STC JCL for the D0Z2ADMT started task by using the JCL template that is shown in Example A-1 on page 485, which has a procedure name of D0Z2ADMT and has the DB2SSID JCL parameter set to D0Z2.
A.1.2 Installing the DSNTIJIN job
DSNTIJIN defines the VSAM cluster for the ADMT task list data set that is used across all instances of the administrative task scheduler of our data sharing group. We ran the DEFINE CLUSTER command that is shown in Example A-2 to create the VSAM cluster that is used by ADMT STCs D0Z1ADMT and D0Z2ADMT.
Example A-2 ADMT TASKLIST data set - DEFINE CLUSTER
DEFINE CLUSTER -
( NAME(DB0ZD.TASKLIST) -
KILOBYTES(40000 40) -
RECORDSIZE(8120 8120) -
CISZ(8192) -
NUMBERED -
SHAREOPTIONS(4 3) ) -
DATA -
( NAME(DB0ZD.TASKLIST.DATA) -
)
A.1.3 Installing the DSNTIJRA job
DSNTIJRA performs the following security-related tasks in RACF.
Defining RACF user IDs
DSNTIJRA defines one user ID for each ADMT started task and one default user ID that is shared across our ADMTs for triggering tasks that we defined in ADMT. The user IDs that we created are shown in the RACF commands that are illustrated in Example A-3.
Example A-3 Create ADMT user IDs
/* STC user IDs */
AU D0Z1ADMT +
DATA('DEFAULT EXECUTION UID') +
NAME('DB2 ADMIN SCHEDULER EXECUTION UID') +
OMVS( UID(0) SHARED PROGRAM(/bin/sh ) HOME(/u/d0z1admt)) +
DFLTGRP(DB2) +
OWNER(DB2)
AU D0Z2ADMT +
DATA('DEFAULT EXECUTION UID') +
OMVS( UID(0) SHARED PROGRAM(/bin/sh ) HOME(/u/d0z2admt)) +
NAME('DB2 ADMIN SCHEDULER EXECUTION UID') +
DFLTGRP(DB2) +
OWNER(DB2)
/* ADMT STC default user that is used in STC JCL DFLTUID parm */
AU D0ZGADMT +
DATA('DEFAULT EXECUTION UID') +
OMVS( UID(0) SHARED PROGRAM(/bin/sh ) HOME(/u/d0zgadmt)) +
NAME('DB2 ADMIN SCHEDULER EXECUTION UID') +
DFLTGRP(DB2) +
OWNER(DB2)
Associating STC user IDs with ADMT STCs
We used the RACF commands that are shown in Example A-4 to associate the STC users that we defined in Example A-3 with their corresponding STC names. We additionally connected each user to RACF group SYS1, as we used that RACF group as a default group for the ADMT STCs. If we had not connected the ADMT users to that group, the ADMT STCs would not have been associated with their user IDs, as defined in the RACF commands of Example A-4.
Example A-4 RACF started class for ADMT
CO D0Z1ADMT GROUP(SYS1)
/* associate user D0Z1ADMT with STC name D0Z1ADMT */
RDEF STARTED D0Z1ADMT.* +
STDATA(USER(D0Z1ADMT) GROUP(SYS1))
CO D0Z2ADMT GROUP(SYS1)
/* associate user D0Z2ADMT with STC name D0Z2ADMT */
RDEF STARTED D0Z2ADMT.* +
STDATA(USER(D0Z2ADMT) GROUP(SYS1))
SETR REFRESH GENCMD(*) GENERIC(*) RACLIST(STARTED)
RACF program control
We used the RACF commands that are shown in Example A-5 to define RACF program control for the ADMT programs.
Example A-5 RACF program control for ADMT
SETROPTS WHEN(PROGRAM)
RDEFINE PROGRAM DSNADMT0 +
ADDMEM('DB0ZT.SDSNLOAD'//NOPADCHK) +
UACC(READ)
RDEFINE PROGRAM DSNARRS +
ADDMEM('DB0ZT.SDSNLOAD'//NOPADCHK) +
UACC(READ)
RDEFINE PROGRAM DSN3ID00 +
ADDMEM('DB0ZT.SDSNLOAD'//NOPADCHK) +
UACC(READ)
SETROPTS WHEN(PROGRAM) REFRESH
RACF passtickets for ADMT started tasks
We used the RACF commands that are shown in Example A-6 to allow for RACF passtickets to be used by the ADMT STCs.
Example A-6 RACF passtickets for ADMT STCs
/* Activate RACF class PTKTDATA if not yet activated*/
SETROPTS CLASSACT(PTKTDATA)
SETROPTS RACLIST(PTKTDATA)
SETROPTS GENERIC(PTKTDATA) GENCMD(PTKTDATA)
/* set up BPX.DAEMON.HFSCTL FACILITY class if not yet configured */
RDEFINE FACILITY BPX.DAEMON.HFSCTL UACC(NONE)
/* permit ADMT STC uses to read BPX.DAEMON.HFSCTL */
PERMIT BPX.DAEMON.HFSCTL CL(FACILITY) ID(D0Z1ADMT) ACCESS(READ)
PERMIT BPX.DAEMON.HFSCTL CL(FACILITY) ID(D0Z2ADMT) ACCESS(READ)
/* set up BPX.SERVER FACILITY class if not yet configured */
RDEFINE FACILITY BPX.SERVER UACC(NONE)
/* permit ADMT STC users to read BPX.SERVER */
PERMIT BPX.SERVER CL(FACILITY) ID(D0Z1ADMT) ACCESS(READ)
PERMIT BPX.SERVER CL(FACILITY) ID(D0Z2ADMT) ACCESS(READ)
/* set up the BPX.DAEMON FACILITY class if not yet configured */
RDEFINE FACILITY BPX.DAEMON UACC(NONE)
/* permit ADMT STC users to read BPX.DAEMON */
PERMIT BPX.DAEMON CL(FACILITY) ID(D0Z1ADMT) ACCESS(READ)
PERMIT BPX.DAEMON CL(FACILITY) ID(D0Z2ADMT) ACCESS(READ)
/* Define PTKTDATA profiles STC procedures D0Z1ADMT, D0Z2ADMT */
RDEFINE PTKTDATA IRRPTAUTH.D0Z1ADMT.* UACC(NONE)
RDEFINE PTKTDATA IRRPTAUTH.D0Z2ADMT.* UACC(NONE)
RDEFINE PTKTDATA D0Z1ADMT +
SSIGNON(KEYMASKED(CACD4AD6D79ECA71)) +
UACC(NONE) APPLDATA('NO REPLAY PROTECTION')
RDEFINE PTKTDATA D0Z2ADMT +
SSIGNON(KEYMASKED(CACD4AD6D79ECA71)) +
UACC(NONE) APPLDATA('NO REPLAY PROTECTION')
/* permit ADMT STC users to access PTKTDATA profiles */
PERMIT IRRPTAUTH.D0Z1ADMT.* CL(PTKTDATA) +
ID(D0Z1ADMT) ACCESS(UPDATE)
PERMIT IRRPTAUTH.D0Z2ADMT.* CL(PTKTDATA) +
ID(D0Z2ADMT) ACCESS(UPDATE)
PERMIT D0Z1ADMT CL(PTKTDATA) +
ID(D0Z1ADMT) ACCESS(UPDATE)
PERMIT D0Z2ADMT CL(PTKTDATA) +
ID(D0Z2ADMT) ACCESS(UPDATE)
/* refresh RACF changes */
SETROPTS RACLIST (PTKTDATA) REFRESH
SETROPTS RACLIST (FACILITY) REFRESH
SETROPTS REFRESH GENERIC(*) RACLIST(PTKTDATA)
A.1.4 Installing the DSNTIJRT job
DSNTIJRT creates DB2 tables, packages, and stored procedures that are required for DB2 routines that are provided for DB2 administration. This process includes creating and granting the DB2 objects that are required for running the administrative task scheduler. If you do not run DSNTIJRT and the administrative task scheduler starts, the administrative task scheduler issues error message DSNA679I. DSNTIJRT creates the following objects:
•Tables
 – SYSIBM.ADMIN_TASKS
 – SYSIBM.ADMIN_TASKS_HIST
•Temporary tables that are used by stored procedures
 – SYSIBM.BIN_REC_INPUT
 – SYSIBM.BIN_REC_OUTPUT
 – SYSIBM.BUFFERPOOL_STATUS
 – SYSIBM.DATA_SHARING_GROUP
 – SYSIBM.DB_STATUS
 – SYSIBM.DB2_CMD_OUTPUT
 – SYSIBM.DB2_SYSPARM
 – SYSIBM.DB2_THREAD_STATUS
 – SYSIBM.DDF_CONFIG
 – SYSIBM.DSLIST
 – SYSIBM.DSN_SUBCMD_OUTPUT
 – SYSIBM.JES_SYSOUT
 – SYSIBM.JOB_JCL
 – SYSIBM.SERVICE_SQL_OUTPUT
 – SYSIBM.SMS_INFO
 – SYSIBM.SMS_OBJECTS
 – SYSIBM.SYSLOG
 – SYSIBM.SYSTEM_HOSTNAME
 – SYSIBM.TEXT_REC_INPUT
 – SYSIBM.TEXT_REC_OUTPUT
 – SYSIBM.USS_CMD_OUTPUT
 – SYSIBM.UTILITY_JOB_STATUS
 – SYSIBM.UTILITY_OBJECTS
 – SYSIBM.UTILITY_RETCODE
 – SYSIBM.UTILITY_SORT_OBJ
 – SYSIBM.UTILITY_SORT_OUT
 – SYSIBM.UTILITY_STMT
 – SYSIBM.UTILITY_SYSPRINT
•Stored procedures and user-defined table functions
ADMT uses the following stored procedures for task scheduling and administrative
routine enablement:
 – Administrative task scheduler routines
 • DSNADM.ADMIN_TASK_LIST
 • DSNADM.ADMIN_TASK_OUTPUT
 • DSNADM.ADMIN_TASK_STATUS
 • SYSPROC.ADMIN_TASK_ADD
 • SYSPROC.ADMIN_TASK_CANCEL
 • SYSPROC.ADMIN_TASK_REMOVE
 • SYSPROC.ADMIN_TASK_UPDATE
 – Administrative enablement routines
 • SYSPROC.ADMIN_COMMAND_DB2
 • SYSPROC.ADMIN_COMMAND_DSN
 • SYSPROC.ADMIN_COMMAND_UNIX
 • SYSPROC.ADMIN_DS_BROWSE
 • SYSPROC.ADMIN_DS_DELETE
 • SYSPROC.ADMIN_DS_LIST
 • SYSPROC.ADMIN_DS_RENAME
 • SYSPROC.ADMIN_DS_SEARCH
 • SYSPROC.ADMIN_DS_WRITE
 • SYSPROC.ADMIN_INFO_HOST
 • SYSPROC.ADMIN_INFO_SMS
 • SYSPROC.ADMIN_INFO_SQL
 • SYSPROC.ADMIN_INFO_SSID
 • SYSPROC.ADMIN_INFO_SYSLOG
 • SYSPROC.ADMIN_INFO_SYSPARM
 • SYSPROC.ADMIN_JOB_CANCEL
 • SYSPROC.ADMIN_JOB_FETCH
 • SYSPROC.ADMIN_JOB_QUERY
 • SYSPROC.ADMIN_JOB_SUBMIT
 • SYSPROC.ADMIN_UTL_EXECUTE
 • SYSPROC.ADMIN_UTL_MODIFY
 • SYSPROC.ADMIN_UTL_MONITOR
 • SYSPROC.ADMIN_UTL_SCHEDULE
 • SYSPROC.ADMIN_UTL_SORT
 • SYSPROC.DSN_WLM_APPLENV
 • SYSPROC.GET_CONFIG
 • SYSPROC.GET_MESSAGE
 • SYSPROC.GET_SYSTEM_INFO
•Packages
 – DSNADM.DSNADMDW
 – DSNADM.DSNADMGC
 – DSNADM.DSNADMGU
 – DSNADM.DSNADMGV
 – DSNADM.DSNADMGW
 – DSNADM.DSNADMIH
 – DSNADM.DSNADMIV
 – DSNADM.DSNADMIZ
 – DSNADM.DSNADMJF
 – DSNADM.DSNADMJP
 – DSNADM.DSNADMJQ
 – DSNADM.DSNADMJS
 – DSNADM.DSNADMSB
 – DSNADM.DSNADMSS
 – DSNADM.DSNADMTA
 – DSNADM.DSNADMTC
 – DSNADM.DSNADMTD
 – DSNADM.DSNADMTH
 – DSNADM.DSNADMTL
 – DSNADM.DSNADMTO
 – DSNADM.DSNADMTR
 – DSNADM.DSNADMTS
 – DSNADM.DSNADMTU
 – DSNADM.DSNADMUM
 – DSNADM.DSNADMUS
 – DSNADMSI.DSNADMSI
A.1.5 ADMTPROC DSNZPARM
The ADMTPROC DSNZPARM contains the JCL procedure that is used to start the DB2 administrative task scheduler that is associated with the DB2 member. To disable the scheduler, provide a blank value for this parameter. In our environment, we configured the following JCL procedure names in ADMTPROC DSNZPARM:
•Member D0Z1: D0Z1ADMT
•Member D0Z2: D0Z2ADMT
A.2 Administrative scheduler operation
Figure A-1 on page 484 provides an architecture overview of operating ADMT in
data sharing.
In data sharing, ADMT provides one administrative scheduler STC per DB2 member, with each ADMT instance running in the same LPAR as its corresponding DB2 member. The ADMT STC names are unique across the data sharing group. In our environment, each ADMT STC uses its own STC user, which must be different from the user that is specified in the DFLTUID parameter of the STC JCL. The ADMT STCs share one VSAM tasklist data set and the ADMT DB2 tables.
A.2.1 Starting ADMT
ADMT is started by DB2 during startup and stopped manually unless you provide the ADMT start STOPONDB2STOP parameter during ADMT start, as shown in Example A-7. If you use that parameter, ADMT is stopped as part of the DB2 shutdown.
Example A-7 ADMT parameter STOPONDB2STOP
//STARTADM EXEC PGM=DSNADMT0,DYNAMNBR=100,REGION=0K,
// PARM=(’DB2SSID=&DB2SSID’,
// ’ DFLTUID=&DFLTUID’,
// ’ TRACE=&TRACE’
// ’ MAXTHD=&MAXTHD’
// ’ ERRFREQ=1440’
// ’ STOPONDB2STOP’)
In our environment, ADMT is not stopped with the DB2 shutdown because we do not use the STOPONDB2TOP parameter. Upon a successful ADMT start, we observed the runtime messages that are shown in Figure A-3.
DSNA671I DSNA6MAI THE ADMIN SCHEDULER D0Z1ADMT IS STARTING
DSNA672I DSNA6MAI START COMMAND FOR ADMIN SCHEDULER D0Z1ADMT NORMAL COMPLETION
Figure A-3 ADMT start messages
When you stop DB2, ADMT loses its connection to DB2 and writes out the message that is shown in Figure A-4.
DSNA679I DSNA6BUF THE ADMIN SCHEDULER D0Z2ADMT CANNOT ACCESS TASK LIST SYSIBM.ADMIN_TASK
DB2 CODE X'00F30002' IN IFI IDENTIFY
Figure A-4 ADMT DB2 unavailable message
A.2.2 Manually operating ADMT
You can stop and start ADMT any time and you can use ADMT modify commands to change its runtime behavior. In our environment, we used the commands that are shown in Example A-8 to operate the ADMT tasks manually for both DB2 members.
Example A-8 Commands operating ADMT
RO SC63,S D0Z1ADMT /* start D0Z1ADMT in SC63 */
RO SC64,S D0Z2ADMT /* start D0Z2ADMT in SC64 */
RO SC63,S D0Z1ADMT /* stop D0Z1ADMT in SC63 */
RO SC64,S D0Z2ADMT /* stop D0Z2ADMT in SC64 */
RO SC63,F D0Z1ADMT,appl=shutdown /* stop D0Z1ADMT in SC63 */
RO SC64,F D0Z2ADMT,appl=shutdown /* stop D0Z1ADMT in SC64 */
RO SC63,F D0Z1ADMT,appl=trace=on /* start trace in D0Z1ADMT in SC63 */
RO SC64,F D0Z2ADMT,appl=trace=on /* start trace in D0Z2ADMT in SC64 */
RO SC63,F D0Z1ADMT,appl=trace=off /* stop trace in D0Z1ADMT in SC63 */
RO SC64,F D0Z2ADMT,appl=trace=off /* stop trace in D0Z2ADMT in SC64 */
A.3 Using ADMT for DB2STOP, DB2START, and statistics monitoring
We used the administrative scheduler to trigger batch jobs in case a DB2 member is stopped or started. We used the administrative scheduler to run the RUNSTATS utility on objects that have no or outdated statistics. To implement this functionality, we completed the
following tasks:
1. Create a REXX exec library for storing the @OSCMDS REXX program that runs DB2 commands during DB2STOP event processing.
2. Create a Sysplex-wide JES2 include library for job skeletons to be used across both ADMT instances.
3. Create LPAR-specific JCL libraries to include JCL members to cater to the system affinity of an ADMT-submitted batch JCL.
4. Call the ADMIN_TASK_ADD stored procedure to add DB2START and DB2STOP job submission tasks for both members of the data sharing group.
5. Call the ADMIN_TASK_ADD stored procedure to add calls to the ADMIN_UTL_MONITOR stored procedure to monitor and resolve outdated statistics on user objects and on the DSNDB06.SYSTSKEYS table space.
A.3.1 DB2START processing
With ADMT, you can define tasks for DB2START event processing. In the example that is shown in Example A-9, we start the SYSPROC.ADMIN_TASK_ADD stored procedure to direct ADMT to submit JCL member D0Z1STRT of JCL library DB0ZM.D0ZGADMT.JCL whenever member D0Z1 is started. We ran a similar SQL call statement to enable DB2START processing for DB2 member D0Z2.
Example A-9 ADMT DB2START ADMIN_TASK_ADD invocation
CALL SYSPROC.ADMIN_TASK_ADD
(NULL,NULL,NULL,NULL,
NULL,NULL,NULL,'DB2START',NULL,NULL,'D0Z1',
NULL,NULL,NULL,'DB0ZM.D0ZGADMT.JCL','D0Z1STRT','YES',
'D0Z1STRT','D0Z1 START',?,?)
Upon successful completion, we used the ADMIN_TASK_LIST user-defined function (UDF) to list the DB2START events that are registered in the administrative scheduler. The query result is shown in Figure A-5.
SELECT
substr(TRIGGER_TASK_NAME,1,8) as TASKNAME
, DB2_SSID
, SUBSTR(JCL_LIBRARY,1,18) AS JCL_LIBRARY
, JCL_MEMBER
, JOB_WAIT
, TASK_NAME
, DESCRIPTION
, CREATOR
, LAST_MODIFIED
FROM table(DSNADM.ADMIN_TASK_LIST()) as tasklist
WHERE TRIGGER_TASK_NAME = 'DB2START';
---------+---------+---------+---------+---------+---------+---------+---
TASKNAME DB2_SSID JCL_LIBRARY JCL_MEMBER JOB_WAIT TASK_NAME
---------+---------+---------+---------+---------+---------+---------+---
DB2START D0Z1 DB0ZM.D0ZGADMT.JCL D0Z1STRT YES D0Z1STRT
DB2START D0Z2 DB0ZM.D0ZGADMT.JCL D0Z2STRT YES D0Z2STRT
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
DSNE616I STATEMENT EXECUTION WAS SUCCESSFUL, SQLCODE IS 100
Figure A-5 Query DB2START events
JCL library and system affinity
The SQL call statement that is shown in Example A-9 on page 493 references JCL library DB0ZM.D0ZGADMT.JCL as a data set that stores the JCL member. Job submission by ADMT requires affinity with the system in which the ADMT’s DB2 member runs; otherwise, job D0Z1STRT fails during DB2 command processing. Job D0Z1STRT ensures system affinity through hardcoded JES2 JCL control statements, which requires extra care in case a DB2 member is moved to a different LPAR. To solve this problem, we created two JCL libraries, one for system SC63 and one for system SC64:
•DB0ZM.SC63.JCL
•DB0ZM.SC64.JCL
We then reference these data sets by defining a common data set alias name that uses the &SYSNAME symbolic variable in the data set alias definition. The alias name is identical to the JCL library name that we used in the ADMT task definition in Example A-9 on page 493. With this technique, the alias name references the appropriate system-related JCL data set, depending on the system (SC63 or SC64) from which the reference is made. The define alias control statement that we used is shown in Example A-10.
Example A-10 Define JCL data set alias using symbolicrelate
def alias (name('DB0ZM.D0ZGADMT.JCL') symbolicrelate('db0zm.&sysname..jcl')
JCL member D0Z1STRT
JCL member D0Z1STR is used to issue a series of DB2 commands that you usually want to run soon after DB2 becomes available. In our scenario, the commands we run have the following purpose:
•Start trace IFCID 318 to enable dynamic statement cache statistics.
•Start audit trace class 10 to capture detail information about authorization failures.
•Issue a START PROFILE command to activate the profile that we defined in 4.3.17, “Using DB2 profiles” on page 180.
•Display an activated trace.
•Display the utility status.
•Display the databases in restricted status.
•Display the spaces in restricted status.
The D0Z1STRT JCL that we created for D0Z1 ADMT DB2START processing is shown in Example A-11.
Example A-11 DB2START D0Z1STRT JCL
//D0Z1STRT JOB (ZACCTNUM),REGION=0M,
// CLASS=A,
// MSGLEVEL=(1,1)
/*JOBPARM S=SC64,L=9999 1
// JCLLIB ORDER=(DB0ZM.D0ZGADMT.INCLUDE) 2
// SET SSID=D0Z1 3
// INCLUDE MEMBER=&SSID.STRT 4
1. We coded a JOBPARM JES control statement to define system affinity. The job that is shown in Example A-11 runs on system SC63.
2. The JCLLIB statement refers to a library that is used to include JCL templates that are used in DB2 data sharing across administrative scheduler instances for job submission.
3. We set the SSID variable to the name of the DB2 subsystem ID. The variable is then used for resolving include member names and to pass the DB2 subsystem IDs parameter for JCL and program parameters processing.
4. The JCL shown in Example A-11 on page 494 uses the SSID variable to include the JCL template D0Z1STRT from JCLLIB data set DB0ZM.D0ZGADMT.INCLUDE.
JCL include member D0Z1STRT
Example A-11 on page 494 references include template D0Z1STRT. D0Z1STRT contains a JCL template that contains a JCL job step to run a series of DB2 commands against the DB2 system that are referred to by the SSID variable. The D0Z1STRT JCL template that we use is shown in Example A-12.
Example A-12 JCL template D0Z1STRT
//STRT01 EXEC PGM=IKJEFT01,DYNAMNBR=20,TIME=1440,
// PARM='DSN S(&SSID.)' 1
//STEPLIB DD DISP=SHR,DSN=DB0ZT.SDSNEXIT
// DD DISP=SHR,DSN=DB0ZT.SDSNLOAD
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD *
-START TRACE (P) CLASS(30) DEST(SMF) IFCID(318) 2
-START TRACE (AUDIT) CLASS(10) DEST(SMF)
-START PROFILE
-DIS PROFILE
-DIS TRACE
-DIS UTIL(*)
-DIS DB(*) RESTRICT LIMIT(*)
-DIS DB(*) SPACE(*) RESTRICT LIMIT(*)
END
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
1. The SSID variable is passed in by the D0Z1STRT job described in “JCL member D0Z1STRT” on page 494.
2. This part of the JCL shows the DB2 commands that are required to complete the tasks that are described in “JCL member D0Z1STRT” on page 494.
Administrative scheduler runtime messages
When we started DB2 member D0Z1, we observed the ADMT runtime messages that are shown in Figure A-6, which resulted from D0Z1ADMT DB2START processing.
SDSF OUTPUT DISPLAY D0Z1ADMT STC24540 DSID 2 LINE 34 COLS 21- 100
COMMAND INPUT ===> SCROLL ===> CSR
$HASP100 D0Z1STRT ON INTRDR FROM STC24540 D0Z1ADMT
IRR010I USERID D0ZGADMT IS ASSIGNED TO THIS JOB.
Figure A-6 Administrative scheduler DB2START messages
The ADMT trace data provided the information about the execution of D0Z1STRT, as shown in Figure A-7.
(IITHD) Receiving DB2 START event
(IIEVENT) - DB2 Subsystem = "D0Z1"
(IIEVENT) - Event = -1
(triggerSchedules) entering
(db2_OPEN) threadid=251464000000000D connected]
(TTHD000) Signal received with command = 1
(TTHD000) Execution begins for task = 2
(TTHD000) Execution begins at time 2012-08-12-14.22.47.000000
(TTHD000) num invocations = 6
(TTHD000) PassTicket generated for user = "D0ZGADMT"
(TTHD000) logged in
(TTHD000ÝJ¨) allocating JCL internal reader data set
(TTHD000ÝJ¨) opening JCL data set = "//'DB0ZM.D0ZGADMT.JCL(D0Z1STRT)'"
(TTHD000ÝJ¨) opening JCL internal reader data set
(TTHD000ÝJ¨) writing records to JCL internal reader data set
(TTHD000ÝJ¨) written records = 7
(TTHD000ÝJ¨) closing JCL data set
(TTHD000ÝJ¨) closing JCL internal reader data set
(TTHD000ÝJ¨) deallocating JCL internal reader data set
(TTHD000ÝJ¨) jobid = "JOB24643"
(TTHD000ÝJ¨) JCL job submitted, jobid = "JOB24643"
(TTHD000ÝJ¨) waiting for job status... (TTHD000ÝJ¨) execution duration (in nb polls) = 1
(TTHD000ÝJ¨) status found for JCL job = "JOB24643"
(TTHD000ÝJ¨) max_rc = 0
(TTHD000ÝJ¨) comp_type = 1
(TTHD000) logged out
(TTHD000) Execution status COMPLETED
(TTHD000) Execution ends at time 2012-08-12-14.22.48.000000
Figure A-7 Administrative scheduler DB2START trace
Verifying the status of DB2START processing
You can verify the status of DB2START processing by using the ADMIN_TASK_STATUS table UDF to query the ADMT status. The result of the query that we ran is provided in Figure A-8.
SELECT
SUBSTR(TASK_NAME,1,8) AS TASKNAME
, SUBSTR(STATUS,1,10) AS STATE
, NUM_INVOCATIONS AS #INV
, SUBSTR(CHAR(START_TIMESTAMP),1,19) AS BETS
, SUBSTR(CHAR(END_TIMESTAMP),1,19) AS ENTS
, JOB_ID
, DB2_SSID AS SSID
FROM table(DSNADM.ADMIN_TASK_STATUS()) as taskstatus
where task_name = 'D0Z1STRT'
---------+---------+---------+---------+---------+---------+---------+---------
TASKNAME STATE #INV BETS ENTS JOB_ID SSID
---------+---------+---------+---------+---------+---------+---------+---------
D0Z1STRT COMPLETED 6 2012-08-12-14.22.47 2012-08-12-14.22.48 JOB24643 D0Z1
Figure A-8 Query DB2START processing status
The query that is shown in Figure A-8 on page 496 provides information about the most recent DB2START event run. You can use the UDF to obtain a history of recent runs. You can limit the number of rows to be returned by passing a numeric input parameter in the UDF interface. An example of such a query and its processing result is illustrated in Figure A-9.
SELECT
SUBSTR(TASK_NAME,1,8) AS TASKNAME
, SUBSTR(STATUS,1,10) AS STATE
, NUM_INVOCATIONS AS #INV
, SUBSTR(CHAR(START_TIMESTAMP),1,19) AS BETS
, SUBSTR(CHAR(END_TIMESTAMP),1,19) AS ENTS
, JOB_ID
, DB2_SSID AS SSID
FROM table(DSNADM.ADMIN_TASK_STATUS(10)) as taskstatus
where task_name = 'D0Z1STRT'
---------+---------+---------+---------+---------+---------+---------+--------
TASKNAME STATE #INV BETS ENTS JOB_ID SSID
---------+---------+---------+---------+---------+---------+---------+--------
D0Z1STRT COMPLETED 1 2012-08-12-02.03.46 2012-08-12-02.03.48 JOB24557 D0Z1
D0Z1STRT COMPLETED 2 2012-08-12-02.14.27 2012-08-12-02.14.30 JOB24565 D0Z1
D0Z1STRT COMPLETED 3 2012-08-12-03.36.19 2012-08-12-03.36.20 JOB24584 D0Z1
D0Z1STRT COMPLETED 4 2012-08-12-03.41.18 2012-08-12-03.41.19 JOB24594 D0Z1
D0Z1STRT COMPLETED 5 2012-08-12-14.11.31 2012-08-12-14.11.32 JOB24632 D0Z1
D0Z1STRT COMPLETED 6 2012-08-12-14.22.47 2012-08-12-14.22.48 JOB24643 D0Z1
DSNE610I NUMBER OF ROWS DISPLAYED IS 6
Figure A-9 Query DB2START history
A.3.2 DB2STOP processing
With ADMT, you can define tasks for DB2STOP event processing. In Example A-13, we start the SYSPROC.ADMIN_TASK_ADD stored procedure to inform ADMT to submit JCL member D0Z1STOP of JCL library DB0ZM.D0ZGADMT.JCL whenever member D0Z1 is stopped. We ran a similar SQL call statement to enable DB2STOP processing for DB2 member D0Z2.
Example A-13 ADMT DB2STOP ADMIN_TASK_ADD invocation
CALL SYSPROC.ADMIN_TASK_ADD
(NULL,NULL, NULL,NULL,
NULL,NULL,NULL,'DB2STOP',NULL,NULL,'D0Z1',
NULL,NULL,NULL,'DB0ZM.D0ZGADMT.JCL','D0Z1STOP','YES',
'D0Z1STOP','DB0Z1 STOP',?,?)
Upon successful completion, we used the ADMIN_TASK_LIST user-defined function (UDF) to list the DB2STOP events that are registered in the administrative scheduler. The query result is shown in Figure A-10.
SELECT
substr(TRIGGER_TASK_NAME,1,8) as TASKNAME
, DB2_SSID
, SUBSTR(JCL_LIBRARY,1,18) AS JCL_LIBRARY
, JCL_MEMBER
, JOB_WAIT
, TASK_NAME
, DESCRIPTION
, CREATOR
, LAST_MODIFIED
FROM table(DSNADM.ADMIN_TASK_LIST()) as tasklist
WHERE TRIGGER_TASK_NAME = 'DB2STOP';
---------+---------+---------+---------+---------+---------+---------+-
TASKNAME DB2_SSID JCL_LIBRARY JCL_MEMBER JOB_WAIT TASK_NAME
---------+---------+---------+---------+---------+---------+---------+-
DB2STOP D0Z1 DB0ZM.D0ZGADMT.JCL D0Z1STOP YES D0Z1STOP
DB2STOP D0Z2 DB0ZM.D0ZGADMT.JCL D0Z2STOP YES D0Z2STOP
DSNE610I NUMBER OF ROWS DISPLAYED IS 2
Figure A-10 Query DB2STOP events
JCL member D0Z1STOP
JCL member D0Z1STOP is used to run a series of DB2 commands that you usually want to run when DB2 shuts down. In our scenario, the commands that we run have the
following purposes:
•Display the DB2 threads.
•Display an activated trace.
•Display the utility status.
•Display the databases in restricted status.
•Display the spaces in restricted status.
The D0Z1STOP JCL that we created for D0Z1 ADMT DB2STOP processing is shown in Example A-14.
Example A-14 DB2STOP D0Z1STOP JCL
//D0Z1STOP JOB (ZACCTNUM),REGION=0M,
// CLASS=A,
// MSGLEVEL=(1,1)
/*JOBPARM S=SC63,L=9999
// JCLLIB ORDER=(DB0ZM.D0ZGADMT.INCLUDE)
// SET SSID=D0Z1
// INCLUDE MEMBER=&SSID.STOP
The SSID and JOBPARM setting and the JCLLIB statement that are used in Example A-14 are similar to the ones in Example A-11 on page 494. For information about these settings, see Example A-11 on page 494.
JCL include member D0Z1STOP
Example A-14 on page 498 references include template D0Z1STOP. D0Z1STOP contains a JCL template that consists of a JCL job step that runs a series of DB2 commands against the DB2 system that is referred to by the SSID variable. The D0Z1STOP JCL template that we use is shown in Example A-15.
Example A-15 JCL template D0ZASTOP
//STOP01 EXEC PGM=IKJEFT01,DYNAMNBR=20,TIME=1440,
// PARM='%@OSCMD' 1
//STEPLIB DD DISP=SHR,DSN=DB0ZT.SDSNEXIT
// DD DISP=SHR,DSN=DB0ZT.SDSNLOAD
// DD DISP=SHR,DSN=DB0ZM.RUNLIB.LOAD
//SYSEXEC DD DISP=SHR,DSN=DB0ZM.D0ZGADMT.EXEC 2
//CMDIN DD DISP=SHR,DSN=DB0ZM.D0ZGADMT.INCLUDE(&SSID.STOC) 3
//SYSTSPRT DD SYSOUT=*
//SYSTSIN DD DUMMY
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
1. In DB2STOP processing, you cannot use the TSO batch DSN processor to process DB2 commands because DB2 has been stopped and does not allow for any further work to be submitted through the traditional DB2 interfaces. Thus, we use SDSF REXX to perform DB2 command processing through an operating system console. The logic of the SDSF REXX program is illustrated in Example A-17.
2. The @OSCMD REXX program is stored in the PO data set that is referenced by the SYSEXEC DD statement.
3. JCL DD statement CMDIN refers to the data set that contains the DB2 commands to be run through SDSF REXX in case of DB2STOP processing.
CMDIN data set
In our environment, the CMDIN data set contains the DB2 commands that shown in Example A-16.
Example A-16 CMDIN DB2 console commands
-D0Z1 DIS TRACE
-D0Z1 DIS THD(*) LIMIT(*)
-D0Z1 DIS UTIL(*)
-D0Z1 DIS DB(*) RESTRICT LIMIT(*)
-D0Z1 DIS DB(*) SPACE(*) RESTRICT LIMIT(*)
@OSCMD SDSF REXX program
During DB2STOP processing, ADMT submits a batch job that runs REXX program @OSCMD to process a series of DB2 commands through the z/OS console interface The DB2 commands are provided through the JCL CMDIN DD data set. The REXX program that is run is shown in Example A-17.
Example A-17 @OSCMD REXX program
/* REXX */
/*
Author.........: [email protected]
Function.......: Use SDSF REXX to execute z/OS command and
send output to standard output
 
For further details on using SDSF REXX see
 Implementing REXX support in SDSF, SG24-7419
http://www.redbooks.ibm.com/abstracts/sg247419.html
 
Input...........: Commands to be executed to be provided in CMDIN DD
*/
trace off
/* set console name to jobname */
isfcons = MVSVAR('SYMDEF',JOBNAME )
 
/* Load the SDSF environment and abort on failure */
IsfRC = isfcalls( "ON" )
if IsfRC <> 0 then do
say "RC" IsfRC "returned from isfcalls( ON )"
exit IsfRC
end
 
/* read commands from CMDIN */
call readcmds
 
/* issue commands and display output */
do xi=1 to CMDIN.0
call runcmds
call displayresponses
call displaycmdoutput
end
 
 
/* Unload the SDSF environment */
call isfcalls "OFF"
exit 0
 
/* Read commands to be executed from CMDIN DD */
readcmds:
ADDRESS TSO,
"EXECIO * diskr CMDIN (STEM CMDIN. FINIS"
if RC > 4 then
do
say "Error during EXECIO CMDIN"
exit 12
end
do i=1 to CMDIN.0
CMDIN.i = "'"||strip(CMDIN.i)||"'"
end
return
 
/* issue commands */
runcmds:
address SDSF "isfexec /"||CMDIN.xi
if RC <> 0 then do
Say "RC" RC "returned from ..."
call DisplayMessages
exit 12
end
return
 
/* Display the user log associated with the action */
displaycmdoutput:
say isfulog.0 "user log lines"
do i = 1 to isfulog.0
say " '"isfulog.i"'"
end
return
/* Display the responses associated with the action */
displayresponses:
if ifsresp.0 > 0 then
say isfresp.0 "response lines"
do i = 1 to isfresp.0
say " '"isfresp.i"'"
end
return
 
/* Display the messages associated with the action */
DisplayMessages:
if ifsrmsg.0 > 0 then
do
say "isfmsg: '"isfmsg"'"
say isfmsg2.0 "long messages in the isfmsg2 stem:"
end
do i = 1 to isfmsg2.0
say " '"isfmsg2.i"'"
end
return
Administrative scheduler runtime messages
When we stopped DB2 member D0Z1, we observed the ADMT runtime messages that are shown in Figure A-11, which resulted from D0Z1ADMT DB2STOP event processing.
$HASP100 D0Z1STOP ON INTRDR FROM STC24540 D0Z1ADMT
IRR010I USERID D0ZGADMT IS ASSIGNED TO THIS JOB.
DSNA679I DSNA6BUF THE ADMIN SCHEDULER D0Z1ADMT CANNOT ACCESS TASK LIST SYSIBM.ADMIN_TASK
DB2 CODE X'00F30002' IN IFI IDENTIFY
Figure A-11 Administrative scheduler DB2STOP messages
The ADMT trace data provided the information that is shown in Figure A-12 on the execution of D0Z1STOP event processing.
(IITHD) Receiving DB2 STOP event
(IIEVENT) - DB2 Subsystem = "D0Z1"
(IIEVENT) - Event = 0
(IIEVENT) ending with RC = x00000000
(TTHD000) Signal received with command = 1
(TTHD000) Execution begins for task = 1
(TTHD000) Execution begins at time 2012-08-12-14.20.34.000000
(TTHD000) num invocations = 6
(TTHD000) PassTicket generated for user = "D0ZGADMT"
(TTHD000) logged in
(TTHD000ÝJ¨) allocating JCL internal reader data set
(TTHD000ÝJ¨) opening JCL data set = "//'DB0ZM.D0ZGADMT.JCL(D0Z1STOP)'"
(TTHD000ÝJ¨) opening JCL internal reader data set
(TTHD000ÝJ¨) writing records to JCL internal reader data set
(TTHD000ÝJ¨) written records = 7
(TTHD000ÝJ¨) closing JCL data set
(TTHD000ÝJ¨) closing JCL internal reader data set
(TTHD000ÝJ¨) deallocating JCL internal reader data set
(TTHD000ÝJ¨) jobid = "JOB24638"
(TTHD000ÝJ¨) JCL job submitted, jobid = "JOB24638"
(TTHD000ÝJ¨) waiting for job status...
(modifyStatus) task 1, status=RUNNING on D0Z1 at 16:2012-08-12-14.20.34.000000
(modifyStatus) admin record(current) at 35: incons=1, <3 tasks
(modifyStatus) status successfully updated
(TTHD000ÝJ¨) execution duration (in nb polls) = 6
(TTHD000ÝJ¨) status found for JCL job = "JOB24638"
(TTHD000ÝJ¨) max_rc = 0
(TTHD000ÝJ¨) comp_type = 1
(TTHD000) logged out
(TTHD000) Execution status COMPLETED
(TTHD000) Execution ends at time 2012-08-12-14.20.40.000000
(modifyStatus) task 1, status=COMPLETED on D0Z1 at 17:2012-08-12-14.20.40.000000
(modifyStatus) admin record(current) at 36: incons=1, <3 tasks
(modifyStatus) status successfully updated
Figure A-12 Administrative scheduler DB2STOP trace
Verifying the status of DB2STOP processing
You can verify the status of DB2STOP processing by using the ADMIN_TASK_STATUS table UDF for querying the ADMT status. The result of the query that we ran is provided in Figure A-13.
SELECT
SUBSTR(TASK_NAME,1,8) AS TASKNAME
, SUBSTR(STATUS,1,10) AS STATE
, NUM_INVOCATIONS AS #INV
, SUBSTR(CHAR(START_TIMESTAMP),1,19) AS BETS
, SUBSTR(CHAR(END_TIMESTAMP),1,19) AS ENTS
, JOB_ID
, DB2_SSID AS SSID
FROM table(DSNADM.ADMIN_TASK_STATUS()) as taskstatus
where task_name = 'D0Z1STOP'
---------+---------+---------+---------+---------+---------+---------+------
TASKNAME STATE #INV BETS ENTS JOB_ID SSID
---------+---------+---------+---------+---------+---------+---------+------
D0Z1STOP COMPLETE 6 2012-08-12-14.20.3 2012-08-12-14.20.4 JOB24638 D0Z1
Figure A-13 Query the DB2STOP processing status
The query that is shown in Figure A-13 provides information about the most recent DB2STOP event run. You can use the UDF also to obtain a history of recent runs. You can limit the number of rows to be returned by passing a numeric input parameter in the UDF interface. An example of such a query and its processing result is illustrated in Figure A-14.
SELECT
SUBSTR(TASK_NAME,1,8) AS TASKNAME
, SUBSTR(STATUS,1,10) AS STATE
, NUM_INVOCATIONS AS #INV
, SUBSTR(CHAR(START_TIMESTAMP),1,19) AS BETS
, SUBSTR(CHAR(END_TIMESTAMP),1,19) AS ENTS
, JOB_ID
, DB2_SSID AS SSID
FROM table(DSNADM.ADMIN_TASK_STATUS(10)) as taskstatus
where task_name = 'D0Z1STOP'
---------+---------+---------+---------+---------+---------+---------+--------
TASKNAME STATE #INV BETS ENTS JOB_ID SSID
---------+---------+---------+---------+---------+---------+---------+--------
D0Z1STOP COMPLETED 2 2012-08-12-02.13.25 2012-08-12-02.13.2 JOB24560 D0Z1
D0Z1STOP COMPLETED 3 2012-08-12-03.34.05 2012-08-12-03.34.0 JOB24579 D0Z1
D0Z1STOP COMPLETED 4 2012-08-12-03.38.50 2012-08-12-03.38.5 JOB24588 D0Z1
D0Z1STOP COMPLETED 5 2012-08-12-03.42.36 2012-08-12-03.42.3 JOB24596 D0Z1
D0Z1STOP COMPLETED 6 2012-08-12-14.20.34 2012-08-12-14.20.4 JOB24638 D0Z1
DSNE610I NUMBER OF ROWS DISPLAYED IS 5
Figure A-14 Query the DB2STOP history
A.3.3 Autonomic statistics monitoring
In our example, we use autonomic statistics monitoring to automatically identify, collect, and maintain accurate statistics in DB2.
For autonomic monitoring, DB2 relies on scheduled calls to the ADMIN_UTL_MONITOR stored procedure to monitor your statistics. When stale, missing, or conflicting statistics are identified, the ADMIN_UTL_EXECUTE stored procedure starts RUNSTATS within defined maintenance windows and resolves the problems. The ADMIN_UTL_EXECUTE stored procedure uses the options that are defined in RUNSTATS profiles to start the RUNSTATS stored procedure. The ADMIN_UTL_MODIFY stored procedure is called at regular intervals to clean up the log file and alert history.
Autonomic statistics and ADMT overview
DB2 uses interactions between the administrative scheduler, certain DB2 -supplied stored procedures, and certain catalog tables for autonomic statistics maintenance.
Figure A-15 illustrates the relationships between the various objects that DB2 uses for autonomic statistics maintenance.
Figure A-15 Object interactions for autonomic statistics maintenance in DB2
DB2 uses the following actions to implement autonomic statistics maintenance:
1. The administrative task scheduler issues calls to the ADMIN_UTL_MONITOR stored procedure according to the schedule that you specify.
2. When the ADMIN_UTL_MONITOR detects missing, out-of-date, or conflicting statistics, it issues a call to the ADMIN_TASK_ADD stored procedure to schedule an immediate run of the ADMIN_UTL_EXECUTE stored procedure.
3. The administrative scheduler calls the ADMIN_UTL_EXECUTE stored procedure.
4. When the call to the ADMIN_UTL_EXECUTE stored procedure occurs within a time window that you specify, it starts the RUNSTATS utility to solve alerts.
5. When the call to the ADMIN_UTL_EXECUTE stored procedure occurs outside of a specified time window, the ADMIN_UTL_EXECUTE stored procedure issues a call to the ADMIN_TASK_ADD stored procedure to reschedule its own execution to the next
time window.
Scheduling autonomic statistics monitoring
We scheduled autonomic statistics monitoring for the following group of DB2 objects:
•User table and index spaces
•DB2 catalog table space DSNDB06.SYSTSKEYS on the first day of each month at
1:00 a.m.
User table and index spaces
We ran the SQL CALL statement that is shown in Example A-18 to schedule autonomic statistics monitoring for user table and index spaces every day at 1:00 a.m.
Example A-18 Statistics monitoring user objects
CALL SYSPROC.ADMIN_TASK_ADD
(NULL,
NULL,
null,
null,
NULL,
NULL,
'0 1 * * *',
NULL,
NULL,
NULL,
NULL, r
'SYSPROC',
'ADMIN_UTL_MONITOR',
'SELECT ''statistics-scope=profile,restrict-ts="DBNAME <> ''''DSNDB06''''"'', 0, 0 ,'''' from SYSIBM.SYSDUMMY1',
NULL,
NULL,
NULL,
'STATSMON1', e
'statistics monitoring on user tablespaces every day at 1 am',
?,
?
)
;
DB2 catalog table space DSNDB06.SYSTSKEY
We ran the SQL CALL statement that is shown in Example A-19 to schedule autonomic statistics monitoring for DB2 catalog table space on the first day of each month at 1:00 a.m.
Example A-19 Statistics monitoring DSNDB06.SYSTSKEY
CALL SYSPROC.ADMIN_TASK_ADD
(NULL,
NULL,
null,
null,
NULL,
NULL,
'0 1 1 * *',
NULL,
NULL,
NULL,
NULL,
'SYSPROC',
'ADMIN_UTL_MONITOR',
'SELECT ''statistics-scope=profile,restrict-ts="DBNAME = ''''DSNDB06'''' AND NAME =''''SYSTSKEY'''' "'', 0, 0 ,'''' from SYSIBM.SYSDUMMY1',
NULL,
NULL,
NULL,
'STATSMON2',
'statistics monitoring systskey tablespace on first day on each month day at 1 am',
?,
?
)
;
 
Upon successful completion, we used the ADMIN_TASK_LIST user-defined function (UDF) to list the autonomic statistics monitoring tasks that are registered in the administrative scheduler. The query result is shown in Figure A-16.
SELECT
substr(TASK_NAME,1,10) as TASKNAME
, substr(POINT_IN_TIME,1,10 ) as PIT
, substr(PROCEDURE_SCHEMA,1,10) as STPSCHEMA
, substr(PROCEDURE_NAME ,1,20) as STPNAME
, substr(DESCRIPTION ,1,40) as DESCRIPTION
, CREATOR
FROM table(DSNADM.ADMIN_TASK_LIST()) as tasklist
WHERE PROCEDURE_NAME = 'ADMIN_UTL_MONITOR';
---------+---------+---------+---------+---------+---------+---------+---------
TASKNAME PIT STPSCHEMA STPNAME DESCRIPTION
---------+---------+---------+---------+---------+---------+---------+---------
STATSMON1 0 1 * * * SYSPROC ADMIN_UTL_MONITOR statistics monitoring
STATSMON2 0 1 1 * * SYSPROC ADMIN_UTL_MONITOR statistics monitoring
Figure A-16 Query statistics monitoring tasks
Administrative scheduler runtime messages
When the administrative scheduler triggered the ADMIN_UTIL_MONITOR stored procedure, the ADMT trace data provided the information that is shown in Figure A-17 on page 507.
(TTHD000) Signal received with command = 1
(TTHD000) Execution begins for task = 6
(TTHD000) Execution begins at time 2012-09-21-09.38.00.000000
(TTHD000) PassTicket generated for user = "D0ZGADMT"
(TTHD000ÝP¨) starting
(TTHD000ÝP¨) connected]
(TTHD000ÝP¨) stored procedure schema = "SYSPROC"
(TTHD000ÝP¨) stored procedure name = "ADMIN_UTL_MONITOR"
(TTHD000ÝP¨) P parmÝ0¨ type=449-0 length=30000 name = "MONITOR_OPTIONS"
(TTHD000ÝP¨) O parmÝ1¨ type=493-0 length=8 name = "HISTORY_ENTRY_ID"
(TTHD000ÝP¨) O parmÝ2¨ type=497-0 length=4 name = "RETURN_CODE"
(TTHD000ÝP¨) O parmÝ3¨ type=449-0 length=1331 name = "MESSAGE"
(TTHD000ÝP¨) num(columns) = 4
(TTHD000ÝP¨) num(variables) = 128
(TTHD000ÝP¨) columnÝ0¨ type=448 length=79 addr = x24E2263E
(TTHD000ÝP¨) columnÝ1¨ type=497 length=8 addr = x24E22693
(TTHD000ÝP¨) columnÝ2¨ type=497 length=4 addr = x24E226A1
(TTHD000ÝP¨) columnÝ3¨ type=449 length=1331 addr = x24E226AB
(TTHD000ÝP¨) "SYSPROC"."ADMIN_UTL_MONITOR"
(TTHD000ÝP¨) call stored procedure, SQLCODE = 0
(setSQLStatus) DSNT400I SQLCODE = 000, SUCCESSFUL EXECUTION
(TTHD000ÝP¨) out parmÝ1¨ = "0x0000011D"
(TTHD000ÝP¨) out parmÝ2¨ = "0x00000000"
(TTHD000ÝP¨) out parmÝ3¨ = ""
(TTHD000ÝP¨) disconnected]
(TTHD000) logged out
(TTHD000) Execution status COMPLETED
(TTHD000) Execution ends at time 2012-09-21-09.38.00.000000
Figure A-17 ADMIN_UTL_MONITOR ADMT trace information
Verifying the status of statistics monitoring processing
The trace information that is shown in Figure A-17 provides processing information for ADMT task number 6 and processing begin at 2012-09-21-09.38.00.000000. We use the query that is shown in Example A-20 to verify the processing status of that task number.
Example A-20 Query for verifying the status of a task
SELECT
SUBSTR(TASK_NAME,1,10) AS TASKNAME
, SUBSTR(STATUS,1,10) AS STATE
, NUM_INVOCATIONS AS #INV
, SUBSTR(CHAR(START_TIMESTAMP),1,19) AS BETS
, SUBSTR(CHAR(END_TIMESTAMP),1,19) AS ENTS
, SQLCODE
, DB2_SSID AS SSID
, SUBSTR(MSG,1,40) AS MSG
FROM table(DSNADM.ADMIN_TASK_STATUS(10)) as taskstatus
where task_name LIKE 'STATSMON%'
and start_timestamp = '2012-09-21-09.38.00.000000'
---------+---------+---------+---------+---------+---------+---------+-------
TASKNAME STATE #INV BETS ENTS
---------+---------+---------+---------+---------+---------+---------+-------
STATSMON1 COMPLETED 26 2012-09-21-09.38.00 2012-09-21-09.38.01
STATSMON2 COMPLETED 26 2012-09-21-09.38.00 2012-09-21-09.38.00
The query output that is shown in Example A-20 on page 507 confirms a status of COMPLETED for both of our statistics monitoring tasks.
Verifying the RUNSTATS utility output
Autonomic statistics monitoring in our example environment triggers RUNSTATS whenever missing or inconsistent statistics are detected. The administrative scheduler calls the SYSPROC.ADMIN_UTL_EXECUTE stored procedure for triggering and controlling RUNSTATS. Upon RUNSTATS completion, you can query table SYSIBM.SYALERTS to verify that RUNSTATS completed successfully and to review the RUNSTATS utility output.
We created the SQL table UDF shown in Example A-21 to retrieve the RUNSTATS utility output of a table space. We provide the table space name and qualifier as input parameters in the SQL table UDF interface.
Example A-21 SQL table UDF to obtain the RUNSTATS output
CREATE FUNCTION UTILOUTPUT
(CREATOR VARCHAR(12), OBJECT VARCHAR(32))
RETURNS TABLE
( STARTTS TIMESTAMP,
STATUS VARCHAR(32),
OUTPUT CLOB(2 M))
LANGUAGE SQL READS SQL DATA NO EXTERNAL ACTION DETERMINISTIC
RETURN
WITH
Q1 (ID,CREATOR, OBJECT) AS
(SELECT
ALERT_ID
,SUBSTR(TARGET_QUALIFIER,1,32)
,SUBSTR(TARGET_OBJECT,1,08)
FROM SYSIBM.SYSAUTOALERTS
WHERE TARGET_QUALIFIER = UTILOUTPUT.CREATOR AND
TARGET_OBJECT = UTILOUTPUT.OBJECT
ORDER BY ALERT_ID DESC
FETCH FIRST ROW ONLY)
,Q2 (STARTTS, STATUS,OUTPUT) AS
(SELECT STARTTS, STATUS, OUTPUT FROM SYSIBM.SYSAUTOALERTS A,Q1
WHERE A.ALERT_ID = Q1.ID ORDER BY STARTTS)
SELECT * FROM Q2
In the example that is shown in Example A-22, we use the SQL table UDF shown in Example A-21 to obtain the RUNSTATS utility output of the most recent utility that is run for table space DSNADMDB.DSNADMTS.
Example A-22 Query for recent RUNSTATS for table space DSNADMDB.DSNADMTS
SELECT output
FROM TABLE(UTILOUTPUT('DSNADMDB','DSNADMTS')) AS A
OUTPUT
2012-09-21 09:38:02.487888> 1DSNU000I 265 09:38:01.88 DSNUGUTC - OUTPUT START
2012-09-21 09:38:02.487899> DSNU1045I 265 09:38:01.96 DSNUGTIS - PROCESSING S
2012-09-21 09:38:02.487910> 0DSNU050I 265 09:38:02.11 DSNUGUTC - RUNSTATS TA
2012-09-21 09:38:02.487920> PROFILE
2012-09-21 09:38:02.487930> DSNU1361I -D0Z2 265 09:38:02.11 DSNUGPRF - THE STAT
2012-09-21 09:38:02.487940> ADMIN_TASKS HAS BEEN USED
2012-09-21 09:38:02.487950> DSNU1368I 265 09:38:02.11 DSNUGPRB - PARSING STAT
2012-09-21 09:38:02.487961> DSNU1369I 265 09:38:02.11 DSNUGPRB - PARSING STAT
A.4 Additional information
For more information about how to implement and configure the administrative task scheduler, see following DB2 for z/OS manuals:
•DB2 10 for z/OS Installation and Migration Guide, GC19-2974
•DB2 10 for z/OS Administration Guide, SC19-2968
•DB2 10 for z/OS Managing Performance, SC19-2978
..................Content has been hidden....................

You can't read the all page of ebook, please click here login for view all page.
Reset