ESQL variables overview

An ESQL variable is a data field that is used to help process a message.

You must declare a variable and state its type before you can use it. The data type of a variable is fixed; if you code ESQL that assigns a value of a different type, either an implicit cast to the data type of the target is implemented or an exception is raised (if the implicit cast is not supported).

To define a variable and give it a name, use the DECLARE statement.

The names of ESQL variables are case-sensitive; therefore, make sure that you use the correct case in all places. The simplest way to guarantee that you are using the correct case is always to define variables using uppercase names.

The IBM® Integration Toolkit marks variables that have not been defined. Remove all these warnings before deploying a message flow.

You can assign an initial value to the variable on the DECLARE statement. If an initial value is not specified, scalar variables are initialized with the special value NULL, and ROW variables are initialized to an empty state. Then, you can change the value of a variable by using the SET statement.

ESQL Variables declared at Module level 'belong' to a single node. However, variables declared at the Schema level are also given to each node that references that schema. So although variables at schema level are only declared once, each ESQL node has its own copy, which is not shared with any other node (unless the variable is marked SHARED).

Three types of built-in node can contain ESQL code and therefore support the use of ESQL variables:

Scope, lifetime, and sharing characteristics of variables

The scope, lifetime, and sharing characteristics of variables describe how widespread and for how long a particular ESQL variable is available:

Scope: Is a measure of the range over which a variable is visible. In the integration node environment, the scope of variables is typically limited to the individual node.
Lifetime: Is a measure of the time for which a variable retains its value. In the integration node environment, the lifetime of variables varies but is typically restricted to the life of a thread within a node.
Sharing characteristics: Indicate whether each thread has its own copy of a variable or whether one variable is shared between many threads. In the integration node environment, variables are typically not shared.

Types of variable

External: External variables (defined with the EXTERNAL keyword) are also known as user-defined properties, see User-defined properties in ESQL. They exist for the entire lifetime of a message flow and are visible to all messages passing through the flow. You can define external variables only at the module and schema level. You can modify the initial values of external variables (optionally set by the DECLARE statement) at design time, by using the Message Flow editor, or at deployment time, by using the BAR editor. You can query and set the values of user-defined properties at run time by using the IBM Integration API. For more information, see Setting message flow user-defined properties at run time by using a custom integration application.
Normal: Normal variables have a lifetime of just one message passing through a node. They are visible to that message only. To define normal variables, omit both the EXTERNAL and SHARED keywords.
Shared: Shared variables can be used to implement an in-memory cache in the message flow, see Optimizing message flow response times. Shared variables have a long lifetime and are visible to multiple messages passing through a flow, see Long-lived variables. Shared variables exist for the lifetime of the integration server process, the lifetime of the flow or node, or the lifetime of the node SQL that declares the variable (whichever is the shortest). Shared variables are initialized when the first message passes through the flow or node after each integration node startup.
See also the ATOMIC option of the BEGIN ... END statement. The BEGIN ATOMIC construct is useful when a number of changes must be made to a shared variable and it is important to prevent other instances seeing the intermediate states of the data.