IZ26382: GENERATED COLUMN NOT USED IN QUERY WHEN GENERATED COLUMN TYPE AND GENERATION EXPRESSION RESULT TYPE ARE MIXED CHAR/GRAPHIC

APAR status

• Closed as program error.

Error description

• In a unicode database, a query expression will not be
  substituted with a matching generated column if:
  
  - the generated column type is CHAR or VARCHAR and the
  generation expression result type is GRAPHIC or VARGRAPHIC
  
  or
  
  - the generated column type is GRAPHIC or VARGRAPHIC and the
  generation expression result type is CHAR or VARCHAR
  
  The result is poor performance of the query because the
  expression needs to be evaluated at execution time instead of
  using the generated column data and any index defined on the
  generated column cannot be exploited.
  
  For example:
  
    CREATE TABLE T ( C1 VARGRAPHIC(60),  GENCOL VARGRAPHIC(180)
  GENERATED ALWAYS AS ( SYSIBM.UPPER( C1 ) ) );
  
  The result of the generation expression UPPER( C1 ) is
  VARCHAR(180) but the generated column GENCOL has type
  VARGRAPHIC(180).  Query expressions that match the generation
  expression SYSIBM.UPPER( C1 ) will not be substituted with the
  generated column.
  

Local fix

• Ideally, declare the generated column type to match the
  generation expression result type, this is easiest done by
  simply omitting the type in the generated column definition, for
  example:
  
    CREATE TABLE T ( C1 VARGRAPHIC(60),  GENCOL GENERATED ALWAYS
  AS ( SYSIBM.UPPER( C1 ) ) );
  
  The generated column GENCOL will derive its type from the
  generation expression, in this case VARCHAR(180).
  
  Alternatively, in the query, you can insert a CAST around the
  expression that matches the generated column type, for example,
  instead of:
  
      select * from T where SYSIBM.UPPER( C1 ) = 'ABC'
  
  rewrite the query as:
  
      select * from T where VARGRAPHIC( SYSIBM.UPPER( C1 ) ) =
  'ABC'
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:                                              *
  * Users prior to DB2 V9.5 FP6                                  *
  ****************************************************************
  * PROBLEM DESCRIPTION:                                         *
  * In a unicode database, a query expression will not           *
  * besubstituted with a matching generated column if:- the      *
  * generated column type is CHAR or VARCHAR and thegeneration   *
  * expression result type is GRAPHIC or VARGRAPHICor- the       *
  * generated column type is GRAPHIC or VARGRAPHIC and           *
  * thegeneration expression result type is CHAR or VARCHARThe   *
  * result is poor performance of the query because              *
  * theexpression needs to be evaluated at execution time        *
  * insteadofusing the generated column data and any index       *
  * defined on thegenerated column cannot be exploited.For       *
  * example:CREATE TABLE T ( C1 VARGRAPHIC(60),                  *
  * GENCOLVARGRAPHIC(180)GENERATED ALWAYS AS ( SYSIBM.UPPER( C1  *
  * ) ) );The result of the generation expression UPPER( C1 )    *
  * isVARCHAR(180) but the generated column GENCOL has           *
  * typeVARGRAPHIC(180).  Query expressions that match           *
  * thegenerationexpression SYSIBM.UPPER( C1 ) will not be       *
  * substituted withthegenerated column.                         *
  ****************************************************************
  * RECOMMENDATION:                                              *
  * Upgrade to V9.5 FP6                                          *
  ****************************************************************
  

Problem conclusion

• First Fixed in V9.5 FP6
  

Temporary fix

Comments

APAR Information

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

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

  IZ26680

Fix information

• Fixed component name

  DB2 UDB ESE SOL

• Fixed component ID

  5765F4102

Applicable component levels

• R950 PSN

     UP