IBM Support

Creating Aliases for DB2 Explain tables

Technote (FAQ)


Question

Explain tables in the database might exist under different schema names.
It is possible to reuse tables under one schema by using aliases for the explain tables.

Cause

There may be scenarios that require explain tables to be shared for different users. This technote reviews the options and illustrates how to create aliases

Answer

Explain tables can be created using the SYSINSTALLOBJECTS procedure.

    db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,NULL)"

This will create explain tables under the shared SYSTOOLS schema.

This means that if connected userid's do not have their own set of explain tables, the explain information will end up in the ones for the SYSTOOLS schema.

The db2exfmt output will also reflect the explain tables used :

    $ db2exfmt -d sample -1 -o ex1
    DB2 Universal Database Version 10.5, 5622-044 (c) Copyright IBM Corp. 1991, 2012
    Licensed Material - Program Property of IBM
    IBM DATABASE 2 Explain Table Format Tool

    Connecting to the Database.
    Connect to Database Successful.
    Using SYSTOOLS schema for Explain tables.
    Output is in ex1.

One might however create explain tables in a different schema e.g.

>

    db2 "CALL SYSPROC.SYSINSTALLOBJECTS('EXPLAIN','C',NULL,'MYSCHEMA')"

We can then see that 2 sets of tables exist :

    $ db2 "list tables for all " | grep -i explain_instance
    EXPLAIN_INSTANCE                MYSCHEMA        T     2016-07-05-11.28.53.232433
    EXPLAIN_INSTANCE                SYSTOOLS        T     2016-07-05-11.15.44.953793

If the current connected user ( and hence default schema ) is different from "MYSCHEMA", then one might assume that by setting the current schema to "MYSCHEMA", an explain request will now use the explain tables which exist under the "MYSCHEMA" schema. But this is not the case as seen below.

    $ db2 values current timestamp

    1                        
    --------------------------
    2016-07-05-11.43.56.415787

      1 record(s) selected.

    $ db2 "explain plan for select * from bennyv.staff"
    DB20000I  The SQL command completed successfully.

    $ db2 "select max(explain_time) from MYSCHEMA.EXPLAIN_INSTANCE"

    1                        
    --------------------------
    -                        

      1 record(s) selected.

    $ db2 "select max(explain_time) from SYSTOOLS.EXPLAIN_INSTANCE"

    1                        
    --------------------------
    2016-07-05-11.43.59.270371

      1 record(s) selected.

The reason for this is that the explain facility uses the current session ID to look for explain tables. This is different from the current schema value.

    $  db2 "values substr(session_user,1,10) , substr(current schema,1,10)"

    1        
    ----------
    BENNYV    
    APPUSER  

      2 record(s) selected.

We can however force the usage of the MYSCHEMA explain tables.

With some straightforward scripting, we can generate an sql script for this :

    $ db2 -x "list tables for schema myschema" | awk '{ printf "create alias  BENNYV.%s for %s.%s ;\n ", $1, $2, $1 }'
     create alias BENNYV.ADVISE_INDEX for MYSCHEMA.ADVISE_INDEX ;
     create alias BENNYV.ADVISE_INSTANCE for MYSCHEMA.ADVISE_INSTANCE ;
     create alias BENNYV.ADVISE_MQT for MYSCHEMA.ADVISE_MQT ;
     create alias BENNYV.ADVISE_PARTITION for MYSCHEMA.ADVISE_PARTITION ;
     create alias BENNYV.ADVISE_TABLE for MYSCHEMA.ADVISE_TABLE ;
     create alias BENNYV.ADVISE_WORKLOAD for MYSCHEMA.ADVISE_WORKLOAD ;
     create alias BENNYV.EXPLAIN_ACTUALS for MYSCHEMA.EXPLAIN_ACTUALS ;
     create alias BENNYV.EXPLAIN_ARGUMENT for MYSCHEMA.EXPLAIN_ARGUMENT ;
     create alias BENNYV.EXPLAIN_DIAGNOSTIC for MYSCHEMA.EXPLAIN_DIAGNOSTIC ;
     create alias BENNYV.EXPLAIN_DIAGNOSTIC_DATA for MYSCHEMA.EXPLAIN_DIAGNOSTIC_DATA ;
     create alias BENNYV.EXPLAIN_INSTANCE for MYSCHEMA.EXPLAIN_INSTANCE ;
     create alias BENNYV.EXPLAIN_OBJECT for MYSCHEMA.EXPLAIN_OBJECT ;
     create alias BENNYV.EXPLAIN_OPERATOR for MYSCHEMA.EXPLAIN_OPERATOR ;
     create alias BENNYV.EXPLAIN_PREDICATE for MYSCHEMA.EXPLAIN_PREDICATE ;
     create alias BENNYV.EXPLAIN_STATEMENT for MYSCHEMA.EXPLAIN_STATEMENT ;
     create alias BENNYV.EXPLAIN_STREAM for MYSCHEMA.EXPLAIN_STREAM ;
     create alias BENNYV.OBJECT_METRICS for MYSCHEMA.OBJECT_METRICS ;

Redirecting this output to a file and then executing against the database gives us the aliases we need and the explain output will be directed to the explain tables in schema MYSCHEMA

Note that insert privileges need to exist for the user against the explain tables.

Document information

More support for: DB2 for Linux, UNIX and Windows
Compiler - Explain

Software version: 9.7, 10.1, 10.5, 11.1

Operating system(s): AIX, HP-UX, Linux, Solaris, Windows

Reference #: 1986748

Modified date: 22 August 2016