IBM Support

User synchronization SQL exception is received after migrating from Lombardi TeamWorks 6.2 to IBM Business Process Manager (BPM) with an SQL Server

Troubleshooting


Problem

After migrating from Lombardi TeamWorks V6.2 to IBM Business Process Manager (BPM) with an SQL Server, you attempt to access ProcessAdmin -> User Synchronization -> Existing User Synchronize. This action results in an SQL exception.

Symptom

An SQL exception that is similar to the following text is received:
[5/15/14 13:34:18:891 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_16836130000107 for new updates...
[5/15/14 13:34:18:918 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_12991292000150 for new updates...
[5/15/14 13:34:18:945 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_10324006000259 for new updates...
[5/15/14 13:34:18:972 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_72479785000138 for new updates...
[5/15/14 13:34:18:999 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_04944736000179 for new updates...
[5/15/14 13:34:19:026 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_11033760000111 for new updates...
[5/15/14 13:34:19:051 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_15731715000191 for new updates...
[5/15/14 13:34:19:077 CST] 00000112 wle_security  I   CWLLG0468I: Checking user p_06307861000101 for new updates...
[5/15/14 13:34:19:104 CST] 00000112 wle_security  I   CWLLG0468I: Checking user brunocc for new updates...
[5/15/14 13:34:19:112 CST] 00000112 wle_security  I   CWLLG0469I: Fullname for user brunocc has changed. Updating...
[5/15/14 13:34:19:130 CST] 00000112 wle           E   CWLLG2229E: An exception occurred in an EJB call.  Error: PreparedStatementCallback; SQL [update LSW_USR_XREF set USER_NAME = ?,FULL_NAME = ?,PROVIDER = ? where USER_ID = ?]; Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.; nested exception is java.sql.BatchUpdateException: Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.
                                 com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; SQL [update LSW_USR_XREF set USER_NAME = ?,FULL_NAME = ?,PROVIDER = ? where USER_ID = ?]; Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.; nested exception is java.sql.BatchUpdateException: Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.
        at com.lombardisoftware.client.delegate.BusinessDelegateException.asBusinessDelegateException(BusinessDelegateException.java:46)
        at com.lombardisoftware.client.delegate.PersistenceServicesDelegateDefault.save(PersistenceServicesDelegateDefault.java:584)
        at com.lombardisoftware.client.persistence.common.factorydelegate.UnversionedFactoryDelegate.save(UnversionedFactoryDelegate.java:82)
        at com.lombardisoftware.client.persistence.common.AbstractUnversionedPOFactory.save(AbstractUnversionedPOFactory.java:158)
        at com.lombardisoftware.server.core.UserCore.getOrCreateUserInfoObject(UserCore.java:246)
        at com.lombardisoftware.server.ejb.security.SecurityCore.syncExistingUsers(SecurityCore.java:490)
        at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
        at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:60)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
        at com.lombardisoftware.utility.spring.TransactionInterceptor$2.call(TransactionInterceptor.java:67)
        at com.lombardisoftware.utility.spring.ProgrammaticTransactionSupport$1.doInTransaction(ProgrammaticTransactionSupport.java:443)
        at org.springframework.transaction.jta.WebSphereUowTransactionManager$UOWActionAdapter.run(WebSphereUowTransactionManager.java:306)
        at com.ibm.ws.uow.embeddable.EmbeddableUOWManagerImpl.runUnderNewUOW(EmbeddableUOWManagerImpl.java:791)
        at com.ibm.ws.uow.embeddable.EmbeddableUOWManagerImpl.runUnderUOW(EmbeddableUOWManagerImpl.java:370)
        at org.springframework.transaction.jta.WebSphereUowTransactionManager.execute(WebSphereUowTransactionManager.java:252)
        at com.lombardisoftware.utility.spring.ProgrammaticTransactionSupport.executeInNewTransaction(ProgrammaticTransactionSupport.java:438)
        at com.lombardisoftware.utility.spring.ProgrammaticTransactionSupport.execute(ProgrammaticTransactionSupport.java:300)
        at com.lombardisoftware.utility.spring.TransactionInterceptor.invoke(TransactionInterceptor.java:65)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
        at com.lombardisoftware.utility.spring.CoreEntryInterceptor.invoke(CoreEntryInterceptor.java:44)
        at com.lombardisoftware.utility.spring.PSCoreEntryInterceptor.invoke(PSCoreEntryInterceptor.java:14)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy101.syncExistingUsers(Unknown Source)
        at com.lombardisoftware.server.ejb.security.SecurityBean$11.execute(SecurityBean.java:190)
        at com.lombardisoftware.server.ejb.security.SecurityBean$11.execute(SecurityBean.java:188)
        at com.lombardisoftware.server.ejb.EjbServerUtils.handleEjbCall(EjbServerUtils.java:86)
        at com.lombardisoftware.server.ejb.security.SecurityBean.handleEjbCall(SecurityBean.java:50)
        at com.lombardisoftware.server.ejb.security.SecurityBean.syncExistingUsers(SecurityBean.java:188)
        at com.lombardisoftware.server.ejb.security.EJSRemoteStatelessSecurity_287acf5b.syncExistingUsers(Unknown Source)
        at com.lombardisoftware.server.ejb.security._SecurityInterface_Stub.syncExistingUsers(_SecurityInterface_Stub.java:998)
        at com.lombardisoftware.client.delegate.SecurityDelegateDefault$16.run(SecurityDelegateDefault.java:505)
        at java.security.AccessController.doPrivileged(AccessController.java:327)
        at com.lombardisoftware.client.delegate.common.WebsphereDelegateHelper.doAsCurrentSubjectContextSensitive(WebsphereDelegateHelper.java:197)
        at com.lombardisoftware.client.delegate.SecurityDelegateDefault.syncExistingUsers(SecurityDelegateDefault.java:502)
        at com.lombardisoftware.servlet.util.manager.SyncUserOrgRM.execute(SyncUserOrgRM.java:42)
        at com.lombardisoftware.servlet.ControllerServlet.processPost(ControllerServlet.java:673)
        at com.lombardisoftware.servlet.ControllerServlet.doCommon(ControllerServlet.java:550)
        at com.lombardisoftware.servlet.ControllerServlet.doPost(ControllerServlet.java:153)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:595)
        at javax.servlet.http.HttpServlet.service(HttpServlet.java:668)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.service(ServletWrapper.java:1230)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:779)
        at com.ibm.ws.webcontainer.servlet.ServletWrapper.handleRequest(ServletWrapper.java:478)
        at com.ibm.ws.webcontainer.servlet.ServletWrapperImpl.handleRequest(ServletWrapperImpl.java:178)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.invokeTarget(WebAppFilterChain.java:136)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:97)
        at com.lombardisoftware.servlet.CachingFilter.doFilter(CachingFilter.java:80)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.ClearThreadCachesFilter.doFilter(ClearThreadCachesFilter.java:34)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.SetUserTimeZoneFilter.doFilter(SetUserTimeZoneFilter.java:61)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.BidiSupportFilter.doFilter(BidiSupportFilter.java:50)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.CrossSiteScriptingFilter.doFilter(CrossSiteScriptingFilter.java:83)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:33)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.CompressionFilter.doFilter(CompressionFilter.java:55)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.lombardisoftware.servlet.SetBPMGenericHeaderFilter.doFilter(SetBPMGenericHeaderFilter.java:28)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.ibm.bpm.servlet.filters.GenericSecurityServletFilter.doFilter(GenericSecurityServletFilter.java:84)
        at com.ibm.ws.webcontainer.filter.FilterInstanceWrapper.doFilter(FilterInstanceWrapper.java:195)
        at com.ibm.ws.webcontainer.filter.WebAppFilterChain.doFilter(WebAppFilterChain.java:91)
        at com.ibm.ws.webcontainer.filter.WebAppFilterManager.doFilter(WebAppFilterManager.java:960)
        at com.ibm.ws.webcontainer.filter.WebAppFilterManager.invokeFilters(WebAppFilterManager.java:1064)
        at com.ibm.ws.webcontainer.servlet.CacheServletWrapper.handleRequest(CacheServletWrapper.java:87)
        at com.ibm.ws.webcontainer.WebContainer.handleRequest(WebContainer.java:914)
        at com.ibm.ws.webcontainer.WSWebContainer.handleRequest(WSWebContainer.java:1662)
        at com.ibm.ws.webcontainer.channel.WCChannelLink.ready(WCChannelLink.java:200)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleDiscrimination(HttpInboundLink.java:459)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.handleNewRequest(HttpInboundLink.java:526)
        at com.ibm.ws.http.channel.inbound.impl.HttpInboundLink.processRequest(HttpInboundLink.java:312)
        at com.ibm.ws.http.channel.inbound.impl.HttpICLReadCallback.complete(HttpICLReadCallback.java:88)
        at com.ibm.ws.tcp.channel.impl.AioReadCompletionListener.futureCompleted(AioReadCompletionListener.java:175)
        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:1864)
Caused by: org.springframework.dao.DataIntegrityViolationException: PreparedStatementCallback; SQL [update LSW_USR_XREF set USER_NAME = ?,FULL_NAME = ?,PROVIDER = ? where USER_ID = ?]; Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.; nested exception is java.sql.BatchUpdateException: Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.
        at org.springframework.jdbc.support.SQLErrorCodeSQLExceptionTranslator.translate(SQLErrorCodeSQLExceptionTranslator.java:292)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:607)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:619)
        at org.springframework.jdbc.core.JdbcTemplate.batchUpdate(JdbcTemplate.java:866)
        at com.lombardisoftware.server.ejb.persistence.dao.UnversionedPODAO.doUpdates(UnversionedPODAO.java:660)
        at com.lombardisoftware.server.ejb.persistence.dao.UnversionedPODAO.saveInternal(UnversionedPODAO.java:574)
        at com.lombardisoftware.server.ejb.persistence.AbstractDAO.save(AbstractDAO.java:94)
        at com.lombardisoftware.server.ejb.persistence.AbstractDAO.save(AbstractDAO.java:70)
        at com.lombardisoftware.server.ejb.persistence.DefaultHandler.save(DefaultHandler.java:202)
        at com.lombardisoftware.server.ejb.persistence.PSDefaultHandler.save(PSDefaultHandler.java:83)
        at com.lombardisoftware.server.ejb.persistence.PersistenceServicesCore.saveInternal(PersistenceServicesCore.java:449)
        at com.lombardisoftware.server.ejb.persistence.PersistenceServicesCore.save(PersistenceServicesCore.java:429)
        at sun.reflect.GeneratedMethodAccessor233.invoke(Unknown Source)
        at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:37)
        at java.lang.reflect.Method.invoke(Method.java:611)
        at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:310)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:182)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:149)
        at com.lombardisoftware.utility.spring.TransactionInterceptor$2.call(TransactionInterceptor.java:67)
        at com.lombardisoftware.utility.spring.ProgrammaticTransactionSupport.executeInExistingTransaction(ProgrammaticTransactionSupport.java:617)
        at com.lombardisoftware.utility.spring.ProgrammaticTransactionSupport.execute(ProgrammaticTransactionSupport.java:329)
        at com.lombardisoftware.utility.spring.TransactionInterceptor.invoke(TransactionInterceptor.java:65)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
        at com.lombardisoftware.utility.spring.CoreEntryInterceptor.invoke(CoreEntryInterceptor.java:44)
        at com.lombardisoftware.utility.spring.PSCoreEntryInterceptor.invoke(PSCoreEntryInterceptor.java:14)
        at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:171)
        at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
        at com.sun.proxy.$Proxy80.save(Unknown Source)
        at com.lombardisoftware.client.delegate.PersistenceServicesDelegateDefault.save(PersistenceServicesDelegateDefault.java:549)
        ... 94 more
Caused by: java.sql.BatchUpdateException: Cannot insert duplicate key row in object 'ps855user.LSW_USR_XREF' with unique index 'LSWC_UXREF_UQ'.
        at com.microsoft.sqlserver.jdbc.SQLServerPreparedStatement.executeBatch(SQLServerPreparedStatement.java:1178)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcPreparedStatement.pmiExecuteBatch(WSJdbcPreparedStatement.java:1037)
        at com.ibm.ws.rsadapter.jdbc.WSJdbcStatement.executeBatch(WSJdbcStatement.java:843)
        at org.springframework.jdbc.core.JdbcTemplate$4.doInPreparedStatement(JdbcTemplate.java:881)
        at org.springframework.jdbc.core.JdbcTemplate.execute(JdbcTemplate.java:591)
        ... 121 more

Cause

The TeamWorks 6.2 source system contained inconsistent data where the user names in LSW_USR did not match the user names in LSW_USR_XREF. As part of migration, users are moved out of LSW_USR into the file registry. References in LSW_USR_XREF must remain in the database.

In the target environment, user synchronization fails on the SQL Server because of a mismatch of upper-case characters versus lower case characters between the user name in the file registry and LSW_USR_XREF. A similar issue is observed when synchronizing users from LSW_USR to LSW_USR_XREF in the source environment.

Resolving The Problem

Add the following code to the 100Custom.xml configuration file on the deployment manager system:
<common merge="mergeChildren">
<case-insensitive-security-cache>true</case-insensitive-security-cache>
</common>

Restart the servers.

[{"Product":{"code":"SSFTN5","label":"IBM Business Process Manager Advanced"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Migration","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"8.5.6;8.5.5","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Product Synonym

BPM

Document Information

Modified date:
15 June 2018

UID

swg21675951

Page Feedback