Using the Exception Monitor in SYSVIEW for DB2 to Automatically Deliver Event Notifications or Cancel Threads
Article ID: 55841
Updated On: 10-15-2023
Products
Issue/Introduction
Overview
SYSVIEW Performance Monitor for DB2 is designed to assist in monitoring the mainframe DB2 database. One of its most powerful tools is the Exception Processor, which can deliver automatic notifications when specified DB2 events occur. The Exception Processor can monitor 4 types of DB2 events. These types are:
- Subsystem Exceptions
- Database Exceptions
- Application Exceptions (includes SQL based)
- IQL Based Exceptions (Shadow Definitions)
Subsystem monitoring focuses on the MSTR, DBM1, DIST and IRLM address spaces and provides a comprehensive predefined list of conditions to evaluate. Statistics such as EXCP rates, Paging, Log Processing, System Stats, Buffer Pool and SQL Counts can be monitored.
Database monitoring focuses on one or more databases. Statistics such as the number of opens or extents for a pageset or if a database/pageset is in a restricted status can be monitored.
Application monitoring is the main exception type used and processes thread information in real-time or uses Accounting information in an individual/summarized method or can process individual SQL statements in real-time without a performance trace. There are more that a thousand predefined Exceptions that can be used.
IQL monitoring uses an Insight Query Language (IQL) request to monitor the DB2 system. When the WHERE clause of the request is met, an exception event is generated. This type of monitor is the most flexible in that every IFCID record can be processed to determine when an event should be declared and exception.
The rest of this article is dedicated to showing how the Exception Processor IQL monitoring can be implemented.
Implementation
There are 2 steps needed to setup an IQL Exception. First, an IQL request need to written, stored in the user request library and eventually started. Second, the IQL Exception needs to created and activated.
- Formulate the IQL request.
These are the specific requirements in making an IQL request useable for exception processing:- Provide an eight character LABEL in the first field. This label will match the IQL Exception name.
- Include a WHERE clause to trigger the exception.
- Include the EXCP specification in the output.
See the following example, which incorporates these requirements. This exception will be generated whenever the userid ABCDEFG runs a connection from the DISTSERV plan. The INTERVAL statement tells the request to check the system every 10 seconds.EXCP0001: TRACE (COLFMT=OFF)
Two more examples of requests that produce exceptions can be found in the HLQ.CDBATRQ library. Members APPLEXCP and SYSEXCPT are provided as samples.
*
* Throw an exception when AUTH-ID ABCDEFG makes a distributed connection* << THREAD-STATS
'EXCP0001'
WHERE AUTH-ID = 'ABCDEFG'
AND PLAN-NAME = 'DISTSERV'
>>
INTERVAL 10 SECONDS
EXCP ;
- Add the exception
The next step is to define an exception that will link this request to the Exception Monitor.
To add the exception, start at the Insight Main Menu. Choose option 0, 4 and then enter ADD on the command line. You can now specify the Exception ID of EXCP0001. You can also limit which subsystem this exception is for, but we will not limit it in this example. Press ENTER. Now you can enter the parameters to change how the exception is displayed on the Exception screen, EXCDISP. We will take the defaults in this example. Press ENTER and then specify your TSO ID and change the starting level to C.Send to TSO user #1 . . ABCDEFG starting at level . C (I=Info, W=Warn,
For the next two screens, just press ENTER to take the defaults. You should then end up on a screen that states:Exception Data Set Updated The exception data set has been modified. This change can not be made dynamically and requires that the exception system be restarted. Press F6 to restart the exception system now or use the F3 End key to defer the change until the next time the exception system is restarted.
Press the PF6 key to refresh the Exception Processor definitions.
Back on the List of IQL Shadow Exception Definitions screen you should see that the exception is noted.STATUS SSN IQL exception IDACTIVE **** EXCP0001
Testing your new Exception
From the Insight Main Menu, enter the T command to bring up the TOOLS menu, then 4 to display a LIST of requests. Now, enter the command L EXCP0001, to locate the request that we wrote. Place an S command next to the entry and press ENTER to bring up the start qualification screen. In the duration field, put the value 0010 to have this request run for only 10 minutes, then press ENTER. You will get a warning message stating DBG55037W Request cannot be DISPLAYed, but that is OK.
Next, go to the Thread Active screen and display the running threads. (You will not see your thread yet.)
Next run an application that requires a distributed thread. Execute a long running SQL statement - after 10-15 seconds, the Thread Active screen should display an entry as follows.
Menu Print Tools Help CA-Insight V6.3 P01F ABCDEFG 08/26/04 13:35: DXXX CZZZ 1 All 2 Connections 3 Curr Contn 4 Contn Hist 5 Lock Summary 6 More. FOCUS O R/THRDACTV Threads Identified to DB2 Item 1-5 o All Actions: S=Select, T=SQL, L=Locks, E=Except, R=Rmt, C=Cancel, M=More... Auth ID Corr ID Plan Conn Last DB2 Elap DB2 CPU ________ ____________ ________ Type Status Event HH:MM:SS MM:SS.TT Crit -------- ------------ -------- ---- -------- ------ -------- -------- ---- _ JEFF db2bp.exe DISTSERV DBAT DDF 4 0.41 1
Now, by accessing the Exception Monitor screen and choosing option 5, you will see that the exception was triggered and the action you specified would have been initiated.
Menu Print Tools Help CA-Insight V6.3 P01F ABCDEFG 08/26/04 13:3 DXXX CZZZ 1 All 2 Subsystem 3 Application 4 Database 5 IQL Type . IN-AL (IN-ALERT, IEXCDISP IQL Based Exceptions In Alert TRIPPED) Actions: H=Help, G="Goto" screen, U=Update current and permanent, T=Temp u -------------------------- IQL BASED EXCEPTIONS ----------------------- _ CRIT 13:34:12-CURRENT EXCP0001
Congratulations! You now have the components needed to monitor any IFCID value and generate an exception. You can also use the Insight User's Guide, Chapter 15 to find more information on this subject.
Environment
Release : 20.0
Component : CA SYSVIEW Performance Management Option for DB2 for z/OS
Resolution
See Overview and Implementation.
Feedback
