background image

Using OMEGAMON II

81

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.

Summary of Contents for Network Adapter DB2

Page 1: ...User s Guide CUA Interface OMEGAMON II for DB2 Version 540 G251246500 August 2004 Candle Corporation 100 North Sepulveda Blvd...

Page 2: ...mmand Center Enterprise Candle Management Workstation Enterprise Reporter Plus ER ERPNet ETEWatch Customizer HostBridge InterFlow Candle InterFlow Lava Console Managing what matters most MessageMate M...

Page 3: ...anel Preferences 41 Specifying Terminal Options 43 Setting Thresholds 46 Assigning User Authorities 48 Maintaining Profiles 49 Setting Filtering Options for Near Term Thread History 54 Specifying Save...

Page 4: ...Interface 93 Moving Interactively between DB EXPLAIN and OMEGAMON II 102 Using the Copy and Paste Facility in OMEGAMON II 104 Accessing OMEGAMON II Trace Information from DB EXPLAIN 106 Appendix A Pan...

Page 5: ...ces Panels Realtime 116 FIGURE 5 Resources Panels Near Term History 117 FIGURE 6 Alerts Panels 119 FIGURE 7 Installation Parameters Panels 121 FIGURE 8 Application Trace Facility Panels 123 FIGURE 9 O...

Page 6: ...6 OMEGAMON II for DB2 User s Guide CUA Interface Version 540...

Page 7: ...2 subsystem application programmers who wish to monitor their programs and database administrators This document covers basic information you need to get started using OMEGAMON including signing on an...

Page 8: ...I for DB2 Reference Manual Classic Interface Version 540 I OMEGAMON II for DB2 Tuning Guide Version 540 I OMEGAMON II for DB2 Historical Reporting Guide Version 540 I OMEGAMON II for DB2 Configuration...

Page 9: ...the Shrink to Fit option Printing problems Your printer ultimately determines the print quality of your output Sometimes printing problems can occur If you experience printing problems potential areas...

Page 10: ...updated material Variables and literals in command syntax examples In examples of command syntax for the OS 390 VM OS 400 and NonStop Kernel platforms uppercase letters indicate actual values literals...

Page 11: ...ed arguments or to group arguments for clarity Example COMPARE workload REPORT SUMMARY HISTOGRAM The workload variable is required The REPORT keyword must be specified with a value of SUMMARY or HISTO...

Page 12: ...CSS eDelivery allows you to download products documentation and maintenance 24 hours a day 7 days a week eNotification notifies you of product updates and new releases In addition you can find inform...

Page 13: ...13...

Page 14: ...14 OMEGAMON II for DB2 User s Guide CUA Interface Version 540...

Page 15: ...ts were made to the historical reporting component Some of these new features are I Sequential output files are produced from the online collector I All record types can be loaded into DB2 tables usin...

Page 16: ...ion 540 includes support for long object names New displays are provided in support of this feature Unicode support Unicode support includes character conversion from Unicode to EBCDIC for online disp...

Page 17: ...new fields New DSNZPARM fields The DSNZPARM Thread Parameters panel now includes numerous new fields Distributed Thread display improvements The Distributed Thread display improvements include the add...

Page 18: ...18 OMEGAMON II for DB2 User s Guide CUA Interface Version 540...

Page 19: ...to help you get started using the CUA interface for OMEGAMON II for DB2 to monitor your DB2 subsystem Chapter Contents Signing On 20 Setting Logon Options 22 Signing Off 24 Panel Features 25 Data Pan...

Page 20: ...on Sign On panel To begin the sign on from the logo panel I Press Enter Result The Sign On panel appears You are required by OMEGAMON II to enter information for only two fields on the Sign On Panel U...

Page 21: ...ield 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...

Page 22: ...ser profiles If you enter the name of a nonexistent user profile OMEGAMON II creates the profile using the name you specified and Candle s default settings Subsystem Profile The name of your subsystem...

Page 23: ...xception is set to trip when it exceeds its threshold then the warning threshold is set to 80 of the critical threshold If a CUA exception is set to trip when it falls below its threshold then the war...

Page 24: ...g any of these methods I press Enter I press F3 I type x or 1 in the entry field and press Enter Returning to OMEGAVIEW from OMEGAMON To return to OMEGAVIEW from OMEGAMON use the trigger key you defin...

Page 25: ...Collector Status Field Displays the status of the online data collector I N T History indicates the collector is active and near term history data is available I Realtime indicates the collector is no...

Page 26: ...Codes Displays available action codes Note Action codes do not apply to every product panel 5 Panel Body Displays performance data or status information Highlighting or color is used to indicate stat...

Page 27: ...using the scroll indicator which appears in the upper right corner of the panel For example Lines __23 to 51 of 117 The first two numbers show the beginning and ending lines currently displayed on th...

Page 28: ...losely 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 push...

Page 29: ...pe the first letter of the choice in the action bar entry field and press Enter Result A pull down menu appears from which you can make a selection When selecting Actions from the action bar you must...

Page 30: ...ars Actions pull down The Actions pull down menu allows you to display more information about the selected object on the panel body System Status DSNZPARM IRLM Parameters GDR Actions Goto Options Tool...

Page 31: ...y of ways You can display all fields only fields with values that you specify or only fields with warning and critical status The selections on the View menu vary Note View is not available on all pan...

Page 32: ...ws you to access OMEGAMON II tool facilities You can select Application Trace or Object Analysis Help pull down The Help pull down menu provides access to the help facilities in OMEGAMON II 1 Preferen...

Page 33: ...ject I Type the action code letter in the entry field of the panel object and press Enter Result Another data panel appears depending on the action code you entered Action Code Description Show Detail...

Page 34: ...field or toggles between two selections such as ON OFF Promptable fields are denoted with a plus sign F5 Refresh Updates the panel with current data F6 DB2 Console Goes to the DB2 or MVS console where...

Page 35: ...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...

Page 36: ...II To access help from the action bar I Type H in the action bar entry field or move the cursor to the entry field next to Help and press Enter Result The Help menu appears as shown below The help men...

Page 37: ...example and press Enter Result All glossary entries containing the word drain appear 3 Select from the list and press Enter Result The glossary definition of drain appears as shown in the figure belo...

Page 38: ...if pressed outside an entry field Note Field level help is not available for all entry fields F2 Extended help Displays additional help information F3 Exit Exits the help system F5 Glossary Accesses t...

Page 39: ...ion 40 Specifying Panel Preferences 41 Specifying Terminal Options 43 Setting Thresholds 46 Assigning User Authorities 48 Maintaining Profiles 49 Setting Filtering Options for Near Term Thread History...

Page 40: ...d 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 Preferen...

Page 41: ...o go quickly to the DB2 Installation Parameter panel type GD G for Goto D for DB2 Installation Parameters in the entry field of the action bar and press Enter If you select Off the action bar entry fi...

Page 42: ...trols the language you want to use for your session Date Format Specifies the date format you want to use for your session For example mm dd yy or dd mm yy Time Format Specifies the time format you wa...

Page 43: ...Status Words On color terminals color status bars represent the performance of status items You also can specify a word or a series of characters to appear for each level of status The word or charact...

Page 44: ...and paste facility to perform the EXPLAIN CUA TSO Toggle Key Use to toggle between CUA and TSO sessions Values for SQL Process Level Description MANUAL After you have selected the SQL text to be EXPLA...

Page 45: ...profile or you have authorization you can save the new terminal options to the profile To save the new terminal options to the active profile I Select Save values into profile and press Enter Result T...

Page 46: ...loads Result The Workloads Index menu appears I Resources Result The Resource Index menu appears I Alerts Result The Alerts Index menu appears 3 Select the workload resource or alert whose threshold v...

Page 47: ...nt session and the subsystem profile is not changed Saving to a profile If you have authorization you can save the new threshold values in the subsystem profile To save the new threshold values in the...

Page 48: ...ptions pull down menu Result The Specify User Authorities window appears Unauthorized users can view this list but cannot make any changes Adding a user To assign user authority to an OMEGAMON II user...

Page 49: ...stem Contains threshold values Subsystem profiles can be shared among users Each DB2 subsystem should have its own customized subsystem profile named with the subsystem ID If you enter DEFAULT for the...

Page 50: ...t copy DEFAULT 3 Type a description for the new profile and press Enter Result The new profile name and description with your user ID identifying you as the owner of the profile are added to the profi...

Page 51: ...of the target partitioned dataset MEMBER is the target partitioned dataset member and PROFILE is the source profile Deleting a profile If you own a profile or have authorization you can delete a profi...

Page 52: ...ct DEFAULT for a subsystem profile OMEGAMON II automatically selects the subsystem profile that matches the ID of the subsystem being monitored Using the Dynamic Profile Update Facility The Dynamic Pr...

Page 53: ...TD 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 Example command for impor...

Page 54: ...thread information to be reported Specify date and time as follows End Date Time The end date and time of the thread information that is being reported Near Term Thread History Filter Options Oldest R...

Page 55: ...rt Interval The number of minutes by which the report period is partitioned You must specify a value that is a multiple of the subinterval value that is evenly divisible into 60 The subinterval is the...

Page 56: ...QL Statement PDS The name of the partitioned dataset into which you want to save the SQL statement text Member Name The name of the partitioned dataset member into which you want to save the SQL state...

Page 57: ...lues menu select option 1 Realtime monitor interfaces Result The Started Task and VTAM Information panel is displayed 2 Press Enter Result The OMEGAMON II for DB2 Classic Interface DB EXPLAIN panel is...

Page 58: ...n the function has completed Step Action 1 Ensure that DB EXPLAIN V300 PSP 9602 or greater is installed on every DB2 subsystem where you want to EXPLAIN statements For information on installing DB EXP...

Page 59: ...EXPLAIN in this field to enable the function For a list of the fields and valid values for completing the Terminal Options panel see Using the Terminal Options window on page 43 Step Action 1 If you...

Page 60: ...DB EXPLAIN options specified in the group profile and the system profile Group Profile DSN Optional field names the dataset containing the DB EXPLAIN group profile Contains overrides to the DB EXPLAIN...

Page 61: ...d Gather Statistics I Type YES to have DB EXPLAIN gather statistics I Type NO to bypass Initial Report Type the name of the initial DB EXPLAIN report to be displayed ESTMT EPATH EPTBL EOBJ or ERECM I...

Page 62: ...Jobname DSNTEP41 31 4m 05 3 IN SQL CALL 5153K 61490 0 DLKEX212 KO2400HP 1 3d 00 0 NOT IN DB2 0 0 0 TDO2S03 KO2400HP 2 1d 00 1 NOT IN DB2 0 0 0 TDO2S44 KTWPLNR 1 0s 00 0 IN SQL CALL 0 0 0 RBRIN3 F1 He...

Page 63: ...ASID Corrid RBRIN3 Authid RBRIN3 Lines 1 to 15 of 15 SQL call is active call information is as follows Thread Status IN SQL CALL SQL Request Type DYNAMIC Total SQL Reqs 2 SQL Call Type OPEN CURSOR SQL...

Page 64: ...Using the Callable Interface 64 OMEGAMON II for DB2 User s Guide CUA Interface Version 540...

Page 65: ...onitoring from the System Status Panel 66 System Status Components and Related Exceptions 69 Investigating an Active Threads Alert 77 Evaluating and Modifying Thresholds 78 Reviewing Near Term History...

Page 66: ...t it for detailed analysis Workloads Information about threads the links between allied agents and a DB2 subsystem Resource DB2 subsystem resource statistics Alerts Summary of DB2 activity active and...

Page 67: ...do not meet performance standards Note This does not apply to panels that appear when you select Active Threads from the System Status panel Investigating an alert When a status indicator turns yello...

Page 68: ...itored object by entering one of these action codes Note Not all action codes apply to every monitored object S Shows more detail A Shows which exceptions tripped the current values for the tripped ex...

Page 69: ...e CICS POOL threads to THRDA exceeds the specified threshold The THRDA value is specified in the CICS resource control table RCT CICS threadmax CICT The percentage of active CICS threads to THRDMAX ex...

Page 70: ...region IMND There are no IMS dependent regions defined to DB2 for the IMSID Region connection failed IMCN The specified IMS dependent region for the IMSID failed to connect to DB2 due to an error Desc...

Page 71: ...ddress space that has DB2 connections and threads exceeds the specified threshold Getpage read ratio GETP The ratio of logical page read getpage requests to physical page read read I O requests is les...

Page 72: ...that a thread has been suspended due to ARCHIVE LOG MODE QUIESCE exceeds the specified threshold value Wait for DB2 service WSRV The amount of time a thread has been waiting for a DB2 service to comp...

Page 73: ...ds the specified threshold Description Code Explanation Primary logs available LOGN The number of available primary active logs is less than or equal to the specified threshold Recovery log archive AR...

Page 74: ...on Create thread waits THDQ The number of users waiting for create thread processing exceeds the specified threshold Indoubt threads INDT The number of terminated threads that are indoubt exceeds the...

Page 75: ...he service time for the volume exceeded the specified threshold The threshold is expressed in milliseconds and must be an integer between 0 9999 Volume TL I O rate VTIO The total I O rate per second f...

Page 76: ...er of triggers executing TRGE Indicates that the number of triggers executing exceeds the threshold The threshold is expressed as a count of triggers currently executing The threshold can be an intege...

Page 77: ...or critical status Exception recommendations For an explanation of an exception that appears on the Analyze Problems window and for a recommendation about how to resolve the problem that caused the ex...

Page 78: ...ny threshold or status setting on a thresholds panel I Move the cursor to the field you want to change and type in the new value Result The Save Profile Confirmation pop up appears You can choose to s...

Page 79: ...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...

Page 80: ...hread activity for a specified reporting period Note The reporting period and reporting intervals are set on the Near Term Thread History Filter Options panel The Start and End dates and times show th...

Page 81: ...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...

Page 82: ...h panel contains Initiating a background application trace The background application trace facility is available only from the CUA component for OMEGAMON II for DB2 To initiate a background applicati...

Page 83: ...ation Trace DSN panel contains three fields All fields are required unless your installation is using SMS for dataset allocation In this case you may omit the Volume information Viewing the list of ba...

Page 84: ...ed Application Traces panel OMEGAMON II displays a pop up panel that permits you to perform these activities I modify or cancel a deferred trace request if the status of the trace is Waiting I view su...

Page 85: ...ame is a required field The dataset must have the following characteristics I physical sequential I variable length blocked I logical record length 32756 I block size 32760 You can select the IFCIDs t...

Page 86: ...record is 64 bytes in length The following table describes each field in the header information in the sequence in which they appear Field Description DSN The user specified dataset name provided whe...

Page 87: ...o display the IFCID Trace pull down menu I enter option 3 Stop IFCID Trace OMEGAMON II for DB2 prompts you to confirm that you want to stop the IFCID trace I Press Enter to confirm A message displays...

Page 88: ...Using the IFCID Trace Facility 88 OMEGAMON II for DB2 User s Guide CUA Interface Version 540...

Page 89: ...in a place accessible to DB EXPLAIN users I analyze executing SQL I access OMEGAMON II application trace and historical reporting information to validate access path analysis This chapter tells how t...

Page 90: ...u can use DB EXPLAIN functions to review and improve SQL These features let you Review application code and monitor specific SQL performance to understand the access paths and their relative costs Sto...

Page 91: ...AMON II for DB2 from DB EXPLAIN you can determine the names of the VSAM datasets or DB2 tables that contain the stored application trace data for OMEGAMON II You can use the OPTIONS command from the O...

Page 92: ...omplementary Functions 92 OMEGAMON II for DB2 User s Guide CUA Interface Version 540 I In a review of an application DB EXPLAIN highlights some potentially poorly performing access paths and you want...

Page 93: ...rocedure to customize the Classic started task Note You may have already completed this procedure during the installation of OMEGAMON II for DB2 Step Action 1 From the Configure OMEGAMON II for DB2 fo...

Page 94: ...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 th...

Page 95: ...pts to create the tablespace PLANTBLS in the DB TOOLS default database I If the PLAN_TABLE cannot be created there DB EXPLAIN attempts to create the PLAN_TABLE in the database DSNDB04 I If it cannot c...

Page 96: ...performance of status items You can also specify a word or a series of characters to appear for each level of status The word or characters appear in a row within the colored status bar On non color t...

Page 97: ...evel Description MANUAL After you have selected the SQL text to be EXPLAINed you must use the function key defined SQL Process key to copy the SQL Then switch to TSO ISPF CUA TSO toggle and paste the...

Page 98: ...The following pop up appears 3 Select option 8 D2 Explain Options from this panel and press Enter Use the D2 EXPLAIN Options panel shown below to specify parameters required to invoke DB EXPLAIN duri...

Page 99: ...ved Must be a PDS The SQL Process Level is specified on the Terminal Options panel under the Options pull down fastpath O2 If SAVE the text is saved but DB EXPLAIN is not invoked If SAVE EXPLAIN DB EX...

Page 100: ...tics for objects referenced by the SQL statement This section is available if you specified Yes for Gather Statistics in the D2 EXPLAIN Options panel under the Option pull down fastpath 08 I ERECM pro...

Page 101: ...Elapsed Time 00 00 05 932 CPU Utilization Total CPU Time 00 00 03 586 Total Parallel Tasks Total Stored Proc CPU 00 00 00 000 Current Parallel Tasks IN DB2 Times Total Current Elapsed Time 00 00 05 9...

Page 102: ...DB EXPLAIN functions I Historical reports to review accounting performance information about application plans and packages that are most frequently used consume the greatest amount of resources or ha...

Page 103: ...s the CUA TSO toggle The next table documents this procedure The procedure in the next table assumes that you have not previously logged onto TSO from within the current OMEGAMON II for DB2 session If...

Page 104: ...ou specified Capturing SQL from a running thread You can use the copy and paste facility to capture running SQL from DB2 control blocks by following the steps in the table Step Action 1 From OMEGAMON...

Page 105: ...QL text you want to EXPLAIN Result OMEGAMON II displays a pop up indicating that the SQL text has been saved 3 The CUA TSO toggle permits you to switch back and forth between your OMEGAMON II session...

Page 106: ...use You can use the OMEGAMON II OPTIONS menu to specify the input datasets or DB2 table names to use for reporting or to change their names and to specify the report filtering selection criteria Proce...

Page 107: ...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 p...

Page 108: ...Accessing OMEGAMON II Trace Information from DB EXPLAIN 108 OMEGAMON II for DB2 User s Guide CUA Interface Version 540...

Page 109: ...s Most of the navigation begins at the System Status panel using the default action code S Show details on System Status panel objects Some of the navigation begins at the Tools pull down menu Navigat...

Page 110: ...at detail system performance or allow you to perform OMEGAMON functions The following figure shows you the navigation areas that are detailed in the flowcharts in this appendix Element Description Box...

Page 111: ...f panels start with the System Status panel and then go to the letter representing the group of panels you want Actions GoTo Options Tools Help Workloads Resources Alerts A B C D E F Installation Para...

Page 112: ...ads portion of the System Status panel For realtime information use the S Show details action code For near term history information do the following I verify that the online collector is running I en...

Page 113: ...GoTo CICS Connections CICS RCT Summary CICS RCT Detail CICS Thread Summary IMS Connections IMS Thread Summary IMS Subsystem Member Information TSO Thread Summary Thread Detail Background Thread Summa...

Page 114: ...ity __Distributed __All Connections Workloads Y Thread History by Report Interval Thread History by Subinterval Thread History Summary Thread History Buffer Pool Summary Thread History DB2 Time Summar...

Page 115: ...panel For realtime information use the S Show details action code For near term history information do the following I verify that the online collector is running I enter the H Historical action code...

Page 116: ...t Skeleton Cursor Tables EDM Snapshot Skeleton Package Table Summary EDM Snapshot Skeleton Package Table Detail Buffer Pool Snapshot Datasets Buffer Manager EDM Pool Statistics Lock Manager Statistics...

Page 117: ...y Report DB2 Command Statistics Summary by Report Buffer Pool Statistics Detail by Report EDM Pool Statistics Detail by Report Lock Manager Statistics Detail by Report Log Manager Statistics Detail by...

Page 118: ...s Panels 118 OMEGAMON II for DB2 User s Guide CUA Interface Version 540 Alerts Panels You can access the following panels by selecting a monitored object from the Alerts portion of the System Status p...

Page 119: ...Pool Snapshot Open Pagesets BufferPool Snapshot Datasets DB2 Activity Subsystem Support Manager All Threads Connected to DB2 Thread Detail Inactive Threads Distributed Allied Thread Summary Distribute...

Page 120: ...allation Parameters Panels 120 OMEGAMON II for DB2 User s Guide CUA Interface Version 540 Installation Parameters Panels You can access the following panels by selecting GoTo from the System Status pa...

Page 121: ...Parms 7 DSNZPARM Dataset and Databases Parms 8 DSNZPARM Data Definition Control Support 9 DSNZPARM Stored Procedures Parms DB2 Installation Parameters D DSNZPARM Thread Parameters DSNZPARM Trace Para...

Page 122: ...els 122 OMEGAMON II for DB2 User s Guide CUA Interface Version 540 Application Trace Facility Panels You can access the following panels by selecting Tools and then selecting Application Trace Facilit...

Page 123: ...est Application Trace Thread Summary from Storage Select Dataset and Trace Parameters Specify Background Trace Collection Options Display Background Trace Requests Create Trace Dataset Application Tra...

Page 124: ...Object Analysis Panels 124 OMEGAMON II for DB2 User s Guide CUA Interface Version 540 Object Analysis Panels You can access the following panels by selecting Tools and then selecting Object Analysis...

Page 125: ...atabase Activity Spacename Activity Thread Activity by Spacename Thread Activity by Database Volume Activity Summary Volume Database Activity Volume Spacename Activity Volume Dataset Activity Volume D...

Page 126: ...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 eit...

Page 127: ...16 Exit GoTo X Lock Claims Owned by a Thread Current SQL Counts SQL Call Being Executed Thread Buffer Pool Activity Thread Resource Limits Statistics Locks Claims Causing a Thread To Wait Thread Lock...

Page 128: ...ncel Thread GoTo Y Thread History Lock Claim Drain Activity Thread HistorySQL Activity Thread History Dynamic SQL Detail Thread History Buffer Pool Activity Thread History Resource Limit Statistics Th...

Page 129: ...ons 70 blue status 67 buffer pool exceptions 72 C callable interface using 93 Candle Web site 12 changing password at logon 21 CICS exceptions 69 Classic Interface DB EXPLAIN panel 93 Classic Interfac...

Page 130: ...help About help 36 Glossary 36 Index 36 User Information 36 Help menu 32 help system function keys 38 glossary 37 panel help 36 hierarchy of panels 109 highlighted fields 27 highlighted phrase in a h...

Page 131: ...escription 51 navigation 109 object analysis 124 Options menu 40 57 93 Preferences 41 Resources near term history 115 Resources realtime 115 Sign On 20 Specify User Authorities 48 System Status 25 sys...

Page 132: ...ted fields 27 logon options 22 setting 78 time format setting 41 time separator character setting 42 time date displaying 41 toggle key CUA TSO 43 Tools menu 32 TSO exceptions 70 turquoise status 67 U...

Reviews: