Middleware

ESQL best practices

by Mehdi El-Filahi

ESQL is a powerfull language allowing to provide fast and flexible development into IIB/ACE/CP4I

The compute node:

When adding an ESQL Node the compute mode is a crucial choice for performance.
Different options are available:

  • Message: This option is often the most commonly used
  • LocalEnvironment: This option is usefull when you need to change the behavior of next nodes
  • LocalEnvironment and Message: This option is usefull when you need to change the behavior of next nodes with the message included
  • Exception: This option is usefull for error management only
  • Exception and Message: This option is usefull for error management only and to log the message or part of the message
  • Exception and LocalEnvironment: This option is usefull for error management only and log the local variable of nodes information
  • All: Try to avoid this option as much as possible.
  • Between Java compute node and ESQL compute node, for performance matter, try to prioritize the ESQL usage.

LocalEnvironment and Environment:

Difference between LocalEnvironment and Environment.

LocalEnvironment is an important option to help next node to know what they will have to do.
Examples:

  • To change the behavior of an HTTP Request node, i will override the variables
    • OutputLocalEnvironment.Variables.HTTP_Method
    • OutputLocalEnvironment.Destination.HTTP.RequestURL
  • To change the destination queue of an MQ Output node, i will override the variable
    • OutputLocalEnvironment.Destination.MQ.DestinationData[1].queueName
  • To change the folder and file to read of a File Input node, i will override the variables
    • OutputLocalEnvironment.Destination.File.Directory
    • OutputLocalEnvironment.Destination.File.Name

Many middleware developers are mistaking the usage of LocalEnvironment and Environment.
Never use the LocalEnvironment to store your own variable, this could lead to a possible bad behavior if the values of a known variable is changed that a node needs to use later. If this happens, the debug becomes quite diffuclt to find and you may waste hours before you find this root cause.

Things to keep in mind when developing:

  • Never use CARDINALITY on a table to check if the table is not empty for a performance matter. CARDINALTY will browse the whole table and return the size of the table. While the EXISTS function will stop at the first occurence.
  • Try to avoid browsing sequences elements with integer index and cardinality, try to user reference pointers and browse next elements with MOVE NEXTSIBLING.
  • When manipulating XML elements, try to use XMLNSC domain instead of XML or XMLNS.
    • Difference between XML, XMLNS, XMLNSC:
    • XML: XML is deprecated and should not be used anymore. It is still present for compatibility with older flows coming from WMB.
    • XMLNS: is a proframmatic parser and considers the XML message as a STRING, validation is not possible either for generation or parse. It is namespace aware. XMLNS will parse the message and will keep the information in memory, so it will use lees cpu and mode memory to handle the message. So XMLNS is more suitable if XPath access is needed.
    • XMLNSC: is the new optimzed way to treat XML messages. It is namespace aware. XMLNSC will parse only sequentially portion of message to finally create a compact tree. This is more optimized to use less memory than XMLNS but may consume a little more CPU.
  • When manipulating json elements, take care about float elements needing scientific representation or not. This must be changed in the Environment variable of the NODE/Integration server.

Shared variables and external variables:

  • A shared variable is a very usefull type of object where the variables keeps living between all the different threads of the application flow.
    • The variable keeps living until the application flow of the integration server is stopped.
  • An external variables is a final object. After assigning a value, it can not be changed anymore.
    • This variable can be usefull for security matter when the values must never be changed.
    • This variable can also be usefull to elevate a user defined property from a flow into the ESQL code.