ESQL OVERVIEW

Extended Structured Query Language (ESQL) is a programming language defined by WebSphere® Message Broker to define and manipulate data within a message flow.

ESQL is based on Structured Query Language (SQL) which is in common usage with relational databases such as DB2®. ESQL extends the constructs of the SQL language to provide support for you to work with message and database content to define the behavior of nodes in a message flow.

The ESQL code that you create to customize nodes within a message flow is defined in an ESQL file, typically named <message_flow_name>.esql, which is associated with the message flow project. You can use ESQL in the following built-in nodes:

• Compute node
• Database node
• Filter node

You can also use ESQL to create functions and procedures that you can use in the following built-in nodes:

• DataDelete node
• DataInsert node
• DataUpdate node
• Extract node
• Mapping node
• Warehouse node

To use ESQL correctly and efficiently in your message flows, you must also understand the following concepts:

• Data types
• Variables
• Field references
• Operators
• Statements
• Functions
• Procedures
• Modules

-Use the ESQL debugger, which is part of the flow debugger, to debug the code that you write. The debugger steps through ESQL code statement by statement, so that you can view and check the results of every line of code that is run.

ESQL data types

-ESQL defines the following categories of data. Each category contains one or more data types.

• Boolean
• Datetime
• Null
• Numeric
• Reference
• String

ESQL variables

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

External :

-External variables (defined with the EXTERNAL keyword) are also known as user-defined properties,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 Broker Archive editor. You can query and set the values of user-defined properties at run time by using the Configuration Manager Proxy (CMP).

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 execution group 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 broker startup.