PK98668: UNNECESSARY CAST WHEN CALLING UPPER AND LOWER FUNCTIONS IN JPA QUERIES

APAR status

• Closed as program error.

Error description

• When using the LOWER or UPPER functions in a JPA Query the
  DB2Dictionary (AbstractDB2Dictionary) translates this function
  to a DB2 Query and adds a cast like UPPER(CAST(myAttribute) AS
  VARCHAR(1000)).
  
  This might be correct in cases where "myAttribute" is not of
  type CHAR or VARCHAR (i.e. is not a String) but if it is of
  type CHAR or VARCHAR (String) it prevents DB2 from using
  an index and therefore leads to significant performance
  reduction.
  
  The dictionary implementations for Oracle, Derby, etc.. does not
  use that cast at all.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  * USERS AFFECTED:  All users who use DB2 with IBM WebSphere    *
  *                  Application Server Feature Pack for EJB     *
  *                  3.0 and try to do UPPER or LOWER on an      *
  *                  OpenJPA entity attribute that is a DB2      *
  *                  index would be affected.                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Performance degrades when user has      *
  *                      entity attribute that is a DB2          *
  *                      index and user tries to do UPPER or     *
  *                      LOWER on the attribute.                 *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  OpenJPA's DB2Dictionary automatically converts any UPPER or
  LOWER JPQL to SQL UPPER(cast XYZ as varchar(1000)).  When this
  is done, any indexes on XYZ would be lost.
  

Problem conclusion

• Modified OpenJPA's DB2Dictionary class to check the DB2 column
  type before doing a cast.  If the column is already a varchar
  or char, then there is no need for the cast and the DB2 index
  will be used.  If the column is not varchar or char, then
  proceed as it did before.
  
  An Interim Fix for PK98668 has been provided for 6.1.0.25, the
  fix for this APAR is currently targeted for inclusion in fix
  packs 6.1.0.31 and 7.0.0.9.
  Please refer to the recommended updates page for delivery
  information:
  http://www.ibm.com/support/docview.wss?rs=180&uid=swg27004980
  

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:

  PM00431

Fix information

• Fixed component name

  WAS EJB3 FEATUR

• Fixed component ID

  5724J0851

Applicable component levels

• R610 PSY

     UP