For each location that you capture from, you must meet these prerequisites. Some locations have additional prerequisites.
For DB2 for Linux, UNIX, and Windows, Version 9.7.4 or later: When you run an SQL stored procedure in a routine editor, in the Run window you can collect actual runtime information about the access plans for the statements in the stored procedure. Select the option Gather performance information from the database, and then select the option Gather actual access plan information from the database. In the Profiling Data view, select the statements that you want to tune by holding the CTRL key and clicking those statements. Right-click the selection and click Start Tuning.
To capture SQL statements from the following sources, you must first follow these steps to open the workflow assistant:
f the package in which a statement is located was bound with EXPLAIN set to YES, then it is captured together with information about its actual access plan.
All occurrences of captured statements in the specified packages are saved when you create a query workload from this type of source. If you select the option Save EXPLAIN information for the captured statements, the EXPLAIN information for each occurrence of a statement is also saved.
f the package in which a statement is located was bound with EXPLAIN set to YES, then it is captured together with information about its actual access plan.
All occurrences of captured statements in the specified packages are saved when you create a query workload from this type of source. If you select the option Save EXPLAIN information for the captured statements, the EXPLAIN information for each occurrence of a statement is also saved.
Immediately after capturing, you can view the actual access plans for a statement by right-clicking it and selecting Show Access Plan Graph.
For static statements: If captured statements occur more than once in the specified packages, then only the EXPLAIN information for the latest occurrence of the statement is saved when you create a query workload from this type of source. Statements are considered duplicates when the package name, package schema, section number, statement number, statement text are identical. Even if you do not select this option, only the latest occurrence of each statement is saved into the query workload.
Capturing from this type of source requires first creating and running an ACTIVITIES event monitor to collect runtime activity on a database. Immediately after capturing, you can view the actual access plans for a statement by right-clicking it and selecting Show Access Plan Graph.
If captured statements occur more than once in the specified packages, then only the EXPLAIN information for the latest occurrence of the statement is saved when you create a query workload from this type of source. Statements are considered duplicates when the executable IDs are identical. Even if you do not select this option, only the latest occurrence of each statement is saved into the query workload.