JR51792: Cache frequently executed TERMCOND query.

APAR status

• Closed as program error.

Error description

• The following frequently run query can be cached when all
  referenced PARTICIPNT rows have a NULL value in the MEMBER_ID
  column.
  
  SELECT T1.MANDATORY, T1.CHANGEABLE, T1.TERMCOND_ID, T1.SEQUENCE,
   T1.TRADING_ID, T1.TIMECREATED, T1.TIMEUPDATED, T1.OPTCOUNTER,
  T1.TCSUBTYPE_ID, T1.STRINGFIELD1, T1.STRINGFIELD2,
  T1.DECIMALFIELD1, T1.BIGINTFIELD1, T1.INTEGERFIELD1,
  T1.FLOATFIELD1, T1.INTEGERFIELD2, T1.INTEGERFIELD3,
  T1.TIMEFIELD1, T1.DECIMALFIELD2, T1.BIGINTFIELD2,
  T1.STRINGFIELD3, T1.BIGINTFIELD3 FROM TERMCOND T1 WHERE
  T1.TCSUBTYPE_ID IN ('POTCLimited', 'ShippingTCShippingCharge',
  'ReturnTCRefundPaymentMethod', 'HandlingChargeTC', 'InvoiceTC',
  'FulfillmentTC', 'PaymentTC', 'DisplayCustomizationTC',
  'ProductSetTCCustomInclusion', 'ReferralInterfaceTC',
  'ShippingTCShippingChargeAdjustment',
  'ProductSetTCCustomExclusion', 'OrderTCOrderComment',
  'ShippingTCShippingMode', 'ProductSetTCInclusion',
  'ProductSetTCExclusion',
  'PriceTCPriceListWithSelectiveAdjustment', 'TermCondition',
  'ShippingTCShipToAddress', 'PriceTCConfigBuildBlock',
  'PriceTCPriceListWithOptionalAdjustment',
  'ReturnTCExchangeMethod', 'RightToBuyTCByAmount',
  'OrderApprovalTC', 'PriceTCMasterCatalogWithOptionalAdjustment',
   'OrderTCOrderItemPAttribute', 'ObligationToBuyTCByAmount',
  'AddressBookTC', 'POTCBlanket', 'POTCIndividual',
  'PriceTCMasterCatalogWithFiltering', 'ReturnTCReturnCharge',
  'PriceTCCustomPriceList') AND trading_id=? and tcsubtype_id in
  (select tcsubtype_id from tcsubtype where tctype_id = ?) and
  (termcond_id not in (select termcond_id from participnt where
  trading_id is null and termcond_id is not null and
  partrole_id=2) or termcond_id in (select termcond_id from
  participnt where trading_id is null and termcond_id is not null
  and partrole_id=2 and (member_id is null or member_id = ? or
  member_id in (select ancestor_id from mbrrel where descendant_id
   = ?) or member_id in (select mbrgrp_id from mbrgrpmbr where
  member_id = ?))))
  

Local fix

Problem summary

• USERS AFFECTED:
  WebSphere Commerce Version 7 customers who have rows in the
  PARTICIPNT table where MEMBER_ID is NULL.
  
  PROBLEM ABSTRACT:
  Cache frequently executed TERMCOND query.
  
  BUSINESS IMPACT:
  Improved performance due to fewer query executions.
  
  RECOMMENDATION:
  

Problem conclusion

• Previously the TradingAgreementRegistry did not cache
  TermCondition objects for a trading agreement when any of them
  has a row in the PARTICIPNT table.  Now that restriction is
  loosened so that it does not cache them when any of them has a
  row in the PARTICIPNT table where MEMBER_ID is not NULL.
  

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

  3C COM PROF ED

• Fixed component ID

  5724I4000

Applicable component levels

• R700 PSY

     UP