Customers who want to make use of extended EXPLAIN need to adapt and execute a DB2 10 sample job from SDSNSAMP library. IBM delivers a job called DSNTESC to create a set of 20 EXPLAIN tables required.

Because Visual Explain is deprecated and OSC (Optimization Service Center) does not support DB2 10 Unicode explain table, users of DB2 9 or 10 for z/OS will install the lastest version of Data Studio 3.1.1. (See https://ruban.de/db2zos/tools.)

Strange behavior! Be prepared to make some manual corrections which need DBA authorization.

Data Studio 3.1.1 Full Client, the most current version, requires 2 more tables:

DB2OSC.DSN_SA_PFLS_AUX         DB=AOCQTDB  TS=AOCOLS02 
DB2OSC.DSN_SA_PROFILES         DB=AOCQTDB  TS=AOCOTS09

The database name AOCQTDB is hard-coded and required by Data Studio 3.1.1. You cannot skip that point.

As already described above, DSNTESC creates a set of 20 tables:

NAME                      COLCOUNT 
------------------------- -------- 
DSN_COLDIST_TABLE         20       
DSN_DETCOST_TABLE         132       
DSN_FILTER_TABLE          17       
DSN_FUNCTION_TABLE        19       
DSN_KEYTGTDIST_TABLE      20       
DSN_PGRANGE_TABLE         14       
DSN_PGROUP_TABLE          41       
DSN_PREDICAT_TABLE        41       
DSN_PTASK_TABLE           24       
DSN_QUERY_AUX             3         
DSN_QUERY_TABLE           15       
DSN_SORT_TABLE            15       
DSN_SORTKEY_TABLE         20       
DSN_STATEMENT_CACHE_AUX   3         
DSN_STATEMENT_CACHE_TABLE 67       
DSN_STATEMNT_TABLE        15       
DSN_STRUCT_TABLE          19       
DSN_VIEWREF_TABLE         12       
DSN_VIRTUAL_INDEXES       145       
PLAN_TABLE                64

Data Studio 3.1.1 then requires a DSN_STATEMENT_CACHE_TABLE with 48 columnns. Users of DS first have to drop the existing DSN_STATEMENT_CACHE_TABLE before DS recreates it and the corresponding DSN_STATEMENT_CACHE_AUX again.

DS also requires one more table that is not part of the DSNTESC sample job on SDSNSAMP: The DSN_USERQUERY_TABLE and its corresponding DSN_USERQUERY_TABLE_AUX.

Sloppy work!