Today we’ll examine the -V option. This is a useful option when you need to run explains of statements that don’t qualify the schema of the tables they access.
The db2top tool can be used to list recent dynamic SQL statements and can be used to run db2expln and db2exfmt on such statements. By default, the EXPLAINs are done using the current schema of the user who launched db2top. When an EXPLAIN is run, db2top attempts to resolve any unqualified references in the SQL statement to the current schema. When the current schema doesn’t match the original schema used when the statement was originally run, the unqualified references cannot be resolved and the EXPLAIN fails.
An example may make this more clear. In the following example, a table is created under a schema named sch1 and is queried without explicitly qualifying the table name by making use of the SET CURRENT SCHEMA statement. The db2top tool then attempts to EXPLAIN this query without changing its schema:
create schema sch1
create table sch1.table1 (col1 integer)
set current schema sch1
select * from table1
Then we launch db2top and press the ‘D’ key to show the Dynamic SQL screen:
db2top -d sample
Pressing the ‘L’ key prompts you for the SQL hash string as shown below:
Once you have entered the hash string and pressed Enter, the “Query text” dialog box appears:
The above dialog box lets you take any one of several actions on your statement, but the ones we are interested in here are db2expln and db2exfmt. Pressing the ‘e’ key to launch db2expln launches an editor with the following error message:
DB2 Universal Database Version 9.7, 5622-044 (c) Copyright IBM Corp. 1991, 2008
Licensed Material - Program Property of IBM
IBM DB2 Universal Database SQL and XQUERY Explain Tool
******************** DYNAMIC ***************************************
==================== STATEMENT ==========================================
ERROR -- From db2exdyn: SQL0204N "KMCDONAL.TABLE1" is an undefined name. SQLSTATE=42704
Pressing ‘x’ to run db2exfmt instead launches an editor with no contents at all. Using the -V option on the command line when starting db2top lets you specify an alternate schema name to use when db2top launches db2expln or db2exfmt on your SQL statement. If you were to repeat the same tasks we performed above only with a -V sch1 specified on the command line when starting db2top, the explain would instead find TABLE1 under schema SCH1 and successfully produce the explain.