Unable to propagate updates using the WebSphere Commerce stagingprop utility *|* -85256DD0005314428525758200651126- *|*

Failed propagation of data from staging server. You receive an exception similar to the following log sample.

Oracle:
The command failed to propagate the change related to the STAGLOG record 40888070. Exhausted ResultsetSQLState: nullVendor Error Code: 17011Stack trace:java.sql.SQLException: Exhausted Resultset at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:3478) at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5980) at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5959) at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:765) at com.ibm.commerce.staging.StagingProp.setValuesInSql(Unknown Source) at com.ibm.commerce.staging.StagingProp.propagateRows(Unknown Source)

DB2:
UPDATE mbrgrpusg SET FIELD1=? WHERE mbrgrp_id=? AND mbrgrptype_id=?SELECT FIELD1 FROM mbrgrpusg WHERE mbrgrp_id=? AND mbrgrptype_id=?begin to propagate table mbrgrpusg The command failed to propagate the change related to the STAGLOG record 10193. [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000SQLState: 24000Vendor Error Code: -99999Stack trace:COM.ibm.db2.jdbc.DB2Exception: [IBM][CLI Driver] CLI0115E Invalid cursor state. SQLSTATE=24000 at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.throw_SQLException(Unknown Source) at COM.ibm.db2.jdbc.app.SQLExceptionGenerator.check_return_code(Unknown Source) at COM.ibm.db2.jdbc.app.DB2ResultSet.getString2(Unknown Source) at COM.ibm.db2.jdbc.app.DB2ResultSet.getString(Unknown Source) at COM.ibm.db2.jdbc.app.DB2ResultSet.getObject(Unknown Source) at COM.ibm.db2.jdbc.app.DB2ResultSet.getObject(Unknown Source) at COM.ibm.db2.jdbc.app.DB2ResultSet.getObject(Unknown Source) at com.ibm.commerce.staging.StagingProp.setValuesInSql(StagingProp.java:832) at com.ibm.commerce.staging.StagingProp.propagateRows(StagingProp.java:749) at com.ibm.commerce.staging.StagingProp.transactTableGroup(StagingProp.java:488) at com.ibm.commerce.staging.StagingProp.propagate(StagingProp.java:452) at com.ibm.commerce.staging.StagingProp.main(StagingProp.java:265)10193: UPDATE table mbrgrpusg - mbrgrp_id-161 mbrgrptype_id-2

This issue can be caused by:

1. Deleting data from the staging database while the stagingprop utility is running.

2. You are attempting to make an update to the primary key of a database entry. The stagingprop utility has the limitation of not being able to perform an update action when the content that is being updated is a primary key.

• If the exception is caused due to deleting a record from the staging database while the stagingprop utility is running rerun the stagingprop utility again. The new delete will be recognized and properly handled.
  
  
• If this issue is caused by update actions being performed on primary keys in the staging environment you can do the following to locate and resolve the issue.
  

  1. Locate the records in the STAGLOG table that show either the unique index or primary keys of a table being modified as a part of an UPDATE action that are referenced in the stagingprop log:
  
  
  UPDATE mbrgrpusg SET FIELD1=? WHERE mbrgrp_id=? AND mbrgrptype_id=?SELECT FIELD1 FROM mbrgrpusg WHERE mbrgrp_id=? AND mbrgrptype_id=?begin to propagate table mbrgrpusgbegin to propagate 2000 rows The command failed to propagate the change related to the STAGLOG record 40888070. Exhausted ResultsetSQLState: nullVendor Error Code: 17011Stack trace:java.sql.SQLException: Exhausted Resultset at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:134) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:179) at oracle.jdbc.dbaccess.DBError.throwSqlException(DBError.java:269) at oracle.jdbc.driver.OracleStatement.prepare_for_new_get(OracleStatement.java:3478) at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5980) at oracle.jdbc.driver.OracleStatement.getObjectValue(OracleStatement.java:5959) at oracle.jdbc.driver.OracleResultSetImpl.getObject(OracleResultSetImpl.java:765) at com.ibm.commerce.staging.StagingProp.setValuesInSql(Unknown Source) at com.ibm.commerce.staging.StagingProp.propagateRows(Unknown Source) at com.ibm.commerce.staging.StagingProp.transactRows(Unknown Source) at com.ibm.commerce.staging.StagingProp.propagate(Unknown Source) at com.ibm.commerce.staging.StagingProp.main(Unknown Source) at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method) at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
  
  In the preceding example the failure appears to be coming from the MBRGRPUSG table. Unprocessed records from this table that are update actions can be found by running the following SQL query:select stgstmp stgtable stgop stgprocessed stgkey1name stgkey2name stgokey1 stgokey2 stgnkey1 stgnkey2 from staglog where stgtable = 'mbrgrpusg' and stgprocessed = 0 and stgrfnbr = 40888070;
  
  Note that the stgrfnbr = 40888070 value is retrieved from the error message:
  
  <Error> The command failed to propagate the change related to the STAGLOG record 40888070. will show the following:
  
  STGSTMP STGTABLE S STGPROCESSED STGKEY1NAME ----------------------------------------------- ------------------ --- ------------------------------- --------------------------
  06-MAR-09 02.12.39.000000 PM mbrgrpusg U 0 mbrgrp_id
  
  STGKEY2NAME STGOKEY1 STGOKEY2 STGNKEY1 STGNKEY2
  ------------------------- ------------------ -------------------- --------------------- --------------------
  mbrgrptype_id 908744 -2 908744 -1
  
  In the preceding example the action is updating MBRGRPUSG.MBRGRPTYPE_ID from -2 to -1. This column is part of the primary key for entries in the MBRGRPUSG table.
  
  

  2. After identifying the appropriate records in the staglog table you need to manually update the target production database with the Primary Key updates.
  
  Note: As the stagingprop utility runs in a transaction after the failure the record might have been rolled back in production. Also it is possible that there are dependencies on the row being updated which will need to be resolved before manually updating the record.
  
  
  

  3. After manually updating the target database update the associated STAGLOG records with STAGPROCESSED=1 to let the stagingprop utility know that these records have been processed.
  
  After locating and dealing with all the problematic records re-run the stagingprop utility If all problematic updates to primary keys have been correctly dealt with this error should no longer occur.
  
  To avoid this type of issue in the future ensure that any updates required to primary keys be done first as a DELETE then an INSERT of the updated data. It is important to be aware of what you are deleting as a DELETE action on entries in certain tables may cause a cascading delete.