IBM Support

A SqlIntegrityConstraintViolationException occurs during the group replication process for IBM Business Process Manager (BPM)

Troubleshooting


Problem

When IBM Business Process Manager (BPM) is configured to use LDAP and LDAP has duplicate groups, a SqlIntegrityConstraintViolationException gets thrown during group replication process. Additionally, upon server startup, LDAP groups are not visible from WebSphere Application Server Administrative Console or Process Admin Console.

Symptom

Group replication is a process of synchronizing external security repositories with the Business Process Manager database. This process is kicked off at following times:

  • Automatically during server start up.
  • Internally when system components request with a Java™ Message Service (JMS) cache reset message.
  • Manually when an administrator logs on to Process Admin Console and clicks theGroup Cache reset button.


With the previously described problem, the error messages are similar to the following text:
Caused by: com.lombardisoftware.client.delegate.BusinessDelegateException: PreparedStatementCallback; SQL [insert into LSW_USR_GRP_XREF (GROUP_ID,GROUP_NAME,DISPLAY_NAME,GROUP_TYPE,DESCRIPTION,GROUP_STATE,PARENT_GROUP_ID) values (?,?,?,?,?,?,?)]; Error for batch element #41: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2;DB2BPM.LSW_USR_GRP_XREF, DRIVER=3.61.65; nested exception is com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #41: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2;DB2BPM.LSW_USR_GRP_XREF, DRIVER=3.61.65

The stack trace is similar to the following text:
Caused by: com.ibm.db2.jcc.am.SqlIntegrityConstraintViolationException: Error for batch element #41: DB2 SQL Error: SQLCODE=-803, SQLSTATE=23505, SQLERRMC=2;DB2BPM.LSW_USR_GRP_XREF, DRIVER=3.61.65
at com.ibm.db2.jcc.am.ed.a(ed.java:672)
at com.ibm.db2.jcc.am.ed.a(ed.java:60)
at com.ibm.db2.jcc.am.ed.a(ed.java:127)
at com.ibm.db2.jcc.t4.cb.a(cb.java:480)
at com.ibm.db2.jcc.t4.cb.a(cb.java:69)
at com.ibm.db2.jcc.t4.q.a(q.java:57)

Cause

IBM Business Process Manager does not support duplicate groups across federated repositories. For example, consider the following scenarios that are not supported by IBM Business Process Manager:

  • Group 'SALES' in a LDAP tree or branch and another group 'SALES' in the same LDAP sub-tree or sub-branch.
  • Group 'SALES' in a LDAP tree or branch and another group 'SALES ' (note the trailing space) in the same LDAP sub-tree or sub-branch.
  • Group 'SALES' in federated repository A (for example, Active Directory) and another group 'SALES' in federated repository B (for example, Tivoli).
  • Group 'SALES' in federated repository A (for example, Active Directory) and another group 'SALES ' (note the trailing space) in federated repository B (for example, Tivoli).

Resolving The Problem

To resolve this issue, complete the following steps:

  1. Stop the IBM Business Process Manager server.

  2. Make sure that you are using a user ID with the required permissions to update the database.

  3. Back up the BPMDB database.

  4. Apply APAR JR44698. This APAR logs all of the duplicate group names that are found across federated repositories. It is recommended that duplicate groups be either deleted or renamed.

  5. In your database, run the following query (after appropriate modifications to the schema name) to identify and delete all security groups that have trailing spaces in their names:
    DELETE FROM BPMADMIN.LSW_USR_GRP_XREF WHERE GROUP_ID IN (SELECT GROUP_ID FROM BPMADMIN.LSW_USR_GRP_XREF WHERE GROUP_TYPE=0 AND GROUP_NAME LIKE '% ');

  6. Commit the changes into the database.

  7. Start the IBM Business Process Manager server and you should see that the SqlIntegrityConstraintViolationException exception is not thrown.

  8. After the server starts, confirm that LDAP groups are visible using the WebSphere Application Server Administrative Console and the Process Admin Console.


[{"Product":{"code":"SSFTN5","label":"IBM Business Process Manager Advanced"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Security","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"},{"code":"PF035","label":"z\/OS"}],"Version":"8.0.1;8.0;7.5.1.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SSFTDH","label":"IBM Business Process Manager Standard"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Security","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"","label":"Linux zSeries"},{"code":"PF027","label":"Solaris"},{"code":"PF033","label":"Windows"}],"Version":"8.0.1;8.0;7.5.1.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}},{"Product":{"code":"SSFTBX","label":"IBM Business Process Manager Express"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Security","Platform":[{"code":"PF016","label":"Linux"},{"code":"","label":"Linux zSeries"},{"code":"PF033","label":"Windows"}],"Version":"8.0.1;8.0;7.5.1.1","Edition":"","Line of Business":{"code":"LOB45","label":"Automation"}}]

Document Information

Modified date:
15 June 2018

UID

swg21619620