IBM Support

IBM Jazz for Service Management DASH hub in HA mode becomes unresponsive during workload execution with more than 300 users accessing the database simultaneously

Troubleshooting


Problem

IBM Jazz for Service Management DASH hub in HA mode becomes unresponsive during workload execution with more than 300 users accessing the database simultaneously

Symptom

[1/22/16 9:33:12:101 CET] 000000b3 DbStore       W com.ibm.isc.ha.stores.db.DbStore saveToDB SQLException: The current transaction has been rolled back because of a deadlock or timeout.  Reason code "2".. SQLCODE=-911, SQLSTATE=40001, DRIVER=3.63.75
[1/22/16 9:33:12:102 CET] 000000b3 AbstractPrefe E AbstractPreferenceServiceImpl storePortletPreferences RepositoryException: Can not save repository. Error: null Key: jsp.message.ha.error.database
      com.ibm.isc.ha.runtime.RepositoryException
   at com.ibm.isc.ha.stores.db.DbStore.saveToDB(DbStore.java:113)
   at com.ibm.isc.ha.stores.ISCStore.save(ISCStore.java:84)
   at com.ibm.isc.ha.stores.StoreManager.save(StoreManager.java:84)
   at com.ibm.isc.ha.repositories.Repository.saveWithoutLock(Repository.java:49)
   at com.ibm.isc.ha.repositories.RepositoryManager.saveWithoutLock(RepositoryManager.java:434)
   at com.ibm.isc.ha.runtime.RepositoryManagerFactory.saveWithoutLock(RepositoryManagerFactory.java:276)
   at com.ibm.isc.datastore.global.UpdateStore.save(UpdateStore.java:153)
   at com.ibm.isclite.service.datastore.preferences.AbstractPreferenceServiceImpl.storePortletPreferences(AbstractPreferenceServiceImpl.java:251)
   at com.ibm.isc.api.contentbox.persistence.PreferenceUtil.setPreferences(PreferenceUtil.java:151)
   at com.ibm.twl.ssd.common.PrefsAJAXServlet.doPost(Unknown Source)
   at javax.servlet.http.HttpServlet.service(HttpServlet.java:595)

Cause

When multiple threads access the database to retrieve the data, it makes the system become unresponsive.

Resolving The Problem

To resolve this issue, create 2 indexes for different tables as follows:

Create an index for the table NODES:
db2 "CREATE INDEX DB2INST1.index_1 ON DB2INST1.NODES (NAME)"

Create second index for the table STORES:
db2 "CREATE INDEX DB2INST1.index_2 ON DB2INST1.STORES (NAME)"

where DB2INST1 is the default schema whose value depends on the specific database instance configuration.

Note: Once the index is created and data is loaded into the table, it is advisable to issue the RUNSTATS command. The RUNSTATS command updates statistics collected on the database tables, columns, and indexes. These statistics are used to determine the optimal access path to the tables. By issuing the RUNSTATS command, the database manager can determine the characteristics of the new index. If data has been loaded before the CREATE INDEX statement is issued, it is recommended that the COLLECT STATISTICS option on the CREATE INDEX statement be used as an alternative to the RUNSTATS command.

[{"Product":{"code":"SSRLR8","label":"Tivoli Components"},"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Component":"Jazz for Service Management","Platform":[{"code":"PF002","label":"AIX"}],"Version":"1.1.2.1;1.1.3","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
17 June 2018

UID

swg21981812