Abstract

The following updates and enhancements are made available in the latest release of IBM Optimization Service Center for z/OS

Content

The following updates and enhancements have been applied to IBM Optimization Service Center for DB2 for DB2 for z/OS:

Version 1.1 - Fix Pack 7 (10/30/2009)

• The dependency on DB2 Connect is removed.
• The on-line help and information center topics are updated and reorganized.
• The number of checks for SYSADM authority by the workload control center are reduced, reducing the impact on auditing procedures.
• An option is added to specify whether to issue EXPLAIN STMTCACHE ALL statements when viewing queries or capturing workloads from the dynamic statement cache.
• The amount of historical EXPLAIN data is reduced, saving disk space in the project folder on your workstation and in the EXPLAIN tables on the data server.
• The performance of service SQL, viewing queries, and the workload control center is improved.
• An option is added to reset the layout of the user interface to the default configuration.
• An option is added to specify SMF or GTF as the destination for trace data.
• The service SQL and service workload functions are changed to always include DDL statements and statistics.
• An indication of the number of matching qualified rows is added to index scan nodes in access plan graphs.
• Explanations are added to the column headings of the view when viewing queries.
• The ability is added to migrate EXLAIN tables from non-UNICODE to UNICODE format.
• Defects related to the following situations are fixed:
  • SQLCODE -104 for static queries when the value DECIMAL POINT subsystem parameter is set to COMMA.
  • A problem in query annotation for outer joins.
  • Statistics advisor repeatedly issuing previously applied recommendations.
  • Log file conflicts for multiple DB2 for z/OS subsystems that share the same z/OS UNIX System Services environment.
• A problem with the windows registry that prevented the installation of Optimization Service Center and Optim Query Tuner Version 2.2 on the same workstation:
• Access plan graphs for SQL statements that have the following characteristics:
  • Contain columns with field procedures.
  • Contain key words for text search.
  • Involve any virtual indexes.
• Handling non-English and special characters in service SQL, query annotation, and the statistics advisor. The time stamp format when identifying queries in EXPLAIN tables.
• Service SQL, including correct DDLs for partitioned table spaces, partitioned indexes, default tables spaces in DB2 9, and others. Requirements from the following PMRs have been fixed:
  • 65388,661,706
  • 83963,379,000
  • 83749,211,788
  • 71037,180,000
  • 69408,032,724
  • 68179,032,724
  • 54162,370,000
  • 48509,379,000
  • 47121.708.708
  • 37758,379,000
  • 21905,075,724
  • 10766,077,724
  • 05937,300,624
  • 05218,113,848
  • 05208,114,848
  • 83310,122,100

Language Support:

Languages other than English are no longer supported in the user-interface, on-line help, and information center topics.

Version 1.1 - Fix Pack 6 (12/22/2008)

• Fixed a bug that caused EXPLAIN to fail queries that contain host variables in a function
• Improved the performance of viewing queries
• Removed duplicate RUNSTATS statements in the statistics advisor recommendations
• Improved the performance of listing workload statements
• Fixed incorrect execution counts in monitor workload
• Fixed a bug that prevented the capture of statements from packages that are bound with the CURRENT DEGREE = 'ANY' option into a workload
• Fixed several bugs in service SQL, including an error when a table name contains special characters and failures to send files to the FTP
• site, and improved the performance of service SQL
• Fixed bugs for tuning SQL statements from data sharing group members
• Changed the defaulli>t EXPLAIN stored procedure to SYSPROC.DSNAEXP
• Fixed the bug in visual plan hint when the user does not have authority to check subsystem parameters
• Hid the Workload Control Center stored procedure in the Bind Package panel when it is not used
• Added links to information resources in the Welcome page
• Added the ability to import and export the list of the subsystems
• Added the ability to show the text of the dynamic SQL statements that failed to execute
• Added the ability to specify a default subsystem
• Added elapsed time to the workload history page
• Added the ability to reset all the preferences to the default values
• Added the ability to show all ignored warning messages

Version 1.1 - Fix Pack 5 (5/30/2008)

• Bundled JCC drivers with the product, revising the DB2 Connect requirement to: "DB2 Connect- Application Server, Enterprise, Personal, or Unlimited Edition, Version 8 or later."
• Fixed OMEGAMON PE integration bugs
• Added character set support for capturing query from file
• Enhanced the display of EXPLAIN-enabled authorization IDs with status indicators Changed the authorization to use Monitor: not only SYSADM but also
• SYSOPR or SYSCTRL can start or stop monitor
• Fixed a bug in the factory mechanism to manage objects in query model

Version 1.1 - Fix Pack 4 (3/24/2008)

• Data-sharing, DB2 for z/OS Version 8 new function mode, and DB2 for z/OS Version 8 compatibility mode are supported.
• SQL capture enhancements:
  • Support for retrieving statements from SQL procedures.
  • Additional filter conditions. Users can retrieve certain statements with the help of dependency tables which record the dependencies between plans or packages and tables, views, indexes, etc.
  • In support of data-sharing, the member name of the specific subsystem is displayed for statements that are retrieved from dynamic statement cache.
  • Additional view options. New predefined view types, including AVG_CPU_DESC and AVG_ELAP_DESC, which indicate average runtime statistics, are available in View name list when statements are retrieved from the statement cache.
  • New statements search option. Users can search certain statements by defining key words from the queries list when retrieving statements from the statement cache. New option of statement search is added on GUI.
  • Users can use a stored procedure to extract statements from dynamic statement cache, which allows users without SYSADM authority to view all of the statements from cache if they are authorized to execute the stored procedure.
• DB2 EXPLAIN enhancements:Support of EXPLAIN for XML tables and TRUNCATE statements.
• Workload control center enhancements:
  • Workload Control Center DDL statements are changed to resolve performance issues.
  • In support of data-sharing, users can capture workloads from the cache, and create monitor profiles, across an entire data-sharing group.
  • Users can export a workload as an XML file from one DB2 subsystem and import the XML file to generate a workload on another DB2 subsystem.
  • Users can customize filters to search statements in a workload according to the following criteria and generate a new workload from the statements that qualify
  • Object based searches, which find all queries that access specified objects, including tables, indexes, databases, and table spaces.
  • Access path based searches, which find queries that use certain access methods including:
    • Types of prefetch
    • Relational-scan, non-matching index scan, multi-index access, and single index access
    • Types of join methods
    • Types of sorts
    • Types of parallelism (IO, CPU, SYSPLEX)
  • Cost based searches, which find the top N queries, where N is a number specified by the user, where any of the following cost estimates are greater than a specified threshold:
    • Accumulated CPU
    • Accumulated I/O
    • Average CPU
    • Average elapsed time
• Table manger enhancements:
  • Support for migrating EXPLAIN tables from DB2 Version 7 to Version 8.
  • Support for migrating Workload Control Center tables from OE/OSC Release 1 to Release 2.
  • Support for migrating workload statistics advisor, workload query advisor, and workload index advisor tables from OE/OSC Release 1 to Release 2.
• Query annotation enhancements:
  • Support for XML table function and XMLCAST is added.
  • Predicates that contain columns with skewed data are highlighted.
  • Predicates that compare columns with default literal values are highlighted.
  • Support for data-sharing environments and DB2 Version 8 compatibility mode.
  • Support to customize the number of digits displayed after the decimal point.
  • Improved format for print function. Query annotations are printed in different rows for easier review.
  • Support to “Expand” and “Collapse” select lists.
• Query report enhancements: Legends are added in the query report, including MAX_FREQ, which indicates the maximum frequency for single column is added in predicate report, NLEAF, which indicates the number of active leaf pages in the index is added in table report, and others.
• Service SQL and service workload enhancements:
  • Service SQL supports the following functions:
  • Column "IDENTITY" clause in the DDL statements.
  • Conversion of partition-by-growth table space to segmented table space, if the user chooses to convert the DDL statements from V9 NFM to V9 CM/V8.
  • Rotated physical partitions in DDL statements with flexible choices.
  • Generating table information (DDL statements, statistics, etc.) related to the pruned query.
  • Generating MQT statistics information in the output file.
  • A new component, service workload, is introduced in OSC/OE to collect DDL statements, statistics, and EXPLAIN information for query workloads. This component is a major enhancement, which is more powerful than single-query-based Service SQL.
• Access plan graph enhancements:
  • Support for XML table function. A new label on nodes displays the XML table attributes of relevant nodes.
  • For table spaces with rotated physical partitions, table partitions are ordered by their logical part in ascending order, instead of by their physical part in the node description.
  • Customization of view contents and the displayed width of attributes in attribute explanations are automatically saved as defaults. CPU cost, I/O cost, and total cost information are available on relevant nodes.
• Statistics advisor and workload statistics advisor enhancements:
  • When a data type changes, the frequency and histogram statistics for related columns become invalid. The statistics advisor and workload statistics advisor both give warnings and recommend that users recollect frequency and histogram statistics.
  • New options are added in RUNSTATS recommendations, including:
    • “SORTDEVT” specifies the device type that DESORT uses to dynamically allocate the required sort work data sets.
    • “SORTNUM” specifies the number of required sort work data sets allocated by DFSORT.
    • “REPORT” specifies whether RUNSTATS is to generate a report about collected statistics.
  • A report similar to the query report can be generated for the statistics information for queries in a workload, including tables, indexes, columns and column groups. Enhanced workload statistics advisor detail report:
    • Users can search and set filters on the workload statistics explanations.
    • Conflicts in the conflict report are grouped by related tables.
    • A new conflict rule is added. When the maximum frequency of a column or a column group is less than the reciprocal of its cardinality, it is considered as a conflict and recommended to recollect statistics.
    • The check of each conflict rule can be enabled and disabled individually.
    • All CGTT (created global temporary tables) referenced in the query or the workload are listed in the explanation to indicate that their statistics can not be collected.
  • 4 parameters are added to the workload statistics advisor. These parameters are used to decide the percentage of columns or column groups whose different types of statistics information are to be collected. When certain attributes of the relative columns or column groups meet the specific criteria defined by the threshold, the workload statistics advisor collects the following information:
    • Single column frequency statistics
    • Single column histogram statistics
    • Column group frequency statistics
    • Column group histogram statistics
  • Optional workload statistics validation. Users can check the updated statistics report of the workload after the RUNSTATS recommendations are executed for any remaining problematic statistics, such as missing and conflicting statistics.
• User Interface enhancements:
  • All three status check results (packages bound, EXPLAIN enabled, OSC/OE tables) will be cached on local machine when connecting to a subsystem. A new option to re-cache from the server is added in case of client/server inconsistencies. Also, a new option is added for users to decide whether the Manager Users page, which is the last step of the Subsystem Configuration wizard, appears when connecting to a subsystem.
  • Instead of listing all EXPLAIN-enabled authorization IDs, only those that have already logged on are listed. The others are packaged and shown in “more”. “Show All” button is added beside the “EXPLAIN-enabled” authorization ID list, allowing users to retrieve all EXPLAIN-enabled IDs manually with a warning message to ask whether to continue since the operation is time-consuming.
  • A message reminds users to collect EXLAIN information before running workload index advisor.
  • Users can run tools and/or advisors in a saved project, without connecting to the subsystem.
  • Users are able to search query statements in “View query” page. Matched results are highlighted and selected.
  • In the workload query advisor table, the “Query Tools” menu is added to both the tool bar and the right-click menu. The content is displayed in multiple pages with 50 results per page.
  • New support for VML in Internet Explorer and SVG in Firefox, are added to the visual plan hint feature.
  • View contents can be saved in TXT, HTML, and CSV formats.
  • For tutorial subsystem, all query sources are included in the query list with only “catalog plan or package” and “monitor” supported. There will be warning messages when an unsupported source is selected. A sample monitor profile is included in the tutorial subsystem.

Version 1.1 - Fix Pack 3 (12/31/2007)

In the visual plan hint feature, added Vector Markup Language (VML) support. Implemented a code update for profile table DDL changing. Please refer to PK49832 for the detailed information.

Version 1.1 - Fix Pack 2 (09/06/2007)

• In the explain function, added support for the TRUNCATE statement and UNICODE explain tables.
• Changed the table name DSN_WCC_OBJECT_RUNTIME to DSN_WCC_OBJ_RUNTM
• In workload control center, fix the problem that monitor profiles cannot be stopped with administrative scheduler.
• In workload control center, fix the problem that multiple captures from the statement cache with administrative scheduler cannot be stopped with administrative scheduler.
• In statistics advisor and workload statistics advisor, avoid recommending collection of FREQUENCY or HISTOGRAM statistics on the columns or column groups whose length exceed the length of COLVALUE/HIGHVALUE/LOWVALUE field in SYSCOLDIST.
• In Visual Plan Hint, allow user to specify PLAN_TABLE qualifier to deploy hint.
• Improved the log/trace performance.
• Reduced the size of installer package.

Version 1.1 - Fix Pack 1 (04/30/2007)

• Enabled support for Windows Vista
• Upgraded to JRE1.5 and Eclipse 3.2.2
• Added support for virtual tables in query annotation
• In workload control center, provided the flexibility to schedule task in both client and server from within the workstation client
• In access plan graph, added support for the TRUNCATE statement
• In access plan graph, added sequential prefetch tool tips on WFSCAN node Reduced memory usage.

Related information

Optimization Service Center Web page
Product documentation