IBM DATABASE 2 Performance Monitor for MVS Version 4

Software Announcement
September 27, 1994
Announcement Letter Number: 294-600


Table of Contents:

(Corrected on November 18, 1994)

In the Software Requirements section of the external letter, the installation of DB2 PM was corrected as follows: "System Modification Program/Extended (SMP/E) Release 7 (5668-949) Service Level 20, or later".

In Brief . . .

DB2 PM for MVS Version 4 Enhancements

  • Provides comprehensive support for the DB2 for MVS/ESA Version 4 instrumentation and important new DB2 for MVS/ESA Version 4 functions:
    • Data Sharing

    • Query CPU Parallelism

    • Stored Procedures

    • Row-Level Locking

    • Read-Through Locks

    • SQL92 Entry-Level

    • Table Check Constraints

    • Partition Independence

As the capacity and capability of relational database technology have grown, so has the need for a timely, accurate performance-monitoring and analysis tool.

DATABASE 2 (TM) Performance Monitor for MVS (DB2 (R) PM for MVS) (5655-102) enables you to monitor, analyze, and optimize the performance of IBM DATABASE 2 for MVS (DB2 for MVS). It includes interactive report generation, background reporting, an explain function, and an online monitor to provide an immediate "snap-shot" view of DB2 for MVS activities, while the system is operational.

Comprehensive support for the DB2 for MVS/ESA (TM) Version 4 instrumentation, as well as for important new DB2 for MVS/ESA Version 4 functions, such as Data Sharing, CPU Parallelism, and Row-Level Locking, make this new version an excellent choice for customers.

DB2 PM for MVS

  • Improves Systems Management by providing immediate access to, and processing of, precise and up-to-date DB2 for MVS performance data for problem determination and resolution
  • Delivers always the precise status of a DB2 for MVS system or its threads by using published interfaces of DB2 for MVS, such as Instrumentation Facility Interface (IFI)
  • Allows for interactive report generation, background reporting, and online exception processing
  • Offers an explain function
  • Permits handling of growing application workload
  • Provides investment protection through tuning of DB2 for MVS to higher levels of availability
  • Enhances user productivity through performance optimization of DB2 for MVS
The first customer shipment of DB2 PM for MVS Version 4 will occur by February 24, 1995, to an already selected set of customers. These customers will participate in a program that is designed to validate that IBM has achieved the quality objectives for DB2 PM in a variety of production environments. When these customers confirm that IBM has achieved the quality objectives, IBM will announce availability and ordering information in a planned availability announcement.

DB2 PM for MVS Version 4 may also be shipped on the same date to customers participating in the Quality Partnership Program (QPP) and the System/390 (R) Parallel Sysplex offering Systems Assurance Product Review (SAPR) for DB2 for MVS/ESA Version 4.

 (TM) Trademark of International Business Machines Corporation
 (R)  Registered trademark of International Business Machines
      Corporation

------------------------------

This announcement is provided for your information only. For additional information, contact your IBM representative, call 800-IBM-4YOU, or visit the IBM home page at: http://www.ibm.com



DESCRIPTION

DB2 PM enables the user to monitor, analyze, and optimize the performance of DB2. It provides interactive report generation, background reporting, and an explain function, as well as precise performance data and exception processing online.

DB2 PM uses published interfaces of DB2 and provides at any time the correct status of a DB2 system or its threads.



Online Monitor

The Online Monitor provides an immediate "snap-shot" view of DB2 activity while the system is operational. Functions provided by the Online Monitor include:

  • Immediate access to data from the DB2 Instrumentation Facility Interface, which permits faster identification, isolation, and resolution of a performance problem.

  • Exception reporting at the thread or system level based on user-defined values that can alert users to potential or existing performance problems. Exception reporting works even if no user is logged on to the DB2 system.

  • Diagnostic Analysis of Monitored Thread enables a user to request DB2 PM to analyze a thread's performance and presents an online diagnosis of the thread's performance constraints.

  • Collection of Performance Data allows a user to start a trace task immediately whenever a performance problem has occurred. The DB2 traces can be started automatically, at certain time periods, or at any exceptional event of interest. Once started, a trace can be stopped automatically or manually.

  • A trace facility allowing the collection of DB2 instrumentation data in a user data set, which can then be processed immediately in batch mode, providing an avenue to more in-depth analysis without any impact on the System Management Facility (SMF) and/or the Generalized Trace Facility (GTF) of MVS.

  • User tailorable display panels that can be refreshed either manually, or automatically at user-specified intervals, to keep the displayed information up-to-date.

  • Online History functions allow you to display thread, statistics, and system parameters information produced in the recent past. Intervals at which history data is collected can be defined by the user.



Batch Reporting

  • Exception Reporting provides a means to focus your attention on accounting and statistics data found to exceed predefined thresholds. The thresholds can be set either manually by the user or automatically based on the specific site workload.

  • "Top-N" reporting identifies and highlights the most excessive consumers of selected DB2 resources in accounting reports and traces. In addition, reports and traces showing only Top-N entries are available.

  • Multiple DB2 subsystems are processed concurrently, and merged reports are provided for distributed transactions executed at more than one DB2 subsystem.

  • Reporting by intervals identifies "peak" DB2 intervals.

  • User-tailored reporting gives the capability to customize site-specific accounting and statistics traces.

  • Accounting, statistics, locking and audit data are converted into the format suitable for loading into DB2 tables.

  • Various types of graphs of accounting and statistics data for performance trend analysis.

  • Panel-driven interface for specifying DB2 PM requests and maintaining DB2 PM environments.

  • A variety of statistics and accounting reports ordered by all available DB2 identifiers (such as plan, program, and authorization ID)

  • Specialized locking, I/O, audit, system parameters and utility reports for a detailed analysis of the corresponding DB2 functions.

  • SQL activity traces and reports for a detailed analysis of SQL statements, cursors, programs and associated activity such as locking, I/O, parallelism.

  • Record Trace, an in-depth reporting tool, used for most complicated performance monitoring and problem determination tasks.



Explain Functions

Explain support provides the following functions:

  • Online Monitor Explain

    The Online Monitor explain function provides a real time analysis of the access path methods chosen by DB2 for a given SQL statement. By modifying the SQL statement text and re-explaining it, a user is able to tune a DB2 system online. The Online Monitor explain processing improves the usability in relation to the native SQL EXPLAIN by using terms in a more natural language (English).

    The distributed explain function allows for explaining remote queries without changing the subsystem by just specifying the current server information.

  • Batch Explain Reports

    These reports are used to see the access path DB2 has chosen for an SQL statement together with the associated catalog information. The unit of reporting in this report set is an explainable SQL statement, identified by either a query number, a text of a SQL statement, a plan or package to which the statement belongs to, or a QMF (TM) query name.

  • Source Explain Facility

    The source explain facility has SQL statements explained directly from the ISPF/PDF editor. This allows the application developer to prototype and tune SQL statements (embedded in a source file) during program coding. The explain facility supports Assembler, C, FORTRAN, COBOL, PL/I, REXX, or SPUFI source files.



Functional Enhancements

DBS PM for MVS Version 4 provides additional functions over DB2 PM for MVS Version 3 Release 2 by supporting the new DB2 for MVS/ESA Version 4 instrumentation and the following important new DB2 for MVS/ESA Version 4 functions.

  • Data Sharing

    DB2 for MVS/ESA Version 4 Data Sharing provides DB2 applications with full read/write concurrent access to data stored on shared DASD between two or more DB2 subsystems. The DB2 subsystem sharing the data belongs to a DB2 data sharing group and each of the subsystems is considered a member of the data sharing group.

    The most important new function introduced to support data sharing is group-scope reporting. In addition to the standard member-scope reports, DB2 PM for MVS Version 4 can merge together instrumentation data produced by members of a group to give a full picture about the use of a shared resource. The following report sets provide both member- and group-scope reporting:

    • Locking

      The new locking traces and reports show most of DB2 serialization (locking, latching, and claim or drain) and events related to data sharing coherency. By incorporating all these events in one report, a comprehensive view of overall locking activity is provided. This maintains a similar report appearance regardless of the event type being reported. The suspension, lockout, timeout and deadlock traces are special cases of the locking trace.

    • Statistics

      The statistics reports present data calculated at regular user-specified intervals. The layout of statistics reports can be tailored by specifying which data blocks and which fields within these blocks should be reported.

    • Audit

      The audit summary report is enhanced in producing detailed reports of aggregated audit data. A new DB2 PM identifier OBJECT allows for reporting by the object of an audit event (for example, authorization failures per table) which is necessary to fully exploit the possibilities of group-scope reporting.

    The Online Monitor Display Thread and Display Statistics functions have been extended to include performance data related to a data sharing environment, for example:
    • Data Sharing Locking Data
    • Group Buffer Pools Activity Data
    In addition, the Display Statistics function provides statistics about DB2's usage of the Coupling Facility.

  • Query CPU Parallelism

    DB2 for MVS/ESA Version 4 Query CPU Parallelism extends the parallel processing first implemented in DB2 for MVS Version 3 (Query I/O Parallelism). It reduces the response time of long-running (read-only) queries by breaking down large queries into smaller units and utilizing multiple processors to run those units in parallel.

    The number of parallel tasks created to process a query is called the degree of parallelism. DB2 determines the degree of parallelism required to optimize response time and also decides whether to use parallel CPU or Parallel I/O processing.

    DB2 PM combines all parallel tasks and provides an integrated view of plan/program execution.

  • Stored Procedures

    DB2 for MVS/ESA Version 4 introduces a new interface, the SQL CALL statement, to allow a requester to invoke user-written SQL programs at a DB2 server. These user-written SQL programs are Stored Procedures. Because they are compiled and bound at the DB2 server, they are local and do not carry the overhead of increased elapsed and CPU times of distributed SQL.

    Although designed primarily for remote work, the CALL statement is supported in both local and distributed DB2 applications. An SQL CALL may invoke either local or remote stored procedures. DB2 can execute multiple stored procedures concurrently.

    DB2 PM identifies times spent in processing stored procedures and traces the flow into and out of stored procedures.

  • Row-Level Locking

    DB2 for MVS/ESA Version 4 Locking and Index Enhancements introduces significant changes in DB2 index management with new Type 2 indexes and row level locking.

    Row level locking allows a transaction to lock only a single row of data within a locked table space. Locking only a row, rather than a page, reduces contention on the data and the probability of deadlocks, especially when a transaction locks only one row at a time and the probability of two transactions wanting the same row is small.

    DB2 PM supports the new lock size in all its locking related reports. The new suspension and lockout reports identify contentions on a per-row basis.

  • Read-Through Locks

    DB2 for MVS/ESA Version 4 introduces a new function, Uncommitted Read. The ability to read uncommitted data will be provided by an option to allow an SQL read of data that has been locked for update. Resource locking is controlled at the application level with a new bind option, and the statement level with new SQL syntax.

    DB2 PM supports the related DB2 instrumentation.

  • SQL92 Entry-Level

    SQL92 Entry-Level enhances the functionality of the relational data model supported by DB2 in order to comply with the SQL '92 Standard at the Entry Level and to keep pace with the evolving ANSI and OSI standards.

    DB2 PM identifies new SET CURRENT RULES statements.

  • Table Check Constraints

    Table Check Constraints addresses part of the objectives for SQL92 Entry Level support.

    Table Check Constraints are provided to enhance the ability of the users to control the integrity of their data. A check constraint designates the values that specific columns of a table can contain.

    In previous releases of DB2, control of data integrity was limited to the use of referential constraints and unique constraints, not sufficient for many business rules. Table check constraints, together with existing data integrity mechanism, provide a stronger general integrity.

    DB2 PM identifies any occurrence of table check activity.

  • Partition Independence

    DB2 for MVS/ESA Version 4 Partition Independence -- Part2 extends independent, parallel partition processing introduced in DB2 for MVS Version 3 to non-partitioning indexes by:

    • Removing the operational restriction of non-partitioned indexes, thus providing the ability to isolate the keys for a data partition and so limit access to a single logical partition.

    • Supporting parallel processing for partitioned table spaces with non-partitioned indexes without the need for serialization.

      DB2 PM supports the related DB2 instrumentation.

The result of all of the above, in terms of functionality, usability, installation, and adaptability, should be increased productivity of DB2 systems programmers and improved responsiveness, reliability, and availability of the customer's DB2 subsystem.



Growth Enablement

By utilizing the functions and capabilities of DB2 PM, the customer's DB2 system programmer should be able to analyze and manage the performance of the DB2 subsystem more effectively. Enhancements are incorporated that reduce the skill level required to utilize existing functions and reduce the time and effort involved in analyzing customers' systems. As the cost of computer professionals increases, the benefit of these functions increases. The net result can be DB2 subsystems with enhanced reliability, availability, and resources available to implement and introduce new applications.

Exception processing, both online and batch, permits handling a growing application workload without increasing staff. Using exception processing and the Interactive Report Facility graphics facility, the customer's staff can detect performance trends and devise solutions for a larger DB2 workload.



Investment Protection

DB2 PM protects customer investment in DB2 by providing a performance monitor to identify potential and/or existing problems more easily and "tune" DB2 to provide better performance. This is accomplished by utilizing the DB2 PM Batch and Online functions.

Investment protection can also be acheived since DB2 PM for MVS Version 4 is upwardly compatible with DB2 PM for MVS Version 3 Release 2, DB2 PM for MVS Version 3 Release 1 and DB2 PM for MVS Version 2 Release 1.1, and fully supports DB2 for MVS Version 3 and DB2 for MVS Version 2 Release 3. Migration from either DB2 PM Release listed above to DB2 PM for MVS Version 4 is simplified by conversion utilities.

Customer investment in DB2 can be protected by utilizing SMP/E (TM) to install and maintain DB2 PM for MVS Version 4.



User Productivity

DB2 end-user productivity can be enhanced by the DB2 systems programmer using the functions of DB2 PM to monitor, analyze, and optimize the performance of DB2.

Exception reporting provides productivity gains for the DB2 system programmer/analyst. Instead of spending time manually reviewing large numbers of accounting and statistic pages, the DB2 programmer/analyst will review shorter reports containing only information that was requested based on user-defined thresholds.

Merged reporting can also provide productivity gains for the DB2 system programmer/analyst. Single merged reports can be generated, eliminating the need to merge and correlate data from different reports manually. Similar benefits are provided by group-scope reporting.

The Online Monitor provides a vehicle so the user can inspect a performance problem as it happens. The user can then identify and resolve the problem faster.



PRODUCT POSITIONING

DB2 PM for MVS Version 4 is a systems management product for customers of IBM's Relational Database Management System (RDBMS) solution, DB2 for MVS. Customers can use DB2 PM for MVS Version 4 to monitor performance of DB2 and tune the system accordingly. DB2 PM for MVS Version 4 provides comprehensive support of DB2 for MVS/ESA Version 4.



STATEMENT OF GENERAL DIRECTION



DataHub (TM) Enablement

IBM intends to enhance DB2 PM to provide DB2 PM functions from a DataHub platform. This means that users will be able to get access to DB2 performance data from the IBM strategic central point of control to manage relational databases by using a consistent object-based graphical user interface.

This statement presents IBM's currently intended plans. IBM will continue to monitor business conditions and requirements and may make changes to those plans as required.



EDUCATION SUPPORT

The appropriate curriculum will be updated, as necessary, to include the enhancements in this announcement.

Details of the education support will be provided by product availability through separate education announcements.

Call IBM Education and Training at 800-IBM-TEACH (426-8322) for education catalogs, schedules, and enrollments.



TECHNICAL INFORMATION



Specified Operating Environment

This product uses only existing attachment interfaces.

Hardware Requirements: DB2 PM for MVS Version 4 minimum hardware configuration:

  • Any ES/9000 (TM), ES/3090 (TM), ES/4381 (TM), 4300, or 308X processor that supports:
    • MVS/ESA System Product Version 5 Release 1
    • MVS/ESA System Product Version 4 Release 1
    • MVS/System Product Version 3 Release 1 (MVS/ESA)
    • MVS/System Product Version 2 Release 2 (MVS/XA (TM))
    • Subsequent releases
  • A nine-track tape drive or cartridge tape drive to install distributed material.

  • A display station that is supported by Interactive System Productivity Facility (ISPF):
    • Interactive System Productivity Facility (ISPF) Version 3 Release 5 (5685-054)

    • ISPF/Program Development Facility (ISPF/PDF) Version 3 Release 5 (5665-402)
  • Use of the graphics facility requires an IBM color graphics display station, or equivalent, that is supported by Graphical Data Display Manager (GDDM (R)):
    • Graphical Data Display Manager (GDDM) Version 2 Release 3 (5665-356)
    • GDDM Presentation Graphics Feature (GDDM/PGF) Version 2 Release 1.1 (5668-812)
Software Requirements: All DB2 PM for MVS Version 4 features require the following licensed programs or their equivalents:
  • For an MVS/ESA System Product Version 5 environment:
    • MVS/ESA SP-JES2 Version 5 Release 1 (5665-068) or

      MVS/ESA SP-JES3 Version 5 Release 1.1 (5665-069)

    • MVS/DFP (TM) Version 3 Release 3 (5665-XA3)
  • For an MVS/ESA System Product Version 4 environment:
    • MVS/ESA SP-JES2 Version 4 Release 1 (5695-047) or

      MVS/ESA SP-JES3 Version 4 Release 1 (5695-048)

    • MVS/DFP Version 3 Release 2 (5665-XA3)
  • For an MVS/System Product Version 3 (MVS/ESA) environment:
    • MVS/SP (TM)-JES2 Version 3 Release 1.3 (5685-001) or

      MVS/SP-JES3 Version 3 Release 1.3 (5685-002)

    • MVS/DFP Version 3 Release 2 (5665-XA3)
  • For an MVS/System Product Version 2 (MVS/XA) environment:
    • MVS/SP-JES2 Version 2 Release 2 (5740-XC6) or

      MVS/SP-JES3 Version 2 Release 2.1 (5665-291) and Version 2 Release 2.0 SPE

    • MVS Data Facility Product Version 2 Release 4 (5665-XA2)
Note:

Selected functions of the IBM DB2 PM Online Monitor utilize a data collector address space and require MVS/ESA Version 3 Release 1.3 as a minimum environment. These functions include:

  • Collection of history data

  • Operation of exception processing when a TSO user is not logged on

  • Monitoring of event exceptions, such as identifying data about deadlocks and timeouts
For all environments:
  • IBM DATABASE 2 for MVS/ESA Version 4 (5695-DB2)

  • IBM DATABASE 2 for MVS Version 3 (5685-DB2)

  • IBM DATABASE 2 for MVS Version 2 Release 3 (5665-DB2)
Note: An online DB2 for MVS system is required for the data collector, the Online Monitor, and the Batch explain report set.

The installation of IBM DB2 PM for MVS Version 4 requires the licensed program System Modification Program/Extended (SMP/E) Release 7 (5668-949) Service Level 20, or later.

DB2 PM Batch Reporting also requires the following licensed program or its equivalent:

  • Data Facility Sort (DFSORT (TM)) Release 12 (5740-SM1)
The DB2 PM Interactive Report Facility (IRF) and Online Monitor functions also require the following licensed programs or their equivalents:
  • MVS/TSO Extensions (TSO/E) Version 2 Release 1 (5685-025)

  • Interactive System Productivity Facility (ISPF) Version 3 Release 5 (5685-054) and ISPF/Program Development Facility (ISPF/PDF) Version 3 Release 5 (5665-402)
Optional Licensed Programs: The following licensed programs or their equivalents are required for you to utilize additional functions of IBM DB2 PM.
  • IRF graphics operation:
    • Graphical Data Display Manager (GDDM) Version 2 Release 3 (5665-356)

    • GDDM Presentation Graphics Feature (GDDM/PGF) Version 2 Release 1.1 (5668-812)
  • Operational support:
    • BookManager (R) READ/MVS Version 1 Release 2 (5695-046) or

      BookManager READ/VM Version 1 Release 2 (5684-062) or

      SAA (R) BookManager READ/2 Version 1 Release 2.1 (5601-454)

Compatibility: DB2 PM for MVS Version 4 is upwardly compatible with
  • DB2 PM for MVS Version 3 Release 2
  • DB2 PM for MVS Version 3 Release 1
  • DB2 PM for MVS Version 2 Release 1.1



Planning Information

DB2 PM for MVS Version 4 will be available before availability to customers pre-selected for the DB2 for MVS/ESA Version 4 (5695-DB2) QPP program and to customers who qualify for the DB2 for MVS/ESA Version 4 SAPR program.

QPP Customers: Customer selection for the QPP of DB2 for MVS/ESA Version 4 is complete. Customers participating in this program and wanting to use DB2 PM for MVS Version 4 to satisfy the requirement for a performance monitor should contact their IBM representative.

SAPR Customers: Customers planning to implement data sharing with DB2 for MVS/ESA Version 4 and wanting to use DB2 PM for MVS Version 4 to satisfy the requirement for a performance monitor, may be eligible to participate in a joint DB2 for MVS/ESA Version 4 and System/390 Parallel Sysplex offering SAPR. They should contact their IBM representative.

Installability: Installability is ensured by utilizing SMP/E.

Migration from DB2 PM for MVS Version 2 Release 1.1, from DB2 PM for MVS Version 3 Release 1 and from DB2 PM for MVS Version 3 Release 2, to DB2 PM for MVS Version 4 is supported. The migration areas for DB2 PM concern DPMOUT files, SAVE files, exception threshold data sets, saved IRF and graphics requests, correlation translations, and time-zone parameters. Where required, migration utilities are provided.

Packaging: DB2 PM will be shipped on 9/6250 magnetic tape and IBM 3480 cartridge.



Security, Auditability, and Control

The announced program DB2 PM uses the security and auditability features of the host MVS/XA or MVS/ESA system.



ORDERING INFORMATION

Ordering instructions and order processing availability will be communicated when IBM announces availability of this product.



TERMS AND CONDITIONS

Licensing: The program in this announcement is licensed under the terms of the IBM Customer Agreement (ICA).

Variable Charges Apply: Yes

System/390 Multiple Operating Systems -- PR/SM (TM) (MOSP): Charge option attachment applies for graduated charge programs licensed to a qualifying machine.

Installation License or Location License Applies: No. A separate license is required for each machine on which the licensed program materials will be used.

Installation License Applies: No

Location License Applies: Yes

Installation License Applies: Yes

Location License Applies: No

Educational Allowance: A 15% educational allowance is applicable toward eligible license charges and is available to qualifying institutions in accordance with the Educational Allowance Attachment.

The educational allowance may not be added to any other discount or allowance.

Version-to-Version Upgrade Credits Apply: Yes

                                                          Single
Replaced                   Replacement                    Version
Program                    Program                        Charging
Name/Number                Name/Number                    Applies

DB2 PM V1/ DB2 PM V2/ 5665-354 5685-140 Y

DB2 PM V2/ DB2 PM V3/ 5685-140 5655-047 Y

DB2 PM V3/ DB2 PM V4/ 5655-047 5655-102

DB2 PM V4/ To a follow-on, if any 5655-102 N/A

Replaced                   Replacement                 Effective
Program                    Program                     Withdrawal
Number                     Number                      Date

5665-354 5685-140 09/30/93 5685-140 5655-047 12/27/94

Warranted: Yes

Licensed Program Materials Availability: Restricted Materials: No. This licensed program will be available without source licensed program materials. It will be available in object code.

Testing Period:

  • Basic License -- Two months
  • DSLO -- Not applicable
Program Services: Central Service, including the IBM Support Center, will be available until discontinued by IBM upon six months' written notice.

Central Service, including the IBM Support Center, for DSLO licenses will be provided only through the customer location designated for the basic license.



CHARGES

Contact your IBM representative for charges information for this announcement.