Updates to the DB2 SQL Performance Analyzer for z/OS V4R1 User's Guide

Product documentation


Abstract

Updates that apply to the IBM® DB2® SQL Performance Analyzer for z/OS®, Version 4 Release 1, User's Guide (SC19-3240-02)

Content

The most recent update is listed first.




Update 7
Date of change: February 27, 2012
Topics: Migrating SQL PA from one DB2 version to another DB2 version
Change description: The topic now contains the following information:

Migrating SQL PA from one DB2 version to another DB2 version
After you have migrated your DB2® subsystem, you must configure SQL PA for it.

Migrating SQL PA to DB2 Version 9.1 for z/OS or to a higher mode

Procedure
    1. Use Tools Customizer to edit the DB2 parameters for the migrated subsystems.
    2. Regenerate the customization jobs.
    3. Submit only the jobs that are customized from the ANLDROPD template, the
    ANLCRE8I template, and the ANLBNDGR template as identified in the
    TEMPLATE column on the Finish Product Customization panel.

Important: Submitting the job to drop the DB2 objects will delete all EXPLAIN tables in the SQL PA database, and you will lose SQL PA history. If you need the existing data in the EXPLAIN tables, save the data before you submit the job, and restore the data after the objects are created.

Migrating SQL PA to DB2 10

About this task

In DB2 10, EXPLAIN tables that are in the format from before DB2 Version 8 are not supported. EXPLAIN tables that are in DB2 Version 8 or DB2 Version 9.1 format and EBCDIC-encoded EXPLAIN tables are deprecated in DB210. For more information about the restrictions for EXPLAIN tables in DB2 10 and about jobs for completing this task, see the DB2 10 for z/OS Installation and Migration Guide.

Procedure

1. Convert your EXPLAIN tables to UNICODE encoding. If you converted your EXPLAIN tables when you migrated your DB2 subsystems to DB2 10, skip this step.
    a. Edit and submit the high-level-qualifier.SDSNSAMP(DSNTIJXA) job. This job changes the format of the EXPLAIN tables to the format of the current DB2 version.
    b. Edit and submit the high-level-qualifier.SDSNSAMP(DSNTIJXB) job. This job generates SQL to create the new objects in UNICODE, rename the existing EXPLAIN tables, and insert the data from these tables into the new tables.
    c. Run the generated SQL from SPUFI or edit the high-levelqualifier.SDSNSAMP(DSNTIXJC) job. This job uses the DB2 cross-loader control statements that are generated in the DSNTIJXB job.
2. Use Tools Customizer to configure SQL PA.
    a. Edit the DB2 parameters for the migrated subsystems.
    b. Regenerate the customization jobs.
    c. Submit only the jobs that are customized from the ANLDROPD template, the ANLCRE8I template, and the ANLBNDGR template as identified in the TEMPLATE column on the Finish Product Customization panel.

Important: Submitting the job to drop the DB2 objects will delete all EXPLAIN tables in the SQL PA database, and you will lose SQL PA history. If you need the existing data in the EXPLAIN tables, save the data before you submit the job, and restore the data after the objects are created.




Update 6
Date of change: August 9, 2011
Topics: ANLPARM user parameters and SQL PA Tools Customizer parameters
Change description: The BYPASSRC option has been added to the TURNOFF parameter.

TURNOFF nnnn BYPASSRC
The TURNOFF user parameter excludes specified messages from the SQL PA output. You must enter each TURNOFF parameter on a separate line. You can specify up to 100 TURNOFF parameters. Specify the 4-digit numeric message number to turn off the message. For example, TURNOFF 6011 would exclude message ANL6011I from the output. To ensure that the return code is not set for the same message number that you specify in the TURNOFF parameter, use the following option:

BYPASSRC
This keyword is optional. When you specify the BYPASSRC keyword, the return code is not set for the same specified message number. For example, TURNOFF 6011 BYPASSRC would exclude message ANL6011I from the output, and the return code would not be set.

The BYPASSRC keyword is used for batch processing only.

The TURNOFF parameter and the BYPASSRC keyword are available only for ANLxnnn messages, where x is 5, 6, or 7, and nnn is the last three digits of the message number. However, the TURNOFF parameter is not available for the ANL5025W, ANL5026W, ANL5027W, ANL5028W, and ANL5029W messages. To avoid these message, increase the appropriate limits.

The default is to show all messages.




Update 5
Date of change: August 9, 2011
Topics: Program messages
Change description: The following message IDs have been changed. The content of the messages did not change.
  • ANL7039I is changed to ANL3041I.
  • ANL7040I is changed to ANL3042I.




Update 4
Date of change: August 9, 2011
Topics: Program messages
Change description: The following messages have been added.

ANL5046W *** Alert: Using the DECFLOAT CAST function results in a stage 2 predicate. Predicate transitive closure and indexing are not allowed.

User response: As appropriate.


ANL5047W *** Alert: Global optimization of this subquery has transformed the SQL statement and materialized a virtual table. Run time might be extended.

User response: As appropriate.


ANL5048W *** Alert: XMLEXISTS is a stage 2 predicate unless an XML values index or a NODEID index is employed. Performance decreases when XML is used. The XMLEXISTS predicate is not a candidate for index screening.

User response: As appropriate.


ANL5049W *** Alert: PRECISE NO will be deprecated and changed to PRECISE YES in DB2 V9 and later releases. When you use the PRECISE parameter, specify PRECISE YES or PRECISE ALL.

User response: As appropriate.


ANL5050W *** Alert: DX access is not a candidate for any type of parallelism.

User response: As appropriate.


ANL5051W *** Alert: XML is not allowed with SELECT DISTINCT, GROUP BY, or ORDER BY clauses, subselects that are not UNION ALL, quantified BETWEEN, DISTINCT, IN, or LIKE predicates, or aggregate DISTINCT functions.

User response: As appropriate.




Update 3
Date of change: August 9, 2011
Topics: Program messages
Change description: The ANL6048I, ANL6049I and ANL6050I messages have been removed.




Update 2
Date of change: July 20, 2011
Topics: Customizing SQL PA
Change description: The following section has been added to explain how to maintain the customization jobs in the customization library:

Maintaining customization jobs
Instead of deleting customization jobs outside of Tools Customizer, you can maintain the correct jobs for a product by completing the steps for recustomization.

About this task
You cannot delete or rename customization jobs from the customization library by starting an ISPF browse or edit session from the Finish Product Customization panel. If you try to delete customization jobs by using this method, the CCQC034S message is issued. If you try to rename customization jobs, the CCQC035S message is issued.

If you delete or rename customization jobs from the customization library by using ISPF outside of Tools Customizer, Tools Customizer will not recognize that the jobs were deleted, and the Finish Product Customization panel will still display them. If you browse or edit jobs that were deleted from the library outside of Tools Customizer, the CCQ027S message is issued.

Procedure
To maintain the correct customization jobs in the customization library, complete the steps in the recustomization scenario.




Update 1
Date of change: July 20, 2011
Topics: Tools Customizer messages
Change description: The following message has been added:

CCQC033S
The customization-library-name has no customized jobs.

Explanation: The specified customization library cannot be browsed or edited because it is empty.
System action: None.
User response: Generate customization jobs for the specified library, and browse or edit the library again.



Publication number

SC19-3240-00

Copyright date

2002, 2011

Original publication date

2010/12/14

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 Tools for z/OS
DB2 SQL Performance Analyzer

Software version:

4.1.0

Operating system(s):

z/OS

Reference #:

7020590

Modified date:

2012-02-27

Translate my page

Machine Translation

Content navigation