User’s Guide: CUA Interface OMEGAMON II ® for DB2 Version 540 G251246500 August 2004 Candle Corporation 100 North Sepulveda Blvd.
Registered trademarks and service marks of Candle Corporation: AF/OPERATOR, AF/REMOTE, Availability Command Center, Candle, Candle CIRCUIT, Candle Command Center, Candle Direct logo, Candle eDelivery, Candle Electronic Customer Support, Candle logo, Candle Management Server, Candle Management Workstation, CandleLight, CandleNet, CandleNet Command Center, CandleNet eBusiness Platform, CandleNet Portal, CL/CONFERENCE, CL/SUPERSESSION, CommandWatch, CT, CT/Data Server, CT/DS, DELTAMON, DEXAN, eBA, eBA*ServiceM
Contents List of Figures . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .5 Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .7 About This Book . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .8 Adobe Portable Document Format . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Chapter 4. Using OMEGAMON II for DB2 with !DB/EXPLAIN . . . . . . . . . . . . . . . . . . . . . 89 Background about !DB/EXPLAIN for DB2 . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .90 Using OMEGAMON II and !DB/EXPLAIN Complementary Functions . . . . . . . . . . . . . .91 Using the OMEGAMON II for DB2 Callable Interface . . . . . . . . . . . . . . . . . . . . . . . . . .93 Moving Interactively between !DB/EXPLAIN and OMEGAMON II . . . . . . . . . . . . . . .
List of Figures FIGURE 1. FIGURE 2. FIGURE 3. FIGURE 4. FIGURE 5. FIGURE 6. FIGURE 7. FIGURE 8. FIGURE 9. FIGURE 10. FIGURE 11. OMEGAMON II for DB2 System Status Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .111 Workloads Panels (Realtime) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .113 Workloads Panels (Near-term history) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .114 Resources Panels (Realtime) . . . . . . . . .
OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540
P Preface This guide is an introduction to the CUA™ interface for OMEGAMON II® for DB2®, Version 540. The CUA interface is based on IBM's Common User Access™ standards designed to facilitate ease of learning and use. OMEGAMON II® for DB2 is a component of OMEGAMON XE for DB2 on z/OS.
About This Book About This Book Who should read this book This manual is intended for data center personnel who are responsible for monitoring DB2 performance, systems programmers and performance analysts who are responsible for a DB2 subsystem, application programmers who wish to monitor their programs, and database administrators.
Adobe Portable Document Format Adobe Portable Document Format Introduction Candle supplies documentation in the Adobe Portable Document Format (PDF). The Adobe Acrobat Reader prints PDF documents with the fonts, formatting, and graphics in the original document. To print a Candle document, do the following: 1. Specify the print options for your system. From the Acrobat Reader Menu bar, select File > Print Setup... and make your selections.
Documentation Conventions Documentation Conventions Introduction Candle documentation adheres to accepted typographical conventions for command syntax. Conventions specific to Candle documentation are discussed in the following sections. Panels and figures The panels and figures in this document are representations. Actual product panels may differ. Required blanks The slashed-b (!) character in examples represents a required blank. The following example illustrates the location of two required blanks.
Documentation Conventions Table 1. Symbols in Command Syntax Symbol [] Usage Denotes optional arguments. Those arguments not enclosed in square brackets are required. Example: APPLDEST DEST [ALTDEST] In this example, DEST is a required argument and ALTDEST is optional. {} Some documents use braces to denote required arguments, or to group arguments for clarity. Example: COMPARE {workload} REPORT={SUMMARY | HISTOGRAM} The workload variable is required.
Candle Customer Service and Satisfaction Candle Customer Service and Satisfaction Background To assist you in making effective use of our products, Candle offers a variety of easy-to-use online support resources. The Candle Web site provides direct links to a variety of support tools that include these services: eSupport allows you to create and update service requests opened with Customer Service and Satisfaction (CSS).
OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540
W What’s New This section identifies the enhancements for Version 540 of OMEGAMON II for DB2. OMEGAMON II® for DB2 is a component of OMEGAMON XE for DB2 on z/OS, Version 300. The changes and additions in OMEGAMON II for DB2 provide new function in addition to reflecting support for features in IBM DB2 Version 8.1. OMEGAMON II for DB2 enables you to obtain additional information about the performance of your systems.
64-bit addressing support Version 8 of DB2 UDB for z/OS supports 64-bit virtual storage. The zSeries 64-bit architecture allows DB2 UDB for z/OS to move various storage areas above the 2-GB bar. OMEGAMON II for DB2 Version 540 now provides the ability to display information from above the 2-GB bar structures. 64-bit addressing support applies to the: I EDM pool I buffer pools I sort pools I RID pools Long name support OMEGAMON II for DB2 Version 540 includes support for long object names.
I GETPAGES I LOCK ESCAL I PACKAGE See the OMEGAMON II for DB2 Reference Manual, Version 540, for descriptions of these new fields. New DSNZPARM fields The DSNZPARM Thread Parameters panel now includes numerous new fields.
OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540
1 Getting Started Introduction This chapter is intended to help you get started using the CUA interface for OMEGAMON II for DB2 to monitor your DB2 subsystem. Chapter Contents Signing On . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting Logon Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Signing Off . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Signing On Signing On Logging on to the application You access the CUA interface for OMEGAMON II by logging on to a VTAM® application. The name of the application (the applid) is defined at your site when the product is installed. Depending on the system configuration at your site, you may be required either to select the application from a menu, or issue a command.
Signing On Sign On panel fields The fields of the Sign On Panel are defined in the table below. Field Description Userid Your 1–8 character user ID, which authorizes your access to OMEGAMON II. Password Your 1–8 character private password, which authorizes your access to OMEGAMON II. Change Password This field enables you to change your password. To leave your password unchanged, accept the default, No. To change your password, type Yes. You are prompted for a new password.
Setting Logon Options Setting Logon Options The Logon Options panel enables you to specify session characteristics that will be applied each time you sign on to OMEGAMON II. It appears automatically the first time you sign on as a new OMEGAMON II user. To modify your logon options at any time, press F11 from the Sign On panel. Logon Options Type the correct values, then press Enter. User Profile . . . . . . . . . $DEFAULT + Subsystem Profile . . . . . . . $DEFAULT + Application ID . . . . . . . .
Setting Logon Options Field Description Application ID The OMEGAMON II classic interface VTAM mode application identifier. DB2 subsystem The DB2 subsystem ID that you want to monitor. Logical rows The number of logical rows (100–9999). Migrate Profile from OMEGAMON Enter Yes if you want to migrate a classic interface user profile to a CUA subsystem profile. Use this feature only to provide a set of values to use as a starting point for customizing the CUA subsystem profile.
Signing Off Signing Off To sign off OMEGAMON II: 1. Press F3 from the System Status panel. Result: The Exit Confirmation pop-up window appears. Exit Confirmation Select one of the following, then press ENTER. _ 1. Exit from OMEGAMON II for DB2 2. Return to OMEGAMON II for DB2 F3 F12=Cancel 2.
Panel Features Panel Features IOMEGAMON II panels share a number of common features. In the following section, we use the System Status panel to show the panel features you will find throughout OMEGAMON II. 1___ Actions GoTo Options Tools Help ------------------------------------------------------------ mm/dd/yy 16:13:49 OMEGAMON II for DB2 System Status System: D41A+ N-T History Select one component with a “/” or an action code.
Panel Features Part 26 Feature Description 4 Action Codes 5 Panel Body Displays performance data or status information. Highlighting or color is used to indicate status. 6 Function Keys Provide navigation and various functions. Displays available action codes. Note: Action codes do not apply to every product panel.
Data Panels Data Panels Description Unlike the System Status panel, most OMEGAMON II full-screen panels present performance information in tabular format. Below, a sample data panel is shown. ____ Actions GoTo Tools Help +----------------------------------------------------------- mm/dd/yy 8:01:00 | Log Manager Statistics System:D41B | +----------------------------------------------------------------------------+ | Collection Interval . . : REALTIME | Start . . . : 10/20 11:50:29 | | Report Interval . . .
Data Panels Pushbuttons OMEGAMON II provides pushbuttons as a way to navigate to panels that are closely related to the current panel. Pushbuttons appear between brackets < > or parentheses ( ) at the bottom of some panels, directly above the function keys. Only selections in brackets < > are selectable. The pushbutton for the current panel appears in parentheses ( ) and is not selectable. To use a pushbutton, place the cursor on the phrase or word in brackets, and press Enter.
Action Bar Action Bar The action bar appears as the top line of every panel. It has an entry field on the left side and selectable keyword choices across the length of the line. When you select a choice from the action bar, a pull-down menu appears. Then you can select a menu item to navigate to other panels in the product. The action bar keyword choices are as follows: I I I I I I Actions GoTo View Options Tools Help Selecting from the action bar To select a keyword choice from the action bar: 1.
Action Bar bypasses the GoTo menu and the DB2 Installation Parameters menu, and goes directly to the DSNZPARM Parameters panel. Enter GDR in the entry field on the action bar of the System Status panel. GDR Actions Goto Options Tools Help System Status DSNZPARM IRLM Parameters Using the fast path mnemonic brings you directly to the DSNZPARM IRLM Parameters panel. The mnemonic character (G, D, R) for each selection is underlined on the panel or menu on which it appears.
Action Bar GoTo pull-down The GoTo menu enables you to quickly move to panels that are related to the current panel, as well as to other product areas. The selections on this menu vary from panel to panel. _ 1. 2. 3. 4. 5. 6. 7. DB2 Installation Parameters... Address Space Info and OMEGAMON Commands Menu Online Collector Information... IRLM Startup Options Stored Procedure Startup Options Panel Index...
Action Bar Options pull-down The Options pull-down menu enables you to customize OMEGAMON II. You can change the panel display, select a different startup profile, set thresholds, and assign user authorities. 1. 2. 3. 4. 5. 6. 7. 8. 9. Preferences... Controls... Set thresholds... Authority List... User Profiles... Subsystem Profiles... Near-term Thread History Filter... Save SQL Text Parameters... D2/Explain Parameters... F1=Help F12=Cancel Note: Additional options may be available from other panels.
Action Codes Action Codes Action codes let you easily analyze the performance of your DB2 subsystem by allowing you to get more information about or perform actions on panel objects. You can use action codes as an alternative to using the Actions menu. Available action codes You can use these action codes in OMEGAMON II. Action Code Show Details S Analyze Problems A Description Shows more detail about the selected item.
Function Keys Function Keys Function keys enable you to perform certain tasks in OMEGAMON II. These keys are displayed along the bottom line of the panel. Each panel lists the available function keys and their assignments for the current panel. If your panel does not display the function keys, press F2 to turn on the display. Function key assignments The default function key assignments are listed below.
Function Keys Function Key Name Assignment F21 Process SQL Copies SQL statements from the SQL Call Being Executed panel or from the SQL Text panel and pastes them into a specified destination. (This function key assignment may differ at your site.) From the DB2 or MVS console, retrieves and executes the last command. F24 CUA/TSO Toggles between the CUA interface and a TSO session. (The assigned key may be different at your site.
Getting Help Getting Help Help from a panel Panel help is available for all panels. To obtain help for a panel: I Move the cursor to a non-input field, and press F1. Result: The associated help panel appears. The word More+, More-, or More-+ in the upper right corner of a help window indicates that you can scroll. When you have finished viewing the help window, press F12 to return to the previous panel, or press F3 to exit Help.
Getting Help Using the Glossary The glossary contains definitions of terms used in OMEGAMON II. The glossary is available from any help window or from any panel through the Help menu. There are three ways to access the glossary. IF you are... THEN...
Getting Help Help function keys The following table describes how function keys work within the help system. Function Key F1 Name Field level help Assignment Displays field-level help if pressed on an entry field. Displays panel-level help if pressed outside an entry field. Note: Field-level help is not available for all entry fields. 38 F2 Extended help Displays additional help information. F3 Exit Exits the help system. F5 Glossary Accesses the glossary.
2 Customizing OMEGAMON II Chapter Contents Introduction . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Specifying Panel Preferences. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Specifying Terminal Options . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Setting Thresholds . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Introduction Introduction You can adjust many features of the OMEGAMON environment to meet your work and system requirements. All customization options are available from the Options pull-down menu. To access the Options menu: I Select Options from the action bar. Result: The Options menu appears. 1. Preferences... 2. Controls... 3. Set thresholds... 4. Authority List... 5. User Profiles... 6. Subsystem Profiles... 7. Near-term Thread History Filter... 8. Save SQL Text Parameters...
Specifying Panel Preferences Specifying Panel Preferences Use the Preferences pop-up window to customize the appearance of information on OMEGAMON II panels. Accessing the Preferences window To access the Preferences pop-up window: I Select Preferences from the Options menu. Result: The Preferences window appears. Preferences Type any changes, then press Enter. Mnemonics . . . . Beep . . . . . . Panel ID. . . . . Time/Date Display Function Key Area Implicit Action . National Language Date Format . . .
Specifying Panel Preferences Field Description Function Key Area Displays a list of the currently available function keys at the bottom of the panel. Implicit Action Allows for an implicit action for selectable items on a display panel. If you select On, a default action occurs when you place the cursor on an entry field, and press Enter. If you select Off, you must type an action code in the entry field for an action to occur.
Specifying Terminal Options Specifying Terminal Options Use the Terminal Options pop-up window to customize settings for the CUA interface. Accessing the Terminal Options window To access the Terminal Options pop-up window: I Select Controls from the Options menu. Result: The Terminal Options window appears. KD2PTRMP Terminal Options Type a new setting or press Enter. Auto Refresh Interval . . . OFF Sec. (5-999 / Off) Use Status Words . . Critical Status Word Warning Status Word Normal Status Word .
Specifying Terminal Options Field Description Idle Status Word Word you want to appear when OMEGAMON II is not monitoring an object (turquoise). For example: Idle. Display Threads by: To display plan names on thread summary displays, enter PLAN in this field. To display package names on thread summary displays, enter PACK. SQL Process Level Options available for copying, pasting, and saving SQL text. You can copy or save text by pressing the SQL Process PF Key.
Specifying Terminal Options Values for SQL Process Level SAVE/EXPLAIN Description Use this option to perform SAVE and EXPLAIN functions. Complete the required fields for the SAVE/EXPLAIN option. Saving Terminal Options When you finish entering your terminal preferences: I Press Enter. Result: The Save Profile Confirmation pop-up window appears. Save Profile Confirmation 1.- Save values for this session only 2.
Setting Thresholds Setting Thresholds Use the Set Thresholds option window to set performance standards for your subsystem. Accessing the Thresholds window To access the various thresholds windows using the Set Thresholds option, follow these steps: Step 1 Action Select Set Thresholds from the Options pull-down menu. Result: The Threshold Index menu appears. 2 Select one of the following, and press Enter: 3 I Workloads Result: The Workloads Index menu appears.
Setting Thresholds Saving threshold values When you finish entering your threshold values: I Press Enter. Result: The Save Profile Confirmation pop-up window appears. 1.- Save values for this session only 2.- Save values into profile F12=Cancel Saving for current session To use the new threshold values only for the current session: I Select Save values for this session only, and press Enter. Result: The new thresholds are used only for your current session, and the subsystem profile is not changed.
Assigning User Authorities Assigning User Authorities The system administrator at your site has access to all user and subsystem profile maintenance functions and can perform profile maintenance functions for other users. The system administrator also can authorize other users to perform profile maintenance functions. The ID of the first person to log on to the CUA interface of OMEGAMON II automatically becomes an authorized user. This person then can add other user IDs to the list as needed.
Maintaining Profiles Maintaining Profiles OMEGAMON II allows you to switch, copy, delete, and modify user profiles and subsystem profiles. Profile types OMEGAMON II uses three different profiles. Profile Description Startup Contains values specified on the Logon Options panel. Used during the logon process. Startup profiles cannot be shared among users. User Contains values specified on the Terminal Options panel. Although users should have their own user profiles, profiles can be shared among users.
Maintaining Profiles To access the Subsystem Profile Maintenance panel: I Select Subsystem Profiles from the Options menu, and press Enter. Result: The Subsystem Profile Maintenance panel appears.
Maintaining Profiles Importing a Profile You can import a profile by copying the partitioned dataset member into the profile. The MVS Modify command for importing a profile is as follows: /F OMIIDB2,NTD KD2IMP ‘PDSNAME MEMBER PROFILE’ Where PDSNAME is the name of the source partitioned dataset, MEMBER is the source partitioned dataset member, and PROFILE is the target profile. Exporting a Profile You can export a profile by copying the profile to the target partitioned dataset member.
Maintaining Profiles To modify a profile description: 1. Type M next to the profile whose description you want to modify and press Enter. Result: The Modify Description window appears. Modify Description Current : User Profile New : _________________________________________ F1=Help F12=Cancel 2. Type the new description, and press Enter. (You cannot modify the description of $DEFAULT.) Result: The profile description is changed on the Profile Maintenance panel.
Maintaining Profiles I Use importing to reverse the above process to recreate a profile based on the exported information. Example command for exporting a profile The MVS Modify command for exporting a profile is as follows: /F OMIIDB2,NTD KD2EXP ‘PDSNAME MEMBER PROFILE’ Where PDSNAME is the name of the target partitioned dataset, MEMBER is the target partitioned dataset member, and PROFILE is the source profile.
Setting Filtering Options for Near-Term Thread History Setting Filtering Options for Near-Term Thread History Use the Near-Term Thread History Filter Options pop-up to specify the reporting period and filtering criteria for data that appears on near-term thread history panels. Accessing the Filter Options window To access the Filter Options pop-up window: I Select Near-term Thread History Filter from the Options menu. Result: The Near-Term Thread History Filter window appears.
Setting Filtering Options for Near-Term Thread History Field Description Relative-Start The number of minutes or hours before the current time on which the reporting period begins. You can specify a relative start instead of an absolute start date and time. Relative-End The number of minutes or hours after the start time on which the reporting period ends. You can specify a relative end instead of an absolute end date and time.
Specifying Save Options for SQL Text Specifying Save Options for SQL Text Use the Save SQL Text Parameters pop-up window to specify options for saving SQL text to a partitioned dataset. Accessing the Save SQL Text Parameters window To access the Save SQL Text Parameters pop-up window: I Select Save SQL Text Parameters from the Options window. Result: The Save SQL Text Parameters Window appears. Save SQL Text Parameters SQL Statement PDS . . : _____________________ Member Name . . . .
Using the Callable Interface Using the Callable Interface The callable interface feature of OMEGAMON II for DB2, Version 500, lets you make direct calls to the.EXP command on the OMEGAMON II commands panel without having to first access TSO. Before using this feature, you should do the following: 1. 2. 3. 4. Customize the Classic started task. Set up !DB/EXPLAIN Set up the CUA options. Set up the !DB/EXPLAIN options.
Using the Callable Interface Step 6 Action Use option 1, Realtime monitor interface members, to submit the job. This job creates a started task in RKD2PAR. The name of this started task is D2TE or the value entered on this panel for the parameter TESTC. Copy this started task to the JES2 procedure library for started tasks. This task dynamically allocates the !DB/EXPLAIN profile dataset. Configuration options for !DB/EXPLAIN will be obtained from this dataset. Note: Do not start this task.
Using the Callable Interface Step 2 Action From the main CUA panel, select Options from the task bar and press Enter. The following pop-up appears. 1. 2. 3. 4. 5. 6. 7. 8. Preferences... Controls... Set thresholds... User authorities... User Profiles... Subsystem Profiles... Near-term Thread History Filter... D2/Explain Options... F1=Help 3 F12=Cancel Select Option 2, Controls, from the pop-up and press Enter.
Using the Callable Interface Step 3 Action Select Option 8, D2/Explain Parameters, from this panel and press Enter. Use the D2/EXPLAIN Options panel, shown below, to specify parameters required to invoke !DB/EXPLAIN during your OMEGAMON session. D2/EXPLAIN Options Extract Id . . . . User Profile DSN . Group Profile DSN. SQL Statement PDS. Member Name. . . Type of Save . . Default Qualifier. Qualifier Option Gather Statistics. Initial Report . . F1=Help F4=Prompt . . . . . . . . . . . . . . . . . . . .
Using the Callable Interface Field Type of Save Description Type of save to be executed when saving SQL text to the member specified within the SQL statement PDS. Values are REPLACE and APPEND. I I REPLACE writes over an existing member or creates the member if it does not currently exist. System default. APPEND concatenates the new SQL text to the existing number. Default Qualifier Qualifier to use to qualify unqualified tables, views, and aliases in SQL statements.
Using the Callable Interface Step 2 Action From the main CUA panel, select Active Threads and press Enter. The Thread Connected to DB2 panel appears.
Using the Callable Interface Step 5 Action Press the Process SQL key. GoTo Options Tools Help +------------------------------------------------------------ mm/dd/yy 8:47:27 | SQL Call Being Executed System: D41A | +--------------------------------------+--------------------------------------+ | Thread: Plan . . . . . : DSNESPCS | Attach: TSO Userid . . : RBRIN3 | | Connid . . . . : TSO | Region ASID . : | | Corrid . . . . : RBRIN3 | | | Authid . . . .
Using the Callable Interface 64 OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540
3 Using OMEGAMON II Introduction This chapter describes how to use OMEGAMON II. Chapter Contents Monitoring from the System Status Panel . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . System Status Components and Related Exceptions. . . . . . . . . . . . . . . . . . . . . . . . . . Investigating an Active Threads Alert . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Evaluating and Modifying Thresholds . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Monitoring from the System Status Panel Monitoring from the System Status Panel The System Status panel provides a central point of control for monitoring your DB2 subsystem. ____ Actions GoTo Tools Help ------------------------------------------------------------ mm/dd/yy 16:13:49 OMEGAMON II for DB2 System Status System: D41A+ N-T History Select one component with a “/” or an action code.
Monitoring from the System Status Panel Status indicators A status indicator is a color bar or a word (for example, Warn) that represents the performance of your DB2 subsystem. Color terminals can display both colors and words; noncolor terminals can display only words. The meaning of the status indicator colors is shown in the following table. Color Status Green Satisfactory performance. Yellow A potential problem exists. A warning threshold was met or exceeded. Red A critical problem exists.
Monitoring from the System Status Panel Performing actions from the System Status panel You can investigate a change in status for a monitored object by entering one of these action codes. S Shows more detail. A Shows which exceptions tripped, the current values for the tripped exceptions, and the threshold values set for the exceptions. H Shows near-term historical data. L Shows all the exceptions that roll up to an object and allows you to change threshold settings and rules.
System Status Components and Related Exceptions System Status Components and Related Exceptions OMEGAMON II monitors your DB2 subsystem by comparing the performance of your subsystem against a set of performance standards. These standards are called thresholds. When the performance of a workload or a resource exceeds a threshold, an exception condition occurs. The status indicators on the System Status panel summarize the status of the thresholds that you set in OMEGAMON.
System Status Components and Related Exceptions IMS exceptions The following exceptions apply to threads related to IMS connections. Description Code Explanation No dependent region IMND There are no IMS dependent regions defined to DB2 for the IMSID. Region connection failed The specified IMS dependent region for the IMSID failed to connect to DB2 due to an error. IMCN TSO exceptions The following exceptions apply to threads related to TSO connections.
System Status Components and Related Exceptions Description Code Explanation DDF bytes received TRCV The amount of data received by a requestor (allied) or server (distributed) DB2 thread in response to SQL requests exceeds the threshold. DDF bytes sent TSND The amount of data sent by a requestor (allied) or server (distributed) DB2 thread in response to SQL requests exceeds the threshold.
System Status Components and Related Exceptions Description Code Explanation Thread elapsed time ETIM The elapsed time for a DB2 thread (from sign-on or create thread) exceeds the specified threshold. Thread terminated indoubt INDB The thread has terminated and is indoubt. Update commit COMT The ratio of updates to commits for the thread exceeds the specified threshold.
System Status Components and Related Exceptions EDM pool exceptions The following exceptions apply to EDM pool resources. Description EDM pool utilization Code EDMU Explanation The percentage of environmental descriptor manager (EDM) pool pages in use to total pool size exceeds the specified threshold. Lock statistics exceptions The following exception applies to lock statistics resources.
System Status Components and Related Exceptions Description Code Explanation DDF send rate DSND The amount of data sent to a requestor (local) or server (remote) DB2 subsystem exceeds the specified threshold. Max logmode conversations MCNV The number of conversations in use exceeds the specified threshold. When the percentage reaches 100%, the exception message displays the number of threads waiting because the preset maximum is exceeded. Queuing conversations can greatly increase response time.
System Status Components and Related Exceptions Active trace exceptions The following exceptions apply to active trace alerts. Description DB2 global trace active Code GTRC Explanation The DB2 global trace is active. It should not be active unless there is a specific purpose, such as diagnostic testing. The DB2 global trace can increase performance overhead. Volume activity exceptions The following exceptions apply to volume activity alerts.
System Status Components and Related Exceptions Description Number of stored procedures executing Code STPE Explanation Indicates that the number of stored procedures executing exceeds the threshold. The threshold is expressed as a count of stored procedures currently executing. It can be an integer from 0 to 99. STPE is a member of the DB2 system (SY) group. The default threshold is 10 and the default state is On. Triggers exceptions The following exceptions apply to alerts relating to triggers.
Investigating an Active Threads Alert Investigating an Active Threads Alert IntroductionThis is an example of how you might investigate a red light from the System Status panel. You can use this information to help you understand how to use the features of OMEGAMON to resolve other DB2 subsystem problems. Active threads alert In this example, the Active Threads alert on the System Status panel has turned red. I Select Active Threads with the A (Analyze Problems) action code, and press Enter.
Evaluating and Modifying Thresholds Evaluating and Modifying Thresholds When you investigate a yellow or red status light from the System Status panel, you may determine that the threshold values that caused the exception to trip did not really indicate a problem in your DB2 subsystem. In this case, you need to change the current threshold values for the exception to values that are appropriate for your site.
Evaluating and Modifying Thresholds Adding or modifying rules You can specify rules for an exception condition to control how the thresholds are applied. To add or modify a rule for an exception: I Select an exception with the R (Rules) action code, and press Enter. Result: The Rules panel appears. You can add rules to an exception or change rules from this panel. Add Rule for ARCM Exception Modify Parameters, than press Enter Rule number. . Plan name. . . Auth Id. . . . Conn type. . .
Reviewing Near-Term History Data Reviewing Near-Term History Data Near-term history panels provide information about DB2 subsystem activity and about completed thread executions. You can use near-term history panels to quickly gain some historical perspective on system activity. Note: To display near-term history data, the online data collector must be active.
Reviewing Near-Term History Data Thread activity for a report interval You also can view summarized information about thread activity for a single report interval. To access thread activity for a single interval: I Select an interval with the I (Subinterval) action code, and press Enter. Result: The Thread History by Report Subinterval panel appears.
Using the OMEGAMON II for DB2 Background Application Trace Facility Using the OMEGAMON II for DB2 Background Application Trace Facility The background application trace facility provides I the ability to start an application trace to run in the background asynchronously to your OMEGAMON II session. Use the panels available from the CUA component of OMEGAMON II. Initiate the application trace and specify the values the application trace to use during execution.
Using the OMEGAMON II for DB2 Background Application Trace Facility If you do not enter a start time for the background trace, OMEGAMON II informs you by means of a pop-up panel that the trace will start immediately. You can choose to begin the trace or to return to the previous panel to enter a start time. If the dataset that you designate for the trace data is not preallocated, OMEGAMON II for DB2 displays a warning message.
Using the OMEGAMON II for DB2 Background Application Trace Facility Activities you can perform in a background trace request When you select a trace from the list of traces on the Deferred Application Traces panel, OMEGAMON II displays a pop-up panel that permits you to perform these activities.
Using the IFCID Trace Facility Using the IFCID Trace Facility Overview The IFCID Trace facility provides the ability to start an IFCID trace to run in the background asynchronously to your OMEGAMON II session. You can initiate the IFCID trace from the OMEGAMON II for DB2 System Status panel. To access the IFCID Trace Facility, follow these steps: 1. On the OMEGAMON II for DB2 System Status panel, select T (Tools). The pull-down menu shows IFCID Trace facility as option 3. 2.
Using the IFCID Trace Facility At the point when you enter option S to select the IFCIDs (whether by trace type, trace class, or IFCID record) and to start the IFCID trace, OMEGAMON II for DB2 asks you to confirm your selection. If you choose to alter your selection, you can enter option R (reset selection). OMEGAMON II for DB2 then allows you to re-enter your selections.
Using the IFCID Trace Facility Connection identifier 8 (character) Correlation identifier 12 (character) DB2 system identifier 4 (character) IFCID number 2 (numeric) Number of self-defining sections 2 (numeric) Stopping an IFCID Trace To stop the IFCID Trace facility, follow these steps: I enter option T (Tools) to display the Tools pull-down menu. I enter option 3 (IFCID Trace Facility) to display the IFCID Trace pull-down menu. I enter option 3 (Stop IFCID Trace).
Using the IFCID Trace Facility 88 OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540
4 Using OMEGAMON II for DB2 with !DB/EXPLAIN Introduction By integrating the two Candle products, OMEGAMON II for DB2 and !DB/EXPLAIN, you create the most complete view possible for understanding and optimizing SQL and DB2 applications.
Background about !DB/EXPLAIN for DB2 Background about !DB/EXPLAIN for DB2 This section provides an overview of the !DB/EXPLAIN functionality. Functions provided by !DB/EXPLAIN for DB2 !DB/EXPLAIN provides a window into the operation of DB2. !DB/EXPLAIN offers benefits for I extracting DB2 data You can extract all or a portion of the data in the DB2 catalog according to the criteria you specify. This flexible technology lets you I – Extract and combine data from multiple PLAN_TABLES.
Using OMEGAMON II and !DB/EXPLAIN Complementary Functions Using OMEGAMON II and !DB/EXPLAIN Complementary Functions This section describes the functions of !DB/EXPLAIN and OMEGAMON II for DB2 that work together to help improve DB2 performance, and suggests ways you can use them. If you use Version 6 or Version 7 of DB2, refer to the !DB/Tools for DB2 Installation and Customization Guide, Version 510, for information on installing !DB/EXPLAIN.
Using OMEGAMON II and !DB/EXPLAIN Complementary Functions I 92 In a review of an application, !DB/EXPLAIN highlights some potentially poorly-performing access paths and you want more detail about actual execution.
Using the OMEGAMON II for DB2 Callable Interface Using the OMEGAMON II for DB2 Callable Interface The callable interface feature of OMEGAMON II for DB2 lets you make direct calls to !DB/EXPLAIN without first having to access TSO. Before using this feature, you should do the following: 1. 2. 3. 4. Customize the Classic started task. Set up !DB/EXPLAIN. Set up the CUA options. Set up the OMEGAMON II for DB2/EXPLAIN options.
Using the OMEGAMON II for DB2 Callable Interface Step 2 Action Press Enter. Result: The system displays the Classic Interface - DB/EXPLAIN panel. OMEGAMON II for DB2 ------- Classic Interface - DB/EXPLAIN -------- SSID: DIRT Option ===> The following information will be used to customize the DB/EXPLAIN interface.
Using the OMEGAMON II for DB2 Callable Interface Setting Up !DB/EXPLAIN Use the following procedure to set up !DB/EXPLAIN for OMEGAMON II for DB2. Step Action 1 Ensure that !DB/EXPLAIN V510 PSP is installed on every DB2 subsystem where you want to EXPLAIN statements. For information on installing !DB/EXPLAIN, see the !DB/Tools for DB2 Installation and Customization Guide, Version 510. 2 Define Destination Plan_Tables. The Destination PLAN_TABLE will be the current SQLID.PLAN_TABLE.
Using the OMEGAMON II for DB2 Callable Interface Step 3 Action Select option 2, Controls, from the pop-up and press Enter. Use the fields on the Terminal Options panel, shown below, to specify preferences for terminal options. For EXPLAIN, the key field on this panel is the SQL Process Level field. Type EXPLAIN or SAVE/EXPLAIN in this field to enable the function. Terminal Options Type a new setting or press Enter. Auto Refresh Interval . . . OFF Sec. (5-999 / Off) Use Status Words . . . . . .
Using the OMEGAMON II for DB2 Callable Interface Values for the SQL Process Level are described in the following table. Fields for Terminal Options SQL Process Level Description Type of processing to use for SQL text when pressing the SQL Process function key. You can process SQL from the following panels: I I I SQL Process PF Key SQL Call being Executed (Thread Detail). See “EXPLAINing SQL Code” on page 61. SQL Text (ATF). You can access the SQL Text panel through the Application Trace Facility.
Using the OMEGAMON II for DB2 Callable Interface Values for SQL Process Level SAVE/EXPLAIN Description Use this option to perform SAVE and EXPLAIN functions. Complete the required fields for the SAVE/EXPLAIN option. Setting Up the D2/EXPLAIN Options Use the following procedure to set up the D2/EXPLAIN options. Step Action 1 If you are not already logged onto CUA, then do so now. 2 From the main CUA panel (KD2001), select Options from the task bar and press Enter. The following pop-up appears. 1. 2.
Using the OMEGAMON II for DB2 Callable Interface Using the D2/EXPLAIN Options Panel The following table provides a list of fields and valid values for completing the D2/EXPLAIN Options panel. Field Description Extract Id 1– to 8–character field that identifies an extract of plans and packages. It represents a logical grouping that typically identifies a DB2 subsystem and an MVS system.
Using the OMEGAMON II for DB2 Callable Interface Field Initial Report Description Type the name of the initial !DB/EXPLAIN report to be displayed: ESTMT, EPATH, EPTBL, EOBJ, or ERECM. I I I I I ESTMT is the actual SQL statement text being explained. If there is an SQL error, SQL error text also appears. Use the pushbuttons to select from the other !DB/EXPLAIN report sections that follow. EPATH is a narrative describing the access path for the SQL statement.
Using the OMEGAMON II for DB2 Callable Interface Step 3 Action Select the PLANNAME you want and press Enter. Result: The system displays the Thread Detail panel. If SQL calls are being executed by the thread, the message (IN-SQL-CALL) also appears. The Thread Detail panel shows detailed information about the activity, an individual thread, or a parallel task, including wait time information.
Moving Interactively between !DB/EXPLAIN and OMEGAMON II Moving Interactively between !DB/EXPLAIN and OMEGAMON II This unit tells you how to move interactively between OMEGAMON II for DB2 and !DB/EXPLAIN using the copy-and-paste facility to review and EXPLAIN poorly performing SQL. It also describes how you can use the full functionality of !DB/EXPLAIN.
Moving Interactively between !DB/EXPLAIN and OMEGAMON II – To use the A (Application Trace Facility) Select, you must have previously run an ATF trace for the plans of interest and placed the results into a dataset. – To use the P (Plan Accounting) Select, you must have previously used the OMEGAMON II for DB2 Historical Collector to collect accounting trace data for analysis.
Using the Copy-and-Paste Facility in OMEGAMON II Using the Copy-and-Paste Facility in OMEGAMON II This unit describes how to use the copy-and-paste facility in OMEGAMON II to capture static or dynamic SQL and move it to !DB/EXPLAIN for analysis. This feature is for use with OMEGAMON II for DB2 versions below 300. If you are using OMEGAMON II for DB2 Version 300 or higher, the callable interface feature is recommended.
Using the Copy-and-Paste Facility in OMEGAMON II Capturing SQL using the ATF You can use the OMEGAMON II ATF to capture SQL statements and move them to !DB/EXPLAIN using the copy-and-paste facility. Follow these summary steps. Step 1 Action From the Application Trace Facility in OMEGAMON II, select the SQL TEXT pushbutton on the Application Trace SQL Detail panel. Result: The SQL Text panel displays.
Accessing OMEGAMON II Trace Information from !DB/EXPLAIN Accessing OMEGAMON II Trace Information from !DB/EXPLAIN This unit describes how you can obtain OMEGAMON II Application Trace and Accounting Report information when you are working in !DB/EXPLAIN. Direction of access The direction of access when obtaining OMEGAMON II Application Trace and Accounting Report information is always one-way: from !DB/EXPLAIN to OMEGAMON II, with OMEGAMON II operating under ISPF.
Accessing OMEGAMON II Trace Information from !DB/EXPLAIN Task You Want to Perform Access the OMEGAMON II Accounting Reports Facility from !DB/EXPLAIN Action to Take On the !DB/EXPLAIN Plans panel, in the SEL (selects) column, type P in the SEL field of the plan for which you want to display accounting report summary information. Result: OMEGAMON II displays the Accounting Reports Facility Historical Reporter Options panel.
Accessing OMEGAMON II Trace Information from !DB/EXPLAIN 108 OMEGAMON II for DB2 User’s Guide: CUA Interface, Version 540
A Panel Navigation Flowcharts Introduction The flowcharts in this appendix document the panels available for monitoring DB2. They provide an overview of the main navigation paths in OMEGAMON. You can use these flowcharts to help you understand how to access the panels you need in order to analyze and resolve DB2 problems. Most of the navigation begins at the System Status panel using the default action code S (Show details) on System Status panel objects.
Flowchart Conventions Flowchart Conventions The following table describes elements in the navigation diagrams. Element Description Box Represents a panel and shows the panel title. (Menu choices may not match the title of the destination panel.) Arrowhead Shows direction of navigation flow.
Flowchart Conventions FIGURE 1. OMEGAMON II for DB2 System Status Panel E D Installation Parameter Panels Application Trace Facility Panels F Object Analysis Panels Actions GoTo Options Tools Help System Status Workloads Resources Alerts A B C Workloads Panels DB2 Resources Panels Alerts Panels To find out how to navigate to a particular panel or group of panels, start with the System Status panel and then go to the letter representing the group of panels you want.
Workloads Panels Workloads Panels The next two figures show how you can access the realtime and near-term history panels for the objects from the Workloads portion of the System Status panel. For realtime information, use the S (Show details) action code.
Workloads Panels FIGURE 2.
Workloads Panels FIGURE 3.
Resources Panels Resources Panels The next two figures show how you can access the realtime and near-term history panels for objects from the Resources portion of the System Status panel. For realtime information, use the S (Show details) action code.
Resources Panels FIGURE 4.
Resources Panels FIGURE 5.
Alerts Panels Alerts Panels You can access the following panels by selecting a monitored object from the Alerts portion of the System Status panel.
Alerts Panels FIGURE 6.
Installation Parameters Panels Installation Parameters Panels You can access the following panels by selecting GoTo from the System Status panel.
Installation Parameters Panels FIGURE 7. Installation Parameters Panels D DSNZPARM Thread Parameters DSNZPARM Trace Parameters System Status DSNZPARM Logging Parameters DSNZPARM Archiving Parameters DSNZPARM Access and Security DB2 Installation Parameters 1. 2. 3. 4. 5. 6. 7. 8. 9.
Application Trace Facility Panels Application Trace Facility Panels You can access the following panels by selecting Tools, and then selecting Application Trace Facility from the System Status panel.
Application Trace Facility Panels FIGURE 8. Application Trace Facility Panels E Application Trace SQL Index GoTo Application Trace SQL Detail System Status Specify Application Trace Collection Options Application Trace Facility 1. Specify foreground trace collection options. 2. Stop foreground trace. 3. View foreground trace summary from storage. 4. View foreground trace summary from dataset. 5. Specify background trace collection options. 6. Display background trace requests. 7. Create trace dataset.
Object Analysis Panels Object Analysis Panels You can access the following panels by selecting Tools, and then selecting Object Analysis.
Object Analysis Panels FIGURE 9.
Thread Detail GoTo Panels Thread Detail GoTo Panels You can access the following realtime thread detail panels by using the GoTo pull-down menu from either the Thread Details panel or the Distributed Thread Detail panel. You can access the following thread history detail panels by using the GoTo pull-down menu from either the Thread History Details panel or the Distributed Thread History Details panel. Note: The online historical data collector must be active for near-term historical data to appear.
Thread Detail GoTo Panels FIGURE 10. Thread Detail GoTo Panels (Realtime) Locks/Claims Causing a Thread To Wait X Lock/Claims Owned by a Thread Thread Lock/Claim/ DrainActivity Current SQL Counts Thread Global Lock Activity SQL Call Being Executed Thread Group Buffer Pool Activity Thread Buffer Pool Activity GoTo 1.Thread Lock Information 2.Current SQL Counts 3.SQL Call Being Executed 4.Thread Buffer Pool Activity 5.Thread Resource Limits 6.Distributed Detail 7.Thread Package Summary 8.
Thread Detail GoTo Panels FIGURE 11. Thread History Detail GoTo Panels (Near-Term History) Y Thread History Lock Waits Thread History Lock/Claim/ Drain Activity Thread History Global Lock Activity Thread History SQL Activity Thread History Dynamic SQL Detail Thread History Buffer Pool Activity GoTo 1.Thread Lock Information 2.Current SQL Counts 3.SQL Call Being Executed 4.Thread Buffer Pool Activity 5.Thread Resource Limits 6.Distributed Detail 7.Thread Package Summary 8.Utility Detail 9.
Index Symbols !DB/EXPLAIN setting up CUA options 95 setup CUA options 96 setup for OMEGAMON II 95 SQL process level 97 Terminal Options pop-up 97 !DB/EXPLAIN and OMEGAMON II moving between products 102 !DB/EXPLAIN and OMEGAMON II for DB2 91 combining functions 89 sample situations 91 !DB/EXPLAIN, functions 90 !DB/EXPLAIN, setting up options 98 A account specifying at logon 21 action bar 29 fast path 29 action codes analyze problems 77 Actions menu 30 active profile 52 active thread exceptions 71 active tra
documentation conventions 10 dynamic profile update facility 52 E EDM pool exceptions 73 exception recommendations 77 exceptions 69 active thread 71 active trace 75 analyzing 77 background 70 buffer pool 72 CICS 69 DB2 activity 74 DDF statistics 73 distributed 70 EDM pool 73 IMS 70 inactive thread 72 lock 74 lock statistics 73 log manager 73 rules 79 TSO 70 utility 70 volume activity 75 Exit Confirmation panel 24 exiting OMEGAMON II 24 EXPLAINing SQL code 61, 100 extracting DB2 catalog data 90 F fast path
moving between !DB/EXPLAIN and OMEGAMON II 102 N national language 41 navigation fast pathing 29 flow charts 109 near-term history panels 115, 117 Normal Status Word 96 O object analysis panels 124 OMEGAMON II profile to migrate 22 OMEGAVIEW trigger key 24 zooming to OMEGAMON II 21 optimizing SQL and DB2 applications 89 Options menu 32, 40 options, logon modifying 22 output dataset requirements IFCID trace facility 85 Resources (realtime) 115 Sign On 20 Specify User Authorities 48 System Status 25 system
colors 67 definition 67 specifying 43 status words specifying 43 stopping IFCID trace facililty 87 subsystem profile 22 switching a profile 52 system administrator 48 System Status panel monitored components 66 panel parts 25 system status panel navigating from 110 V View menu 31 volume activity exceptions 75 VTAM applid logging onto 20 W warning status 67 Warning Status Word 96 Web site, Candle 12 workloads panels 112 Y yellow status 67 T terminal features 43 terminal options 45 Terminal Options panel
