Converting IBS_SPEC_ASSIGNMENTS to Module Level Associations

Historically, the IBM TRIRIGA Application Platform stores all associations between records in the IBS_SPEC_ASSIGNMENTS table. As an alternative, since 3.7, the platform was enhanced to support Module Level Associations tables. The Module Level Associations (MLA) converter tool takes the IBS_SPEC_ASSIGNMENTS table and distributes the IBM® TRIRIGA® records that are found into smaller tables that are created by module type.

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

Note: For more information, see the 3.7 Module Level Assignments video. As of 3.8, the Module Level Assignments converter tool is now renamed Module Level Associations.

Contents

I. Prerequisites

To use the Module Level Associations (MLA) enhancements, you must first install or upgrade to IBM TRIRIGA Application Platform 3.7 or later. After installing or upgrading, you must also perform your normal platform-upgrade regression testing. Make sure that your applications behave as expected. Validate the install or upgrade prior to starting the MLA conversion.

Warning: We highly recommend that you perform the MLA conversion in a test environment before doing so in a production environment. Validate your business processes before you run the MLA converter tool in a production environment. It is not possible to reverse this conversion process. The only way to return to the single IBS_SPEC_ASSIGNMENTS table is to restore it from your backup.

II. Important Information

  • Supported databases: Currently, this converter supports Oracle and DB2 databases only. It does not support Microsoft SQL Server.
  • Early adopters: Module Level Associations (MLA) is for "Early Adopters" and should be fully tested in a test environment, including all business processes, before putting it into a production environment.
  • Reverse associations: The Reverse Association flag in the IBM TRIRIGA Report Manager is disabled since this functionality is no longer supported. After converting your database to use Module Level Associations, the Reverse Association flag in queries and reports is ignored and only forward associations are allowed in reports and queries. 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, additional indexing, triggers, or other non-as-delivered database features were added to the IBS_SPEC_ASSIGNMENTS table. The default tablespace will be used as defined in TRIRIGADB.properties.
  • Limitations: Make sure to review the usage of 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 may not perform well. This includes, but is not limited to, the as-shipped WPM ETLs, the BIRT disclosure reports, and other custom ETLs, reports, and SQL.

III. Preparing for the Conversion

Procedure

  1. Make sure that your IBM TRIRIGA Application Platform build number is the same as the build number of the MLA converter.
    • If your IBM TRIRIGA Application Platform version is earlier than 3.7, then upgrade to 3.7 or later.
    • Perform your normal platform-upgrade regression testing.
    • Make sure that your applications behave as expected. Validate the upgrade prior to starting the MLA conversion.
  2. Create a backup of your current database before running the converter in your working environment.
    • It is not possible to reverse this conversion process. The only way to return to the single IBS_SPEC_ASSIGNMENTS table is to restore it from your backup.
    • The database that you use should be comparable in size to the database that you would use in a production environment.
    • The length of time that the conversion process takes will be proportional to the capabilities of the database server, the chunk size, and the size of your IBS_SPEC_ASSIGNMENTS table.
    • In our lab test environments, the conversion took anywhere from four (4) to ten (10) hours on a large IBS_SPEC_ASSIGNMENTS table.
    • When we used a low-powered database server, the same conversion took days.
  3. Make sure that your database server has enough memory and disk space to perform the conversion.
    • Make sure that your database server has enough available space for the conversion. We recommend at least double the size of the IBS_SPEC_ASSIGNMENTS table as available space.
    • This conversion is a resource-intensive process.
    • Make sure that your database adheres to the IBM TRIRIGA Best Practices for System Performance.
  4. Test your database I/O speed and connection with our Performance Monitor in the IBM TRIRIGA Administrator Console. The database test results should return highlighted in green.
  5. To repeat, make sure that you run this converter in a supported database type: Oracle or DB2. Currently, this converter does not support Microsoft SQL Server.
  6. Make sure that your database does not have any customized configurations around IBS_SPEC_ASSIGNMENTS. These customized configurations include partitioning, materialized views, triggers, etc.
  7. Make sure that your IBM TRIRIGA system no longer processes reverse association filters.
    • The Reverse Association flag is no longer supported or allowed with queries and reports in the IBM TRIRIGA Report Manager.
    • To determine if your IBM TRIRIGA system still has reverse associations:
      1. Go to the Administrator Console > Database Query Tool.
      2. Run the following SQL query: Q13a Find ALL queries using reverse associations (PLATFORM_AUDIT - No Hops)
      3. If you do not have this query, go to the Administrator Console > Must Gather Tool.
      4. Clear the Send Report to IBM TRIRIGA Support option.
      5. Clear any other fields that have testing or irrelevant data.
      6. Make sure to select the Perform Audit option.
      7. Select Submit Must Gather and wait a few minutes.
      8. When the process is completed, the query starting with Q13a can be found in the Database Query Tool.
      9. If any reverse association queries are found, then determine if they are still used, and fix or remove them before the conversion. If an IBM TRIRIGA query with a Reverse Association flag is encountered in an MLA-converted database, the query is run, a message is logged, and the reverse association is ignored.
      10. For guidance, see the Best Practices, Chapter 7 IBM TRIRIGA Tuning, Section 7.3.4 Reverse Association Queries.
  8. If you are using Linux, you must use the export command to set the MLA_CONV_DIR environment property to equal the directory in which the converter file resides.
    • Run this command in the same terminal session from which the converter will be launched.
    • For example: export MLA_CONV_DIR="/home/user/IBM/Tririga/tools/MLA-Converter"
  9. Determine the chunk size for the conversion. By default, the chunk size is set to 100 million records.
    • To change the default value, run the following SQL statement. For example, this statement sets the chunk size to 50 million records:
    • insert into ENVIRONMENT_PROPERTIES (PROPERTY, ENVIRONMENT, VALUE) values ('isa.chunk.size', 'platform', '50000000')
    • A zero (0) chunk size means no chunking. In this case, the converter will attempt to copy all of the data for a particular module into its association table.
    • A zero (0) chunk size is not recommended unless you have a high-end database server.
  10. Make sure that there are no other active connections to the database that you are converting.
    • Turn off any application servers, and any Oracle SQL Developer or IBM Data Studio sessions, that are running against the database server.
    • If you do not turn them off, the conversion may fail because of a deadlock or transaction timeout.

IV. Running the Conversion

Procedure

  1. When you are ready, run the converter executable file in your working environment.
    • Do not move the converter executable from the <installation-directory>/tools/MLA-Converter directory. The converter must be left in place and executed from there.
    • Do not start any application servers or establish any other connections to the database during the conversion process.
  2. Open the mla-conversion.log file to track the conversion progress. This log file is actively updated.
    • We recommend that you open the log file with BareTail or any other software that lets you automatically reload the file when it is updated, to avoid closing and opening the log file again to see the progress being logged.
    • In our lab test environments, the conversion took anywhere from four (4) to ten (10) hours on a large IBS_SPEC_ASSIGNMENTS table.
    • In general, the larger the IBS_SPEC_ASSIGNMENTS table is, the longer the conversion takes.
  3. If there are any issues during the conversion process, review the mla-conversion.log file.
    • Fix any errors as suggested. Restart the converter.
    • If your database is in an unstable state and you cannot fix it, you will need to restore the database from your backup.
    • Until your database is converted successfully, you will not be able to start or run an application server against the database.

V. After the Conversion Finishes

Procedure

  1. To make sure that your database is converted successfully, review the mla-conversion.log file.
  2. Start each of your application servers one by one.
  3. Perform your normal platform regression testing. Make sure that your applications behave as expected.
  4. Validate the conversion.
  5. After you are satisfied with the conversion, you can delete or drop the IBS_SPEC_ASSIGNMENTS_OLD table.
    • It is not possible to reverse this conversion process.
    • The only way to return to the single IBS_SPEC_ASSIGNMENTS table is to restore it from your backup.
  6. If your database is converted successfully, do not run the converter again.