JR57317: Improve performance of di-preprocess utility with deep search sequencing enabled

APAR status

• Closed as program error.

Error description

• Preprocessing might after deep search sequencing is enabled. The
  following query that is executed from
  wc-dataimport-preprocess-catgroup-global-sequence.xml file is
  very slow and can fail on the database side due to a large
  transaction log and file system space:
  
  SELECT CATGRPREL.CATGROUP_ID_CHILD,
  CATGRPREL.CATGROUP_ID_PARENT, CATGRPREL.CATALOG_ID,
  CATGRPREL.SEQUENCE FROM CATGRPREL, TI_CGPUB_0 CD
  WHERE CATGRPREL.CATGROUP_ID_CHILD = CD.CATGROUP_ID AND
  CD.PUBLISHED IN (?unpublished?, ?published?) AND
  CATGRPREL.CATALOG_ID IN (SELECT DISTINCT CATGPENREL.CATALOG_ID
  FROM CATGPENREL, TI_CATENTRY_0 CE WHERE
  CATGPENREL.CATENTRY_ID=CE.CATENTRY_ID))   UNION   (SELECT
  CATTOGRP.CATGROUP_ID AS CATGROUP_ID_CHILD, 0 AS
  CATGROUP_ID_PARENT, CATTOGRP.CATALOG_ID, CATTOGRP.SEQUENCE FROM
  CATTOGRP, TI_CGPUB_0 CD WHERE   CATTOGRP.CATGROUP_ID =
  CD.CATGROUP_ID AND CD.PUBLISHED IN (?unpublished?, ?published?)
  AND   CATTOGRP.CATALOG_ID IN (SELECT DISTINCT
  CATGPENREL.CATALOG_ID FROM CATGPENREL, TI_CATENTRY_0 CE WHERE
  CATGPENREL.CATENTRY_ID=CE.CATENTRY_ID)   )   ORDER BY
  CATGROUP_ID_CHILD, CATALOG_ID
  
  
  
  Error: DB2 SQL Error: SQLCODE=-968, SQLSTATE=57011,
  SQLERRMC=null,
  DRIVER=4.7.85
  SQLState:  57011
  ErrorCode: -968
  

Local fix

Problem summary

• USERS AFFECTED:
  WebSphere Commerce Version 7 and Version 8.
  
  PROBLEM ABSTRACT:
  Improve performance of di-preprocess utility with deep search
  sequencing enabled
  
  BUSINESS IMPACT:
  Takes a very long time to rebuild index and catalog updates
  might not be available immediately.
  
  RECOMMENDATION:
  

Problem conclusion

• The code included in this package allows running custom SQL,
  such as RUNSTAT, while performing index preprocessing. This is
  done by adding the following section to the appropriate location
   in any preprocessor XML template file:
  
  <_config:data-processing-config
  processor="com.ibm.commerce.foundation.dataimport.preprocess.Pre
  paredStatementDataPreProcessor"
  masterCatalogId="#MASTER_CATALOG_ID#">
     <_config:table definition="" name="" />
       <_config:query sql="CALL SYSPROC.ADMIN_CMD ('RUNSTATS ON
  TABLE ?schema?.?tablename? ON KEY COLUMNS and INDEXES ALL')"/>
         <_config:property name="schema" value="DB2ADMIN"/>
         <_config:property name="tablename"
  value="TI_CATENTRY_#INDEX_SCOPE_TAG#,
  TI_ATTR_#INDEX_SCOPE_TAG#_#lang_tag#"/>
  </_config:data-processing-config>
  
  
  This code logic allows executing a SQL statement with the use of
   multi-valued properties. The statement will be executed once
  for each value in the property list.
  
  Note: The properties section is for declaring values for
  substitution but there is a restriction. Multiple single value
  properties are supported, but only one multi-value property can
  be used and this multi-value property (comma separated) will be
  processed last.
  

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