Print SQL Information

The Print SQL Information (PRTSQLINF) command returns information about the embedded SQL statements in a program, SQL package (used to store the access plan for a remote query), or service program. This information is then stored in a spooled file.

PRTSQLINF provides information about:

  • The SQL statements being executed
  • The type of access plan used during execution. How the query is implemented, indexes used, join order, whether a sort is used, whether a database scan is used, and whether an index is created.
  • A list of the command parameters used to precompile the source member for the object.
  • The CREATE PROCEDURE and CREATE FUNCTION statement text used to create external procedures or User Defined Functions.

This output is like the information that you can get from debug messages. However, while query debug messages work at runtime, PRTSQLINF works retroactively. You can also see this information in the second-level text of the query governor inquiry message CPA4259.

You can issue PRTSQLINF in a couple of ways. First, you can run the PRTSQLINF command against a saved access plan. You must execute or at least prepare the query (using the SQL 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. It could well change after the first run. PRTSQLINF's requirement of a saved access plan means that the command cannot be used with OPNQRYF.

You can also run PRTSQLINF against functions, stored procedures, triggers, SQL packages, and programs from System i® Navigator. This function is called Explain SQL. To view PRTSQLINF information, right-click an object and select Explain SQL.