Start of change

Collecting service SQL documentation

Service SQL documentation is the standard format for query problem analysis documentation, for use by IBM® Support. It includes the data definition statements (DDL), catalog statistics, EXPLAIN records, and other data that describes a Db2 for z/OS® environment.

Before you begin

  • Ensure that the latest Db2 maintenance is applied.
  • You must have appropriate Db2 administrative authority.
  • If you want to report an access path performance problem and you have not already done so, complete the following tasks first:
    1. Investigate the problem, as described in Investigating access path problems.
    2. If you are unable to resolve the problem, collect diagnostic data, as described in Collecting data for access path performance problems.

About this task

The service SQL documentation includes information that describes the following aspects of the environment for Db2 for z/OS SQL statements:

  • Objects such as tables, indexes and views that are referenced by the SQL statements
  • Catalog statistics the Db2 to select access paths for the SQL statements
  • EXPLAIN table information that describes access paths for the SQL statements
  • Db2 module details
  • Subsystem parameter settings

IBM Support can use this information to re-create and analyze the performance problems.

Procedure

To generate problem analysis documentation for a Db2 environments:

  1. If you have not already done so, run EXPLAIN statements to populate the EXPLAIN table records for the SQL statement or workload.
  2. Generate the data definition statements, EXPLAIN table records, and catalog statistics that describe the Db2 environment for the SQL statement or workload.
    You can use the following approaches to call the ADMIN_INFO_SQL stored procedure:
    • Run the DSNTEJ6I sample job that is supplied with Db2 in the prefix.SDSNSAMP data set. DSNTEJ6I calls DSNADMSB which executes SYSPROC.ADMIN_INFO_SQL. The job prologue contains detailed instructions for customizing the job. For more information, see DSNTEJ6I.
    • Use the capture query environment feature of tools such as IBM Db2 Administration Foundation for z/OS and IBM Db2 for z/OS Developer Extension
      Tip: Always select the Parallelism option, even if your system does not use parallelism. This option ensures that important statistics are included in the Service SQL data.

    The output from ADMIN_INFO_SQL is intended primarily for the use of IBM Support. The format and content of the output might change at any time.

What to do next

For information about submitting the service SQL data to IBM Support, see Contacting IBM Support about Db2 problems.

End of change