Gather information about embedded SQL statements with the PRTSQLINF command

The PRTSQLINF command gathers information about the embedded SQL statements in a program, SQL package (the object normally used to store the access plan for a remote query), or service program. It then puts the information in a spooled file. PRTSQLINF provides information about:

• The SQL statements being executed
• The type of access plan used during execution. This includes information about how the query will be implemented, the indexes used, the join order, whether a sort is done, whether a database scan is sued, and whether an index is created.
• A list of the command parameters used to precompile the source member for the object.

To gather this information, run PRTSQLINF against a saved access plan. This means you must execute or at least prepare the query (using SQL's PREPARE statement) before you use the command. It is best to execute the query because the index created as a result of PREPARE is relatively sparse and may well change after the first run. PRTSQLINF's requirement of a saved access plan means the command cannot be used with OPNQRYF.

PRTSQLINF gives output that is similar to the information you can get from debug messages, but PRTSQLINF must be run against a saved access plan. The query optimizer automatically logs information messages about the current query processing when your job is in debug mode. So, query debug messages work at runtime while PRTSQLINF works retroactively.