Good IIB ESQL Coding Practices

Array Index Notation
Avoid traversing the elements in sequence, starting from 1, until n is reached for every iteration of the loop. Instead, use a reference variable and the MOVE NEXTSIBLING syntax.

For a loop of 100 elements, index access notation results in 5000 (50 x 100) element traversals instead of 100 with the MOVE statement (50 times more). For a larger list of 1000 elements, it is worse; 500000 (500 x 1000) traversals instead of 1000 (500 times more).

Example of using reference variables

 DECLARE empRef REFERENCE TO OutputRoot.JSON.Data.Employee.Item[1];  
 WHILE LASTMOVE(empRef) DO  
  empRef.Name = 'John Smith';  
  MOVE empRef NEXTSIBLING;  
 END WHILE;  

Cardinality Function
The following guidelines apply to using this function:

• Don't use it to set an iterative limit on a loop that then uses array index notation.
• Don't use it to count the number of elements in a large, unparsed file since this will likely cause an abend.
• Don't use it to test for the existence of an element

Tree Access Order and Path References
Message trees are a complex data structure that behave much like a doubly linked list.
Ways to optimize performance include:

• locating often referenced items near the top of a child list
• defining reference variables for complex element paths in order to avoid repeated evaluations
• avoid generic recursive tree walks.

Use XMLNSC for XML Processing
XMLNSC is the most efficient XML parser in IBM® Integration Bus. Use it in preference to MRM and other XML parsers

Minimize Message Flow Node
Try to avoid chains of compute nodes in a simple msgflow that could be easily combined into one node.

Source: IBM © Integration Bus Information Centre

Create a JSON Array in IBM Integration Bus

CREATE FIELD OutputRoot.JSON.Data.to_BusinessPartnerTax IDENTITY (JSON.Array)to_BusinessPartnerTax;
SET OutputRoot.JSON.Data.to_BusinessPartnerTax.Item[1].BusinessPartner = '';
SET OutputRoot.JSON.Data.to_BusinessPartnerTax.Item[1].BPTaxType = '';
SET OutputRoot.JSON.Data.to_BusinessPartnerTax.Item[1].BPTaxNumber = '';

ESQL Functions

UUIDASCHAR
A function that returns a universally unique identifiers (UUID) as a CHARACTER value.

DECLARE uuid CHAR;

SET uuid =  UUIDASCHAR(source_blob_uuid);
SET uuid =  UUIDASCHAR;

If (source_blob_uuid) is not specified, UUIDASCHAR creates a UUID and returns it as a CHARACTER value. ed.
If (source_blob_uuid) is specified, UUIDASCHAR converts an existing BLOB UUID to the character form.

The result is NULL if a NULL parameter is supplied.

ESQL Arrow Notation

The arrow notation (the greater than (>) and less than (<)
symbols) is used to indicate the direction of search and the index to be specified:

SET OutputRoot.DFDL.MyMessage.StringElement1[>] = 'This is the first occurrence of StringElement1';
SET OutputRoot.DFDL.MyMessage.StringElement1[<2] = 'This is the last but one occurrence of
 StringElement1';
SET OutputRoot.DFDL.MyMessage.StringElement1[<1] = 'This is the last occurrence of StringElement1';

You can refer to the last instance of a repeating field using the special [<] array index, and to instances relative to the last (for example, the second to last) as follows:

Field[>] indicates the first element. This is equivalent to Field[1].
Field[<] indicates the last element.
Field[<1] indicates the last element.
Field[<2] indicates the last but one element (the penultimate element).
You can also use the array index [>] to represent the first element, and elements relative to the first element in a similar way.

Create an XMLNSC Tree in ESQL

CREATE LASTCHILD OF OutputRoot DOMAIN 'XMLNSC' NAME 'XMLNSC'; 
CREATE LASTCHILD OF OutputRoot.XMLNSC NAME 'FileData'; 
CREATE FIRSTCHILD OF OutputRoot.XMLNSC.FileData NAME 'BlobPayload' value InputRoot.BLOB.BLOB; 
CREATE LASTCHILD OF OutputRoot.XMLNSC.FileData NAME 'Filename' VALUE InputLocalEnvironment.File.Name;
CREATE LASTCHILD OF OutputRoot.XMLNSC.FileData NAME 'Filedir' VALUE InputLocalEnvironment.File.Directory;

The above code will create the following XML payload.

<FileData>
   <BlobPayload>XXX</BlobPayload>
   <Filename>YYY</Filename>
   <Filedir>ZZ</Filedir>
</FileData>


ESQL Code Snippets

1. Convert XMLNSC to BLOB 

Sometimes you may need to convert your payload to a BLOB(Binary Large Object). To be able to accomplish this the ASBITSTREAM function has to be used. The ASBITSTREAM function will return a bit stream representation of your payload. When I need to put my payload to MQ queue I convert it to a BLOB.

 DECLARE myChar CHAR CAST(myBLOB AS CHAR CCSID InputRoot.Properties.CodedCharSetId Encoding InputRoot.Properties.Encoding);  

2. Convert BLOB to CHAR

 DECLARE myChar CHAR CAST(myBLOB AS CHAR CCSID InputRoot.Properties.CodedCharSetId Encoding InputRoot.Properties.Encoding);  

3. Convert CHAR to BLOB

 DECLARE myBlob BLOB CAST( myChar AS BLOB CCSID InputRoot.Properties.CodedCharSetId);  

4. Convert BLOB to XMLNSC 

 CREATE LASTCHILD OF OutputRoot.XMLNSC DOMAIN('XMLNSC') PARSE(myBlob, InputRoot.Properties.Encoding, InputRoot.Properties.CodedCharSetId);  

5. Left Padding

 RIGHT('0000000000' || CAST(field AS CHAR),10);  

The above will left zero pad field to a 10 long field.

6. Nil Element & Namespace Declaration

 SET OutputRoot.XMLNSC.ns:myElement.(XMLNSC.NamespaceDecl)xmlns:"xsi" ='http://www.w3.org/2001/XMLSchema-instance';    
 SET OutputRoot.XMLNSC.ns:myElement.(XMLNSC.Attribute)xsi:nil = 'true';   

7. Convert Payload to a String

 DECLARE myBlob BLOB;  
 SET myBlob = ASBITSTREAM(InputRoot.XMLNSC CCSID InputRoot.Properties.CodedCharSetId ENCODING InputRoot.Properties.Encoding);  
 DECLARE myChar CHAR CAST(myBlob AS CHAR CCSID InputRoot.Properties.CodedCharSetId Encoding InputRoot.Properties.Encoding);  

8. Backup a JSON Payload without losing arrays

 CREATE LASTCHILD OF OutputRoot DOMAIN('JSON') TYPE Name NAME 'JSON';  
 CREATE FIELD OutputRoot.JSON.Data IDENTITY(JSON.Object)Data;  
 SET OutputRoot.JSON.Data = InputLocalEnvironment.Backup.Data;   

9.Pass Parameters to an HTTP request Node 

 OutputLocalEnvironment.Destination.HTTP.QueryString.param1 = 'param1';  

Each parameter must be individually set.

10. Convert BLOB to JSON 

 CREATE LASTCHILD OF OutputRoot DOMAIN('JSON') PARSE(InputRoot.BLOB.BLOB);  

11. Select value from an ESQL array 

This statement allows you to select the name without iterating through OutputLocalEnvironment.Variables.Person[] where the Id value matches the nameId.

 SET name = the(select item fieldvalue(r.Name) from OutputLocalEnvironment.Variables.Person[] as r where r.Id = nameId);   

You may be interested in the following posts:

Good IIB ESQL Coding Practice

Properties versus MQMD folder behavior

Create a JSON Array in IBM Integration Bus