Visual Explain for DB2 for z/OS

Download the latest version of Visual Explain for DB2 for z/OS (including all fixes and enhancements that are listed on this page).

DOWNLOAD: Visual Explain Version 8

The DB2 Visual Explain for z/OS Control Center plugin allows Visual Explain to be launched from DB2 Control Center for DB2 for z/OS, Version 8 subsystems.

After you have installed the control center plugin, you can launch Visual Explain from DB2 Control Center. In DB2 Control Center, select a database and click Selected -> DB2 Visual Explain V8.

1. Download the Visual Explain package to your workstation
2. Double-click the VEsetup.exe file and follow the onscreen instructions to install Visual Explain.
3. Stored procedures used by VISUAL EXPLAIN
   Visual Explain uses some sample stored procedures. This information lists the stored procedures and how they are used:

   DSNACCMD

   ServiceSQL uses this stored procedure to generate complete ALT BUFFERPOOL statements when collecting service information to send to IBM service during problem investigations. If this stored procedure is not installed an incomplete ALT BUFFERPOOL statement is generated and the systems programmer is guided to perform specific display bufferpool commands manually to provide IBM with bufferpool assignments. (Click here for more information)

   DSNWZP

   DSNWZP stored procedure is required to support Browse Subsystems Parameters function. The stored procedure is also used by Service SQL for collection and transmission of subsystem parameter settings to IBM service.

   DSN8EXP (See APAR PQ90022)

   This stored procedure supports the explain with stored procedure option. The purpose of explain with stored procedure is to allow users that should be able to explain SQL against objects that users do not have authority to execute queries against. For example, a developer may have responsibilities for SQL performance on a payroll application, but may not have SELECT, INSERT, UPDATE , DELETE access to the object. The developer can use the stored procedure to execute the explain against the object. The developer does not require access to the objects, but does require authority to execute the stored procedure.

   DSNUTILS

   Statistics Advisor generates RUNSTATS statements. These RUNSTATS statements can be executed dynamically if the customer has a license to execute RUNSTATS, the DSNUTILS stored procedure is installed, and the user has appropriate authorities to execute the RUNSTATS commands. Of course, the RUNSTATS input can also be copied into a batch job and executed outside of statistics advisor.

   Java Universal Driver (See APAR PQ62695)

   Installing the Java Universal Driver on DB2 for z/OS provides Visual Explain with stored procedures used by Visual Explain. Additionally, the SYSIBM.SQLCAMESSAGE stored procedure is installed and provides error message description for SQL errors in Visual Explain.

4. Click Start > Programs > Visual Explain for DB2 zOS > Visual Explain to start Visual Explain.

DB2 Subsystem Prerequisites

• Windows NT Version 4.0, Windows 2000, or Windows XP
• 150 MB of hard disk space
• 256 MB of RAM
• Pentinum III equivalent or above
• Either DB2 Connect™ Personal Edition Version 7 (or later), or DB2 Client Application Enabler™ (CAE) with a DB2 Connect Enterprise Edition Version 7 (or later) gateway
• A TCP/IP connection to a DB2 for z/OS Version 7 or Version 8 host system
• A java-enabled browser to view HTML files

• Statistics Advisor
• List cached statements and explain from statement cache
• Detailed explain information about (incomplete list):
  • Qualified rows estimates
  • Detailed index costing information
  • Parallelism details
  • Sort details

• Windows NT Version 4.0, Windows 2000, or Windows XP
• 150 MB of hard disk space
• 256 MB of RAM
• Pentinum III equivalent or above
• DB2 UDB Control Center V8,Visual Explain for DB2 for z/OS V8
• Either DB2 Connect Personal Edition Version 7 (or later), or DB2 Client Application Enabler (CAE) with a DB2 Connect Enterprise Edition Version 7 (or later) gateway
• A TCP/IP connection to a DB2 for z/OS Version 7 or Version 8 host system
• A java-enabled browser to view HTML files

• Access Plan Graph: can show hint to show the syscoldist as INVALID if invalid stats info is inserted.
• Access Plan Graph: enabled printing Access Graph on multi-page query.
• Access Plan Graph: resolved the problem of the overflowing numeric card values of table node and QB node in APG.
• Access Plan Graph: support PM/PE to successfully load VE message information on APG label screen.
• ServiceSQL: the MQT's statistics is supported in the output files.
• ServiceSQL: the FIELDPROC clause is supported in the table DDL.
• ServiceSQL: fix the invalid limit-key format for Date type in the partition index DDL.
• ServiceSQL: fix the invalid quote in the partition limit-key DDL.
• Statistics Advisor: The information about CGTT(created global temporary tables) in the query is listed in the explanation indicating their statistics cannot be collected.
• Statistics Advisor: The invalid frequency statistics due to unmatched value length is recommended to be recollected.
• Tune SQL: explain tables created by OSC are supported in VE.
• Allow users change system catalog qualifier.
• Remove misleading exception information from VE trace.
• Use reflect to avoid error when enabling jcc trace.

• Statistics Advisor: support new configuration parameter for SORTNUM.
• Tune SQL:keep the query's format when saving into history
• Tune SQL:add an option to enable/disable JCC trace
• Tune SQL:show APG even when EXPLAIN with stored procedure throws warning
• Tune SQL: show SQL warning in the message field after the execution.
• ServiceSQL: the VOLATILE attribute in the table ddl is supported.
• ServiceSQL: the IDENTITY column attribute in the table ddl is supported.
• ServiceSQL: the rotated-partition ddl is supported.
• ServiceSQL:remove the "WITH RESTRICT ON DROP" clause in table ddl for the convenience of L2.
• Access Plan Graph: display the logical partition number on table space partitions
• Access Plan Graph: change DDLs for DSN_STATEMNT_TABLE and DSN_FUNCTION_TABLE
• Access Plan Graph: add the enhancement to print the SQL text with the graph
• Statistics Advisor: avoid to recommend collecting stats on global temprary tables.
• Statistics Advisor: fix the defect of trace.
• Access Plan Graph: fix the defect for SET CURRENT SQLID and SCHEMA after clicking the 'Explain...' menu from List Static SQL
• ServiceSQL:fix the defect in view's ddl caused by encoding CCSID.
• List static SQL:fix the problem of listing static SQL in unicode
• Tune SQL:fix definite loop when explaining a query with into clause and host variables

• Statistics Advisor: add 4 literals for default values.
• Statistics Advisor: enable tolerance of conflicts between close time.
• Statistics Advisor: modify the rules of column group correlation.
• Maintain visual explain: enhance the ability of maintaining explain tables.
• List cache statement: adjust requisite privilege to get apg from statement cache.
• Tune SQL: provide dialogs to create database and table space for explain tables.
• ServiceSQL: new naming convention for VE ServiceSQL files.
• ServiceSQL: new IBM service ftp server is supported.
• ServiceSQL: the generated JCL files support DSNTEP2 instead of DSNTEP3.
• ServiceSQL:ServiceSQL provides more formatted explain table output.
• List cache statement: VE doesn't display statement text of dynamic cache.
• List PLAN TABLE Records: fix the defect to search with mixcase package name.
• VE Help: remove CPU cost, IO cost and total cost.
• Access Plan Graph: fix a defect to display the cardinality for UNION/UNION ALL nodes.
• Statistics Advisor: avoid COLUMN(ALL) collection when generating recommendation.
• ServiceSQL: fix the defect that the semicolon is missing in table foreign key constraint in v7.
• ServiceSQL: fix the defect that DSSIZE occurs in large tablespace's ddl.
• ServiceSQL: fix the defect of invalid trackmod in a lob table space ddl.
• Tune SQL: fix the defect of an inappropriate message box appears if the save history statement checkbox is enabled.

• Statistics Advisor: VE shows NULL when users click "Execute Runstats" and try to execute runstats commands.
• Access Plan Graph: Fix the "Report bug to developer" occured when the user tried to explain some specific pruned query.
• Update the VE help.

• Service SQL:Fix the typo of "AUDIT CHANGE" in table ddl.
• Service SQL:Fix the improper messages for pruned query.
• Service SQL:Fix the incorrect 'SECQTY -4' in DDL statements.
• Service SQL: Fix the bug about file names in "Send Files" panel.
• Access Plan Graph: Fix the query block's output cardinality.
• Access Plan Graph: Fix no output after generating access plan graph.
• Access Plan Graph: Fix the explain failure with table functions.
• Access Plan Graph: Fix the error in building multiple index scan nodes.
• Access Plan Graph: Fix the error on the multi-column statistics report.
• Tune SQL: Fix the bug about missing the comma when VE translates the query with host variables.
• Tune SQL: Fix the bug of "Report Bug to Developer error" when the user failed to SET CURRENT SCHEMA in his subsystem.
• Installation: Fix the problem of "JVM not found" in silent installation.
• Service SQL:Add the DDL statements for the ALIAS and SYNONYMS.
• Service SQL:Modify the JCL header in the generated files.
• Service SQL:Modify the generated file names according to a better naming convention.
• Service SQL GUI: Add the "Documentation ID" label in the Service SQL panel.
• VE Plugin: Enable statistics advisor from List Plan Table and List Static SQL.

• Installation: Enable silent install to use bundled jvm.
• Tune SQL: Provide a field to enable SET CURRENT SCHEMA for V8 NF mode.
• List Static SQL: Add in a new column STMTNOI.
• Service SQL: Generate the index DDLs in ascending OBID order.
• Access Plan Graph Report: add a new column IsSargable in the predicate summary.
• Show Plan Table: Fix generating Access Plan Graph on DB2 upgrading from V6 to V7.
• List Static SQL: Fix showing HEX string in DB2 V8 CM.
• VE API: Fix SQL truncating when calling VE from DB2 PE.
• Statistics Advisor: Fix generating ColumnGroup in RUNSTATS commands.
• Service SQL: Fix generating SECQTY -4.
• Service SQL: Fix generating stats file with invalid host format.
• Service SQL: Fix generating creator name on the view's ddl.
• Service SQL: Fix generating invalid MQT DDL in host format.
• Access Plan Graph: Fix generating double CTE node.
• Access Plan Graph: Fix generating APG failed by missing a workfile scan node.
• Access Plan Graph: Fix explaining failed on Type Q workfiles.
• Enable VE: Fix bug when existing wrong V7 format explain tables.

• Installation: Enable silent install with the response file.
• Installation: Upgrade the bundled jvm version.
• Service SQL: Generate ALT BP commands or DISPLAY BP commands.
• Service SQL: Generate all view DDLs.
• Statistics Advisor: Simple reports for explaination.
• List Statement Cache: Add panels for creating a database and tablespace when creating DSN_STATEMENT_CACHE_TABLE.
• Use the latest used folder when open/save files.
• Update the VE help files.
• Tune SQL: Identify an SQL statement with common-table-expression.
• Tune SQL: Set default value for CURRENT REFRESH AGE/CURRENT MAINTANED TYPES.
• Tune SQL: Fix generating APG when exists type Q workfile in PLAN_TABLE.
• Service SQL: Pick up PARTITIONED keyword for DPSIs.
• Service SQL: Truncate H2K and L2K when converting from V8 NFM to V7.
• Service SQL: Generate full DDLs for a MQT like a table.
• Service SQL: Remove PADDED/NOT PADDED/NOT CLUSTER when DB2 V8 CM.
• Service SQL: Remove COMPRESS for LOB tablespace or TEMP database.
• Service SQL: Add space after every commas.
• Service SQL: Fix limitkey value for the Date type.
• List Statement Cache: Enable the table qualifier box when listing statement cache.
• Access Path Graph: List matching predicates in indexed column sequence.
• Access Path Graph: Enable displaying APG for pruned sql statements.
• Query Report: Provide vapability for query report to open new browser window.
• Statistics Advisor: Fix VE freezing when switching windows.
• Statistics Advisor: Generates runstats with sysda and shrlevel concatenated.
• Enable VE: Allow to override the stogroup when creating indexes.
• Enable VE: Add CCSID clause when creating tables.
• List Static SQL: Fix the error when the static SQL contains Demark characters.
• List Static SQL: Fix showing hex string after fallback from V8 to V7.
• Fix start-up errors when using DB2 Connect PE.
• Fix the method to decide on DB2 version in VE and SA.
• Set the SQLID on the connection when the connected user is deferrent from the connected SQLID

• Installation: Fix replacement rule for INI files.
• Enable Visual Explain: Add an SQLID field.
• Enable Visual Explain: Add panels for creating a database and table space when creating DSN_FUNCTION_TABLE.
• Enable Visual Explain: Add panels for creating a database, table space and table when backing up explain tables.
• Fix truncation of leading zeros in the microsecond portion of TIMESTAMP.
• VE tries connecting twice to a database subsystem even with an invalid password.
• Fix link for glossary help page on the toolbar.
• Fix explain for statements that contain Chinese characters.
• Access Path Graph: Disable the save file option in the access path panel when no file is loaded.
• Access Path Graph: Fix scrolling function in access path panel for explanation output.
• Access Path Graph: Fix detail info window when switching from graph only to graph detail view.
• Access Path Graph: Fix help and suggestion links in the access path graph, such as HBJoin node.
• Access Path Graph: Fix predicate texts in the descriptor window that include xml characters.
• Enable Visual Explain: Allow lowercase characters as input.
• Enable Visual Explain: Fix Migrate Visual Explain when migrating PLAN_TABLE data from V7 format to V8.
• List Static SQL: Fix retrieval of statements when using access path filters.

• Installation: Fix JVM check for Control Center Plugin
• Installation: Fix DB2TEMPDIR reference when using DB2 Connect V7
• Convert service name to port number
• Fix cancel button when trying to connect to a database
• Help: Add intro.htm file to help directory
• Browse Subsystem Parameters:Fix match case option in subsystem parameters
• Maintain Visual Explain: Fix maintain visual explain if one or more explain tables are missing
• Enable Visual Explain: Fix detection & migration of DB2 z/OS V7 explain tables to V8
• View External explain tables: Allow 0 for statement number for explain table filtering
• List Static SQL: Fix delete last filter
• Service SQL: Fix CURSOR_HOLD issues when using a type 2 JDBC driver
• Service SQL: Fix create view HEX in V8 CM, add DB2 version in JCL header
• TuneSQL: Enable CURRENT MAINTAINED TABLE TYPES
• TuneSQL: Fix table function descriptor
• TuneSQL: Fix XML error "The entity name must immediately follow the '&' in the entity reference."
• TuneSQL: Fix out of bounds error when extracting table reference cost
• TuneSQL: Display decimal values and time values correctly in descriptors
• Stats Advisor: Fix space in RUNSTATS output between SYSDA and SHRLEVEL
• Trace: Format timestamps in trace file

v1.0.5 (2005/02/23):

• Disable carriage return removal when saving a statement to command history
• Fix access path filters for list static SQL statements
• Remove service sql fix so indexes partitioned on date are generated
• Fix glossary toolbar icon link
• Statistics Advisor update
• Fix Visual Explain install script when upgrading from v1.01 or v1.02
• Fix Visual Explain install script when DB2 Connect is reinstalled to a different directory
• Fix Control Center plugin install script for jvm not found error

v1.0.4 (2004/11/08):

• TuneSQL: Pruned queries would throw a "Report bug to developer" error. TuneSQL now processes a pruned query successfully.
• Statement Cache Plugin: Display more informative column names for stmtcache plugin, popup text in filter result panel for column name descriptions
• Enable IVE: Check bp size for creating function table and statement cache table.
• List static SQL statements: qualifier is used as a qualifier for explain tables, OWNER should be used
• When scrolling through a query result, return any sqlcode back to VE GU
• Disable stats advisor and other plugins in tutorial mode
• Non-English versions of Visual Explain: list static sql statements issues a JAVA.LANG.STRING error when retrieving statements

v1.0.3a (2004/10/04):

• Service SQL fix for partitioning index

v1.0.3 (2004/09/03):

• Visual Explain plugin: when an error message pops up for a plugin (statement cache), the panel for the plugin still pops up and is empty. The fix doesn't show the empty panel anymore.
• Visual Explain.: Data from statement table does not migrate successfully due to new STMT_ENCODE column in V8. Fix uses a blank char for pre-V8 data.
• Installation: prevent installation of VE if:
  • DB2 Connect (or any DB2 UDB with a JDBC driver) is not found
  • Visual Explain is currently installed in the target directory. Visual Explain has to be uninstalled prior to installation of new version / fixpack.
• Service SQL: Correctly honor SQLID when SQLID provided is different from logon id. Also provide option to change SQLID within Service SQL.
• Service SQL: Correct CREATE INDEX DDL limitkeys with DATE column
• Tune SQL will default to an empty input screen.
• Tune SQL: The UPDATE panel for sql statement is now scrollable.
• List static sql: change QUERYNO > -1 predicate for both explainable and non-explainable statements.
• Tune SQLFix COLGROUPVALUE message to: Invalid column COLVALUE value in SYSCOLDISTtable
• Visual Explain: Move to IBM JRE 1.4.2, add Stats Advisor plugin
• Tune SQL: Buttons in TuneSQL panel do not overwrite save history checkbox
• Save connection properly when TuneSQL closes and opens a JDBC connection
• Installation: Preserve user config files and xml files
• README now highlights important stored procedures which are used by Visual Explain to obtain maximum capability and error message reporting.

v1.0.2 (2004/06/29):

• TuneSQL: Show table type for workfile nodes
• TuneSQL: Explain stored procedure support
• Create & migrate added for DSN_FUNCTION_TABLE
• TuneSQL: display infopop only when any index on the table does not have stats and table does not have a CARDF
• In the connect panel any change to the userid textfield will be mirrored in the sqlid field
• Avoid unecessary bind/check for bind
• Update VE to use XML parser v2.x
• TuneSQL: Fix parameter marker replacement for host variable with closing parentheses:
  • Example: VE incorrectly converts (C1=:HV1) to (C1=? instead of (C1=?)
• Fix usage of SET CURRENT SQLID : exists in enable ive, maintain ive, and tunesql for dynamic statements
• For list static SQL statements explainable: do not include queryno -1
• Fully qualify select on explain tables
• Include stmtcache plugin
• Service SQL: syscoldists colvalue > 254 hex string

v1.0.1 (2004/04/29):

• XML config file fix for the following error: An invalid XML character (Unicode: 0xfb) was found in the value of attribute "name".
• Fix descriptor information in TuneSQL. Displays column names and predicate text for filter factor correctly.
• Enable IVE: Correct migration of explain tables from V6 to V7.
• List Static SQL Statments: Use QUERYNO instead of STMTNO for plan/package if possible.
• Tune SQL: Correctly process carriage return for SQL input panel.
• Service SQL: Generate SQL file when SQL interface is used.
• Service SQL: JCL change to improve V7 / V8 service:

  //DSNPLI8  JOB MSGLEVEL=(1,1),CLASS=A,MSGCLASS=A,
    //             USER=SYSADM,PASSWORD=XXXXXXXX
    //**
    /*ROUTE PRINT NNNNNNN.XXXXX
    //PLI8     EXEC TSOBATCH,DB2LEV=DB2A
    //*

• VE: add LOCATION = '' (16 char blanks for V7) for some queries on explain tables for performance improvement.
• VE: Correctly handles decimal point as a comma.

v1.0 (2004/03/26):

• More context-sensitive tuning suggestions are provided. You can link to these suggestions directly from the graph. You can also link from the graph to additional statistics and descriptions for each object or operation that is used in the access plan.
• Each graph can display multiple query blocks, so that you can view the entire access plan in one graph. In previous versions of Visual Explain, each graph displayed only one query block. In this version, you still have the option to view only one query block at a time.
• You can use Visual Explain to catalog and uncatalog databases on your local machine.
• You can use Visual Explain to run a query and view the formatted results.