Gathering information about the environments in which SQL statements run on DB2 for z/OS

When you are working with IBM® Support to resolve a problem with tuning an SQL statement, you can capture the statement's environment in a set of files. You can then upload the files to IBM Support.

Before you begin

Contact IBM Support for help troubleshooting the tuning process for the SQL statement.
If IBM Support opens a PMR for your request, obtain the PMR number.

About this task

The tools for collecting information about the environment generate a set of files that you upload to an FTP server that IBM Support uses.

Procedure

To generate and send this information, open one of the following locations:

In the Capture section of the Query Tuner Workflow Assistant, open any of the following pages and click the Capture Query Environment button.
- Under Non-DB2 Sources
  - Input Text
  - File
  - SQL or Routine Editor
  - SQL Category
  - XML File
- Under DB2 for z/OS Sources, open any page.
You can select a captured statement and click the icon to import the statement into the Query Environment window, You can also paste a statement into the window directly, or import a statement from a file.
1. If the statement is particularly long and complex, include the creators and names of the tables that the statement references in the Table information section. Adding this information can aid IBM Support in understanding your problem better.
2. Click Next.
3. If you plan to recreate the query environment on a DB2® for z/OS® subsystem that is at a version different from the version of the currently connected subsystem, select the version of the target subsystem in the Convert for different version of DB2 for z/OS field.
4. If you are working with IBM Support and have a PMR number, type the number in the PMR or ETR Number field. If you are re-creating the query environment on another subsystem and are not working with IBM Support, you can just type zeros in the three entry fields for the PMR number.
5. Click the Generate Report button to create the files that can be used to recreate the query environment. The files are listed in the Files to upload field after they are generated.
6. Ensure that the FTP settings are correct for the target location of the files. Then, click the Upload Files button.
7. After you receive notification that the file transfer is complete, click Finish.
In the Data Source Explorer, expand the connection to the DB2 for z/OS subsystem that the SQL statement runs on. Then, right-click the subsystem and select Analyze and Tune > Capture Environment > Capture Query Environment. Follow steps a through g above.
On the left side of the Review section, after you run advisors and analysis tools on an SQL statement, click Capture Query Environment. Clicking on the option opens the Collect Diagnostic Data about the Query page of the Review section. This page works almost identically to the Query Environment window. The first difference is that the SQL statement is not visible. The second difference is the layout. There are two subsections: Options and FTP Server Settings. In the first section, you can add information about tables referenced in the statement, set options for generating the files for re-creating the environment, enter a PMR number, generate the report, and upload the files. In the second section, you can specify the connection information for the target FTP server. This second section also lists the files that you generate.

Feedback