Host Variable Data

This section of the Explain report is produced if HOSTVAR(YES) is specified for the OMEGAMON for Db2® Performance Expert explain plan or package.

If you define host variables which are not consistent with the corresponding column definition, Db2 selects an inefficient access path.

In Explain Report - Host Variables Data Block, the access path selected is table space scan even though an index is defined on the only column referenced in the WHERE clause. As the example in Explain Report - Host Variables Data Block shows, Db2 has selected table space scan because the column definition is three characters, but the corresponding host variable is defined as four characters. By changing the host variable definition to three characters, a matching index scan is selected by Db2.

For details on how to specify EXPLAIN commands refer to the Report Command Reference.

Explain Report - Host Variables Data Block

Here is an example of an Explain Report for the Host Variables Data block

 DECLARE C1 CURSOR FOR
 SELECT DEPTNO, DEPTNAME, LOCATION
 FROM DSNB610.DEPT
 WHERE DEPTNO = :HOSTVAR_STRUCTURE.DEPARTMENT_NUMBER
 ORDER BY DEPTNO


 --------



+------------------------------------------------------------------+
| TABLE SPACE SCAN - NO INDEX IS USED                              |
| STANDARD SEQUENTIAL PREFETCH WILL BE PERFORMED                   |
+------------------------------------------------------------------+



 --------



KEY                                      KEY                               KEY
NO. COLUMN NAME       COL.TYPE LNG NULL  CARD. ORDER LOW2KEY    HIGH2KEY   USED
--- -----------       -------- --- ----  ----- ----- ---------- ---------- ----
  1 WORKDEPT          CHAR       3 YES       8 ASC.  C'B01      C'E11      <===



 --------



HOST VAR. TYPE  LENGTH  IND.  HOST VARIABLE NAME
--------------- ------  ---- ----------------------------------------
FIXED CHARACTER      4  NO    HOSTVAR_STRUCTURE.DEPARTMENT_NUMBER