ESQL Performance Statistics Logging — Overview
In IBM App Connect Enterprise (ACE), message flows often contain multiple Compute nodes, each having its own ESQL module where you write ESQL statements and functions to tailor the behavior of that node. During message processing, these ESQL statements execute to define, manipulate, and transform data flowing through the integration flow.
When performing a performance analysis of a message flow, it is often necessary to determine how much time each ESQL statement takes to execute as part of a transaction, especially when the Node-level Accounting and Statistics data shows a high elapsed time for a Compute node, or when a BIP8514W performance alert is logged against a Compute node. To facilitate this, ACE provides ESQL Performance Statistics Logging, which records the execution time and CPU usage of each ESQL statement executed within Compute nodes.
Enabling ESQL Performance Statistics
By default, ESQL performance statistics are disabled. To enable them, you must configure the ESQL ResourceManager section in the server.conf.yaml file of your Integration Server.
Example Configuration:
ResourceManagers:
ESQL:
performanceStatsReportingOn: 'true'
performanceStatsOutputPath: 'C:/ACEData/esqlStats/'
# Ensure this folder exists and has write permission for the IntegrationServer.
After updating the configuration file, restart the Integration Server to start generating ESQL performance statistics logs.
Command-Line Alternative
For Integration Servers owned by a Node, the same configuration can be applied using the mqsichangeproperties command:
mqsichangeproperties BKR12 -e default -o ImbESQLManager -n performanceStatsReportingOn,performanceStatsOutputPath -v true,C:/ACEData/esqlStats/
Once enabled, the ESQL performance statistics file will be created in the configured output directory with filenames in the format:
<NodeName>_<IntegrationServerName>_ESQLPerfStats.txt
By default:
- Up to 4 files are created.
- Each file can grow up to 25 MB in size.
The generated ESQL statistics output file provides details about the total execution time of each Compute node and the execution time of the individual ESQL statements within it, as shown below. The execution times are reported in microseconds.
New Configuration Options in ACE v12.0.12.20
Starting with ACE v12.0.12.20 Fix Pack, you can now customize the output file size, number of rotated files and a logging threshold time for ESQL performance statistics.
Example Configuration:
ResourceManagers:
ESQL:
performanceStatsFileSize: 50
performanceStatsNumberOfFiles: 5
performanceStatsOutputPath: 'C:/ACEData/esqlStats/'
performanceStatsReportingOn: true
performanceStatsLogThresholdMillis: 1000
Command-Line Equivalent:
mqsichangeproperties BKR12 -e default -o ImbESQLManager -n performanceStatsReportingOn,performanceStatsOutputPath,performanceStatsNumberOfFiles,performanceStatsFileSize,performanceStatsLogThresholdMillis -v true,C:/ACEData/esqlStats/,5,50,1000
Threshold-Based Logging :
By default, ESQL statistics logs the execution times of all ESQL statements from all Compute nodes. In high-load environments, this can lead to very large log files or frequent overwriting.
To address this, the ESQL Resource Manager now includes a threshold-based logging mechanism — giving users the ability to log only ESQL executions that exceed a defined execution time threshold.
This feature significantly reduces log volume and focuses attention on performance-critical operations.
New Property: performanceStatsLogThresholdMillis
Specifies a threshold (in milliseconds) that determines when to log ESQL statement execution details. When this property is set (for example, to 1000), the Integration Server evaluates the total execution time of each Compute node during message flow processing. If a Compute node’s total execution time exceeds the specified threshold (in this case, 1000 milliseconds or 1 second), the server logs the execution times of all ESQL statements within that node. Compute nodes that execute faster than the configured threshold are excluded from the log.
Only the ESQL statements belonging to Compute nodes whose overall execution time meets or exceeds the threshold are included in the performance log. This allows you to easily identify which specific ESQL statements within those Compute nodes contributed most to the overall execution time.
In the above image, the refreshCache Compute node takes approximately 2 seconds to execute. This node invokes a procedure named refreshcache, which accounts for the majority of the total execution time of the Compute node, as shown in the log.
Summary
The ESQL Performance Statistics Logging feature provides developers and operators with a structured mechanism to monitor and analyze ESQL execution efficiency. By leveraging configurable execution time thresholds, the feature isolates and reports only the slowest or most resource-intensive ESQL paths. This targeted approach facilitates focused performance tuning while minimizing data volume and operational overhead.