Asynchronous Workflows
Asynchronous Workflows¶
Asynchronous processes are triggered when a record is inserted into the BPA_ASYNC_SYS_TAB table. This table holds essential information needed for running Workflows in asynchronous mode. The thread polling job, which operates within each middle-tier container (ODP container), constantly monitors this table. Its purpose is to retrieve records and execute Workflows seamlessly.
The following diagram shows the main components and their interactions with the Database tables.
Asynchronous execution begins with the Polling Thread. It retrieves records from the BPA_ASYNC_SYS_TAB table in batches. By default, the batch size is 100, but this value can be configured. If no data is available to read, the Polling Thread waits for a pre-defined time of 15 seconds, which is also configurable.
When a batch is read by the Polling Thread, it delegates the Asynchronous Executions to a separate set of Execution Threads. The Polling Thread then waits until all the records in the batch are processed by the Execution Threads before proceeding to read the next batch. This process continues until there are no records left in the BPA_ASYNC_SYS_TAB table.
By default, 10 Execution Threads perform asynchronous executions in parallel. The number of Execution Threads is also configurable. If the execution succeeds on the first attempt, the record immediately moves into the BPA_ASYNC_SYS_AUDIT_TAB. However, if it fails during the initial execution, the record status is updated to IP (In Progress), and the retry count is set to 1. The system will then retry executing these records in the IP state 3 times at 15-minute intervals. If the retry count reaches 3 with the IP status, the record is moved into the BPA_ASYNC_SYS_AUDIT_TAB table with the status FAILED. Here, the number of retries (3 times) and the retry interval (15 minutes) values are not configurable.
Note
There is No guaranteed order for Asynchronous executions. If the order of execution is important, consider using Before or After timing mechanisms.
About BPA_ASYNC_SYS_AUDIT_TAB Table¶
As the name suggests, this table primarily serves auditing purposes. It also contributes to enhancing the overall performance of the Asynchronous execution process by systematically relocating both successful and failed records into this table. Users with an interest in their Asynchronous jobs can conveniently query this table to retrieve relevant details.
As a simple rule
- To view asynchronous records that are yet to execute, query the BPA_ASYNC_SYS_TAB table.
- To see all completed (successful or failed) asynchronous executions, query the BPA_ASYNC_SYS_AUDIT_TAB table.
BPA_ASYNC_RECORDS is a convenient database view that combines data from both the BPA_ASYNC_SYS_TAB and BPA_ASYNC_SYS_AUDIT_TAB tables. It provides a unified view of asynchronous records, ordered by date with the latest records displayed first. This simplifies querying by eliminating the need to query two separate tables.
Please note that there is a cleanup job to clear BPA_ASYNC_SYS_AUDIT_TAB table data. Refer this link for more info on this.
Error Logging¶
If any asynchronous execution encountered errors, the relevant information is recorded in the ERROR_LOG column of the BPA_ASYNC_SYS_TAB and BPA_ASYNC_SYS_AUDIT_TAB (once moved) tables. Users can query these tables to identify execution errors if they encounter any. It’s a helpful way to troubleshoot and address issues related to asynchronous processes.
Configurations Related to Asynchronous Tasks Handling¶
As mentioned earlier, users have the flexibility to adapt settings according to their specific requirements. Users can add/change the below keys into the bpa_system_settings_tab table. Note that any changes made to these keys will require a container restart for the new values to take effect.
Configuration Name | Description |
---|---|
ASYNC_THREAD_LIMIT | This configures how many tasks can run parallelly. The Default value is 10. |
ASYNC_THREAD_READ_BATCH_SIZE | This configures how many records read at once to get started with the Asynchronous tasks. The Default value is 100. |
ASYNC_THREAD_DELAY_SECONDS | This configures how many seconds the Thread sleeps if no data is to be found for processing. The Default value is 15 seconds. |
Performance Improvement Tips¶
When using Asynchronous Workflows with a large set of records, the following options can be undertake to improve the performance.
- Increasing the batch size
As described earlier, a user can increase the batch size using the ASYNC_THREAD_READ_BATCH_SIZE option. This will help to reduce the number of trips toward the database thereby reducing the strain on the database.
- Increasing the number of executions threads.
As described earlier, the fetched asynchronous executions are executed by a separate set of threads parallelly. So, if we can increase the number of threads, then the computer can execute more at the same time. A user can change this value using ASYNC_THREAD_LIMIT configuration.
Note
All the configurations above should be done within the available memory and processor limits of the system. So before making any changes, it is advisable to do a proper analysis of the status of the system.
DB Pool Size Configuration¶
In most cases, about 99%, it is unnecessary to adjust the DB pool size. The DB pool comprises pre-created connections, with a fixed number, optimized for system functionality. However, if the ASYNC_THREAD_LIMIT configuration is increased, it may be necessary to adjust the DB pool size accordingly.
A straightforward guideline is to ensure that the DB pool size is at least one more than the value specified by ASYNC_THREAD_LIMIT. For instance, if ASYNC_THREAD_LIMIT is 10, the DB pool size should be set to at least 11.