Converting IBS_SPEC_ASSIGNMENTS to Module Level Associations

IBM TRIRIGA Application Platform stores all associations between records in the IBS_SPEC_ASSIGNMENTS table. An alternative is to convert the IBS_SPEC_ASSIGNMENTS table to Module Level Associations (MLA) tables. The MLA converter tool takes the IBS_SPEC_ASSIGNMENTS table and distributes the IBM® TRIRIGA® records into smaller tables for each module type.

Why convert to MLA tables?

IBM TRIRIGA Application Platform uses the IBS_SPEC_ASSIGNMENTS table to track all record associations, therefore any operation that requires associated records must join to this table. On some environments the join can involve a table with billions of rows, leading to performance degradation. MLA tables are smaller and therefore improve performance. MLA tables also reduce database contention in comparison to the IBS_SPEC_ASSIGNMENTS table. Also, MLA tables are indexed. The IBS_SPEC_ASSIGNMENTS table should not be indexed.

Converter executable

The converter executable is located in the directory where you installed IBM TRIRIGA Application Platform 5.0 at <installation-directory>/tools/MLA-Converter.

Note: For more information, see the 3.7 Module Level Assignments video. Module Level Associations was formerly known as Module Level Assignments.

Prerequisites

Warning: Perform the MLA conversion in a test environment before converting in a production environment. Validate your business processes before you run the MLA converter tool in a production environment. Take a backup of your database because you cannot reverse the conversion. The only way to return to the single IBS_SPEC_ASSIGNMENTS table is to restore it from your backup.

After an installation or upgrade, you must also perform your normal platform-upgrade regression testing. Make sure that your applications behave as expected. Validate the installation or upgrade before you convert.

Important information

Supported databases: Currently, this converter supports Oracle and Db2 databases only. It does not support Microsoft SQL Server.
Testing: Test the MLA conversion process in a test environment, including all business processes before you implement it in a production environment.
Reverse associations: The Report Manager Reverse Association flag is disabled because the underlying functions are no longer supported. After converting your database to use MLA, the Reverse Association flag in queries and reports is ignored and only forward associations are allowed. However, reverse associations as defined in the Association Manager and created through workflow tasks are still supported, and are used to fix any of the reports that are missing data because of the use of the flag.
Customized databases: The converter does not support customized databases where partitioning, other indexing, triggers, or other nonstandard database features are added to the IBS_SPEC_ASSIGNMENTS table. The default table space is defined in TRIRIGADB.properties.
Limitations: Review any SQL queries that access the IBS_SPEC_ASSIGNMENTS table for performance reasons. The IBS_SPEC_ASSIGNMENTS table is still usable, but it is a view and might not perform well. This includes, but is not limited to, the shipped WPM ETLs, the BIRT disclosure reports, and other custom ETLs, reports, and SQL statements.
Converted table naming conventions: Converted MLA tables are named MA_MODULE NAME. For example, the triPeople module filename is MA_TRIPEOPLE.