JR50690: StagingProp fails because the maximum number of recursive SQL levels are reached for the FOLDER table trigger.

APAR status

• Closed as program error.

Error description

• StagingProp fails with an error similar to the following when
  performing a delete operation on the FOLDER table:
  
  ****************************************
  20140703-124553| 317068899: DELETE from table folder -
  folder_id=************** <scope merchant>
  20140703-124553| <Error> The command failed to roll back the
  production database.
  <SQLException> No more data to read from socket
  SQLState: 08000
  Vendor Error Code: 17410
  Stack trace:
  java.sql.SQLRecoverableException: No more data to read from
  socket
   at
  oracle.jdbc.driver.T4CMAREngine.unmarshalUB1(T4CMAREngine.java:1
  200)
   at
  oracle.jdbc.driver.T4CMAREngine.unmarshalSB1(T4CMAREngine.java:1
  155)
  ********************************************
  
  
  
  
  On the database side, an error similar to the following may be
  seen:
  ****************************
  SQL> delete from wcs_app.folder where folder_id =
  3074457345618273106;
  delete from wcs_app.folder where folder_id = 3074457345618273106
                      *
  ERROR at line 1:
  ORA-00036: maximum number of recursive SQL levels (50) exceeded
  ORA-00036: maximum number of recursive SQL levels (50) exceeded
  ORA-06512: at "WCS_APP.DSTG_FOLDER", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.DSTG_FOLDER'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  ORA-06512: at "WCS_APP.FOLDERCASCADE", line 1
  ORA-04088: error during execution of trigger
  'WCS_APP.FOLDERCASCADE'
  

Local fix

• To avoid failure of the StagingProp process, remove the FOLDER
  and FOLDERCASCADE tables from STGMERTAB to ensure StagingProp
  completes successfully.
  

Problem summary

• USERS AFFECTED:
  WebSphere Commerce v7.0 - Oracle users who propagate the FOLDER
  table using StagingProp utility
  
  PROBLEM ABSTRACT:
  StagingProp fails because the maximum number of recursive SQL
  levels are reached for the FOLDER table trigger.
  
  BUSINESS IMPACT:
  Unable to propagate FOLDER and FOLDERCASCADE data to production
  
  RECOMMENDATION:
  

Problem conclusion

• The trigger in wcs.trigger.foundation.foldercascade.sql  was
  rewritten to avoid the failure.
  
  -------------------------------------------------------------
  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 EDITION

• Fixed component ID

  5724I3800

Applicable component levels

• R700 PSY

     UP