Troubleshooting
Problem
An error message “InfoSphere MDM Server for PIM – Error, CWPEL0014E: Error processing request – Update failed, the operation has been rolled back” is displayed in the GUI when the Attribute Collection Associations for a particular attribute in a spec is removed or added. The result is that an incorrect display of attributes is seen for example in a workflow where this attribute collection is used.
Symptom
An error message “InfoSphere MDM Server for PIM – Error, CWPEL0014E: Error processing request – Update failed, the operation has been rolled back” is displayed in the GUI when user tries to update the Attribute Collection Association of a particular attribute in a spec.
The outcome is that the transaction to update the Attribute Collection is not successful and causes incorrect display of attributes for example in the workflow if it contains this attribute collection. For example the attribute that was supposed to be removed from the Attribute Collection Association is not removed and thus appears in the workflow.
Cause
When the Attribute Collection Associations for a particular attribute in a spec is removed or added, a "delete sql" or "insert sql" request is sent to the database to modify this attribute collection when the user clicks on save. In cases where the db configuration does not meet configuration recommendations, such statements might fail with SQL0101 error.
The result of this error is that it causes an incorrect display of attributes for example in the workflow where this particular Attribute Collection is used.
This is an example of the sql that is being sent to the DB which was taken from the appsvr/default.log:
2012-06-26 14:35:01,565 [jsp_1: enterLogin.jsp] INFO com.ibm.ccd.wpc_user_scripting.ldap - Information: [Login.script]doWpcOnlyAuthentication
2012-06-26 14:35:01,661 [jsp_1: enterLogin.jsp] INFO com.ibm.ccd.common.security.common.DefaultAuthenticationHandler - Information: User Admin (trigo Admin) from egl logged in at Tue Jun 26 14:35:01 CEST 2012.
2012-06-26 14:39:24,804 [jsp_31: /ccd_content/spec_tree.jsp] ERROR com.ibm.ccd.common.context.common.DBContext - Error with query:
DELETE FROM tctg_agn_attr_group_node
WHERE agn_attr_group_id = 4206
AND agn_spec_id = 3488
AND agn_version_id <= 10000000
AND agn_next_version_id = 999999999
AND agn_company_id = 10001
AND (agn_node_id in (7001,7020,6979,7018,6987,6971,6977,7004,6966,6990,6968,6992,6984,6986,7006,7013,6962,7015,6998,6967,7019,7010,6993,6964,7009,6989,6963,6981,6991,6973,6976,6999,6985,7000,6972,7007,6978,7005,6970,6997,6996,6988,6974,7011,7024,7022,6961,6994,6965,6975,7012,6980,6995,7023,7014,6969,7021,7008,7017,7003,6983,6982,7002,7016,6929,6820,6808,6897,6917,6943,6927,6884,6822,6948,6881,6807,6946,6801,6940,6825,6786,6857,6842,6920,6817,6875,6783,6947,6788,6889,6805,6782,6907,6932,6878,6777,6809,6852,6921,6905,6923,6933,6762,6944,6896,6916,6956,6887,6784,6800,6926,6863,6960,6837,6831,6904,6803,6810,6911,6821,6768,6958,6906,6811,6796,6957,6847,6855,6935,6775,6868,6872,6902,6862,6797,6785,6791,6891,6870,6835,6882,6876,6919,6888,6841,6830,6845,6850,6834,6853,6772,6901,6819,6898,6840,6894,6867,6865,6779,6789,6941,6930,6864,6915,7210,6814,6778,6925,6895,6854,6849,6765,6826,6846,6886,6844,6848,6918,6959,6945,6804,7209,6938,6954,6839,6773,6770,6792,6823,6936,6953,6934,6879,6827,6939,6806,6799,7208,6815,6802,6798,6828,6950,6873,6787,6931,6860,6942,7207,6908,6780,6771,6890,6951,6774,6763,6794,6861,6766,7202,6812,6776,6833,6892,6880,6899,6824,6816,6767,6937,7204,6813,6877,6883,6795,6949,6769,6869,6790,6838,6910,7203,6832,6843,6922,6858,6866,6793,6913,6856,6893,6955,7206,6885,6900,6781,6912,6914,6836,6764,6903,6909,6829,7205,6851,6818,6871,6924,6928,6952,6859,6874,6686,6699,6660,6668,6743,6731,6653,6752,6662,6678,6659,6655,6697,6760,6727,6680,6740,6753,6672,6715,6754,6695,6757,6745,6712,6679,6725,6674,6656,6706,6691,6652,6704,6710,6694,6734,6693,6735,6682,6721,6665,6676,6714,6755,6746,6702,6761,6741,6663,6667,6703,6713,6732,6681,6698,6707,6737,6729,6700,6675,6718,6690,6730,6666,6748,6709,6670,6654,6677,6657,6661,6651,6683,6669,6724,6671,6696,6742,6658,6750,6728,6664,6747,6708,6738,6650,6726,6733,6689,6692,6649,6717,6687,6719,6739,6751,6758,6722,6723,6673,6744,6716,6749,6759,6705,6684,6685,6688,6720,6711,6756,6701,6736))
2012-06-26 14:39:24,954 [jsp_31: /ccd_content/spec_tree.jsp] ERROR com.ibm.ccd.common.jspTools.UtilBean - CWPCM0002E:Generic error / Exception:
---------------
Page: Spec Create/Edit
CWPEL0014E:Error processing request
Update failed, the operation has been rolled back., Exception:Update failed, the operation has been rolled back.
Update failed, the operation has been rolled back.
at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source)
at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source)
at com.ibm.ccd.common.context.common.DBContext.executeUpdate(Unknown Source)
at com.ibm.ccd.common.util.db2.SQLConstJava.attrGroupDeleteChildNodes(Unknown Source)
at com.ibm.ccd.common.util.SQLConstJava.attrGroupDeleteChildNodes(Unknown Source)
at com.ibm.ccd.element.common.AttrGroupDataGatherer.deleteChildNodes(Unknown Source)
at com.ibm.ccd.element.common.AttrGroupDataGatherer.updateAllAttrGroupsForDynamicSpecNodes(Unknown Source)
at com.ibm.ccd.element.common.MutableSpec.update(Unknown Source)
at com.ibm.ccd.element.common.MutableSpec.toDB(Unknown Source)
at com.ibm.ccd.element.view.SpecView.processRequest(Unknown Source)
at com.ibm.ccd.element.view.SpecView.getHTML(Unknown Source)
at ccd_content.spec_tree._jspService(Unknown Source)
at com.ibm.ws.webcontainer.jsp.runtime.HttpJspBase.service(HttpJspBase.java:103)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:831)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1657)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1597)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:131)
at com.ibm.ccd.ui.filters.FuzaoRequestFilter.doFilter(Unknown Source)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
at com.ibm.ccd.ui.filters.ResponseHeaderFilter.doFilter(Unknown Source)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
at com.ibm.ccd.ui.filters.ResponseHeaderFilter.doFilter(Unknown Source)
at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:188)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:116)
at com.ibm.ws.webcontainer.filter.WebAppFilterChain._doFilter(WebAppFilterChain.java:77)
at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:908)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:934)
at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:502)
at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:179)
at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:91)
at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:864)
at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1592)
at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:186)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:452)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:511)
at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:305)
at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:83)
at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:165)
at com.ibm.io.async.AbstractAsyncFuture.invokeCallback(AbstractAsyncFuture.java:217)
at com.ibm.io.async.AsyncChannelFuture.fireCompletionActions(AsyncChannelFuture.java:161)
at com.ibm.io.async.AsyncFuture.completed(AsyncFuture.java:138)
at com.ibm.io.async.ResultHandler.complete(ResultHandler.java:204)
at com.ibm.io.async.ResultHandler.runEventProcessingLoop(ResultHandler.java:775)
at com.ibm.io.async.ResultHandler$2.run(ResultHandler.java:905)
at com.ibm.ws.util.ThreadPool$Worker.run(ThreadPool.java:1604)
Caused by: com.ibm.db2.jcc.am.SqlException: DB2 SQL Error: SQLCODE=-101, SQLSTATE=54001, SQLERRMC=1, DRIVER=3.63.75
at com.ibm.db2.jcc.am.fd.a(fd.java:682)
at com.ibm.db2.jcc.am.fd.a(fd.java:60)
at com.ibm.db2.jcc.am.fd.a(fd.java:127)
at com.ibm.db2.jcc.am.yn.c(yn.java:2644)
at com.ibm.db2.jcc.am.yn.d(yn.java:2632)
at com.ibm.db2.jcc.am.yn.b(yn.java:2034)
at com.ibm.db2.jcc.t4.cb.i(cb.java:225)
at com.ibm.db2.jcc.t4.cb.c(cb.java:48)
at com.ibm.db2.jcc.t4.q.b(q.java:38)
at com.ibm.db2.jcc.t4.sb.h(sb.java:114)
at com.ibm.db2.jcc.am.yn.fb(yn.java:2029)
at com.ibm.db2.jcc.am.yn.a(yn.java:3156)
at com.ibm.db2.jcc.am.yn.c(yn.java:726)
at com.ibm.db2.jcc.am.yn.executeUpdate(yn.java:710)
at org.apache.commons.dbcp.DelegatingStatement.executeUpdate(DelegatingStatement.java:196)
at com.ibm.ccd.common.db.Query.executeUpdate(Unknown Source)
... 47 more
Error:
An error message “InfoSphere MDM Server for PIM – Error, CWPEL0014E: Error processing request – Update failed, the operation has been rolled back” is displayed in the GUI. In the logs, it shows that an SQL0101N error has occurred.
Definition of SQL0101N :
The statement is too long or too complex.
Explanation of SQL0101N :
The statement could not be processed because it exceeds a system limit for either length or complexity, or because too many constraints or triggers are involved.
Diagnosing The Problem
In this specific case, the DB2 configuration parameter STMTHEAP value was set too low.
Resolving The Problem
Step 1:
Please check that the DB configuration meets the configuration requirements, especially for parameter stmtheap. You can check the config requirements at :
http://publib.boulder.ibm.com/infocenter/pim/v9r1m0/topic/com.ibm.pim.ssc.doc/scripts/pim_ref_db2_checklist_script.html?resultof=%22%64%62%5f%63%68%65%63%6b%6c%69%73%74%22%20
It is generally recommended that $TOP/bin/db_checklist/db2_checklist.sql is run to verify if the DB2 settings are correct and are in the recommended range.
You will need to run the following if not set already:
db2 update db cfg using stmtheap automatic
Note: Prerequite is that the DB2 Parameter SELF_TUNING_MEM is set to "ON".
Step 2:
Running Step 1 alone is not sufficient as the error that has already occurred still needs to be corrected. As the user will see/not see the attribute that should be added/removed in the workflow, it can be corrected directly in the Attribute Collection Console. The affected Attribute Collection can be modified by either adding or removing the respective attribute.
Was this topic helpful?
Document Information
Modified date:
16 June 2018
UID
swg21605185