Chapter 8. Visual Explain 233
To use Visual Explain on any of the statements, select the statement from the display. The full
SQL statement appears in the lower part of the display for verification. Click Run Visual
Explain (Figure 8-22) to analyze the statement and prepare a graphical representation of the
query.
Exit the Visual Explain window and the Explainable Statements window when you have
completed your analysis. You may either retain the performance data or remove it from the
system at this time, depending on your requirements.
8.7 Non-SQL interface considerations
Obviously, the Database Performance Monitor can capture implementation information for
any SQL-based interface. Therefore, any SQL-based request can be analyzed with Visual
Explain. SQL-based interfaces range from embedded SQL to Query Manager reports to
ODBC and JDBC.
Some query interfaces on the iSeries servers are not SQL-based and, therefore, are not
supported by Visual Explain. The interfaces not supported by Visual Explain include:
򐂰 Native database access from a high level language, such as Cobol, RPG, etc.
򐂰 Query
򐂰 OPNQRYF command
򐂰 OS/400 Create Query API (QQQQRY)
The query optimizer creates an access plan for all queries that run on the iSeries server. Most
queries use the SQL interface. They generate an SQL statement, either directly (SQL Script
window, STRSQL command, SQL in high-level language (HLL) programs) or indirectly (Query
Monitor/400).
Other queries do not generate identifiable SQL statements (Query, OPNQRYF command)
and cannot be used with Visual Explain via the SQL Performance Monitor. In this instance,
the name SQL, as part of the SQL Performance Monitor, is significant.
The statements that generate SQL and that can be used with the Visual Explain via the SQL
Performance Monitor include:
򐂰 SQL statements from the SQL Script Center
򐂰 SQL statements from the Start SQL (STRSQL) command
򐂰 SQL statements processed by the Run SQL Statement (RUNSQLSTM) command
򐂰 SQL statements embedded into a high level language program (Cobol, Java, or RPG)
򐂰 SQL statements processed through an ODBC or JDBC interface
Note: Query optimizer information is generated only for an SQL statement or query
request when an open data path ( ODP) is created. When an SQL or query request is
implemented with a reusable ODP, then the query optimizer is not invoked. Therefore,
there is no feedback from the query optimizer in terms of monitor data or debug messages.
Also, the statement is not explainable in Visual Explain. The only technique for analyzing
the implementation of a statement in reusable ODP mode is to look for an earlier execution
of that statement when an ODP was created for that statement.

Get DB2 Universal Database for iSeries Administration: The Graphical Way on V5R3 now with the O’Reilly learning platform.

O’Reilly members experience books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.