IBM Support

IC88630: SLOWER QUERY PLAN CAN BE MADE ON VARCHAR2 COMPABILITY DB IF EQUA LITY JOIN PREDICATES ON CHAR OR GRAPHIC COLS WITH HOST VARIABLES

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as program error.

Error description

  • Databases that are created with Varchar2 Compability enabled
    may experience poorly performing query access plans for queries
    that have equality join predicates on CHAR or GRAPHIC columns
    and the same columns are used in an equal predicate with a
    VARCHAR or VARGRAPHIC host variable or parameter marker.
    
      Your environment will need to be configured to use Deferred
    Prepare via the registry variable
    DB2_DEFERRED_PREPARE_SEMANTICS=YES or DB2_COMPATIBILITY_VECTOR
    to be impacted.
    
      To verify is you are impacted by this problem, obtain the
    access plan and look for a FILTER operation above NLJOIN, MSJOIN
    or HSJOIN operation. For example
    
    0.178997
     FILTER
     (  25)
     79.6035
       NA
       |
     111.873
     NLJOIN
     (  26)
     79.5749
       NA
    
      and where the operator details for the FILTER contains
    residual predicates referencing $Cx columns. For example:
    
    71) Residual Predicate,
    Comparison Operator: Equal (=)
    Subquery Input Required: No
    Filter Factor: 0.04
    
    Predicate Text:
    --------------
    ($C2 = $C1)
    

Local fix

  • Turn off Deferred Prepare or explicitly cast the host
    variables / parameter markers to the type of the column used in
    the predicates.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * Deferred Prepare user                                        *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to db2 Version 9.7 FixPack 8                         *
    ****************************************************************
    

Problem conclusion

  • Problem was first fixed in Version 9.7 FixPack 8
    

Temporary fix

Comments

APAR Information

  • APAR number

    IC88630

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    970

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2012-11-27

  • Closed date

    2013-04-08

  • Last modified date

    2013-04-08

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    IC91273

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • R970 PSN

       UP



Document information

More support for: DB2 for Linux, UNIX and Windows

Software version: 9.7

Reference #: IC88630

Modified date: 08 April 2013