Question & Answer
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.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21986748