JR48653: CMVC 232668 - dbclean utility script is not cleaning up entries in PX_CDPOOL table properly after applying JR45153.

APAR status

• Closed as program error.

Error description

• dbclean is no longer cleaning PX_CDPOOL table in default dbclean
  configuration.
   The query looks for data in PX_CDPROMO table, which is no
  longer populated after applying JR45153.
  
   The dbclean queries affected are:
  
   1. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in (select
  PX_PROMOTION_ID from PX_PROMOTION where (sysdate - ?)  >=
  enddate  and PX_PROMOTION_ID in (select PX_PROMOTION_ID from
  PX_CDSPEC where CODETYPE = 1 or CODETYPE = 2))) and PX_CDPOOL_ID
  not in (select PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5)
  
   2. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in (select
  PX_PROMOTION_ID from PX_PROMOTION where STATUS = 2 or STATUS = 4
  and PX_PROMOTION_ID in (select PX_PROMOTION_ID from PX_CDSPEC
  where CODETYPE = 1 or CODETYPE = 2))) and PX_CDPOOL_ID not in
  (select PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5)
  
   3. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5 and ORDERS_ID in
  (select ORDERS_ID from ORDERS where STATUS = 'C' and (sysdate -
  ?) >= LASTUPDATE and ORDERS_ID not in (select ORDERS_ID from
  ORDERITEMS where ORDERITEMS_ID in (select ORDERITEMS_ID from
  rmaitem where ORDERITEMS_ID is not null)) and ORDERS_ID not in
  (select ORDERS_ID from ORDERITEMS where ORDERITEMS.STATUS !=
  'C'))) and PX_CDPOOL_ID in (select PX_CDPOOL_ID from PX_CDPROMO
  where PX_PROMOTION_ID in (select PX_PROMOTION_ID from PX_CDSPEC
  where CODETYPE = 1 or CODETYPE = 2))
  
   4. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5 and ORDERS_ID in
  (select ORDERS_ID from ORDERS where STATUS ='S' and (sysdate -
  ?) >= LASTUPDATE and ORDERS_ID not in (select ORDERS_ID from
  ORDERITEMS where ORDERITEMS_ID in (select ORDERITEMS_ID from
  rmaitem where ORDERITEMS_ID is not null)))) and PX_CDPOOL_ID in
  (select PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in
  (select PX_PROMOTION_ID from PX_CDSPEC where CODETYPE = 1 or
  CODETYPE = 2))
  
   5. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5 and ORDERS_ID in
  (select ORDERS_ID from ORDERS where STATUS ='D' and (sysdate -
  ?) >= LASTUPDATE and ORDERS_ID not in (select ORDERS_ID from
  ORDERITEMS where ORDERITEMS_ID in (select ORDERITEMS_ID from
  rmaitem where ORDERITEMS_ID is not null)))) and PX_CDPOOL_ID in
  (select PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in
  (select PX_PROMOTION_ID from PX_CDSPEC where CODETYPE = 1 or
  CODETYPE = 2))
  

Local fix

• Manually remove the affected data
  

Problem summary

• USERS AFFECTED:
   WebSphere Commerce v7 users who have applied JR45153 and wish
  to clean PX_CDPOOL data using dbclean utility.
  
   PROBLEM ABSTRACT:
   dbclean utility script is not cleaning up entries in PX_CDPOOL
  table properly after applying JR45153.
  
   BUSINESS IMPACT:
   Data is not getting cleaned which could potentially cause a
  performance issue on the site if not addressed
  
   RECOMMENDATION:
  

Problem conclusion

• A new set of dbclean queries have been introduced (i.e. new
  entries into CLEANCONF table) to handle the case where
  PX_CDPROMO is no longer being populated after having applied
  APAR JR45153.
   The old queries will remain untouched since there could still
  be previous entries populated in the PX_CDPROMO table.
  
   The dbclean queries affected are:
  
   1. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in (select
  PX_PROMOTION_ID from PX_PROMOTION where (sysdate - ?)  >=
  enddate  and PX_PROMOTION_ID in (select PX_PROMOTION_ID from
  PX_CDSPEC where CODETYPE = 1 or CODETYPE = 2))) and PX_CDPOOL_ID
  not in (select PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5)
  
   2. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in (select
  PX_PROMOTION_ID from PX_PROMOTION where STATUS = 2 or STATUS = 4
  and PX_PROMOTION_ID in (select PX_PROMOTION_ID from PX_CDSPEC
  where CODETYPE = 1 or CODETYPE = 2))) and PX_CDPOOL_ID not in
  (select PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5)
  
   3. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5 and ORDERS_ID in
  (select ORDERS_ID from ORDERS where STATUS = 'C' and (sysdate -
  ?) >= LASTUPDATE and ORDERS_ID not in (select ORDERS_ID from
  ORDERITEMS where ORDERITEMS_ID in (select ORDERITEMS_ID from
  rmaitem where ORDERITEMS_ID is not null)) and ORDERS_ID not in
  (select ORDERS_ID from ORDERITEMS where ORDERITEMS.STATUS !=
  'C'))) and PX_CDPOOL_ID in (select PX_CDPOOL_ID from PX_CDPROMO
  where PX_PROMOTION_ID in (select PX_PROMOTION_ID from PX_CDSPEC
  where CODETYPE = 1 or CODETYPE = 2))
  
   4. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5 and ORDERS_ID in
  (select ORDERS_ID from ORDERS where STATUS ='S' and (sysdate -
  ?) >= LASTUPDATE and ORDERS_ID not in (select ORDERS_ID from
  ORDERITEMS where ORDERITEMS_ID in (select ORDERITEMS_ID from
  rmaitem where ORDERITEMS_ID is not null)))) and PX_CDPOOL_ID in
  (select PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in
  (select PX_PROMOTION_ID from PX_CDSPEC where CODETYPE = 1 or
  CODETYPE = 2))
  
   5. delete from PX_CDPOOL where PX_CDPOOL_ID in (select
  PX_CDPOOL_ID from PX_CDUSAGE where STATUS = 5 and ORDERS_ID in
  (select ORDERS_ID from ORDERS where STATUS ='D' and (sysdate -
  ?) >= LASTUPDATE and ORDERS_ID not in (select ORDERS_ID from
  ORDERITEMS where ORDERITEMS_ID in (select ORDERITEMS_ID from
  rmaitem where ORDERITEMS_ID is not null)))) and PX_CDPOOL_ID in
  (select PX_CDPOOL_ID from PX_CDPROMO where PX_PROMOTION_ID in
  (select PX_PROMOTION_ID from PX_CDSPEC where CODETYPE = 1 or
  CODETYPE = 2))
   -------------------------------------------------------------
   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 DEV ED W

• Fixed component ID

  5724I3900

Applicable component levels

• R700 PSY

     UP