IBM Support

Improve performance and alleviate deadlocks by adding ENTITY_TYPE column to the CONTACT table index

Troubleshooting


Problem

The performance reduction and potential deadlocks (SQLCODE 913) in a multi-threaded environment, while executing getPartyByEntityId and persistEntity transactions, are caused by the lack of an indexed ENTITY_TYPE column in the CONTACT table. Since that column is actively used in queries during Hybrid transactions, its addition to the CONTACT table index can positively affect performance on all supported databases and alleviate the SQLCODE 913 deadlock issue for DB2 for z/OS database.

Symptom

System slowing and frequent deadlocks (-913 SQLCODE) within a multi-threaded environment can occur when executing Hybrid getPartyByEntityId and persistEntity transactions.

Resolving The Problem

Run the following SQL statements to recreate the I3_CONTACT index that includes the ENTITY_TYPE column:

For DB2 for z/OS database:

DROP INDEX I3_CONTACT;
CREATE INDEX I3_CONTACT ON CONTACT
(ENTITY_ID ASC,
ENTITYLINK_ST_TP_CD ASC,
PERSON_ORG_CODE ASC,
ENTITY_TYPE ASC
)
USING STOGROUP <STOGROUP_NAME>;


For DB2 database:

DROP INDEX I3_CONTACT;

CREATE INDEX I3_CONTACT ON CONTACT
(ENTITY_ID ASC,
ENTITYLINK_ST_TP_CD ASC,
PERSON_ORG_CODE ASC,
ENTITY_TYPE ASC
);

For Oracle database:

DROP INDEX I3_CONTACT;

CREATE INDEX I3_CONTACT ON CONTACT
(ENTITY_ID ASC,
ENTITYLINK_ST_TP_CD ASC,
PERSON_ORG_CODE ASC,
ENTITY_TYPE ASC
) TABLESPACE <INDEX_SPACE>;

[{"Product":{"code":"SSWSR9","label":"IBM InfoSphere Master Data Management"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":"--","Platform":[{"code":"PF002","label":"AIX"},{"code":"PF016","label":"Linux"},{"code":"PF027","label":"Solaris"}],"Version":"11.0.0","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSLVY3","label":"Initiate Master Data Service"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Product":{"code":"SSPVUA","label":"IBM InfoSphere Master Data Management Server"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Component":" ","Platform":[{"code":"","label":""}],"Version":"","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
27 April 2022

UID

swg21639731

Page Feedback