SE54757: CMVC 227876 - FIX HE IDRESGEN (ID RESOLVER) HANG ISSUE WITH ORACLE DATABASE

7.0.0-WS-WCServer-FP009

APAR status

• Closed as program error.

Error description

• The idresgen utility may appear to hang or perform unexpectedly
  slowly when invoked against Oracle databases. Upon review of the
   trace.txt file in the WebSphere Commerce logs directory, the
  last message may appear similar to the following:
  ...
  2012-01-01 00:00:00.000, <Thread-0>,
  com.ibm.wca.IdResGen.IdResolve::obtainConnection
  Trace: The new database connection is used.
  ...
  
  This can occur due to a large number of calls made by the
  idresgen utility to the Oracle JDBC driver implementation of the
  Java DatabaseMetaData getImportedKeys() API.
  This fix resolves this issue by reducing the number of calls to
  only when necessary.
  See the "Workaround" section for additional information.
  
  The idresgen utility may not be able to fetch data from the KEYS
  table for primary-key retrieval purposes if the value inside the
  TABLENAME column isn't lowercase.  This fix resolves this issue
  by removing the requirement for the tablename to be in lowercase
  for operation with the idresgen utility.
  

Local fix

• Although the idresgen utility should ideally not make more than
  necessary calls to the Java DatabaseMetaData getImportedKeys()
  API, certain aspects of adverse performance experienced by the
  utility can be resolved by implementing guidelines provided as
  "workarounds" in the "Solution" section of the Oracle Metalink
  Note ID 796359.1
  

Problem summary

• USERS AFFECTED:
  Websphere Commerce users on v7.0 who use the idresgen utility
  
  PROBLEM ABSTRACT:
  The idresgen utility requires performance improvements and
  provide case insensitivity support for the KEYS table.
  
  BUSINESS IMPACT:
  Resolution of primary-keys given some unique-key constraint may
  be unnecessarily slow and cause loading of data into the
  database to be delayed
  
  RECOMMENDATION:
  

Problem conclusion

• Resolution is n-fold:
  1) Performance issues pertaining to fetch of constraints that
  link a table to its parent table(s), i.e., usage of the Java
  DatabaseMetaData getImportedKeys() API, has been limited to only
  those tables for which constraint fetching is required
  2) Fetch of new and/or existing primary-keys given a unique-key
  constraint specification using SELECT statements has been
  refined to use parameter markers instead of constant literals.
  This decreases time required to compile or prepare each SELECT
  statement to only once
  3) Fetch of new primary-keys using the KEYS table no longer
  requires the values in the TABLENAME column to be of a certain
  case
  -------------------------------------------------------------
  The latest available maintenance information can be obtained
  from the Recommended Fixes for WebSphere Commerce technote:
  http://www.ibm.com/support/docview.wss?rs=3046&uid=swg21261296
  

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:

Fix information

• Fixed component name

  WC BUS EDIT AS4

• Fixed component ID

  5724I3803

Applicable component levels

• R701 PSY

     UP