Performance tuning for parallel processing

This appendix describes performance tuning that can be required to run the CI Process (tccri7203m000) session using parallel processing.

The actual performance of CI Process (tccri7203m000) depends on a number of factors, including the number of available CPUs, and the setup of application and database servers. The amount of data contained in tables is another major factor. In general, the following tables contain large amounts of data:

  • Integration Transactions (tfgld482)
  • Reconciliation Data (tfgld495)
  • Finalized Transactions (tfgld106)
  • WIP and Inventory Transactions (JSC) (ticst300)
  • PCS WIP and Inventory Transactions (tipcs300)
  • Sales Order History (tdsls450, tdsls451, and tdsls456)

In LN FP5 and later a feature is available to update tables directly through the RDBMS. This feature is implemented in porting set 8.7a.03 and later.

Updates directly through the RDBMS are more efficient than updates via the bshell. The difference is that the entire transaction is handed over to the RDBMS. This type of update is limited to tables or data for which no recalculation of home amounts and/or exchange rates is required. This can be the case if, for example, the home amounts of a reporting currency become the amounts in local currency. Another example can be removal of home amounts. This can be the case if reporting currencies are deleted, or in logistical tables if the currency system changes to a standard currency system.

The CI Process (tccri7203m000) session will check whether updates directly through RDBMS can be applied. The session will use the conventional (slower) updates if the update directly through RDBMS is not allowed.

Whether you can use RDBMS updates, is determined by the following:

  • In the CI Rates (tccri7100m000) session, the value of the Express in Base Currency field must match that of the corresponding field in the Currency Rates (tcmcs0108m000) session. For example, in EURO companies the exchange rates from EUR (local) to transactional currencies can remain if the Express in Base Currency setting does not change during conversion.

    Note: This applies to all rate types of every base currency in the Currency Rates (tcmcs0108m000) session.

  • Technical limitations

    These restrictions apply:

    • The DB-driver must be a level-2 driver.

    • The following tables are audited nor mirrored:

      tfgld495, tfgld482, tdsls451, and tdsls456

  • Version 8.7a.03 or later of the porting set must be used. TIV-level of the porting set must be 1744 or later. You can check this number by running $BSE/bin/bshell6.2 –V on the command line (on Windows, use %BSE\bin\ntbshell –V)

If the currency initialization process starts in the CI Process (tccri7203m000) session, this session will access the conversion cluster and relevant companies, and then determine which type of conversion is required. If updates directly through the RDBMS can be applied, some data will be generated in the CI Conversion Tables by Cluster Companies (tccri725) and CI Cluster Conversion Tables Update Groups (tccri726) tables.

Note: CI Cluster Conversion Tables Update Groups (tccri726) are generated only for Integration Transaction (tfgld482) entries in the CI Conversion Tables by Cluster Companies (tccri7125m000) session.

In the CI Conversion Tables by Cluster Companies (tccri7125m000) session the Mass Updates field will be selected if CI Process (tccri7203m000) will update a table directly through RDBMS updates.

The Integration Transactions (tfgld482) table may contain transactions that belong to multiple financial companies. The integration transactions that can be handled with updates directly through the RDBMS will be grouped in update groups. Transactions in one update group can be converted in one RDBMS update. If Integration Transactions (tfgld482) must be converted without updates directly through RDBMS (as well), the table conversion can still be split. In this way, the conventional update of the table will be processed using parallel processes.

Reconciliation Data (tfgld495) must be split only if updates directly through the RDBMS cannot be applied. This will be the case if, for example, recalculation of home amounts or exchange rates is required.

Note: 
  • If the conversion of Reconciliation Data (tfgld495) is split, the table will not be updated with updates directly through the RDBMS.
  • The Finalized Transactions (tfgld106) table can be converted using parallel processes. Updates directly through the RDBMS are not supported.

Tuning example

This example applies to conversion tasks that are handled without updates directly through RDBMS, and that can be split in CI Conversion Tables by Cluster Companies (tccri7125m000).

An internal conversion with parallel processing may involve conversions with and without updates directly through the RDBMS, depending on the currency setup per company.

Suppose data of three companies must be converted using nine parallel bshells. The picture shows this situation with different colors for the three companies.

The table converted in task 1.1 is very large. At the same time, three other large tables are converted also (2.1, and 2.2, and 3.1). If the large conversion task 1.1 can be split, you can decide to have six bshells processing tasks 1.1, while three bshells will process task 2.1, 2,2, and 3.1. After splitting task 1.1, the six larger tasks that process this task will be handled before the smaller (purple-colored) tasks.

The same approach can be used if, for example, conversion of table 2.3 and 2.4 can be split. If table 2.3 is processed by two bshells in parallel, and 2.4 is processed by three bshells in parallel, there is still enough capacity left for the remaining parallel bshells to process the smaller (green-colored) tasks at the same time.

After splitting the tasks, the load for the parallel processes will look more balanced.

The total elapsed time has been reduced from 28 to 17 time units.

CI Process (tccri7203m000): trial conversion vs. real conversion

In simulation mode, the database transactions of the updates directly through the RDBMS will be tested. This type of updates can be used within the conversion of Reconciliation Data (tfgld495), Integration Transactions (tfgld482), Sales Order History Lines (tdsls451), and Sales Order History Delivery Lines (tdsls456). Transactions will be rolled-back in trial mode. This can take considerably more time than the actual transaction in a real conversion.

In the conventional conversion logic, the update of table data is not performed during trial conversion. Note that whereas these tables will be processed in a shorter time during trial conversion, processing them in the real conversion will take more time.
Note: During currency initialization, no other users or processes must be active in the companies of the conversion cluster, even if the CI Process (tccri7203m000) session is used to perform a trial conversion.
To prevent issues with table locks or reading uncommitted transactions, be aware of the following:
  • Updates directly through the RDBMS will be large transactions that are tested but not committed during trial conversion. This might result in a table locks on Reconciliation Data (tfgld495),Integration Transactions (tfgld482), Sales Order History Lines (tdsls451), and Sales Order History Delivery Lines (tdsls456).
  • SQL Server: By default, the database driver uses the “Read uncommitted" isolation level to prevent shared locks, and the exclusive lock for update and delete actions. As a result, during(trial) conversion, other users or processes might read uncommitted transactions in Reconciliation Data (tfgld495), Integration Transactions (tfgld482), Sales Order History Lines(tdsls451), and Sales Order History Delivery Lines (tdsls456). For more information, refer to Infor Enterprise Server Technical Reference Guide for SQL Server Database Driver (U8173US).

Oracle settings

The updates directly through the RDBMS will be large transactions. As a result, a large amount of undo table space will be needed. LN is tested with an undo table space of 32 GB. We recommend that you configure the undo table space to extend the data files automatically with, for example, 8 GB per time. This will help prevent ORA-1555 (Snapshot too old) errors from occurring. Keep in mind the maximum data file size.

It is not easy to predict whether the size of undo table space will be sufficient. We recommend that you look for ORA-1555 errors in the event-viewer logs of LN when CI Process (tccri7203m000) is running.

In general, to have queries perform well, the Oracle database requires proper tuning.

LN has been tested with the following settings applied to the LN environment via the $BSE/lib/default/db_resource file:

  • ora_init:0101000
  • ora_max_array_fetch:100
  • ora_max_array_insert:100
  • ora_alter_session: set " _optim_peek_user_binds "= false

You should consider merging the above lines with the batch-based db resource for conversion.

In some implementations non-default values are applied to hidden Oracle parameters (the so-called underscore parameters). These settings might influence the execution plans of the queries. Consider to reset them to their defaults. This can be done by adding them to the ora_alter_session property in the $BSE//lib/defaults/db_resource file.

SQL Server settings

You should consider changing the logging on the LN database during currency initialization to Simple and create a full backup after completing the currency initialization.

Set up the $BSE/lib/default/db_resource file to enable array fetching, and then set the fetch size to 100.

DB2 settings

Set up the $BSE/lib/default/db_resource file to enable array fetching, and then set the fetch size to 100.