ESQL code tips

Array subscripts [ ] are expensive in terms of performance because of the way in which subscript is evaluated dynamically at run time. By avoiding the use of array subscripts wherever possible, you can improve the performance of your ESQL code. You can use reference variables instead, which maintain a pointer into the array and which can then be reused; for example:

DECLARE myref REFERENCE TO InputRoot.XML.Invoice.Purchases.Item[1];
-- Continue processing for each item in the array
WHILE LASTMOVE(myref)=TRUE DO
   -- Add 1 to each item in the array
   SET myref = myref + 1;
   -- Do some processing
   -- Move the dynamic reference to the next item in the array
   MOVE myref NEXTSIBLING;
END WHILE;

ESQL array processing example:

The following example shows ESQL being used to process records read from a database. The repeated use of array subscripts such as Environment.Variables.DBData[A] increases the processing time significantly:

SET Environment.Variables.DBDATA[] =
(
SELECT T.*
FROM Database.{'ABC'}.{'XYZ'} as T
);

DECLARE A INTEGER 1;
DECLARE B INTEGER CARDINALITY(Environment.Variables.*[]);
SET JPcntFODS = B;
WHILE A <= B DO
            CALL CopyMessageHeaders();
            CREATE FIELD OutputRoot.XML.FODS;
            DECLARE outRootRef REFERENCE TO OutputRoot.XML.Data;

            SET outRootRef.Field1 = Trim(Environment.Variables.DBDATA[A].Field1);
            SET outRootRef.Field2 = Trim(Environment.Variables.DBDATA[A].Field2);
            SET outRootRef.Field3 = Trim(Environment.Variables.DBDATA[A].Field3);
            SET outRootRef.Field4 = Trim(Environment.Variables.DBDATA[A].Field4);
            SET outRootRef.Field5 = Trim(Environment.Variables.DBDATA[A].Field5);
            . . .
            . . .
            SET outRootRef.Field37 = CAST(Environment.Variables.DBDATA[A].Field37)

            SET A = A + 1;
            PROPAGATE;
END WHILE;

You can reduce the processing time significantly by using reference variables

Reduce the number of DECLARE statements (and therefore the performance cost) by declaring a variable and setting its initial value within a single statement. Alternatively, you can declare multiple variables of the same data type within a single ESQL statement rather than in multiple statements. This technique also helps to reduce memory usage.

The EVAL statement is sometimes used when there is a requirement to dynamically determine correlation names. However, it is expensive in terms of CPU use, because it involves the statement being run twice. The first time it runs, the component parts are determined, in order to construct the statement that will be run; then the statement that has been constructed is run.

The following techniques can significantly improve performance when you are using PASSTHRU statements:

• Avoid the use of the PASSTHRU statement with a CALL statement to invoke a stored procedure. As an alternative, you can use the CREATE PROCEDURE ... EXTERNAL ... and CALL ... commands.
• When you are working with SQL statements that require literal or data values, use host variables, which map a column value to a variable. This enables dynamic SQL statements to be reused within the database. An SQL PREPARE on a dynamic statement is an expensive operation in performance terms, so it is more efficient to run this only once and then EXECUTE the statement repeatedly, rather than to PREPARE and EXECUTE every time.

For example, the following statement has two data and literal values, 100 and IBM:

PASSTHRU('UPDATE SHAREPRICES AS SP SET Price = 100 WHERE SP.COMPANY = ‘IBM'');

This statement is effective when the price is 100 and the company is IBM. When either the Price or Company changes, another statement is required, with another SQL PREPARE statement, which impacts performance.

PASSTHRU('UPDATE SHAREPRICES AS SP SET Price = ? WHERE SP.COMPANY = ?',
InputRoot.XML.Message.Price,InputRoot.XML.Message.Company);

db2 connect to <database name>
db2 get snapshot for database on <database name>

db2 connect to <database name>
db2 get snapshot for dynamic SQL on <database name>

DECLARE refPtr REFERENCE to InputRoot.XMLNSC.A.B.C.D;

To access element E of the message tree, use the correlation name refPtr.E.

You can use REFERENCE and MOVE statements to help reduce the amount of navigation within the message tree, which improves performance. This technique can be useful when you are constructing a large number of SET or CREATE statements; rather than navigating to the same branch in the tree, you can use a REFERENCE variable to establish a pointer to the branch and then use the MOVE statement to process one field at a time.

String manipulation functions used within ESQL can be CPU intensive; functions such as LENGTH, SUBSTRING, and RTRIM must access individual bytes in the message tree. These functions are expensive in performance terms, so minimizing their use can help to improve performance. Where possible, also avoid executing the same concatenations repeatedly, by storing intermediate results in variables.

Performance can be reduced under the following conditions:

• You are using ESQL processing to manipulate a large message tree
• The message tree consists of repeating records or many fields
• You have used explicit SET statements with field reference paths to access or create the fields
• You have observed a gradual slowing of message flow processing as the ESQL processes more fields or repetitions

This problem occurs when you use field references, rather than reference variables, to access or create consecutive fields or records.

The following example shows independent SET statements using field reference paths to manipulate the message tree. The SET statement takes a source and target parameter, where either or both parameters are field references:

SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field = '1';

Performance is affected by the SET statement being used to create many more fields, as shown in the following example:

SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field1 = '1';
SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field2 = '2';
SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field3 = '3';
SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field4 = '4';
SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field5 = '5';

In this example, the five fields that are created are all children of ParentA. Before the specified field can be created or modified, the integration node must navigate the named message tree to locate the point in the message tree that is to be altered. For example:

• To access field 1, the SET statement navigates to ParentA, then to the first field, involving two navigations.
• To access field 5, the SET statement navigates to ParentA, then traverses each of the previous fields until it reaches field 5, involving six navigations.

Navigating over all the fields that precede the specified field causes the loss in performance.

DECLARE myChar CHAR;
DECLARE thisRecord INT 0;
WHILE thisRecord < 10000 DO
	SET thisRecord = thisRecord + 1;
	SET myChar = InputRoot.MRM.myParent.myRepeatingRecord[thisRecord];
END WHILE;  

• When accessing InputRoot.MRM.myParent.myRepeatingRecord[1], one navigation takes place to get to the first record
• When accessing InputRoot.MRM.myParent.myRepeatingRecord[2], two navigations take place to get to the second record
• When accessing InputRoot.MRM.myParent.myRepeatingRecord[N], N navigations take place to get to the N-th record

Therefore, the total number of navigations for this WHILE loop is: 1 + 2 + 3 + .... + N, which is not linear.

If you are accessing or creating consecutive fields or records, you can solve this problem by using reference variables.

When you use reference variables, the statement navigates to the main parent, which maintains a pointer to the field in the message tree. The following example shows the ESQL that can be used to reduce the number of navigations when creating new output message tree fields:

SET OutputRoot.XMLNS.TestCase.StructureA.ParentA.field1 = '1';
DECLARE outRef REFERENCE TO OutputRoot.XMLNS.TestCase.StructureA.ParentA;
SET outRef.field2 = '2';
SET outRef.field3 = '3';
SET outRef.field4 = '4';
SET outRef.field5 = '5';

When referencing repeating input message tree fields, you can use the following ESQL:

DECLARE myChar CHAR;
DECLARE inputRef REFERENCE TO InputRoot.MRM.myParent.myRepeatingRecord[1];
WHILE LASTMOVE(inputRef) DO
	SET myChar = inputRef;
	MOVE inputRef NEXTSIBLING NAME 'myRepeatingRecord';
END WHILE;

For further information, see Creating dynamic field references.