Considerations For Collecting Optimizer db2support data

Body

  For diagnosis of query performance and optimizer plan selection issues it is sometimes necessary to collect db2support optimizer data (using the db2support with -cl 1 option).  db2support in optimizer mode collects several pieces of important information,  including configuration and catalog information,  but most importantly explains the input query and produces a db2exfmt output,  and db2look output based on the explain information. 

  Normally once the SQL is identified for the relevant query,  the query text can be placed in a file, and provided as part of the db2support command using the -sf <filename>  flag.  There are some situations however in which the db2support command may be unable to successfully compile and collect the db2exfmt information.  This results in the db2support collection being incomplete and important data being missing.   Some common reasons for this are:

1) Syntax issues in the query text,  especially a missing statement delimiter,  or other problems of that nature. 

2) The query text as identified from the application or from monitoring data may use an implicit schema which is not explicitly provided in the query text.  The intended schema may not match the implicit schema used by the db2support command based on the user issuing db2support, and thus the compilation of the query will fail because the object references will not be resolved to the same schema the application uses.  

3) Use of DGTTs in the query.  When a problem query references declared global temporary tables,  db2support cannot explain the query using the SQL text alone,  since the appropriate session tables need to be present in the current session for the query to compile successfully.  As of V10.5 FP7 there is a db2support option -prerun which can be used to setup the DGTTs within the db2support command. 

  For example,  if we have the following DGTT definition and query: 

DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPLIST (C1 CHAR(6)) IN UTEMP;
INSERT INTO SESSION.EMPLIST VALUES ('000010');
INSERT INTO SESSION.EMPLIST VALUES ('000020');
INSERT INTO SESSION.EMPLIST VALUES ('000030');

SELECT * FROM SESSION.EMPLIST EM,  IMAIONE.EMPLOYEE E
WHERE E.EMPNO = EM.C1;

  Any commands which are put in the file specified with the -prerun option will be executed first prior to issuing the explain of the query specified using the -sf option.  This is useful when DGTTs are involved as the appropriate statements can be placed in the prerun file to define any DGTTs necessary for the explain of the query.   Thus if we have a file dgtt.sql and dgttquery.sql with

dgtt.sql:

DECLARE GLOBAL TEMPORARY TABLE SESSION.EMPLIST (C1 CHAR(6)) IN UTEMP;
INSERT INTO SESSION.EMPLIST VALUES ('000010');
INSERT INTO SESSION.EMPLIST VALUES ('000020');
INSERT INTO SESSION.EMPLIST VALUES ('000030');

dgttquery.sql
SELECT * FROM SESSION.EMPLIST EM,  IMAIONE.EMPLOYEE E
WHERE E.EMPNO = EM.C1;

Issuing  db2support -d sample -cl 1 -prerun dgtt.sql -sf dgttquery.sql will collect the full db2support data for the query. 

As of V10.5 FP7,  if db2support with -cl 1 and -sf is used,  and an error is encountered when trying to compile the query provided,  an explicit message will be output to the console to inform the user that a problem was encountered.   Prior to FP7,  it was easy to miss the fact that an error occurred on explaining the query.   For example, if we submit a query with a misspelling of the table name,  it will fail with sql0204 on compilation:  

query.sql:
select * from imaione.employe
;

db2support -d sample -cl 1  -sf query.sql

DBT7116E  The db2support command failed because the explain facility encountered an error.
A failure was detected when collecting  "explain"
Warning: Some data may not be collected
Optimizer data collection failed

  In this case the db2support command in addition will not generate the normal db2support.zip file but will only generate an optimizer.log file with an indicator of the error sqlcode,  so that the problem can be addressed immediately

select * from imaione.employe
SQL0204N  "IMAIONE.EMPLOYE" is an undefined name.  SQLSTATE=42704

       SQL Code: "-204"
Error invoking "-tvf/net2/d_TMP/TMP/imaione/opt_db2support/DB2SUPPORT/OPTIMIZER/bad_query.sql"