IV24930: INDEX IDX_STMT_REL_SUBJ CAUSES POOR PERFORMANCE WHEN USING A DB2DATABASE

8.0.0.3-WS-WSRR-ZOS-IFIV65487_IV79085_IV87422_IV87429_IV89477
8.0.0.3-WS-WSRR-MultiOS-IFIV65487_IV79085_IV87422_IV87429_IV89477
WebSphere Service Registry and Repository V8.0 Fix Pack 1

APAR status

• Closed as program error.

Error description

• CRTST stored procedure does a select * and uses
  IDX_STMT_REL_SUBJ index which is a 6 column index on a 6 column
  table. So an index scan is not much more efficient than a table
  scan. Changing the index to a 3 column index has a huge impact
  on the performance. This APAR is intended to change the 6 column
   index to a 3 column index.
  

Local fix

Problem summary

• Performance when loading complex WSDL documents might be very
  poor when using a DB2 database.
  
  Other aspects of WSRR performance might also be suboptimal.
  

Problem conclusion

• It was found that real world DB2 performance is improved by
  removing the IDX_STMT_REL_SUBJ index defined in the provided
  createWsrrTables1.sql database script.
  
  This index has now been removed from the script and this change
  is targeted for inclusion in fix pack 3 for WSRR v7.5 and fix
  pack 1 for WSRR v8.0.
  
  For existing WSRR DB2 databases, the manual step to apply this
  APAR is simply to drop the IDX_STMT_REL_SUBJ index from the
  main WSRR database.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  SERVICE REGISTR

• Fixed component ID

  5724N7200

Applicable component levels

• R750 PSY

     UP

• R800 PSY

     UP