JR53061: Custom SQL query result caching

APAR status

• Closed as program error.

Error description

• The data cache does not provide a convenient way to cache and
  invalidate custom SQL query results.
  

Local fix

• Customers can write cacheable commands, and cache the results in
   a servlet cache, using the Stores.war/WEB-INF/cachespec.xml
  file.
  

Problem summary

• Custom SQL query result caching
  

Problem conclusion

• This iFix provides a new WCDataCache method to execute a custom
  SQL query on a cache miss, or return a cached result on a cache
  hit.  By specifying information about the custom SQL statement,
  such as the names of the database tables that contribute to the
  query result, custom code can cause appropriate dependency ids
  to be added to cache entries.  By defining custom database
  triggers similar to the database triggers defined in the
  schema/<dbtype>/wcs.cacheivl.trigger.sql scripts, custom SQL
  query cache entries can be invalidated by the
  DynaCacheInvalidation scheduler job.
  
  Here is the description of the new
  WCDataCache.executeParameterizedQuery method:
  
  Enumeration
  com.ibm.commerce.datatype.WCDataCache.executeParameterizedQuery(
  String astrQuery, Serializable[] aarserParameters, int
  aInitialIndex, TreeMap<String, ArrayList<TreeMap<String,
  Object>>> amapTableName2ArrayListOfMapColumnName2Value, String
  astrLogicalCacheName, Date aExpiryTime) throws SQLException,
  ECSystemException
  
  
  Returns a query result for the specified query and parameters.
  If a result has previously been cached, the cached result is
  returned. Otherwise, the query is executed, and the result is
  cached and returned.
  
  Parameters:
  astrQuery the SQL query.
  aarserParameters if this is not null, it provides the parameters
  for the parameter markers in the query.
  aInitialIndex the initial zero based index for the returned
  enumeration.
  amapTableName2ArrayListOfMapColumnName2Value used to create
  dependency ids. Each key is a database table name, and each
  value is either null or is an ArrayList of TreeMap from column
  name to column value, which may be null. When present, the
  column names and values indicate particular rows of a table upon
  which the results of the query depend. When the ArrayList or a
  TreeMap from column names to column values is null, then the
  cached query result should be invalidated when any change to the
  table is made.
  astrLogicalCacheName the logical name of the data cache to be
  used. If this is null, then the default logical cache name is
  com.ibm.commerce.datatype.GenericJDBCQueryCache.
  aExpiryTime the time when this cache entry should expire, or
  null for no expiry.
  Returns:
  the query results. Each element is a List of values representing
  a row in the query result set. The caller should cast each text
  value as a String, and each numeric value as a Number, as
  different database types return different kinds of numeric data
  types. The returned query results must not be modified by the
  caller, as they may be cached. In order to handle large result
  sets without holding the whole result set in memory at the same
  time, the returned Enumeration holds onto an open ResultSet
  object. To close the ResultSet object, the caller should iterate
  over the entire Enumeration until hasMoreElements returns false.
  Throws:
  SQLException
  ECSystemException
  

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 EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP