ESQL

WebSphere MQ Integrator Broker
ESQL Reference
Version 2.1
SC34-6172-00
ESQL Reference
Version 2.1
SC34-6172-00
Note!
Before using this information and the product it supports, be sure to read the general information under Appendix D,
“Notices” on page 175.
First Edition (June 2002)

This edition applies to IBM® WebSphere® MQ Integrator Broker Version 2.1 and to all subsequent releases and
modifications until otherwise indicated in new editions.
© Copyright International Business Machines Corporation 2002. All rights reserved.
US Government Users Restricted Rights – Use, duplication or disclosure restricted by GSA ADP Schedule Contract
with IBM Corp.
Contents
Figures . . . . . . . . . . . . . . vii Finding the statement or function you need . . . 42
Using drag and drop to create ESQL . . . . . . 44
Tables . . . . . . . . . . . . . . . ix ESQL conventions . . . . . . . . . . . . 45
Case sensitivity of ESQL syntax . . . . . . 45
Comments. . . . . . . . . . . . . . 45
About this book . . . . . . . . . . . xi Special characters in ESQL . . . . . . . . 46
Who this book is for . . . . . . . . . . . xi ESQL keywords . . . . . . . . . . . . 47
What you need to know to understand this book . . xi
Terms used in this book . . . . . . . . . . xi
Chapter 4. ESQL statements . . . . . 49
ATTACH . . . . . . . . . . . . . . . 50
Part 1. Introduction to using ESQL CALL . . . . . . . . . . . . . . . . 51
with WebSphere MQ Integrator COMPOUND. . . . . . . . . . . . . . 52
CREATE FIELD . . . . . . . . . . . . . 53
Broker . . . . . . . . . . . . . . . 1
Examples of using the CREATE statement . . . 55
CREATE FUNCTION . . . . . . . . . . . 57
Chapter 1. What is ESQL . . . . . . . 3 Example . . . . . . . . . . . . . . 57
Statements . . . . . . . . . . . . . . . 3 CREATE PROCEDURE . . . . . . . . . . 58
Operators and expressions . . . . . . . . . . 4 External stored procedures . . . . . . . . 59
Functions . . . . . . . . . . . . . . . 4 Examples . . . . . . . . . . . . . . 60
Nested statements . . . . . . . . . . . . 4 DECLARE . . . . . . . . . . . . . . . 63
Accessing message fields . . . . . . . . . . 5 DELETE . . . . . . . . . . . . . . . 64
Data types . . . . . . . . . . . . . . . 5 Example . . . . . . . . . . . . . . 64
Numbers . . . . . . . . . . . . . . 6 Handling database errors . . . . . . . . . 64
Strings . . . . . . . . . . . . . . . 7 DETACH . . . . . . . . . . . . . . . 65
Datetime types . . . . . . . . . . . . 7 EVAL . . . . . . . . . . . . . . . . 66
INTERVAL . . . . . . . . . . . . . . 8 IF. . . . . . . . . . . . . . . . . . 69
BOOLEAN . . . . . . . . . . . . . 10 INSERT. . . . . . . . . . . . . . . . 70
REFERENCE . . . . . . . . . . . . . 10 Example . . . . . . . . . . . . . . 70
NULLs . . . . . . . . . . . . . . . 10 Handling database errors . . . . . . . . . 70
Operators . . . . . . . . . . . . . . . 11 MOVE . . . . . . . . . . . . . . . . 71
Arithmetic operators . . . . . . . . . . 11 Example . . . . . . . . . . . . . . 71
Logical operators . . . . . . . . . . . 12 PASSTHRU . . . . . . . . . . . . . . 73
Comparison operators . . . . . . . . . . 13 Using the drag and drop facility with
Operator precedence . . . . . . . . . . 16 PASSTHRU . . . . . . . . . . . . . 75
Principle Statement Types . . . . . . . . . 16 Considerations when calling stored procedures 75
Comparison of Filter, Compute, and Database nodes Limitations . . . . . . . . . . . . . 76
and Database ESQL statements . . . . . . . . 17 PROPAGATE . . . . . . . . . . . . . . 77
RETURN . . . . . . . . . . . . . . . 79
Chapter 2. Messages and ESQL . . . . 21 SET . . . . . . . . . . . . . . . . . 81
Referring to information in messages . . . . . . 21 THROW . . . . . . . . . . . . . . . 83
Syntax of field references . . . . . . . . . 21 UPDATE . . . . . . . . . . . . . . . 84
Correlation names in nodes that do not create an Examples . . . . . . . . . . . . . . 84
output message . . . . . . . . . . . . 23 Handling database errors . . . . . . . . . 84
Correlation names in nodes that create an output WHILE . . . . . . . . . . . . . . . . 85
message . . . . . . . . . . . . . . 24
Referencing repeating fields . . . . . . . . 25 Chapter 5. ESQL functions . . . . . . 87
Anonymous field references . . . . . . . . 27 String manipulation functions . . . . . . . . 87
Navigating a message tree anonymously using LENGTH . . . . . . . . . . . . . . 87
dynamic references . . . . . . . . . . . 28 LOWER, LCASE. . . . . . . . . . . . 88
Referencing field types . . . . . . . . . 30 LTRIM . . . . . . . . . . . . . . . 88
Referencing information in XML messages . . . 30 OVERLAY . . . . . . . . . . . . . . 88
POSITION. . . . . . . . . . . . . . 89
Part 2. Writing ESQL . . . . . . . . 39 RTRIM . . . . . . . . . . . . . . . 89
SUBSTRING . . . . . . . . . . . . . 89
TRIM . . . . . . . . . . . . . . . 90
Chapter 3. Writing ESQL . . . . . . . 41
© Copyright IBM Corp. 2002 iii
UPPER, UCASE . . . . . . . . . . . . 90 Using SELECT for simple message transformation 125
Numeric functions . . . . . . . . . . . . 90 Using SELECT for complex message
ABS . . . . . . . . . . . . . . . . 90 transformation . . . . . . . . . . . . . 128
BITAND . . . . . . . . . . . . . . 91 Column functions . . . . . . . . . . . . 131
BITNOT . . . . . . . . . . . . . . 91 Use of the ITEM keyword . . . . . . . . . 132
BITOR . . . . . . . . . . . . . . . 91 Effects of the THE keyword . . . . . . . . 132
BITXOR . . . . . . . . . . . . . . 92 Using SELECT to return a scalar value . . . . . 133
CEIL . . . . . . . . . . . . . . . 92 Multiple items in the FROM clause (Join) . . . . 133
FLOOR . . . . . . . . . . . . . . . 93 Using join to translate data . . . . . . . . . 136
MOD . . . . . . . . . . . . . . . 93 Joining items in the FROM clause . . . . . . 137
ROUND . . . . . . . . . . . . . . 93 Selecting from a list of scalars . . . . . . . . 137
SQRT . . . . . . . . . . . . . . . 93
TRUNCATE . . . . . . . . . . . . . 94 Chapter 9. Querying external
Datetime functions . . . . . . . . . . . . 94 databases . . . . . . . . . . . . . 139
EXTRACT . . . . . . . . . . . . . . 94
Examples of external database queries . . . . . 140
CURRENT_DATE . . . . . . . . . . . 94
Create a database table . . . . . . . . . 140
CURRENT_TIME . . . . . . . . . . . 95
Create a table in a case sensitive database
CURRENT_TIMESTAMP . . . . . . . . . 95
system . . . . . . . . . . . . . . 141
CURRENT_GMTDATE . . . . . . . . . 95
Use the ITEM keyword . . . . . . . . . 141
CURRENT_GMTTIME. . . . . . . . . . 95
Use the WHERE clause . . . . . . . . . 142
CURRENT_GMTTIMESTAMP . . . . . . . 95
Handling database errors . . . . . . . . . 142
LOCAL_TIMEZONE . . . . . . . . . . 96
Field functions . . . . . . . . . . . . . 96
BITSTREAM . . . . . . . . . . . . . 96 Part 3. Appendixes . . . . . . . . 145
CARDINALITY . . . . . . . . . . . . 96
FIELDNAME . . . . . . . . . . . . . 96 Appendix A. Examples . . . . . . . 147
FIELDTYPE . . . . . . . . . . . . . 97 Message used in this book . . . . . . . . . 147
LASTMOVE . . . . . . . . . . . . . 97 Using a trace to view a message structure . . . . 149
SAMEFIELD . . . . . . . . . . . . . 97 Tracing the message environment . . . . . 151
Complex functions . . . . . . . . . . . . 97 Tracing the exception list . . . . . . . . 152
CASE . . . . . . . . . . . . . . . 97 Tracing the message data . . . . . . . . 152
CAST . . . . . . . . . . . . . . . 99 How to manipulate a variable length BLOB
SELECT . . . . . . . . . . . . . . 100 message . . . . . . . . . . . . . . . 153
Database state functions . . . . . . . . . . 101 Define a new message . . . . . . . . . 153
Miscellaneous functions . . . . . . . . . . 102 Create a message flow . . . . . . . . . 153
COALESCE . . . . . . . . . . . . . 102 Exception lists . . . . . . . . . . . . . 157
NULLIF . . . . . . . . . . . . . . 102 Example of CREATE statement . . . . . . . 164
PASSTHRU . . . . . . . . . . . . . 102
UUIDASBLOB . . . . . . . . . . . . 102
Appendix B. Using the Compute node
UUIDASCHAR . . . . . . . . . . . . 103
to work with messages . . . . . . . 167
167
Copying messages between parsers . . . . . . 167
Chapter 6. Using SELECT with ROW
Like parsers . . . . . . . . . . . . . 167
and LIST constructors . . . . . . . 105 Unlike parsers . . . . . . . . . . . . 168
ROW and LIST constructors . . . . . . . . 105 Using the Compute node for data conversion . . 168
ROW constructor . . . . . . . . . . . 105 Using the Compute node for message
A LIST constructor . . . . . . . . . . 106 transformation . . . . . . . . . . . . . 170
ROW and LIST combined . . . . . . . . 106 Using tagged or delimited messages to convert
ROW and LIST comparisons . . . . . . . 107 EBCDIC NL to ASCII CR LF . . . . . . . . 170
Chapter 7. Transforming data from Appendix C. How to read syntax

one data type to another (CAST) . . . 111 diagrams . . . . . . . . . . . . . 173
The CAST function . . . . . . . . . . . 111
Source expression . . . . . . . . . . . 112
Appendix D. Notices . . . . . . . . 175
CCSID. . . . . . . . . . . . . . . 112
Trademarks . . . . . . . . . . . . . . 176
ENCODING . . . . . . . . . . . . . 112
Supported casts. . . . . . . . . . . . 112
Implicit casts . . . . . . . . . . . . 117 Glossary of terms and abbreviations 177
Data types of values from external sources . . 122
Bibliography . . . . . . . . . . . . 183
Chapter 8. Using the SELECT function 125
iv WebSphere MQ Integrator Broker ESQL Reference

WebSphere MQ Integrator Broker Version 2.1 MQSeries information available on the Internet . . 185
cross-platform publications . . . . . . . . . 183
WebSphere MQ Integrator Broker Version 2.1 Index . . . . . . . . . . . . . . . 187
platform-specific publications . . . . . . . . 183
MQSeries Everyplace publications . . . . . . 183
Sending your comments to IBM . . . 191
Softcopy books . . . . . . . . . . . . . 184
Portable Document Format (PDF) . . . . . 184
Contents v
vi WebSphere MQ Integrator Broker ESQL Reference
Figures
1. Using a dynamic reference . . . . . . . 28 10. Tree representation produced by Trace node 150
2. DocType declaration . . . . . . . . . 35 11. Exception list structure . . . . . . . . 157
3. An external stored procedure . . . . . . 60 12. Database exception list structure . . . . . 159
4. An ESQL procedure . . . . . . . . . . 61 13. Conversion exception list structure . . . . 160
5. CREATE PROCEDURE: recursive example 61 14. Parser exception list structure . . . . . . 161
6. CREATE PROCEDURE: results of recursive 15. User exception list structure. . . . . . . 162
example. . . . . . . . . . . . . . 62 16. Retrieving the exception error code . . . . 163
7. Using EVAL to translate XML attributes to tags 68 17. CREATE statement example . . . . . . 164
8. Example using the PROPAGATE statement 77 18. Message flow using tagged or delimited
9. Messages produced on OUT terminal by messages to substitute CR LF pairs for NL
PROPAGATE statement . . . . . . . . 78 characters . . . . . . . . . . . . . 170
© Copyright IBM Corp. 2002 vii

viii WebSphere MQ Integrator Broker ESQL Reference
Tables
1. Format of interval strings and qualifiers . . . 9 7. Supported CASTs . . . . . . . . . . 112
2. Results of AND and OR operations in ESQL 13 8. Implicit CASTs for comparison . . . . . . 118
3. Result of NOT operation in ESQL . . . . . 13 9. Implicit CASTs for arithmetic operations 120
4. List of syntax elements and corresponding 10. Conversions from WebSphere MQ Integrator
ESQL correlation names . . . . . . . . 32 Broker to SQL data types . . . . . . . 121
5. Finding the statement or function you need 42 11. Implicit CASTS for database data types to
6. Differences in the use of RETURN with the WebSphere MQ Integrator Broker types . . . 122
Compute, Filter, and Database nodes . . . . 79 12. How to read syntax diagrams . . . . . . 173
© Copyright IBM Corp. 2002 ix

x WebSphere MQ Integrator Broker ESQL Reference
About this book
This book describes how to use the ESQL expressions that are necessary for
configuring message nodes in WebSphere MQ Integrator Broker.
WebSphere MQ messages can be manipulated, constructed, and reformatted by

nodes in the message flow, using a specialized form of standard database
Structured Query Language (SQL). This specialized form is known as Extended
SQL, or ESQL, and supports WebSphere MQ Integrator Broker processing of the
message structure. This means that although you do not have to define the
message structure to the Control Center, you do have to understand the definition
to be able to construct valid ESQL for message manipulation.
This book is divided into parts:

Part 1, “Introduction to using ESQL with WebSphere MQ Integrator Broker” on
page 1 This section describes the basic concepts of ESQL and is intended for
people new to the language and unfamiliar with WebSphere MQ Integrator
Broker message structure.
If you have not used ESQL before, or do not have a good understanding of
message structure, you are recommended to read these chapters.
For more information about message nodes see WebSphere MQ Integrator
Broker Using the Control Center.
Part 2, “Writing ESQL” on page 39
This section describes all the statements and functions and operators that
you might need to use. It also describes how to query external databases.
A set of examples, an explanation about how to read syntax diagrams, a glossary

of terms and abbreviations, and a bibliography are provided at the end of this
book.
Who this book is for

This book is intended for anyone who wants to create or modify WebSphere MQ
Integrator Broker message flows using message nodes.
What you need to know to understand this book

You need to have read and understood the general introduction to all aspects of
MQ Integrator Broker in WebSphere MQ Integrator Broker Introduction and Planning
and WebSphere MQ Integrator Broker Using the Control Center.
Terms used in this book

All references in this book to MQ Integrator Broker are to WebSphere MQ
Integrator Broker Version 2.1, unless otherwise stated.
All references in this book to Windows NT® are also applicable to Windows® 2000
unless otherwise stated. WebSphere MQ Integrator Broker components that are
installed and operated on Windows NT can also be installed and operated on
Windows 2000.
© Copyright IBM Corp. 2002 xi

xii WebSphere MQ Integrator Broker ESQL Reference
Part 1. Introduction to using ESQL with WebSphere MQ
Integrator Broker
Chapter 1. What is ESQL. . . . . . . . . . 3
Statements . . . . . . . . . . . . . . . 3
Operators and expressions . . . . . . . . . . 4
Functions . . . . . . . . . . . . . . . 4
Nested statements . . . . . . . . . . . . 4
Accessing message fields . . . . . . . . . . 5
Data types . . . . . . . . . . . . . . . 5
Numbers . . . . . . . . . . . . . . 6
Strings . . . . . . . . . . . . . . . 7
Datetime types . . . . . . . . . . . . 7
INTERVAL . . . . . . . . . . . . . . 8
BOOLEAN . . . . . . . . . . . . . 10
REFERENCE . . . . . . . . . . . . . 10
NULLs . . . . . . . . . . . . . . . 10
Operators . . . . . . . . . . . . . . . 11
Arithmetic operators . . . . . . . . . . 11
Using arithmetic operators with datetime
functions . . . . . . . . . . . . . 11
Logical operators . . . . . . . . . . . 12
Comparison operators . . . . . . . . . . 13
Operator precedence . . . . . . . . . . 16
Principle Statement Types . . . . . . . . . 16
Comparison of Filter, Compute, and Database nodes
and Database ESQL statements . . . . . . . . 17
Chapter 2. Messages and ESQL . . . . . . . 21

Referring to information in messages . . . . . . 21
Syntax of field references . . . . . . . . . 21
Correlation names in nodes that do not create an
output message . . . . . . . . . . . . 23
Correlation names in nodes that create an output
message . . . . . . . . . . . . . . 24
Referencing repeating fields . . . . . . . . 25
If you know how many instances there are . . 25
If you do not know how many instances there
are . . . . . . . . . . . . . . . 26
Anonymous field references . . . . . . . . 27
Navigating a message tree anonymously using
dynamic references . . . . . . . . . . . 28
Referencing field types . . . . . . . . . 30
Field types in MQRFH2 headers . . . . . 30
Referencing information in XML messages . . . 30
Overview . . . . . . . . . . . . . 30
XML declaration . . . . . . . . . . . 33
XML body and empty elements . . . . . 34
DocType Declaration . . . . . . . . . 35
Constructing XML in the Compute node. . . 37
© Copyright IBM Corp. 2002 1

2 WebSphere MQ Integrator Broker ESQL Reference
Chapter 1. What is ESQL
SQL is the industry standard language for accessing and updating database data.
ESQL is a language derived from SQL Version 3 and is particularly suited to
manipulating both database and message data.
This chapter introduces the following ESQL concepts:

v “Statements”
v “Operators and expressions” on page 4
v “Functions” on page 4
v “Nested statements” on page 4
v “Accessing message fields” on page 5
v “Data types” on page 5
v “Operators” on page 11
v “NULLs” on page 10
v “Comparison of Filter, Compute, and Database nodes and Database ESQL
statements” on page 17
Statements
An ESQL program consists of a number of statements that are executed in the
order they are written. ESQL has a number of different types of statement that
perform different types of operation. All ESQL statements start with a keyword
that identifies the type of statement and end with a semicolon. As an example,
consider the following ESQL program:
DECLARE x INTEGER;
SET x = 42;
This program consists of two statements. The first starts with the keyword
DECLARE and ends at the first semicolon. The second statement starts with the
keyword SET and ends at the second semicolon. These two statements are written
on separate lines and it is conventional (but not required) that they be so. You will
notice that the language keywords are written in capital letters. This is also the
convention within this book but is not required.
The first statement declares a variable called x of type INTEGER, that is, it reserves
a space in the computer’s memory large enough to hold an integer value and
allows this space to be subsequently referred to in the program by the name x. The
second statement sets the value of the variable x to 42. A number appearing in an
ESQL program without decimal point and not within quotes is known as an
integer literal.
ESQL has a whole range of data types and each has its own way of writing literal
values. These are described in “Data types” on page 5.
For a full description of all the ESQL statements, see Chapter 4, “ESQL statements”
on page 49.

Operators and expressions
Operators and expressions

ESQL also has a set of operators that allow variables and literals to be combined
into expressions. Consider the following ESQL program:
DECLARE OuterRadius FLOAT;
DECLARE InnerRadius FLOAT;
DECLARE Length FLOAT;
DECLARE Volume FLOAT;
SET OuterRadius = 4.0;

SET InnerRadius = 2.5;
SET Length = 11.0;
SET Volume =
( (OuterRadius * OuterRadius) -
(InnerRadius * InnerRadius) ) * Length * 3.142;
Here you can see the multiply operator (*) and the subtract operator (-) being used
in conjunction with pairs of brackets to perform a calculation. ESQL is similar to
many other programming languages in the way in handles operators and
expressions. ESQL has a full set of operators; however, not all operators can be
applied to all types of data because some combinations would not lead to a
meaningful operation. See “Operators” on page 11 for information about these.
Some operators return a boolean value. An example of this is the greater than
operator (>), which compares two values and returns the boolean value TRUE if
the first is greater than the second and otherwise returns FALSE. SQL language
documentation often refers to such operators as predicates.
Functions
ESQL provides a number of predefined functions, that is, routines that calculate a
resultant value from a number of given input values. These can be used freely
within expressions. Consider the following ESQL program snippet:
SET Diameter = SQRT(Area / 3.142) * 2;
In this example you can see the function SQRT (square root) being used. It is being
given the value inside the brackets (itself the result of an expression – a divide
operation) and its result is being used in a further expression – a multiply
operation. ESQL has a comprehensive range of predefined functions. See Chapter 5,
“ESQL functions” on page 87 information about all the ESQL functions.
You can also use your own external functions and procedures.
Nested statements
Some statements allow other statements to be nested within them. Consider the
following ESQL program snippet:
IF Size > 100.00 THEN
SET X = 0;
SET Y = 0;
SET REVERSE = FALSE;
ELSE
SET X = 639;
SET Y = 479;
SET REVERSE = TRUE;
END IF;

Nested statements
In this example, you can see a single IF statement containing the optional ELSE
clause. Both the IF and ELSE portions contain three nested statements. Those
within the IF clause are executed if the operator > returns the value TRUE (that is,
if Size has a value greater than 100.00) otherwise, those within the ELSE clause are
executed.
Many statements can have expressions nested within them, but only a few can
have statements nested within them. The key difference between an expression and
a statement is that an expression calculates a value and demands that it be used
whereas a statement neither produces a value nor requires that one be used.
Accessing message fields

An important aspect of ESQL is its ability to access fields in messages. In ESQL,
message fields are accessed via field references. These have the special
characteristic that, when a field reference occurs within an expression, the value
obtained is the value of the field to which it refers (not the value of the reference
itself). Similarly when a field reference occurs as the target of a SET statement, it is
the value of the referred-to message field that is set. Consider the following ESQL
program:
DECLARE Priority INTEGER;
SET Priority = InputRoot.MQMD.Priority;
This program declares an integer variable called Priority and then sets its value to
the value of the Priority field of an MQMD structure within an input message by
using the field reference literal InputRoot.MQMD.Priority. Similarly the ESQL
program:
SET OutputRoot.MQMD.Priority = 7;
sets the value of the Priority Field of an MQMD structure within an output
message to the integer value 7.
The references above are field reference literals. Field references can also exist as
declared variables. Consider the following ESQL program:
DECLARE Priority REFERENCE TO OutputRoot.MQMD.Priority;
SET Priority = 7;
This also sets the Priority Field of an MQMD structure within an output message
to 7. In this case, Priority is a reference variable that has been initialized to point to
a particular message field. Reference variables can also use literal offsets as shown
in the following example:
DECLARE Mqmd REFERENCE TO OutputRoot.MQMD;
SET Mqmd.Priority = 7;
For more information about field references, see “Referring to information in

messages” on page 21.
Data types
Within a broker, the fields of a message contain data that has a definite data type.
It is also possible to use intermediate variables to help process a message. All such
variables must be declared with a data type before use. Fields and declared
variables differ in that a variable’s data type is fixed, and attempts to assign values
of a different type will result in either implicit cast or an exception. Message fields
on the other hand do not have a fixed data type, and values of a different type can
be assigned. The field simply adopts the new value and type.
Chapter 1. What is ESQL 5

Data types
It is not always possible to predict the data type that will result from evaluating an
expression. This is because expressions are "compiled" without reference to any
kind of message schema, and so some type errors are not caught until runtime.
The following sections list all the data types used with ESQL.
Numbers
The INTEGER, FLOAT, and DECIMAL data types are collectively known as
numeric data types.
INTEGER, INT
The INTEGER data type holds an integer number in 64-bit two’s
complement form. This gives a range of values between
-9223372036854775808 and +9223372036854775807. In addition to the
normal integer literal format, integer literals can be written in hexadecimal
notation, for example 0x1234abcd.
The hexadecimal letters A to F can be written in uppercase or lowercase, as
can the 'x' after the initial zero.
Note: If a literal of this form is too large to be represented as an integer, it

is represented as a decimal.
FLOAT
The FLOAT data type holds a 64 bit exponent approximation of a real
number. Float literals are defined using scientific notation, for example
6.6260755e-34.
"e" identifies the exponent of the number (10 to the power of). The case of
the "e" is not significant so "E" can be used instead if preferred.
DECIMAL
The DECIMAL data type holds an exact representation of a decimal
number. Decimals have a precision and scale. Precision is the total number
of digits of a number, and scale is the number of digits to the right of the
decimal point.
v The minimum precision is 1
v The maximum precision is 31
v The minimum scale is 0
v The maximum scale is 30
You cannot define precision and scale when declaring a DECIMAL,
because they are assigned automatically. It is only possible to specify
precision and scale when casting to a DECIMAL. Decimal literals are
defined using normal mathematical representation, for example 1234.56.
When casting to DECIMAL:
v If the precision and scale are not specified, the output defaults to the
precision and scale of the input.
v If the precision and scale are specified, but precision is too small for
input, a runtime error is generated.
v If the precision and scale are specified, but scale is smaller than input,
truncation occurs.
For information about numeric functions, see “Numeric functions” on page 90.

Data types
Strings
The CHARACTER, BLOB, and BIT data types are collectively known as string data
types.
CHARACTER, CHAR
The character data type holds a variable length string of Unicode
characters. A character string literal consists of any number of characters in
single quotes. If you want to include a single quote within a character
string literal, you must use another single quote as an escape character.
For example, the assignment SET X=’he’’was’’’ puts the value he’was’
into X.
BLOB The BLOB data type holds a variable length string of 8-bit bytes. It is
commonly used to represent arbitrary binary data. A byte string literal
consists of the letter X followed by a string of hexadecimal digits enclosed
in single quotes, as in the following example:
SET X = X'0123456789ABCDEF'
There must be an even number of digits in the string, because two digits
are required to define each byte. Each digit can be one of the hexadecimal
digits. Both the initial X and the hexadecimal letters can be specified in
uppercase or lowercase.
BIT The BIT data type holds a variable length string of binary digits. It is
commonly used to represent arbitrary binary data that does not contain an
exact number of bytes. A bit string literal consists of the letter B followed
by a string of binary digits enclosed in single quotes, as in the following
example:
SET X=B'0100101001'
Any number of digits, which must be either 0 or 1, can be specified. The

initial B can be specified in uppercase or lowercase.
For information about string functions, see “String manipulation functions” on

page 87.
Datetime types
The DATE, TIME, GMTTIME, TIMESTAMP, GMTTIMESTAMP, and INTERVAL data types are
collectively known as datetime data types.
DATE The DATE datatype holds a Gregorian calender date (year, month, and
day). The format of a DATE literal is the word DATE followed by a space,
followed by a date in single quotes in the form ’yyyy-mm-dd’. For
example:
DECLARE MyDate DATE;
SET MyDate = DATE '2000-02-29';
Leading zeroes in the year, month, and day must not be omitted.
TIME The TIME data type holds a time of day in hours, minutes, seconds, and
fractions of a second. The format of a TIME literal is the word TIME
followed by a space, followed by a time in single quotes in the form
’hh:mm:ss.ffffff’. For example:
DECLARE MyTime TIME;
SET MyTime = TIME '11:49:23.656';

Data types
Each of the hour, minute, and second fields in a TIME literal must always
be two digits with the exception of the optional fractional seconds field
that, if present, can be up to 6 digits in length.
GMTTIME
The GMTTIME data type is similar to the TIME data type , except that its
values are interpreted as values in Greenwich Mean Time. GMTTIME literals
are defined in a similar way to TIME values. For example:
DECLARE GetGmttime GMTTIME;
SET GetGmttime = GMTTIME '12:00:00';
TIMESTAMP
The TIMESTAMP data type holds a DATE and a TIME. The format of a
TIMESTAMP literal is the word TIMESTAMP followed by a space, followed by a
timestamp in single quotes in the form ’yyyy-mm-dd hh:mm:ss.ffffff’. For
example:
DECLARE MyTimeStamp TIMESTAMP;
SET MyTimeStamp = TIMESTAMP '1999-12-31 23:59:59';
Each of the hour, minute and second fields in a TIMESTAMP literal must
always be two digits. The exception is the optional fractional seconds field
which, if present, can be up to 6 digits in length.
GMTTIMESTAMP
The GMTTIMESTAMP data type is similar to the TIMESTAMP data type, except
that the values are interpreted as values in Greenwich Mean Time.
GMTTIMESTAMP values are defined in a similar way to TIMESTAMP values, for
example:
DECLARE GetGMTTimeStamp GMTTIMESTAMP;
SET GetGMTTimeStamp = GMTTIMESTAMP '1999-12-31 23:59:59.999999';
For information about datetime functions, see “Datetime functions” on page 94.
INTERVAL
The INTERVAL data type holds an interval of time. There are two kinds of INTERVAL
values:
v One that is specified in years and months.
v One that is specified in days, hours, minutes and seconds (including fractions of
a second).
The split between months and days arises because the number of days in each
month varies. An interval of one month and a day is not really meaningful, and
certainly cannot be sensibly converted into an equivalent interval in numbers of
days only.
An interval literal is defined by the following syntax:

INTERVAL <interval string> <interval qualifier>

Data types
The format of interval string and interval qualifier are defined by the following
table:
Table 1. Format of interval strings and qualifiers
Interval qualifier Interval string format Example
YEAR '<year>' or '<sign> <year>' '10' or '-10'
YEAR TO MONTH '<year>-<month>' or '<sign> <year>-<month>' '2-06' or '- 2-06'
MONTH '<month>' or '<sign> <month>' '18' or '-18'
DAY '<day>' or '<sign> <day>' '30' or '-30'
DAY TO HOUR '<day> <hour>' or '<sign> <day> <hour>' '1 02' or '-1 02'
DAY TO MINUTE '<day> <hour>:<minute>' or '<sign> <day> '1 02:30' or '-1 02:30'
<hour>:<minute>'
DAY TO SECOND '<day> <hour>:<minute>:<second>' or '<sign> '1 02:30:15' or '-1 02:30:15.333'
<day> <hour>:<minute>:<second>'
HOUR '<hour>' or '<sign> <hour>' '24' or '-24'
HOUR TO MINUTE '<hour>:<minute>' or '<sign> <hour>:<minute>' '1:30' or '-1:30'
HOUR TO SECOND '<hour>:<minute>:<second>' or '<sign> '1:29:59' or '-1:29:59.333'
<hour>:<minute>:<second>'
MINUTE '<minute>' or '<sign> <minute>' '90' or '-90'
MINUTE TO SECOND '<minute>:<second>' or '<sign> '89:59' or '-89:59'
<minute>:<second>'
SECOND '<second>' or '<sign> <second>' '15' or '-15.7'
Where an interval contains both a year and a month value, a hyphen is used
between the two values. In this instance, the month value must be within the
range [0, 11]. If an interval contains a month value, and no year value, the month
value is unconstrained.
A space is used to separate days from the rest of the interval.
If an interval contains more than one of HOUR, MINUTE, and SECOND, a colon is
needed between the values. The values of these fields are constrained as follows:
Field Valid values
HOUR 0-23
MINUTE 0-59
SECOND 0-59.999...
Some examples of valid interval values are:

v 72 hours
v 3 days: 23 hours
v 3600 seconds
v 90 minutes: 5 seconds
Some examples of invalid interval values are:

v 3 days: 36 hours
A day field is specified, so the hours field is constrained to [0,23].
v 1 hour: 90 minutes
An hour field is specified, so minutes are constrained to [0,59].

Data types
Here are some examples of interval literals:
INTERVAL '1' HOUR
INTERVAL '90' MINUTE
INTERVAL '1-06' YEAR TO MONTH
BOOLEAN
The BOOLEAN data type holds a Boolean value. It can have the values:
v TRUE
v FALSE
v UNKNOWN. Refer to “IF” on page 69 for information about UNKNOWN.
Boolean literals consist of the keywords TRUE, FALSE, and UNKNOWN. They can
appear in uppercase or lowercase.
REFERENCE
The REFERENCE data type holds the location of a field in a message. A reference
value can point to a declared variable; it can not point to a constant, a database
table, or a database column. A reference literal is an hierarchic path name
consisting of a period (.) separated list of path elements. The first element in the
list is known as a correlation name and identifies the message tree to be accessed
(and in some cases also identifies a position within it). The subsequent elements
identify field type, names and indexes within the message tree.
For example:
InputRoot.MQMD.Priority
is a field reference literal that refers to the Priority field contained within an
MQMD structure within an input message. Individual names can be enclosed
within double quotes to allow names containing blank spaces or primary SQL
keywords to be used. For example :
Environment.Variables."State Data"."Return"
Path elements can also contain a type and an index. These are contained within
round and square brackets respectively. For example, the path:
InputRoot.XML.Data.(XML.Element)Invoice[3].(XML.Attribute)Period
identifies an XML attribute called Period within the third XML tag called Invoice
(indexes start at 1) within the first field (of any type) called Data within the first
field (again of any type) called XML.
NULLs
All ESQL data types (except REFERENCE) support the concept of the null value. A
value of null means that the value is unknown, undefined, or uninitialized. Null
values can arise by referencing message fields that do not exist, accessing database
columns for which no data has ever been supplied, or by using the keyword
NULL, which supplies a null literal value. Note that NULL is a distinct state and is
not the same as any other value. In particular, for integers it is not the same thing
as the value 0 and for character variables it is not the same thing as a string of
zero characters. The rules of ESQL arithmetic fully take null values into account
and users are usually unaware of their existence. Generally (but not always) these
rules mean that if any operand is NULL the result is NULL. This is described in
more detail in later in the book.

Operators
Operators
Arithmetic operators
ESQL provides the following numeric operators:
Unary Operator -
The result is the negation of the operand (that is, it has the same
magnitude as the operand but the opposite sign). It is possible to negate
both numeric values (INTEGER, DECIMAL and FLOAT) and intervals
(INTERVAL).
Operator +
The result is the sum of the two operands. It is possible to add numeric
values, to add intervals and also to add an interval to a datetime value
(DATE, TIME, TIMESTAMP, GMTTIME, and GMTTIMESTAMP).
Operator -
The result is the difference of the two operands. It is possible to:
v Subtract one numeric value from another
v Subtract one datetime from another. The result is an interval.
v Subtract one interval from another
v Subtract an interval from a datetime value
Operator *
The result is the product of the two operands. It is possible to multiply
numeric values and also to multiply an interval by a numeric value.
Operator /
The result is the dividend of the two operands. It is possible to divide
numeric values and also to divide an interval by a numeric value.
Operator ||
The result is the concatenation of the two operands. It is possible to
concatenate string values (CHARACTER, BIT and BLOB).
In most cases, if either operand is NULL, the result is NULL. However, in cases
using the || (concatenation) operator, if a valid string has NULL concatenated to
it, the result is the original string. Note that in cases where the operands are
different types, special rules apply. These are described in “Implicit casts” on
page 117.
Using arithmetic operators with datetime functions

Adding an interval to a datetime value: The simplest operation you can perform
is to add an interval to, or subtract an interval from, a datetime value. For
example, you could write the following expressions:
DATE '2000-03-29' + INTERVAL '1' MONTH
TIMESTAMP '1999-12-31 23:59:59' + INTERVAL '1' SECOND
Adding or subtracting two intervals: Two interval values can be combined using
addition or subtraction. The two interval values must be of compatible types. For
example, it is not valid to add a year-month interval to a day-second interval. So
the following example is not valid:
INTERVAL '1-06' YEAR TO MONTH + INTERVAL '20' DAY

Operators
The interval qualifier of the resultant interval is sufficient to encompass all of the
fields present in the two operand intervals. For example:
INTERVAL '2 01' DAY TO HOUR + INTERVAL '123:59' MINUTE TO SECOND
would result in an interval with qualifier DAY TO SECOND, because both day and
second fields are present in at least one of the operand values.
Subtracting two datetime values: Two datetime values can be subtracted to

return an interval. In order to do this an interval qualifier must be given in the
expression to indicate what precision the result should be returned in. For
example:
(CURRENT_DATE - DATE '1776-07-04') DAY
would return the number of days since the 4th July 1776, whereas:
(CURRENT_TIME - TIME '00:00:00') MINUTE TO SECOND
would return the age of the day in minutes and seconds.
Scaling intervals: An interval value can be multiplied by or divided by an integer

factor:
INTERVAL '2:30' MINUTE TO SECOND / 4
Logical operators
ESQL provides the following logical operators:
Operator AND
The result is the logical AND of the two operands. Both operands must be
boolean values.
Operator OR
The result is the logical OR of the two operands. Both operands must be
boolean values.
Operator NOT
The result is the logical NOT of the operand, which must be a boolean
value.
NULL and UNKNOWN values are handled specially by these operators. The
principles are:
v NULL and UNKNOWN are treated the same.
v If an operand is NULL the result will be NULL unless the operation result is
already dictated by the other parameter.

Logical operators
The full behavior is defined by the following tables:
Table 2. Results of AND and OR operations in ESQL
Value of P Value of Q Result of P AND Q Result of P OR Q
TRUE TRUE TRUE TRUE
TRUE FALSE FALSE TRUE
TRUE UNKNOWN UNKNOWN TRUE
FALSE TRUE FALSE TRUE
FALSE FALSE FALSE FALSE
FALSE UNKNOWN FALSE UNKNOWN
UNKNOWN TRUE UNKNOWN TRUE
UNKNOWN FALSE FALSE UNKNOWN
UNKNOWN UNKNOWN UNKNOWN UNKNOWN
Table 3. Result of NOT operation in ESQL

Operand Result of NOT
TRUE FALSE
FALSE TRUE
UNKNOWN UNKNOWN
Comparison operators
ESQL provides the following comparison operators (predicates):
Operator >
The result is TRUE if the value of the first operand is greater than that of
the second. Otherwise the result is FALSE (but see the note on NULL
values below).
Operator <
The result is TRUE if the value of the first operand is less than that of the
second. Otherwise the result is FALSE (but see the note on NULL values
below).
Operator >=
The result is TRUE if the value of the first operand is greater than or is
equal to that of the second. Otherwise the result is FALSE (but see the note
on NULL values below).
Operator <=
The result is TRUE if the value of the first operand is less than or is equal
to that of the second. Otherwise the result is FALSE (but see the note on
NULL values below).
Operator =
The result is TRUE if the value of the first operand is equal to that of the
second. Otherwise the result is FALSE (but see the note on NULL values
below).
Operator <>
The result is TRUE if the value of the first operand is not equal to that of
the second. Otherwise the result is FALSE (but see the note on NULL
values below).

Operator BETWEEN
The operator BETWEEN allows you to test whether a value or expression
is between two different values.
BETWEEN operator
ASYMMETRIC
expression BETWEEN endpoint-1 AND endpoint-2
SYMMETRIC
The default form of this operator is known as asymmetric. This requires

you to specify the smallest endpoint value first, followed by the largest.
(This is equivalent to specifying the ASYMMETRIC keyword).
Alternatively, you can specify the optional SYMMETRIC keyword after
BETWEEN. If you do this, the order in which you specify the two
endpoint values is not significant. For example, the following two
expressions are identical:
2 BETWEEN SYMMETRIC 1 AND 3
2 BETWEEN SYMMETRIC 3 AND 1
Both expressions return the value TRUE.
In all the above cases, if any operand is NULL, the result is UNKNOWN (that is, a
NULL value of type BOOLEAN). Note that in cases where the operands are
different types, special rules apply. These are described in “Implicit casts” on
page 117
Operator IN
The operator IN allows you to test whether a value is equal to one of a list
of values.
IN operator
operand-1 IN ( operand-2 )
The result is TRUE if the left operand is not NULL and is equal to one of
the right operands. The result is FALSE if the left operand is not NULL
and is not equal to any of the right operands none of which have NULL
values. Otherwise the result is UNKNOWN. Note that in cases where the
operands are different types, special rules apply. These are described in
“Implicit casts” on page 117
Operator LIKE
The operator LIKE searches for strings that have a certain pattern. The
standard LIKE predicate for performing simple string-pattern matching is
supported.
The pattern is specified by a string in which the percent (%) and
underscore (_) characters can be used to have special meaning:

v The underscore character _ represents any single character.
For example, the following finds matches for ‘IBM’ and for ‘IGI’, but not
for ‘International Business Machines’ or ‘IBM Corp’:
Body.Trade.Company LIKE ‘I__’
v The percent character % represents a string of zero or more characters.
For example, the following finds matches for ‘IBM’, ‘IGI’, ‘International
Business Machines’, and ‘IBM Corp’:
Body.Trade.Company LIKE ‘I%’
If you want to use the percent and underscore characters within the
expressions that are to be matched, you must precede these with an
ESCAPE character, which defaults to the backslash (\) character.
For example, the following predicate finds a match for ‘IBM_Corp’.

Body.Trade.Company LIKE ‘IBM\_Corp’
You can specify a different escape character by using the ESCAPE clause
on the LIKE predicate. For example, you could also specify the previous
example like this:
Body.Trade.Company LIKE ‘IBM$_Corp’ ESCAPE ‘$’
Operator IS
The primary purpose of the operator IS is to allow you to test whether a
value is NULL. Note that the comparison operator (=) does not allow this
because the result of comparing with NULL is NULL. It also allows you to
use a more natural English syntax when testing boolean values.
IS operator
TRUE
Operand IS FALSE
NOT UNKNOWN
NULL
The result is TRUE if the value of the left operand is equal (or not equal if
the NOT clause is present) to the specified value (TRUE, FALSE,
UNKNOWN or NULL).
Operator EXISTS
EXISTS(SELECT * FROM something WHERE predicate)
The EXISTS function returns a boolean value indicating whether a SELECT

function returned one or more values (TRUE) or none (FALSE).

Operator precedence
Operator precedence
When an expression involves more than one operator, the order in which the
expression is evaluated might affect the result. Consider the following example:
SET a = b + c * d;
ESQL’s precedence rules require that c is multiplied by d and this result is added
then to b. This rule says that multiplication takes precedence over addition and
thus reordering the expression as follows:
SET a = c * d + b;
would make no difference. ESQL’s precedence rules are set out below but it is
generally considered good practice to use brackets to make the meaning clear. The
order of precedence is:
1. Brackets
2. Unary operators (unary - and NOT)
3. Multiplication and division
4. Concatenation
5. Addition and subtraction
Operations at the same level are evaluated working from left to right.
Principle Statement Types

This table summarizes the different statement types available in ESQL and what
they do. For more details, see Chapter 4, “ESQL statements” on page 49.
Statement type Description

Basic statements:
DECLARE Creates a local variable.
SET Assigns a value to a local variable, message
field, or an attribute of a field.
IF Tests a condition and executes one set of
statements or another.
WHILE Repeatedly executes one set of statements as
long as a condition is true.
Message tree manipulation statements:
MOVE Moves a REFERENCE variable around a
message tree or to variables.
CREATE Creates a new field in a message.
DETACH Detaches a sub-tree from a message.
ATTACH Attaches a detached sub-tree to a message.
Database update statements:
INSERT Inserts a new row into a database.
DELETE Deletes a row from a database.
UPDATE Changes the contents of an existing database
row.
PASSTHRU Passes a given ESQL string to a database.
Node interaction statements:

Principle Statement Types
PROPAGATE Propagates a message to any nodes attached
to the node’s output terminal.
RETURN Ends ESQL execution and returns a value to
the node, depending on the type of node.
THROW Ends ESQL execution and ends the
transaction.
Other statements:
EVAL Executes a given ESQL string.
Comparison of Filter, Compute, and Database nodes and Database

ESQL statements
All ESQL constructs (statements, operators, and functions) can be used in all of the
three principal node types (Filter, Compute, and Database). All constructs have
exactly the same effect regardless of which type of node they are used in although
there are exceptions dictated by the differing nature of the nodes (described
below).
The two key differences between Filter, Compute, and Database nodes are:
v The number of output terminals they have.
v Whether they propagate their input message to their output terminals or create a
new message and propagate that message.
More specifically:
v The Filter node is a message flow path decision node that cannot update the
WebSphere MQ Integrator Broker message but can update databases.
v The Compute node can update the WebSphere MQ Integrator Broker message
and update databases.
v The Database node cannot update the WebSphere MQ Integrator Broker message
but can update databases.
The database node is the simplest node. It does not create a new message and has
one output terminal only. The operations it can perform are thus somewhat
limited. It can:
v Update a database.
v Update the Environment or LocalEnvironment message trees.
v Propagate the input message to its output terminal.
The following example uses the database node to INSERT, UPDATE, and DELETE.
IF Root.XML.TestCase.Action = ’INSERT’ THEN
INSERT INTO Database.STOCK (STOCK_ID, STOCK_DESC, STOCK_QTY_HELD,
BROKER_BUY_PRICE, BROKER_SELL_PRICE, STOCK_HIGH_PRICE, STOCK_HIGH_DATE,
STOCK_HIGH_TIME) VALUES
(CAST(Root.XML.TestCase.stock_id AS INTEGER),
Root.XML.TestCase.stock_desc,
CAST(Root.XML.TestCase.stock_qty_held AS DECIMAL),
CAST(Root.XML.TestCase.broker_buy_price AS DECIMAL),
CAST(Root.XML.TestCase.broker_sell_price AS DECIMAL),
Root.XML.TestCase.stock_high_price,
CURRENT_DATE,
CURRENT_TIME);
ELSE
IF Root.XML.TestCase.Action = ’DELETE’ THEN
DELETE FROM Database.STOCK WHERE STOCK.STOCK_ID =
CAST(Root.XML.TestCase.stock_id AS INTEGER);

ELSE
IF Root.XML.TestCase.Action = ’UPDATE’ THEN
UPDATE Database.STOCK as A SET STOCK_DESC = Root.XML.TestCase.stock_desc
WHERE A.STOCK_ID = CAST(Root.XML.TestCase.stock_id AS INTEGER);
END IF;
END IF;
END IF;

The Compute node is more complex. It also has one output terminal only but
creates one or more new messages for propagation. The operations it can perform
are:
v Propagate the input message to its output terminal.
v Create a new message and propagate it to its output terminal .
The filter node is also more complex than the database node but in a different way
to the Compute node. It does not create a new message but has four output
terminals. The operations it can perform are:
v Propagate the incoming message to one of three output terminals.
There are two statements that have different effects when used in the different
node types. The first is the RETURN statement. In all the node types, it is expected
that ESQL execution is ended by a RETURN statement. There might be one
RETURN statement as the very last statement or there might be several RETURN
statements, each of which is reached only under particular conditions (for example,
they are nested inside IF statements). The value returned affects message
propagation to further nodes. In the case of the Database and Compute nodes, the
message is propagated to the output terminal only if the value returned is TRUE.
In the filter node, however, it is propagated to the ″true″ terminal if the return
value is TRUE, to the ″false″ terminal if it is FALSE, and to the ″unknown″
terminal if it is UNKNOWN. In this respect, the behavior of the Database and
Compute nodes is a subset of the behavior of the filter node: their ″out″ terminals
are equivalent to the filter node’s ″true″ terminal. Refer to “RETURN” on page 79
for detailed information about the RETURN statement.
WebSphere MQ Integrator Broker provides a THROW statement to create

exceptions. The THROW statement, described in “THROW” on page 83, will throw
a user exception.
If you decide to use these functions to check the success or failure of the database
calls you make, you must analyze and handle any errors that arise. If you do not
do this when an error occurs, the database might be in an indeterminate state. To
back out a database transaction, use the THROW statement with the exception
type USER.
There is some special handling of return values:

v In all cases if there is no RETURN statement at the end of the ESQL a return
value of TRUE is assumed.
v In the filter node only, the ESQL can consist of an expression only. The behavior
is as if the ESQL read RETURN expression.
You are recommended, however, to always have an explicit RETURN statement as
the last statement of an ESQL program.
The second statement that has a different effect when used in the different node
types is the PROPAGATE statement. In Compute node, PROPAGATE causes the
output message to be propagated to further nodes and the message tree to be
cleared ready for reuse. This allows a Compute node to generate and propagate
multiple output messages from one input message. When using PROPAGATE

statements in this way, it is likely you will want to return FALSE to inhibit the
automatic propagation that occurs when ESQL processing ends. In the case of
database and filter nodes the effect of the propagate statement is not defined and
should not be used (in a future release a meaning might be assigned to propagate
for these nodes).

Chapter 2. Messages and ESQL
WebSphere MQ Integrator Broker provides a message brokering function that can
transform messages from one format to another. The brokers that manage these
transformations need to interpret the structure and content of the messages they
receive to perform the full range of transformation functions available with
WebSphere MQ Integrator Broker.
This chapter describes:

v “Referring to information in messages”
Referring to information in messages

To access data in any of the four trees (message, environment, local environment,
or exception list), you use field references. These tell the parser how to navigate
the tree to the required element of data. In its simplest form, a field reference is a
sequence of identifiers, separated by periods.
Correlation names are used in field references to describe standard parts of the tree
formats. The correlation names used differ depending on whether the processing of
the node creates an output message. For example, a Trace node does not alter the
content of the message as it passes through the node, but a Compute node might
construct a new output message.
Syntax of field references

The full syntax for field references is as shown below:
CorrelationName
.
PathElement
PathElement:
( TypeExpression ) NameIdentifier [ ]
{ NameExpression } IndexExpression
* <
IndexExpression
>
IndexExpression
LAST
[<] and [>] are legitimate. An example using < and > is shown on page 23.
A field reference consists of a correlation name followed by zero or more path

elements separated by dots (″.″). The correlation name identifies a well-known
starting point in a message tree. The starting point must therefore be either a

Field references
declared reference variable or one of the predefined start points, for example
InputRoot. The path elements define a path from the start point to the desired
field. For example:
InputRoot.XML.Data.Invoice
This example causes the broker to start at the location InputRoot (that is, the root
of the input message to a Compute node) and then to perform a sequence of
navigations. Firstly, it navigates from root to the first child field ″XML″, then to the
first child field of the ″XML″ field ″Data″. Finally, the broker navigates to the first
child field of the ″Data″ field ″Invoice″. Whenever this field reference occurs in an
ESQL program, the invoice field is accessed.
This form of field reference is simple, convenient, and is the most commonly used.
However, it does have two limitations. Firstly, because the names used must be
valid ESQL identifiers, you can use names that conform to the rules of ESQL only.
That is, the names can contain alphanumeric characters including underscore only,
the first character can not be numeric and names must be at least one character
long. You can avoid this limitation by enclosing such names in double quotes. For
example:
InputRoot.XML."Customer Data".Invoice
If you need to refer to fields that contain quotation marks, you must use two pairs
of quotation marks around the reference. For example:
Body.Message."""hello"""
The second limitation is that, because the names of the fields appear in the ESQL
program, they have to be known when the program is written. This limitation can
be avoided by using the alternate syntax that uses braces ( ″{ ... }″ ). This syntax
allows you to use any expression that returns a non-null value of type character.
For example:
InputRoot.XML."Customer Data".{’Customer-’ || CurrentCustomer}.Invoice
In this example, the invoices are contained in a folder with a name is formed by
concatenating the character literal ″Customer-″ with the value in
″CurrentCustomer″ (which must be a declared variable of type character).
Enclosing anything in double quotes in ESQL makes it an identifier. Conversely,
enclosing anything is single quotes makes it a character literal.
Each element of a field reference can contain an index clause. These are denoted by
square brackets ( ″[ ... ]″ ) and accept any expression which returns a non-null
value of type integer. This clause identifies which of several fields with the same
name is to be selected. Fields are effectively numbered from the first starting at
one. If this clause is not present, it is assumed that the first field is required. Thus,
the two examples below have exactly the same meaning:
InputRoot.XML.Data[1].Invoice
InputRoot.XML.Data.Invoice[1]
This construct is most commonly used with an index variable so that a loop steps
though all such fields in sequence. For example:
WHILE count < 32 DO
SET TOTAL = TOTAL + InputRoot.XML.Data.Invoice[count].Amount;
END WHILE;

Field references
However, use this kind of construct with care because it implies that the broker
must count the fields from the beginning each time round the loop. If the repeat
count is large, performance is poor. In such cases, a better alternative is to use a
field reference variable.
Index expressions can optionally be preceded by a less than sign ( ″<″ ) indicating
that the required field is to be indexed from the last field, not the first. In this case,
the index 1 refers to the last element and the index 2 refers to the penultimate
element. For completeness a greater than sign can be used to indicate counting
from the first field. For example, if there are four fields called ″Invoice″:
InputRoot.XML.Data.Invoice -- Selects the first
InputRoot.XML.Data.Invoice[1] -- Selects the first
InputRoot.XML.Data.Invoice[>] -- Selects the first
InputRoot.XML.Data.Invoice[>1] -- Selects the first
InputRoot.XML.Data.Invoice[>2] -- Selects the second
InputRoot.XML.Data.Invoice[<] -- Selects the fourth
InputRoot.XML.Data.Invoice[<1] -- Selects the fourth
InputRoot.XML.Data.Invoice[<2] -- Selects the third
InputRoot.XML.Data.Invoice[<3] -- Selects the second
An index clause can also consist of an empty pair of square brackets ( ″[ ]″ ). This
indicates that all fields with matching names are to be selected. This construct is
used with functions that expect lists (for example, SELECT or CARDINALITY).
For backwards compatibility the LAST keyword is still supported.
Finally, each element of a field reference can contain a type clause. These are
denoted by round brackets ( ″( )″ ) and accept any expression that returns a
non-null value of type integer. The presence of a type expression restricts the fields
that are selected to those of the matching type. This construct is most commonly
used with generic XML where there are many element types and it is possible for
one XML element to contain both attributes and further XML elements with the
same name. For example:
<Item Value = ’1234’ >
<Value>5678</Value>
</Item>
In this example, the XML element ″Item″ has two child elements both called
″Value″. They can be distinguished by using type clauses:
Item.(XML.Attribute)Value -- Selects the attribute
Item.{0x03000000)Value -- Selects the attribute
Item.(XML.Element)Value -- Selects the Element
In these examples the constants are used but you are not restricted to using
constants.
Correlation names in nodes that do not create an output

message
The Compute node only creates an outgoing message. This means that any ESQL
expression written within most nodes only has to reference one message (the input
message), and in these circumstances the correlation names that can be used are as
follows:
Root Identifies the root of the message passing through the node.
Body Identifies the last child of the root of the message, that is the “body” of the
message. This is an alias for Root.*[LAST].
Chapter 2. Messages and ESQL 23

Correlation names
See “Anonymous field references” on page 27 for a description of how to
use *.
DestinationList
Identifies the structure that contains the current local environment
variables available to the node. Now known as the LocalEnvironment,
although the DestinationList correlation name can still be used.
Properties
Identifies the standard properties of the input message.
ExceptionList
Identifies the structure that contains the current exception list that the node
has access to.
Environment
Identifies the structure that contains the current global environment
variables available to the node. Environment can be read and updated
from any ESQL-supported node.
LocalEnvironment
Identifies the structure that contains the current local environment
variables available to the node.
Correlation names in nodes that create an output message

For a Compute node the correlation names are different because there are two
messages (trees) involved – the input message and the output message. The
correlation names in ESQL within these nodes are:
Environment
Identifies the structure that contains the current global environment
variables available to the node. Environment can be read and updated
from any ESQL-supported node.
InputRoot
Identifies the root of the input message.
InputBody
Identifies the “body” of the input message. This is an alias for
InputRoot.*[LAST].
See “Anonymous field references” on page 27 for a description of how to
use *.
InputProperties
Identifies the standard properties of the input message.
InputDestinationList
Now known as the InputLocalEnvironment, although the
InputDestinationList correlation name can still be used.
InputExceptionList
Identifies the structure that contains the exception list for the message
passing through the node.
InputLocalEnvironment
Identifies the structure that contains the local environment variables for the
message passing through the node.
OutputRoot
Identifies the root of the output message.

Correlation names
OutputDestinationList
Now known as the OutputLocalEnvironment, although the
OutputDestinationList correlation name can still be used. See
OutputLocalEnvironment for details.
OutputExceptionList
Identifies the structure that contains the exception list that the node is
generating.
While this correlation name is always valid, it only has meaning when the
“Compute Mode” property of the Compute node indicates that the
Compute node is propagating the exception list.
In a Compute node, there is no correlation name “OutputBody”. New
correlation names can be introduced by SELECT expressions, quantified
predicates, and FOR statements. You can create non-correlation names in a
node by using reference variables.
OutputLocalEnvironment
Identifies the structure that contains the local environment variables being
sent out from the node.
While this correlation name is always valid, it only has meaning when the
“Compute Mode” property of the Compute node indicates that the
Compute node is propagating the destination list.
Referencing repeating fields

If you are passing a message with several repeats of an element through a message
flow and you want to delete some of the repeats, you should be aware that the
numbering of the repeats is reordered after each delete. For example, if you have a
message with five repeats of a particular element and in the message flow you had
the following ESQL:
SET OutputRoot.MRM.e_PersonName[1] = NULL;
SET OutputRoot.MRM.e_PersonName[4] = NULL;
You might expect elements 1 and 4 to be deleted. However, because repeating

elements are stored on a stack, when you delete one the one above it takes its
place. This means that in the above example, elements 1 and 5 are deleted. To
avoid this problem, you are recommended to do deletes in reverse order, that is
delete element 4 first, then delete element 1.
If you know how many instances there are

If you know how many instances there are of a repeating field, and you want to
access a specific instance of such a field, you can use an array index as part of a
field reference. For example, if you wanted to filter on the first line of an address,
to expedite the delivery of an order, you could write an expression such as:
Body.Invoice.Customer.InvoiceAddress.Address[1] = 'Patent Office'
The array index [1] indicates that it is the first instance of the repeating field that
you are interested in (array indices start at 1). An array index such as this can be
used at any point in a field reference, so you could, for example, filter on:
Body.Invoice."Item"[1].Quantity > 2
If you do not know exactly how many instances of a repeating field there are, you
can look at the last instance, or a relative field (for example, the third field from
the end). You can refer to the last instance of a repeat by using the special LAST
array index, as in:
Body.Invoice."Item"[LAST].Quantity > 2

Alternatively, you can use the CARDINALITY function to determine how many
instances of a repeating field there are, and use the result to refer to the second to
last, for example. The following example shows how to do this:
Body.Invoice."Item"[CARDINALITY
(Body.Invoice."Item"[]) - 2].Quantity > 2
In this case, the CARDINALITY function is passed a field reference that ends in [].
The meaning of this is "count all instances of the Item field". The [] at the end
appears superfluous, because the context indicates that this is the meaning, but its
presence is required. This makes the syntax consistent with other instances where
it is necessary to refer to "all instances" of something. Remember that array indices
start at 1, so the array index in the above example refers to the third-from-last
instance of the Item field.
Note: If you are using a while loop to process elements of a message, to improve
performance, it is recommend that you set a variable to the value of the
CARDINALITY prior to entering the loop.
CARDINALITY is described in “CARDINALITY” on page 96.
If you do not know how many instances there are

It is more likely that you do not know how many instances of a repeating field
there are in a message. This is the situation that arises with the Item field in the
example message in “Message used in this book” on page 147. In order to write a
filter that takes into account all instances of the Item field, you need to use a
construct that can iterate over all instances of a repeating field. The quantified
predicate allows you to execute a predicate against all instances of a repeating
field, and collate the results.
For example, you might want to verify that none of the items that were being
ordered had quantity greater than 50. To do this you could write:
FOR ALL Body.Invoice.Purchases."Item"[] AS I (I.Quantity <= 50)
There are several things to note about this example. With the quantified predicate
itself, the first thing to note is the "[]" on the end of the field reference after the
"FOR ALL". The square brackets tell you that you are iterating over all instances of
the Item field.
In some cases, this syntax appears unnecessary because you can get that
information from the context, but it is done for consistency with other pieces of
syntax.
The "AS" clause associates the name I with the current instance of the repeating
field. This is similar to the concept of iterator classes used in some object oriented
languages such as C++. The expression in parentheses is a predicate that is
evaluated for each instance of the Item field.

A description of this example is:
1. Iterate over all instances of the field Item inside Body.Invoice.
2. For each iteration:
a. Bind the name I to the current instance of Item.
b. Evaluate the predicate I.Quantity <= 50. If the predicate:
v Evaluates to TRUE for all of the instances of Item, return TRUE.
v Is FALSE for any instance of Item, return FALSE.
v For a mixture of TRUE and UNKNOWN, it returns UNKNOWN.
The above is a description of how the predicate is evaluated if the "ALL" keyword
is used. An alternative is to specify "SOME", or "ANY", which are equivalent. In
this case the quantified predicate returns TRUE if the sub-predicate returns TRUE
for any instance of the repeating field. Only if the sub-predicate returns FALSE for
all instances of the repeating field does the quantified predicate return FALSE. If a
mixture of FALSE and UNKNOWN values are returned from the sub-predicate, an
overall value of UNKNOWN is returned.
To illustrate this, the following examples are based on the message described in
“Message used in this book” on page 147. In the following filter expression
FOR ANY Body.Invoice.Purchases."Item"[] AS I (I.Title = 'The XML Companion')
the sub-predicate evaluates to TRUE, however this next expression returns FALSE:
FOR ANY Body.Invoice.Purchases."Item"[] AS I (I.Title = 'C Primer')
because the ″C Primer″ is not included on this invoice. If in this instance some of
the items in the invoice had not included a book title field, the sub-predicate
would have returned UNKNOWN, and the quantified predicate would have
returned the value UNKNOWN.
Great care must be taken to deal with the possibility of null values appearing. You
are therefore recommended to write this filter with an explicit check on the
existence of the field, as follows:
FOR ANY Body.Invoice.Purchases."Item"[] AS I (I.Book IS NOT NULL AND
I.Book.Title = 'C Primer')
The "IS NOT NULL" predicate ensures that if an Item field does not contain a Book,
a FALSE value is returned from the sub-predicate.
Using the SELECT expression: Another way of dealing with arbitrary repeats of
fields within a message is to use a SELECT expression. This is described in
“Column functions” on page 131.
Anonymous field references

It is possible to refer to the array of all children of a particular entity by using a
path element of "*". So, for example:
InputRoot.*[]
is a path that identifies the array of all children of InputRoot. This is often used in
conjunction with an array subscript to refer to a particular child of an entity by
position, rather than by name. So, for example:
InputRoot.*[LAST]
Refers to the last child of the root of the input message, that is, the "body"
of the message.

Anonymous field references
InputRoot.*[1]
Refers to the first child of the root of the input message. (This is the
message properties.)
It is useful to be able to find out the name of an entity that has been identified
with a path of this kind. To do this, you can use the FIELDNAME function. This
function takes a path as its only parameter and returns as a string the field name
of the entity to which the path refers. Here are some examples of its usage:
FIELDNAME(InputRoot.XML)
Returns 'XML'.
FIELDNAME(InputBody)
Returns the name of the last child of InputRoot, which could be 'XML'.
FIELDNAME(InputRoot.*[LAST])
Returns the name of the last child of InputRoot, which could be 'XML'.
The FIELDNAME function is described in “FIELDNAME” on page 96.
Navigating a message tree anonymously using dynamic

references
A variable of type REFERENCE can be used as a dynamic reference to navigate a
message tree. This acts in a similar way to a message cursor or a variable pointer.
It is generally simpler and more efficient to use reference variables in preference to
array indexes when accessing repeating structures. Reference variables are accepted
everywhere field references are accepted and come with a set of statements and
functions to allow detailed manipulation of message trees. For example:
/* Declare the dynamic reference*/

DECLARE myref REFERENCE TO OutputRoot.MyArray.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;
/* Move the dynamic reference to the next item in the array*/
MOVE myref NEXTSIBLING;
END WHILE;
Figure 1. Using a dynamic reference
This example declares a dynamic reference, myref, which points to the first item in
a message array. The value in the first item is incremented by one, and then the
pointer (dynamic reference) is moved to the next item. Once again the item value
is incremented by one. This process continues until the pointer moves outside of
the scope of the message array (all the items in this array have been processed)
and the LASTMOVE function returns FALSE. LASTMOVE is described in
“LASTMOVE” on page 97.
Before you can use a dynamic reference you must first declare it. A dynamic
reference is declared and initialized in a single statement.

Dynamic references
To declare a dynamic reference
DECLARE identifier REFERENCE TO field reference
The examples below show how to declare a dynamic reference. They are based on
“Message used in this book” on page 147:
DECLARE ref1 REFERENCE TO InputRoot.Invoice.Purchases.Item[1];
DECLARE ref2 REFERENCE TO InputRoot.Invoice.Purchases.NonExistentField;
DECLARE scalar1 CHARACTER;

DECLARE ref3 REFERENCE TO scaler1;
In the second example, ref2 points to InputRoot because the specified field does
not exist.
With the exception of the MOVE statement, which changes the position of the
dynamic reference, a dynamic reference can be used anywhere that a static
reference would be. The value of the dynamic reference in any expression or
statement is the value of the field or variable to which it currently points. For
example, using the message in “Message used in this book” on page 147, the value
of Invoice.Customer.FirstName is ’Andrew’. If the dynamic reference myref is set
to point at the FirstName field as follows:
DECLARE myref REFERENCE TO Invoice.Customer.FirstName;
The value of myref is ’Andrew’. It is also possible to extend this dynamic reference
as follows:
SET myref.Billing.Address[1] = ’Oaklands’;
This changes the address in our example to ’Oaklands Hursley Village Hampshire
SO213JR.
The position of a dynamic reference remains fixed even if a tree is modified. To

illustrate this point the steps that follow use the message illustrated in “Message
used in this book” on page 147 as their input message and create a modified
version of this message as an output message:
1. Copy the input message to the output message.
2. To modify the output message, first declare a dynamic reference ref1 that
points at the first item, ″The XML Companion″.
DECLARE ref1 REFERENCE TO OutputRoot.XML.Invoice.Purchases.Item[1];
The dynamic reference is now equivalent to the static reference

OutputRoot.XML.Invoice.Purchases.Item[1].
3. Use a create statement to insert a new first item for this purchase
CREATE PREVIOUSSIBLING OF ref1 VALUES ’Item’;
The dynamic reference is now equivalent to the static reference

OutputRoot.XML.Invoice.Purchases.Item[2].

Referencing field types
Dynamic references are used in the following statements:
Referencing field types

Some message parsers have complex models in which it is not enough to identify a
field simply by its name and an array subscript. In these cases, an optional field
type can be associated with an element of data in the tree format.
Field types in MQRFH2 headers

When you construct MQRFH2 headers in a Compute node, there are two types of
fields:
1. Fields in the MQRFH2 header structure (for example, Format and
NameValueCCSID
2. Fields in the MQRFH2 NameValue buffer (for example mcd and psc)
To differentiate between these two possible field types, you must insert a value in
front of the referenced field in the MQRFH2 field to identify its type (a value for
the NameValue buffer is not required because this is the default). The value you
must specify for the header structure is (MQRFH2.Field).
For example:
v To create or change an MQRFH2 Format field, specify the following ESQL:
SET OutputRoot.MQRFH2.(MQRFH2.Field)Format = ’MQSTR ’;
v To create or change the psc folder with a topic:
SET OutputRoot.MQRFH2.psc.Topic = ’department’;
v To add an MQRFH2 header to an outgoing message that is to be used to make a
subscription request:
DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) DO
SET OutputRoot.*[I] = InputRoot.*[I];
SET I=I+1;
END WHILE;
SET OutputRoot.MQRFH2.(MQRFH2.Field)Version = 2;
SET OutputRoot.MQRFH2.(MQRFH2.Field)Format = ’MQSTR’;
SET OutputRoot.MQRFH2.(MQRFH2.Field)NameValueCCSID = 1208;
SET OutputRoot.MQRFH2.psc.Command = ’RegSub’;
SET OutputRoot.MQRFH2.psc.Topic = "InputRoot"."MRM"."topel";
SET OutputRoot.MQRFH2.psc.QMgrName = ’DebugQM’;
SET OutputRoot.MQRFH2.psc.QName = ’PUBOUT’;
SET OutputRoot.MQRFH2.psc.RegOpt = ’PersAsPub’;
Referencing information in XML messages

Overview
There are some instances when it is not enough to identify a field just by name
and array subscript. Some message parsers have more complicated models to
expose; it is to cope with these cases that an optional type can be associated with
element. The message model exposed by the generic XML parser makes heavy use
of this facility to deal with the more complicated XML features.
The generic XML model contains a large number of different syntax elements. The
information contained here is intended as a reference on how to refer to and
manipulate the entire array of these elements. For a more detailed discussion on
what each syntax element is and how they are parsed into a message tree, see the
WebSphere MQ Integrator Broker Working with Messages book.

Referencing information in XML
Paths and types: When a type is not present in a path element, this indicates that
the type of the syntax element is not important. That is, a path element of name
matches any syntax element with the name of name, regardless of the element type.
In the same way that a path element can specify a name and not a type, a path
element can specify a type and not a name. This type of path element matches any
syntax element that has the specified type, regardless of name. The following is an
example of this:
FIELDNAME(InputBody.(XML.Element)[1 ])
This example returns the name of the first element in the body of the message
(assuming it is an XML message). The following example of generic XML shows a
case of when it is necessary to use types in paths:
<tag1 attr1=’abc’>
<attr1>123<attr1>
</tag1>
The path InputBody.tag1.attr1 refers to the attribute called attr1, because

attributes appear before nested elements in a syntax tree generated by an XML
parser. To refer to the element called attr1 it is necessary to use a path:
InputBody.tag1.(XML.Element)attr1
However, it is always advisable to include types in these cases to be explicit about

which syntax element is being referred to.
Note that the following ESQL:

SET OutputRoot.XML.Element1.(XML.Element)Attribute1 = ’123’;
is essentially shorthand for the following, fully-qualified path :

SET OutputRoot.XML.(XML.Element)Element1.(XML.Element)Attribute1.(XML.Content) = ’123’;
Consider the following XML:

<?xml version="1.0"?>
<!DOCTYPE Order SYSTEM "Order.dtd">
<Order>
<ItemNo>1</ItemNo>
<Quantity>2</Quantity>
</Order>
The path InputBody.Order in fact refers to the (XML.DocTypeDecl)syntax element,

because this appears before the XML Body in the syntax tree and has the same
name. To refer to the element ItemNo it is necessary to use a path
InputBody.(XML.Element)Order.ItemNo. The following example demonstrates the
same idea, using the following XML input message:
<doc><i1>100</i1></doc>
To assign 112233 to <i1>, you need to use the following ESQL expression:
SET OutputRoot.XML.(XML.Element)doc.I1=112233;
Ordering: When building an XML message in a Compute node, the order in

which your lines of ESQL appear is vital. Consider the following XML:
<Order>
<ItemNo>1</ItemNo>
</Order>

If you want to add a DocType Declaration to this, you could erroneously write the
following ESQL :
SET OutputRoot = InputRoot;
-- Enter SQL below this line. SQL above this line might be
regenerated, causing any modifications to be lost.
SET OutputRoot.XML.(XML.XmlDecl) = ’’;
SET OutputRoot.XML.(XML.XmlDecl).(XML.Version) = ’1.0’;
SET OutputRoot.XML.(XML.DocTypeDecl)Order =’’ ;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.SystemId) = ’NewDtdName.dtd’;
This ESQL generates the following XML, which is not well-formed and fails when
written from an MQOutput node :
<Order>
<ItemNo>1</ItemNo>
</Order>
<?xml version="1.0"?>
<!DOCTYPE Order SYSTEM "Order.dtd">
Table 4 is a definitive list of syntax elements and their corresponding ESQL

correlation names. These fall into three broad categories: XML Declaration, XML
Body and DocType Declaration. Examples for each of these three categories follow
the table.
Table 4. List of syntax elements and corresponding ESQL correlation names
Syntax element Correlation name
Syntax elements used in the XML declaration:
XmlDecl (XML.XmlDecl)
Version (XML.Version)
Encoding (XML.″Encoding″)
Standalone (XML.Standalone)
Syntax elements used in the XML body:

xElement (XML.Element) - note (XML.tag) also
supported
Attribute (XML.Attribute) - note (XML.attr) also
supported
Content (XML.Content) - note (XML.pcdata) also
supported
WhiteSpace (XML.WhiteSpace)
Comment (XML.Comment)
EntityReferenceStart (XML.EntityReferenceStart)
EntityReferenceEnd (XML.EntityReferenceEnd)
ProcessingInstruction (XML.ProcessingInstruction)
CDataSection (XML.CDataSection)
Syntax elements used in the DocType Declaration:

DocTypeDecl (XML.DocTypeDecl)
PublicId (XML.PublicId)
SystemId (XML.SystemId)

Table 4. List of syntax elements and corresponding ESQL correlation names (continued)
IntSubset (XML.IntSubset)
ElementDef (XML.ElementDef)
EntityDecl (XML.EntityDecl)
ParameterEntityDecl (XML.ParameterEntityDecl)
EntityDeclValue (XML.EntityDeclValue)
ExternalEntityDecl (XML.ExternalEntityDecl)
ExternalParameterEntityDecl (XML.ExternalParameterEntityDecl)
UnparsedEntityDecl (XML.UnparsedEntityDecl)
NotationReference (XML.NotationReference)
AttributeList (XML.AttributeList)
AttributeDef (XML.AttributeDef)
AttributeDefValue (XML.AttributeDefValue)
AttributeDefDefaultType (XML.AttributeDefDefaultType)
AttributeDefType (XML.AttributeDefType)
NotationDecl (XML.NotationDecl)
DocTypeWhiteSpace (XML.DocTypeWhiteSpace)
DocTypeComment (XML.DocTypeComment)
DocTypePI (XML.DocTypePI)
XML declaration
Consider the following ESQL in a Compute node :
-- Create an XML Declaration
SET OutputRoot.XML.(XML.XmlDecl) = ’’;
-- Set the Version within the XML Declaration

SET OutputRoot.XML.(XML.XmlDecl).(XML.Version) = ’1.0’;
-- Set the Encoding within the XML Declaration

SET OutputRoot.XML.(XML.XmlDecl).(XML."Encoding") = ’UTF-8’;
-- Set Standalone within the XML Declaration

SET OutputRoot.XML.(XML.XmlDecl).(XML.Standalone) = ’no’;
This ESQL generates the following XML declaration:

<?xml version="1.0" encoding="UTF-8" standalone="no"?>
Note that (XML."Encoding") must contain the double quotes because Encoding is a
reserved word in WebSphere MQ Integrator Broker.
For further information on the syntax elements involved in the XML declaration,
see WebSphere MQ Integrator Broker Working with Messages.

XML body and empty elements
For most parsers, an empty element of the form <tag></tag> is identical to one of
the form <tag/>. The default behavior of the Generic XML Parser in WebSphere
MQ Integrator Broker is to generate empty elements in the first of these two forms.
If you specifically require the second (short) form of empty element, set the content
of the element to be NULL. That is,
SET OutputRoot.XML.Element1.Element2.(XML.Content) = NULL;
generates the following XML:

<Element1><Element2/></Element>

DocType Declaration
Consider the following ESQL in a Compute node:
-- Create a DocType Declaration named ’test’

SET OutputRoot.XML.(XML.DocTypeDecl)test = ’’;
-- Set a public and system ID for the DocType Declaration

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.SystemId) = ’test.dtd’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.PublicId) = ’//this/is/a/URI/test’;
-- Create an internal subset to hold our DTD definitions

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset) = ’’;
-- Create a Notation Declaration called ’TeX’

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.NotationDecl)TeX = ’’;
-- The Notation Declaration contains a SystemId and a PublicId

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.NotationDecl)TeX.(XML.SystemId) = ’//TexID’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.NotationDecl)TeX.(XML.PublicId) = ’//this/is/a/URI/TexID’;
-- Create an Entity Declaration called ’ent1’

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.EntityDecl)ent1 = ’’;
-- This must contain an Entity Declaration Value

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.EntityDecl)ent1.(XML.EntityDeclValue) = ’this isan entity’;
-- Similarly for a Parameter Entity Declaration

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ParameterEntityDecl)ent2 = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ParameterEntityDecl)ent2.(XML.EntityDeclValue) =’#PCDATA |
subel2’;
-- Create Both types of External Entity, each with a public and system ID
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ExternalParameterEntityDecl)extent1 = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ExternalParameterEntityDecl)extent1.(XML.SystemId) =
’more.txt’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ExternalParameterEntityDecl)extent1.(XML.PublicId) =
’//this/is/a/URI/extent1’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ExternalEntityDecl)extent2 = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ExternalEntityDecl)extent2.(XML.SystemId) = ’more.txt’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ExternalEntityDecl)extent2.(XML.PublicId) =
’//this/is/a/URI/extent2’;
-- Create an Unparsed Entity Declaration called ’unpsd’

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.UnparsedEntityDecl)unpsd = ’’;
-- This has a SystemId, PublicId and Notation Reference
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.UnparsedEntityDecl).(XML.SystemId) = ’me.gif’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.UnparsedEntityDecl).(XML.PublicId) =
’//this/is/a/URI/me.gif’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.UnparsedEntityDecl).(XML.NotationReference) = ’TeX’;
Figure 2. DocType declaration (Part 1 of 2)

-- Create some whitespace in the DocType Declaration
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.DocTypeWhiteSpace) = ’ ’;
-- Create a Processing Instruction named ’test’

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.DocTypePI)test = ’Do this’;
-- Add a DocTypeComment
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.DocTypeComment) = ’this is a comment’;
-- Create a variety of Elements
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ElementDef)subel2 = ’(#PCDATA)’;

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ElementDef)subel1 = ’(subel2 | el4)+’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ElementDef)el1 = ’(#PCDATA)’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ElementDef)el2 = ’(#PCDATA | subel2)*’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ElementDef)el4 = ’(#PCDATA)’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.ElementDef)el6 = ’(#PCDATA)’
;-- Create an AttributeList for element subel1
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)subel1 = ’’;
-- Create an attribute called ’size’ with enumerated values ’big’ or ’small’

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)subel1.(XML.AttributeDef)size = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)subel1.(XML.AttributeDef)size.
(XML.AttributeDefType) = ’(big | small)’;
-- Set the default value of our attribute to be ’big’

SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)subel1.(XML.AttributeDef)size.
(XML.AttributeDefValue) = ’big’;
-- Create another attribute - this time we specify the DefaultType as being #REQUIRED
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)subel1.(XML.AttributeDef)shape = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)subel1.(XML.AttributeDef)shape.
(XML.AttributeDefType) = ’(round | square)’;
-- Create another attribute list for element el5 with one attribute, containing CDATA which is #IMPLIED
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)el5 = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)el5.(XML.AttributeDef)el5satt = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)el5.(XML.AttributeDef)el5satt.
(XML.AttributeDefType)CDATA = ’’;
SET OutputRoot.XML.(XML.DocTypeDecl).(XML.IntSubset).(XML.AttributeList)el5.(XML.AttributeDef)el5satt.
(XML.AttributeDefDefaultType) = ’IMPLIED’;
Figure 2. DocType declaration (Part 2 of 2)

This generates the following DocType Declaration (note that carriage returns have
been added for ease of viewing):
<!DOCTYPE test PUBLIC "//this/is/a/URI/test" "test.dtd"

[<!NOTATION TeX PUBLIC "//this/is/a/URI/TexID" "//TexID">
<!ENTITY ent1 "this is an entity">
<!ENTITY % ent2 "#PCDATA | subel2">
<!ENTITY % extent1 PUBLIC "//this/is/a/URI/extent1" "more.txt">
<!ENTITY extent2 PUBLIC "//this/is/a/URI/extent2" "more.txt">
<!ENTITY unpsd PUBLIC "//this/is/a/URI/me.gif" "me.gif" NDATA TeX> <?test Do this?>

<!ELEMENT subel2 (#PCDATA)>
<!ELEMENT subel1 (subel2 | el4)+>
<!ELEMENT el1 (#PCDATA)>
<!ELEMENT el2 (#PCDATA | subel2)*>
<!ATTLIST subel1
size (big | small) "big"
shape (round | square) #REQUIRED>
<!ATTLIST el5
el5satt CDATA #IMPLIED>
]>
For further information on the syntax elements involved in the DocType

Declaration, see WebSphere MQ Integrator Broker Working with Messages
Constructing XML in the Compute node

Compute nodes can create XML that is not well formed according to the XML
specification but any attempt by an output node to turn the badly-formed XML
into a bitstream will fail. An example of creating badly-formed XML is the
following, which constructs two top-level tags:
SET OutputRoot.XML.Element1 = ’a’;
SET OutputRoot.XML.Element2 = ’b’;


Part 2. Writing ESQL
Chapter 3. Writing ESQL . . . . . . . . . 41 LENGTH . . . . . . . . . . . . . . 87
Finding the statement or function you need . . . 42 LOWER, LCASE. . . . . . . . . . . . 88
Using drag and drop to create ESQL . . . . . . 44 LTRIM . . . . . . . . . . . . . . . 88
ESQL conventions . . . . . . . . . . . . 45 OVERLAY . . . . . . . . . . . . . . 88
Case sensitivity of ESQL syntax . . . . . . 45 POSITION. . . . . . . . . . . . . . 89
Comments. . . . . . . . . . . . . . 45 RTRIM . . . . . . . . . . . . . . . 89
Special characters in ESQL . . . . . . . . 46 SUBSTRING . . . . . . . . . . . . . 89
Prefixed identifiers for MRM elements . . . 46 TRIM . . . . . . . . . . . . . . . 90
ESQL keywords . . . . . . . . . . . . 47 UPPER, UCASE . . . . . . . . . . . . 90
Reserved keywords used in ESQL . . . . . 47 Numeric functions . . . . . . . . . . . . 90
Nonreserved keywords used in ESQL . . . 48 ABS . . . . . . . . . . . . . . . . 90
BITAND . . . . . . . . . . . . . . 91
Chapter 4. ESQL statements . . . . . . . . 49 BITNOT . . . . . . . . . . . . . . 91
ATTACH . . . . . . . . . . . . . . . 50 BITOR . . . . . . . . . . . . . . . 91
CALL . . . . . . . . . . . . . . . . 51 BITXOR . . . . . . . . . . . . . . 92
COMPOUND. . . . . . . . . . . . . . 52 CEIL . . . . . . . . . . . . . . . 92
CREATE FIELD . . . . . . . . . . . . . 53 FLOOR . . . . . . . . . . . . . . . 93
Examples of using the CREATE statement . . . 55 MOD . . . . . . . . . . . . . . . 93
CREATE FUNCTION . . . . . . . . . . . 57 ROUND . . . . . . . . . . . . . . 93
Example . . . . . . . . . . . . . . 57 SQRT . . . . . . . . . . . . . . . 93
CREATE PROCEDURE . . . . . . . . . . 58 TRUNCATE . . . . . . . . . . . . . 94
External stored procedures . . . . . . . . 59 Datetime functions . . . . . . . . . . . . 94
Examples . . . . . . . . . . . . . . 60 EXTRACT . . . . . . . . . . . . . . 94
Example 1: an external stored procedure. . . 60 CURRENT_DATE . . . . . . . . . . . 94
Expected results for example 1 . . . . . . 60 CURRENT_TIME . . . . . . . . . . . 95
Example 2: an ESQL procedure . . . . . . 61 CURRENT_TIMESTAMP . . . . . . . . . 95
Example 3: recursive use of CREATE CURRENT_GMTDATE . . . . . . . . . 95
PROCEDURE. . . . . . . . . . . . 61 CURRENT_GMTTIME. . . . . . . . . . 95
DECLARE . . . . . . . . . . . . . . . 63 CURRENT_GMTTIMESTAMP . . . . . . . 95
DELETE . . . . . . . . . . . . . . . 64 LOCAL_TIMEZONE . . . . . . . . . . 96
Example . . . . . . . . . . . . . . 64 Field functions . . . . . . . . . . . . . 96
Handling database errors . . . . . . . . . 64 BITSTREAM . . . . . . . . . . . . . 96
DETACH . . . . . . . . . . . . . . . 65 CARDINALITY . . . . . . . . . . . . 96
EVAL . . . . . . . . . . . . . . . . 66 FIELDNAME . . . . . . . . . . . . . 96
IF. . . . . . . . . . . . . . . . . . 69 FIELDTYPE . . . . . . . . . . . . . 97
INSERT. . . . . . . . . . . . . . . . 70 LASTMOVE . . . . . . . . . . . . . 97
Example . . . . . . . . . . . . . . 70 SAMEFIELD . . . . . . . . . . . . . 97
Handling database errors . . . . . . . . . 70 Complex functions . . . . . . . . . . . . 97
MOVE . . . . . . . . . . . . . . . . 71 CASE . . . . . . . . . . . . . . . 97
Example . . . . . . . . . . . . . . 71 CAST . . . . . . . . . . . . . . . 99
PASSTHRU . . . . . . . . . . . . . . 73 SELECT . . . . . . . . . . . . . . 100
Using the drag and drop facility with Database state functions . . . . . . . . . . 101
PASSTHRU . . . . . . . . . . . . . 75 Miscellaneous functions . . . . . . . . . . 102
Considerations when calling stored procedures 75 COALESCE . . . . . . . . . . . . . 102
Limitations . . . . . . . . . . . . . 76 NULLIF . . . . . . . . . . . . . . 102
PROPAGATE . . . . . . . . . . . . . . 77 PASSTHRU . . . . . . . . . . . . . 102
RETURN . . . . . . . . . . . . . . . 79 UUIDASBLOB . . . . . . . . . . . . 102
SET . . . . . . . . . . . . . . . . . 81 UUIDASCHAR . . . . . . . . . . . . 103
THROW . . . . . . . . . . . . . . . 83
UPDATE . . . . . . . . . . . . . . . 84 Chapter 6. Using SELECT with ROW and LIST
Examples . . . . . . . . . . . . . . 84 constructors . . . . . . . . . . . . . 105
Handling database errors . . . . . . . . . 84 ROW and LIST constructors . . . . . . . . 105
WHILE . . . . . . . . . . . . . . . . 85 ROW constructor . . . . . . . . . . . 105
Example 1 . . . . . . . . . . . . 105
Chapter 5. ESQL functions . . . . . . . . 87 Example 2 . . . . . . . . . . . . 105
String manipulation functions . . . . . . . . 87 A LIST constructor . . . . . . . . . . 106

Example 1 . . . . . . . . . . . . 106
Example 2 . . . . . . . . . . . . 106
ROW and LIST combined . . . . . . . . 106
ROW and LIST comparisons . . . . . . . 107
Example 1 . . . . . . . . . . . . 107
Example 2 . . . . . . . . . . . . 107
Example 3 . . . . . . . . . . . . 108
Chapter 7. Transforming data from one data

type to another (CAST) . . . . . . . . . 111
The CAST function . . . . . . . . . . . 111
Source expression . . . . . . . . . . . 112
CCSID. . . . . . . . . . . . . . . 112
ENCODING . . . . . . . . . . . . . 112
Supported casts. . . . . . . . . . . . 112
Implicit casts . . . . . . . . . . . . 117
Implicit CASTs for comparisons . . . . . 117
Implicit CASTs for arithmetic operations . . 119
Implicit CASTs for assignment. . . . . . 121
Data types of values from external sources . . 122
Chapter 8. Using the SELECT function . . . . 125

Using SELECT for simple message transformation 125
Using SELECT for complex message
transformation . . . . . . . . . . . . . 128
Column functions . . . . . . . . . . . . 131
Use of the ITEM keyword . . . . . . . . . 132
Effects of the THE keyword . . . . . . . . 132
Using SELECT to return a scalar value . . . . . 133
Multiple items in the FROM clause (Join) . . . . 133
Using join to translate data . . . . . . . . . 136
Joining items in the FROM clause . . . . . . 137
Selecting from a list of scalars . . . . . . . . 137
Chapter 9. Querying external databases . . . 139

Examples of external database queries . . . . . 140
Create a database table . . . . . . . . . 140
Create a table in a case sensitive database
system . . . . . . . . . . . . . . 141
Use the ITEM keyword . . . . . . . . . 141
Use the WHERE clause . . . . . . . . . 142
Handling database errors . . . . . . . . . 142

Chapter 3. Writing ESQL
This chapter describes the things that you need to know before you start writing
ESQL. It covers:
v “Finding the statement or function you need” on page 42
v “Using drag and drop to create ESQL” on page 44
v “ESQL conventions” on page 45
The statements and functions in the following chapters are described using syntax
diagrams. For information about how to read these diagrams, see Appendix C,
“How to read syntax diagrams” on page 173.

Finding the statement or function you need
Table 5. Finding the statement or function you need
CATEGORY FUNCTIONS/ STATEMENTS/ OPERATORS RELATED KEYWORDS
Variable manipulation
Manipulation of all sources of variables
Basic manipulation for ALL types v “DECLARE” on page 63 v REFERENCE TO
of variable
v “SET” on page 81 v NAME, TYPE, VALUE
v CAST, see Chapter 7, “Transforming data from one data v ENCODING, CCSID, AS
type to another (CAST)” on page 111 for details.
Selective assignment to any v “CASE” on page 97 ELSE, WHEN, THEN, END
variable
v “COALESCE” on page 102
Creation of values v “UUIDASBLOB” on page 102 –
v “UUIDASCHAR” on page 103

Manipulation of message trees
Assignment and deletion to or v “CREATE FIELD” on page 53 v OF, FROM, VALUES, DOMAIN, PARSE
from a message tree
v “ATTACH” on page 50 v TO, AS, FIRSTCHILD, LASTCHILD,
v “DETACH” on page 65 PREVIOUSSIBLING, NEXTSIBLING
v “SELECT” on page 100 (used with SET statement) v FROM, AS, ITEM, THE, SUM, COUNT, MAX, MIN
v “ROW and LIST constructors” on page 105

v “ROW and LIST constructors” on page 105
Information relating to message v “FIELDNAME” on page 96 –
trees or subtrees
v “FIELDTYPE” on page 97
v “CARDINALITY” on page 96
v “BITSTREAM” on page 96
Processing repeating fields v FOR v ALL, ANY, SOME
v “SELECT” on page 100 v FROM, AS, ITEM, THE, SUM, COUNT, MAX, MIN
Processing based on data type
String processing
Numeric information about v “LENGTH” on page 87 IN
strings
v “POSITION” on page 89
String conversion v “UPPER, UCASE” on page 90 –
v “LOWER, LCASE” on page 88

String manipulation v “LTRIM” on page 88, “TRIM” on page 89, “RTRIM” on v LEADING, TRAILING, BOTH, FROM
page 89 v PLACING, FROM, FOR
v “OVERLAY” on page 88 v FROM FOR
v “SUBSTRING” on page 89
v || (Concatenation), see “Arithmetic operators” on
page 11

Table 5. Finding the statement or function you need (continued)
Numeric processing
Bitwise operations v “BITAND” on page 91 –
v “BITNOT” on page 91
v “BITOR” on page 91
v “BITXOR” on page 92
General v “ABS” on page 90, ABSVAL –
v “CEIL” on page 92, CEILING

v “FLOOR” on page 93
v “ROUND” on page 93
v “TRUNCATE” on page 94
v “SQRT” on page 93
v “MOD” on page 93
Date time processing
v “CURRENT_DATE” on page 94 YEAR, MONTH, DAY, HOUR, MINUTE, SECOND
v “CURRENT_GMTDATE” on page 95
v “CURRENT_TIME” on page 95
v “CURRENT_GMTTIME” on page 95
v “CURRENT_TIMESTAMP” on page 95
v “CURRENT_GMTTIMESTAMP” on page 95
v “LOCAL_TIMEZONE” on page 96
v “EXTRACT” on page 94
Reference
“MOVE” on page 71 TO, PARENT, FIRSTCHILD, LASTCHILD,
PREVIOUSSIBLING, NEXTSIBLING
Flow of control
Conditional statement (within an v IF...THEN...ELSE (see “IF” on page 69) v END
ESQL node)
v WHILE...DO (see “WHILE” on page 85) v END
Exiting an ESQL node v “RETURN” on page 79 USER, EXCEPTION, CATALOGUE, MESSAGE,
VALUES
v “PROPAGATE” on page 77
v “THROW” on page 83
Boolean evaluation for conditional statements
Functions that return a boolean v BETWEEN, see “Comparison operators” on page 13 for SYMMETRIC, ASYMMETRIC, AND
value details.
v EXISTS, see “Comparison operators” on page 13 for
details.
v IN, see “Comparison operators” on page 13 for details.
v LIKE, see “Comparison operators” on page 13 for
details.
v “NULLIF” on page 102
v “LASTMOVE” on page 97
v “SAMEFIELD” on page 97
Operators used in boolean v <, >, =, >=, <=, <> –
expressions.
See “Comparison operators” on page 13 for details.
v NOT, see “Logical operators” on page 12 for details.
v OR, see “Logical operators” on page 12 for details.
Chapter 3. Writing ESQL 43

Table 5. Finding the statement or function you need (continued)
Broker database interaction
Actions on tables v “UPDATE” on page 84 v AS, SET, WHERE
v “INSERT” on page 70 v INTO, VALUES
v “DELETE” on page 64 v FROM, AS, WHERE
v SELECT, see Chapter 8, “Using the SELECT function” v FROM, AS, ITEM, THE, SUM, COUNT, MAX, MIN
on page 125.
v “PASSTHRU” on page 73
Results of actions v SQLCODE, see “Database state functions” on page 101 –
for details.
v SQLSTATE, see “Database state functions” on page 101
for details.
v SQLNATIVEERROR, see “Database state functions” on
page 101 for details.
v SQLERRORTEXT, see “Database state functions” on
page 101 for details.
Dynamic ESQL
“EVAL” on page 66 –
Using drag and drop to create ESQL

WebSphere MQ Integrator Broker supplies a drag and drop facility to help you
create ESQL in Compute, Filter, and Database nodes.
An ESQL palette is provided to allow you to select, drag and drop ESQL
statements into the ESQL editor pane. These statements are listed as keywords in
alphabetical order. Each entry in the palette has a string associated with it that
describes the full syntax of the statement. Many statement strings contain
replaceable terms that signify where the user must supply an expression or value.
The palette also contains a list of functions, which can be similarly dragged and
dropped. When dropped onto a replaceable term, the original text is replaced by
the dropped text: the dropped text is not added to the original text. By first
dragging and dropping statements onto the pane and then dropping functions into
the statements, you can write ESQL quickly and without error.
You can also drag elements from the input and output message structure palettes
into statements or functions. If the element that you have chosen is repeatable, for
example an array of order items, a dialog is opened to prompt you for the index
entry of the item you are updating in the array. For more information, see
WebSphere MQ Integrator Broker Using the Control Center.

Using drag and drop to create ESQL
ESQL conventions
This section describes the following ESQL conventions.
v “Case sensitivity of ESQL syntax”
v “Special characters in ESQL” on page 46
v “Reserved keywords used in ESQL” on page 47
v “Comments”
Case sensitivity of ESQL syntax

The following text describes when the case in which ESQL statements are specified
is significant.
v Field reference literals are case sensitive.
v ESQL language words are not case sensitive.
Comments
ESQL has two types of comment: single line and multiple line. A single line
comment starts with the characters -- and ends at the end of the line.
In arithmetic expressions you must take care not to initiate a line comment
accidentally. For example, consider the expression:
1 - -2
Removing all white space from the expression results in:

1--2
which is interpreted as the number 1, followed by a line comment.
A multiple line comment starts with /* anywhere in ESQL and ends with */.

Special characters in ESQL
Special characters in ESQL
; semicolon End of ESQL statement
. period Message hierarchy separator / decimal point
= equals Variable comparison / assignment
> greater than Variable comparison
< less than Variable comparison
[] square brackets Array subscript
’ single quote Delimit string constant
|| double vertical bar Concatenation
() round brackets Expression delimiter
″ double quote Delimit field or message hierarchy name
* asterisk All subscripts / multiply
+ plus Arithmetic add
- minus Arithmetic subtract / date
separator/negation
/ forward slash Arithmetic divide
_ underscore LIKE single wild card
% percent LIKE multiple wild card
\ backslash LIKE escape character
: colon Time separator
, comma List separator
<> less than greater than Not equals
–– double minus ESQL single line comment
/* */ slash & asterisk ESQL multiline comment
? question mark Substitution variable in PASSTHRU
<= less than or equal to
>= greater than or equal
to
Prefixed identifiers for MRM elements

To make referencing MRM elements in ESQL statements easier, you can use
prefixed identifiers. Identifiers then consist of a prefix and suffix separated by the
caret character (^). This caret character is valid only for MRM elements: ESQL
makes use of the suffix only. Prefixed identifiers are described in WebSphere MQ
Integrator Broker Working with Messages.

ESQL keywords
ESQL keywords
If you have existing declared ESQL variables or constants with names that are the
same as nonreserved ESQL keywords (for example NAME), your message flows
will continue to function correctly without modification. This is because the new
ESQL syntax is unambiguous and the ESQL parser in the broker is context
sensitive. However, if you check out a message flow and edit a node that contains
such names, the ESQL syntax checker in the Control Center is not as sensitive and
might flag these names as errors, which makes editing the ESQL awkward. You are
recommended to do one of the following:
1. Edit your ESQL to enclose all such variable and constant names in quotes.
2. Obtain SupportPac™ IC01, and use the program mqsifindandreplace to search
for these names and replace them with the quoted equivalent.
Reserved keywords used in ESQL

The following keywords are reserved in upper, lower, or mixed case. You cannot
use these keywords for variable names. However, you can now use reserved
keywords as names in a field reference. Thus, the only reason for enclosing these
names in double quotes is that the name contains a character that is not allowed in
names (for example, blank). Emboldened words denote words that are new for this
release.
ALL DISTINCT NOT

ASYMMETRIC FROM SYMMETRIC
BOTH ITEM TRAILING
CASE LEADING WHEN

Nonreserved keywords used in ESQL
Nonreserved keywords used in ESQL
The following keywords are not reserved in upper, lower, or mixed case. You are
not recommended to use them, but you can if you choose. Existing message flows
containing these keywords continue to work as before. However, if you use these
words in new message flows, they are not accepted by the syntax checker.
Emboldened words denote words that are new for this release. * denotes words
that are not used in WebSphere MQ Integrator Broker Version 2.1, but are words
reserved for future releases.
AND DOMAIN LASTCHILD SAMEFIELD

ANY ELSE LASTMOVE SECOND
AS ELSEIF* LEAVE SELECT
ATTACH ENCODING LIKE SET
BEGIN END LIST SEVERITY
BETWEEN ESCAPE LOCAL_TIMEZONE SOME
BIT EVAL LOOP* SQL
BLOB EXCEPTION MAX SQLCODE
BOOLEAN EXISTS MESSAGE SQLERRORTEXT
BY* EXIT MIN SQLEXCEPTION
CALL FALSE MINUTE SQLNATIVEERROR
CATALOG FIELD MODIFIES SQLSTATE
CCSID FLOAT MONTH SQLWARNING
CHAR FOR MOVE SUM
CHARACTER FORMAT NAME THE
CONDITION FOUND NEXTSIBLING THEN
CONTINUE FUNCTION NULL THROW
COUNT GMTTIME OF TIME
CREATE GMTTIMESTAMP OR TIMESTAMP
CURRENT_DATE GROUP ORDER TO
CURRENT_GMTDATE HANDLER OUT TRUE
CURRENT_GMTTIME HAVING PARSE TYPE
CURRENT_GMTTIMESTAMP HOUR PASSTHRU UNKNOWN
CURRENT_TIME IF PLACING UNTIL*
CURRENT_TIMESTAMP IN PREVIOUSSIBLING UPDATE
DATA INOUT PROCEDURE USER
DATABASE INSERT PROPAGATE UUIDASBLOB
DATE INT READS UUIDASCHAR
DAY INTEGER REFERENCE VALUE
DECIMAL INTERVAL REPEAT* VALUES
DECLARE INTO RETURN WHERE
DELETE IS RETURNS WHILE
DETACH ITERATE ROW YEAR
DO LAST RTRIM

Chapter 4. ESQL statements
This chapter describes all the ESQL statements. For an explanation of what a
statement is, see “Statements” on page 3.

ATTACH
ATTACH
ATTACH
ATTACH dynamic reference TO field reference AS FIRSTCHILD

LASTCHILD
PREVIOUSSIBLING
NEXTSIBLING
The ATTACH statement attaches a portion of a message tree into a new position in
the message hierarchy.
The following example illustrates how you could use the ATTACH statement,
together with the DETACH statement described in “DETACH” on page 65 to
modify a message structure. The dynamic reference supplied to the DETACH
statement must point to a modifiable message tree such as Environment,
LocalEnvironment, OutputRoot, OutputExceptionList, or InputLocalEnvironment.
There are some limitations to the use of ATTACH. In general, elements detached
from the output trees of a Compute node might not be attached to the
environment or to input trees. For example, if you take the following message:
<Data>
<Order>
<Item>cheese
<Type>stilton</Type>
</Item>
<Item>bread</Item>
</Order>
<Order>
<Item>garlic</Item>
<Item>wine</Item>
</Order>
</Data>
the following ESQL statements:

DECLARE ref1 REFERENCE = OutputRoot.XML.Data.Order[1].Item[1];
DETACH ref1;
ATTACH ref1 TO OutputRoot.XML.Data.Order[2] AS LASTCHILD;;
result in the following new message structure:

<Data>
<Order>
<Item>bread</Item>
</Order>
<Order>
<Item>garlic</Item>
<Item>wine</Item>
<Item>cheese
<Type>stilton</Type>
</Item>
</Order>
</Data>
For information about dynamic references see “Navigating a message tree

anonymously using dynamic references” on page 28.

CALL
CALL
CALL
CALL–ProcedureName –( ExpressionList )
ExpressionList:
Expression
The CALL statement calls a named procedure that has been defined using the
CREATE PROCEDURE statement. This procedure can be implemented in ESQL or
externally as a database stored procedure. The CALL statement passes the
parameters into the procedure in the order given to it. Parameters that have been
defined as IN or INOUT are evaluated before the CALL is made, but parameters
defined as OUT are always passed in as NULL parameters of the correct type.
When the procedure has completed any parameters declared as OUT or INOUT
are updated to reflect any changes made to them during the procedure’s execution.
Parameters defined as IN are never changed during the cause of a procedure’s
execution.
The number of and type parameters passed to the procedure must match the
number and type of the parameters as declared in the CREATE PROCEDURE
definition.
For an example of the use of CALL, see the following examples in CREATE
PROCEDURE: Figure 3 on page 60 and Figure 4 on page 61.
Chapter 4. ESQL statements 51

COMPOUND
COMPOUND
COMPOUND
BEGIN–Statements–END;
The COMPOUND statement defines a new variable name scope and gives the
statements defined within it the status of a single statement. This allows the
statements to become the body of a function or a procedure.

CREATE FIELD
CREATE FIELD
CREATE
FIELD Target
AS Alias values clauses
from clause
FIRSTCHILD OF Target
LASTCHILD AS Alias DOMAIN–expr values clauses
from clause
parse clause
PREVIOUSSIBLING OF Target
NEXTSIBLING AS Alias DOMAIN–expr values clauses
repeat clauses
from clause
parse clause
values clauses:
TYPE (integer)–expr NAME (character)–expr VALUE–expr
from clause:
FROM–source
parse clause:
PARSE ( bitstream )
, encoding , ccsid
, message set , message type , message format
repeat clauses:
REPEAT
VALUE–expr
The CREATE statement creates a new message field positioned either at a given
location or relative to a currently existing location. New fields can be created only
when the target field reference points to a modifiable message, for example
Environment, InputLocalEnvironment, OutputLocalEnvironment, OutputRoot, or
OutputExceptionList.
If the FIELD clause is present, the field specified by ″target″ is navigated to

(creating the fields if necessary) and any values clause or from clause is executed.
Note that this form of CREATE statement does not necessarily create any fields at
all; it only ensures that the given fields do exist.
If array indices are used in the target field reference, only one instance of a
particular field can be created. Thus, if you write a SET statement starting:
SET OutputRoot.XML.Message.Structure[2].Field = ...

CREATE FIELD
at least one instance of ″Structure″ must already exist in the message. That is, the
only fields in the tree that are created are ones on a direct path from the root to the
field identified by the field reference.
If a PREVIOUSSIBLING, NEXTSIBLING, FIRSTCHILD, or LASTCHILD clause is

present, the field specified by ″target″ is navigated to (creating the fields if
necessary) in exactly the same way. A new field is then created and attached in the
specified position (for example as PREVIOUSSIBLING or FIRSTCHILD). This form
of CREATE statement always creates a new field and places it in the specified
position.
Thus, if two CREATE FIRSTCHILD OF ″target″ statements are executed, the

second statement creates a new field as the first child of the target, and displaces
the previously-created first child to the right in the message tree (so it is no longer
the first child). Similarly, CREATE LASTCHILD OF ″target″ navigates to the target
field and adds a new field as its rightmost child, displacing the previous last child
to the left.
CREATE PREVIOUSSIBLING OF ″target″ creates a field to the immediate left of

the field specified by ″target″ (so the depth of the tree is not changed) and
similarly, CREATE NEXTSIBLING OF ″target″ creates a field to the immediate right
of the field specified by ″target″. When creating PREVIOUSSIBLING or
NEXTSIBLING, you can use the REPEAT keyword to specify that the type and
name of the new field should be copied from the current field.
If present, the AS clause causes the named reference variable to be moved to point
at the newly-created field. This is often useful because having created a field, it is
likely you will want to involve it in some further processing.
If present, the DOMAIN clause causes the new field to belong to a new parser of
the specified type. This clause expects a root field name (for example, XML or
MQRFH2). If the DOMAIN clause is present, but the value supplied is a zero
length character string, a new parser of the same type as the parser that owns the
field specified by ″target″ is created. An exception is thrown if the supplied
domain name is not of the CHARACTER data type or its value is NULL. Note that
the DOMAIN clause cannot be specified when the FIELD clause is used because, in
this case, it is not certain that a new field will be created.
The new field’s type and name can be copied from the target field using the
REPEAT clause. Alternatively, the new field’s type, name, and value can be:
v copied from any existing field (using the FROM clause)
v specified explicitly (using the VALUES clause)
v defined by parsing a bitstream (using the PARSE clause)
In the case of the FROM and PARSE clauses, children of the new field can also be
created. The REPEAT clause allows a target reference variable to be moved to point
at the new field.
For the values clauses, the type, name, and value (or any subset of these) can be
specified by any expression that returns a suitable data type (INTEGER for type,
CHARACTER for name and any scalar type for value). An exception is thrown if
the value supplied for a type or name is NULL.
For the FROM clause, ″source″ must be a field reference. The new field’s type,
name, and value are taken from the source field, any child fields are detached (the

CREATE FIELD
field could have already existed in the case of a FIELD clause), and then the new
field is given copies of the source field’s child fields (and grandchildren, and so
on).
In all cases the new field might belong to a new parser of a type specified by the
optional DOMAIN clause. This expects to be the root field name. If no domain is
specified, the domain is of the same type as the parent field (for example MQMD,
XML, or MRM).
The PARSE clause can be used with one, three or six parameters. These can be any
expression that returns a non-NULL value of a suitable data type:
v The bitstream to be parsed (bit array, byte array, or character)
v The encoding of the bitstream (integer)
v The character code set identifier (CCSID) of the bitstream (integer)
v The message set of the bitstream (character) for MRM messages.
v The message type of the bitstream (character) for MRM messages.
v The message format of the bitstream (character) for MRM messages.
In this case the type, name and value of the new field are determined by the
parser to which the element belongs. Child fields are created as defined by the
given bitstream and the encoding rules built into the parser. The following list
shows the valid values for the encoding clause:
v MQENC_INTEGER_NORMAL (0x00000001L)
v MQENC_INTEGER_REVERSED (0x00000002L)
v MQENC_DECIMAL_NORMAL (0x00000010L)
v MQENC_DECIMAL_REVERSED (0x00000020L)
v MQENC_FLOAT_IEEE_NORMAL (0x00000100L)
v MQENC_FLOAT_IEEE_REVERSED (0x00000200L)
v MQENC_FLOAT_S390 (0x00000300L)
The values used for the CCSID clause follow the normal numbering system. For
example, 1200 = UCS-2, 1208 = UTF-8.
Examples of using the CREATE statement

The following example creates the specified field:
CREATE FIELD OutputRoot.XML.Data;
The following example creates a field with no name, type, or value as the first
child of ref1:
CREATE FIRSTCHILD OF ref1;
The following example creates a field using the specified type, name, and value:
CREATE NEXTSIBLING OF ref1 TYPE NameValue NAME ’Price’ VALUE 92.3;
The following example creates a field with a type and name, but no value, that is
added before the sibling indicated by the dynamic reference (ref1):
CREATE PREVIOUSSIBLING OF ref1 TYPE Name NAME ’Quantity’;

CREATE FIELD
The following example creates a field named ’Component’; and moves the
reference variable ″targetCursor″ to point at it:
CREATE FIRSTCHILD OF targetCursor AS targetCursor NAME ’Component’;
The following example creates a new field as the right sibling of the field pointed
to by the reference variable ″targetCursor″ having the same type and name as that
field. The statement then moves ″targetCursor″ to point at the new field:
CREATE NEXTSIBLING OF targetCursor AS targetCursor REPEAT;
For a further example of the CREATE statement, see “Example of CREATE

statement” on page 164.

CREATE FUNCTION
CREATE FUNCTION
CREATE FUNCTION FunctionName ( ParameterList ) RETURNS DataType Statement
ParameterList:
Parameter
Parameter:
ParameterName DataType
The CREATE FUNCTION statement defines a function. The function’s name is

supplied using ″FunctionName″ and the function’s parameters are supplied using
″ParameterList″. The function returns a value of the type ″DataType″ and is
implemented using the single ESQL statement, ″Statement″. This statement can also
be a compound statement, for example BEGIN ... END; .
ESQL functions, unlike ESQL procedures, can have input parameters only.
Functions are local in scope to the current node only. If you want to use the same
function from more that one node, you must define it within each node.
Example
The following example takes any given integer and returns it multiplied by two:
DECLARE X INTEGER;
SET X = 2;
DECLARE Y INTEGER;
SET Y = MultiplyByTwo(X);
CREATE FUNCTION MultiplyByTwo(Value INTEGER) RETURNS INTEGER

RETURN Value * 2;

CREATE PROCEDURE
CREATE PROCEDURE
CREATE PROCEDURE ProcedureName ( ParameterList ) RoutineBody
ParameterList:
Parameter
Parameter:
IN ParameterName DataType
OUT
INOUT
RoutineBody:
Statement
EXTERNAL NAME ExternalRoutineName
The CREATE PROCEDURE statement defines a procedure. A procedure is a

subroutine that has no return value. The procedure’s name is supplied using
″ProcedureName″, the procedure’s parameters are supplied using ″ParameterList″
and the procedure is implemented using the single ESQL statement ″Statement″.
This statement can also be a compound statement, for example BEGIN ... END; .
Alternatively, you can use the EXTERNAL clause instead of ″Statement″, which
calls the named routine as a stored procedure in the database specified by the
containing node’s data source. The procedure cannot be implemented as a database
stored procedure for OS/390®.
ESQL procedures, unlike ESQL functions can have OUT and INOUT parameters in
addition to IN parameters. This allows them to return values to the caller, although
they have no RETURN value as such.
All procedures (internal or external) are local in scope to the current node only. In
order to use the same procedure from more than one node, you must define it in
each node.
OUT parameters passed into a procedure (whether internal or external) always

contain a NULL value of the correct type when they are received by the procedure.
This happens irrespective of their value before the CALL.
IN and INOUT parameters can be NULL when received by the procedure.

Therefore, when writing external procedures, in languages like C, you must use
NULL indicators to ensure your procedure can process the data correctly. For
example, the following passes NULL to the procedure testProc:
DECLARE nullChar CHARACTER; CALL testProc( nullChar );
This happens because nullChar was not assigned a value before the CALL.

CREATE PROCEDURE
External stored procedures
To call a stored procedure the procedure must be defined in the database as well
as in the broker. Figure 3 on page 60 demonstrates how you can define and call a
stored procedure in Oracle Version 8.1.6 or 8.1.7 and DB2 Version 7.1. Although the
database definitions vary between the databases (as expected), the ESQL does not.
The names given to parameters in the ESQL do not have to match the names they
are given on the database side. However, the external NAME the procedure has
must to match its defined name in the database, including any package or
container specifications. Enclose ″ExternalRoutineName″ in quotation marks if it
contains a character not allowed in an identifier.
All external procedures have the following restrictions:

v Parameters cannot be of the ESQL Reference type.
v Parameters cannot map onto the database LOB types (for example, BLOB and
CLOB).
v Results sets cannot be returned or used as input or output parameters.

CREATE PROCEDURE
Examples
Example 1: an external stored procedure
ESQL Definition:
DECLARE inputParm CHARACTER;
DECLARE outputParm CHARACTER;
DECLARE inputOutputParm CHARACTER;
SET inputParm = ‘Hello’;

SET inputOutputParm = ‘World’;
CALL swapParms( inputParm, outputParm, inputOutputParm );
CREATE PROCEDURE swapParms (

IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER
) EXTERNAL NAME dbSwapParms;
To register this Stored Procedure with DB2, copy the following script to a file (eg test1.sql) and execute:
db2 -td@ -vf test1.sql from DB2 command prompt.
-- DB2 Example Stored Procedure

DROP PROCEDURE dbSwapParms @
CREATE PROCEDURE dbSwapParms
( IN in_param CHAR(32),
OUT out_param CHAR(32),
INOUT inout_param CHAR(32))
LANGUAGE SQL
BEGIN
SET out_param = inout_param;
SET inout_param = in_param;
END @
To register this Stored Procedure with Oracle, copy the following script to a file (eg test1.sql) and execute:
sqlplus <userid>/<password> @test1.sql
CREATE OR REPLACE PROCEDURE dbSwapParms
( in_param IN VARCHAR2 ,
out_param OUT VARCHAR2,
inout_param IN OUT VARCHAR2 )
AS
BEGIN
out_param := inout_param;
inout_param := in_param;
END;
/
Figure 3. An external stored procedure
Expected results for example 1

You can expect the following results:
1. The value of the IN parameter does not (and cannot by definition) change.
2. The value of the OUT parameter has become ‘World’.
3. The value of the INOUT parameter has changed to be ‘Hello’.

CREATE PROCEDURE
Example 2: an ESQL procedure
The following is the same procedure as Figure 3 on page 60, but implemented as an
ESQL internal procedure. The CALL syntax for this procedure is also the same as
well as the result.
CREATE PROCEDURE swapParms (

IN parm1 CHARACTER,
OUT parm2 CHARACTER,
INOUT parm3 CHARACTER )
BEGIN
SET parm2 = parm3;
SET parm3 = parm1;
END;
Figure 4. An ESQL procedure
Example 3: recursive use of CREATE PROCEDURE

The following example procedure walks a tree, visiting all places at and below the
given starting point, and reports what it has found:
SET OutputRoot.MQMD = InputRoot.MQMD;
DECLARE answer CHARACTER;

SET answer = ’’;
CALL navigate(InputRoot.XML, answer);

SET OutputRoot.XML.Data.FieldNames = answer;
CREATE PROCEDURE navigate (IN root REFERENCE, INOUT answer CHARACTER)

BEGIN
SET answer = answer || ’Reached Field... Type:’ || CAST(FIELDTYPE(root) AS CHARACTER)||
’: Name:’ || FIELDNAME(root) || ’: Value :’ || root || ’: ’;
DECLARE cursor REFERENCE TO root;

MOVE cursor FIRSTCHILD;
IF LASTMOVE(cursor) THEN
SET answer = answer || ’Field has children... drilling down ’;
ELSE
SET answer = answer || ’Listing siblings... ’;
END IF;
WHILE LASTMOVE(cursor) DO
CALL navigate(cursor, answer);
MOVE cursor NEXTSIBLING;
END WHILE;
SET answer = answer || ’Finished siblings... Popping up ’;

END;
Figure 5. CREATE PROCEDURE: recursive example

CREATE PROCEDURE
When given the following input message:
<Person><Name>John Smith</Name><Salary period=’monthly’ taxable=’yes’>-1200</Salary></Person>
the procedure produces the following output, which has been manually formatted:
Reached Field... Type:16777232: Name:XML: Value :: Field has children... drilling down
Reached Field... Type:16777216: Name:Person: Value :: Field has children... drilling down
Reached Field... Type:16777216: Name:Name: Value :John Smith: Field has children... drilling down
Reached Field... Type:33554432: Name:: Value :John Smith: Listing siblings... Finished siblings... Popping up
Finished siblings... Popping up
Reached Field... Type:16777216: Name:Salary:Value :-1200: Field has children... drilling down
Reached Field... Type:50331648: Name:period: Value :monthly: Listing siblings... Finished siblings... Popping up
Reached Field... Type:50331648: Name:taxable: Value :yes: Listing siblings... Finished siblings... Popping up
Reached Field... Type:33554432: Name:: Value :-1200: Listing siblings... Finished siblings... Popping up
Figure 6. CREATE PROCEDURE: results of recursive example

DECLARE
DECLARE
DECLARE statement
DECLARE –variable name BIT

BLOB
BOOLEAN
CHAR
CHARACTER
DATE
DECIMAL
FLOAT
GMTTIME
GMTTIMESTAMP
INT
INTEGER
INTERVAL
REFERENCE TO field_reference
TIME
TIMESTAMP
The DECLARE statement declares a scalar or reference variable that can be used to
store a temporary value. A variable cannot be used before it has been declared and
a variable name cannot be a reserved word.
Declared scalar variables have a NULL value until they are initialized; reference
variables are always initialized.

DELETE
DELETE
DELETE statement
DELETE FROM Database. table_name

schema_name.

AS correlation_name WHERE search_condition
The DELETE statement deletes rows from a table in an external database based on
a search condition.
A correlation name is created that can be used inside the search condition to refer
to the values of columns in the table. This correlation name is either the name of
the table (without the data source qualifier) or the explicit qualifier specified.
Example
If you have a Database node that has been configured with a connection to a table
SHAREHOLDINGS. The following statement could be written to configure the
Database node:
DELETE FROM Database.SHAREHOLDINGS AS H
WHERE H.ACCOUNTNO = Body.AccountNumber;
This removes all rows from the SHAREHOLDINGS table where the ACCOUNTNO
field in the table is equal to the AccountNumber in the message.
Handling database errors

For information about handling database errors, see “Handling database errors” on
page 142.

DETACH
DETACH
DETACH
DETACH dynamic_reference
The DETACH statement detaches a portion of a message tree without deleting it. This
portion can be reattached using the ATTACH statement.
For information about dynamic references, see “Navigating a message tree

For an example of DETACH, see the example in “ATTACH” on page 50.

EVAL
EVAL
EVAL expression
EVAL ( SQL_character_value )
The EVAL statement takes a character value, interprets it as an SQL statement, and
executes it.
The very similar EVAL function (also described here) also takes a character value
but interprets it as an ESQL expression, which returns a value.
EVAL takes one parameter in the form of an expression, and evaluates this
expression and casts the resulting value to a character string if it is not one already.
The expression that is passed to EVAL must therefore be able to be represented as
a character string.
After this first stage evaluation is complete, the behavior of EVAL depends on
whether it is being used as a complete ESQL statement, or in place of an
expression that forms part of an ESQL statement:
v If it is a complete ESQL statement, the character string derived from the first
stage evaluation is executed as if it were an ESQL statement.
v If it is an expression that forms part of an ESQL statement, the character string is
evaluated as if it were an ESQL expression and EVAL returns the result.
In the following examples, A and B are integer scalar variables, and scalarVar1 and
OperatorAsString are character string scalar variables.
The following examples are valid uses of EVAL:

v SET OutputRoot.XML.Data.Result = EVAL(A+B);
The expression A+B is acceptable because, although it returns an integer value,
integer values are representable as character strings, and the necessary cast is
therefore performed before EVAL continues with its second stage of evaluation.
v SET OutputRoot.XML.Data.Result = EVAL(’A’ || operatorAsString || ’B’);
v EVAL(’SET ’ || scalarVar1 || ’ = 2;’);
The semicolon included at the end of the final string literal is necessary because
if EVAL is being used in place of an ESQL statement, its first stage evaluation
must return a string that represents a valid ESQL statement, including the
terminating semicolon.
Variables declared within an EVAL statement do not exist outside that EVAL
statement. In this way EVAL is similar to a function, in which locally-declared
variables are local only and go out of scope when the function is exited.
The real power of EVAL is that it allows you to dynamically construct ESQL
statements or expressions. In the second and third valid examples above, the value
of scalarVar1 or operatorAsString can be set according to the value of an incoming
message field, or other dynamic value, thus allowing you to effectively control
what ESQL is executed without requiring a potentially lengthy IF THEN ladder.

EVAL
However, you must consider the performance implications in using EVAL.
Dynamic construction and execution of statements or expressions is necessarily
more time-consuming than simply executing pre-constructed ones. If performance
is vital, you might find it preferable to write more specific, but faster, ESQL.
The following are not valid uses of EVAL:

v SET EVAL(scalarVar1) = 2;
In this example, EVAL is being used to replace a field reference, not an
expression.
v SET OutputRoot.XML.Data.Result[] = EVAL((SELECT T.x FROM Database.y AS
T));
In this example, the (SELECT T.x FROM Database.y) passed to EVAL returns a
list, which is not representable as a character string.
The following example is acceptable because (SELECT T.x FROM Database.y AS T)

is a character string literal, not an expression in itself, and therefore is
representable as a character string.
SET OutputRoot.XML.Data.Result[]
= EVAL(’(SELECT T.x FROM Database.y AS T)’);
Figure 7 on page 68 shows how to use EVAL to translate XML attributes to tags but
note that this is just an example. The task would be done more efficiently with
field references.

EVAL
DECLARE thePath CHARACTER;
DECLARE newPath CHARACTER;
DECLARE theData CHARACTER;
DECLARE theChildName CHARACTER;
DECLARE theChildType INTEGER;
DECLARE J INTEGER;
DECLARE C INTEGER;
SET thePath = FIELDNAME(InputRoot.XML.*[1]);
EVAL(’SET OutputRoot.XML.’ || thePath || ’ = CAST(InputRoot.XML.*[1] AS CHAR);’);
WHILE (thePath IS NOT NULL) DO
EVAL(’SET I = CARDINALITY(InputRoot.XML.’ || thePath || ’.*[]);’);
SET J = 1;
WHILE ( J <= I ) DO
EVAL(’SET theChildType = FIELDTYPE(InputRoot.XML.’ || thePath || ’.*[J]);’);
EVAL(’SET theChildName = FIELDNAME(InputRoot.XML.’ || thePath || ’.*[J]);’);
/* check for reserved words (eg ITEM) */
IF UPPER(theChildName) IN (’ITEM’) THEN
SET theChildName = ’"’ || theChildName || ’"’;
END IF;
IF (theChildType = 0x01000000) THEN
EVAL(’SET C
= CARDINALITY(OutputRoot.XML.’ || thePath || ’.’ || theChildName || ’[]) + 1;’);
SET newPath = thePath || ’.’ || theChildName || ’[’ || CAST(C AS CHAR) || ’]’;
EVAL(’SET theData = InputRoot.XML.’ || newPath || ’;’);
EVAL(’SET OutputRoot.XML.’ || newPath || ’ = theData;’);
SET OutputRoot.XML.theStack.entry[CARDINALITY(OutputRoot.XML.theStack.*[])+1]
= thePath || ’.’ || theChildName || ’[’ || CAST(C AS CHAR) || ’]’;
END IF;
IF (theChildType = 0x03000000) THEN
EVAL(’SET theData = InputRoot.XML.’ || thePath || ’.*[’ || CAST(J AS CHAR) || ’];’);
EVAL(’SET OutputRoot.XML.’ || thePath || ’.’ || theChildName || ’[LAST]
= ’’’ || theData || ’’’;’);
END IF;
SET J = J + 1;
END WHILE;
SET thePath = OutputRoot.XML.theStack.entry[1];
SET OutputRoot.XML.theStack.entry[1] = NULL;
END WHILE;
SET OutputRoot.XML.theStack = NULL;
Figure 7. Using EVAL to translate XML attributes to tags

IF
IF
IF
IF condition THEN controlled_statements END IF;
IF
IF condition THEN controlled_statements 1 ELSE
controlled_statements 2 END IF;
The IF statement executes one set of statements or another based on the result of
evaluating a condition.
The IF statement takes one of the following forms:

IF condition THEN
controlled_statements
END IF;
or:
IF condition THEN
controlled_statements 1
ELSE
controlled_statements 2
END IF;
If the condition evaluates to TRUE, the first group of controlled_statements are

executed; otherwise the second group (if present) is executed. UNKNOWN and
false are treated the same in this respect. Therefore if the condition evaluates to
UNKNOWN, the second group is executed.

INSERT
INSERT
INSERT statement
INSERT INTO Database. table_name

schema_name.
VALUES( scalar_expression )
,
( column_name )
The INSERT statement adds a new row to an external database.
The optional column name list identifies a list of columns in the target table into
which values are to be inserted. Any columns not mentioned in the column name
list will have their default values inserted.
A runtime error might be generated if problems occur during the insert operation.
For example, the database table might have constraints defined that the insert
operation might violate. In these cases, an exception is thrown unless the node has
been configured to modify this behavior.
Example
The following example assumes that the dataSource property on the Database node
has been configured and that the database identified by that data source has a
table called "TABLE1" with columns A, B, and C.
Given a message that has the following generic XML body:

<A>
<B>1</B>
<C>2</C>
<D>3</D>
</A>
the following INSERT statement inserts a new row into the table with the values
(1, 2, 3).
INSERT INTO Database.TABLE1(A, B, C) VALUES (Body.A.B, Body.A.C, Body.A.D);

page 142.

MOVE
MOVE
MOVE
MOVE target TO source

PARENT
FIRSTCHILD
LASTCHILD TYPE expr NAME expr
PREVIOUSSIBLING
NEXTSIBLING
TYPE expr NAME expr
REPEAT TYPE
REPEAT NAME
REPEAT TYPE NAME
The MOVE statement changes the field to which a reference variable identified by
″target″ points.
If a TO clause is present, it changes the target reference to point at the same entity
as that pointed to by ″source″. This can either be a message field or a declared
variable.
If a PARENT, PREVIOUSSIBLING, NEXTSIBLING, FIRSTCHILD, or LASTCHILD

clause is present, the MOVE statement attempts to move the target reference
variable in the direction specified relative to its current position. If any field exists
in the given direction the move succeeds. If there is no such field, it fails, that is
the reference variable continues to point to the same field or variable as before and
the LASTMOVE function returns false. You can use the LASTMOVE function to
determine the success or failure of a move.
If a TYPE clause, NAME clause, or both are present, the target is again moved in
the direction specified (PREVIOUSSIBLING or NEXTSIBLING, or FIRSTCHILD or
LASTCHILD) but to a field with the given type, name, or both. If the specified
move cannot be made (that is, a field with the given type or name does not exist),
the target remains unchanged and the LASTMOVE function returns false. The
TYPE clause, NAME clause, or both clauses can contain any expression that
returns a value of a suitable data type (INTEGER for type and CHARACTER for
name). An exception is thrown if the value supplied is NULL.
When using MOVE with PREVIOUSSIBLING or NEXTSIBLING, you can specify

REPEAT, TYPE, and NAME keywords that move the target to the previous or next
field with the same type and name as the current field. The REPEAT keyword is
particularly useful when moving to a sibling of the same kind because you do not
have to write expressions to define the type and name.
Example
MOVE cursor FIRSTCHILD TYPE 0x01000000 NAME ’Field1’;
This example moves the reference variable ″cursor″ to the first child field of the
field to which ″cursor″ is currently pointing and that has the type 0x01000000 and
the name ’Field1’.

MOVE
Note that the MOVE statement never results in the creation of new fields.
A common usage of the MOVE statement is to step from one instance of a

repeating structure to the next. The fields within the structure can then be accessed
by using a relative field reference. For example:
WHILE LASTMOVE(sourceCursor) DO
SET targetCursor.ItemNumber = sourceCursor.item;
SET targetCursor.Description = sourceCursor.name;
SET targetCursor.Price = sourceCursor.prc;
SET targetCursor.Tax = sourceCursor.prc * 0.175;
SET targetCursor.quantity = 1;
CREATE NEXTSIBLING OF targetCursor AS targetCursor REPEAT;
MOVE sourceCursor NEXTSIBLING REPEAT TYPE NAME;
END WHILE;
For further examples of moving a reference variable, see Figure 1 on page 28.
For more information about reference variables, see “Navigating a message tree

PASSTHRU
PASSTHRU
Use PASSTHRU to write ESQL statements that:
v Bypass the WebSphere MQ Integrator Broker Broker Parser
v Go straight to the configured back-end database
v Execute a coded statement
PASSTHRU can be used as both a statement and a function.
The first parameter of PASSTHRU must be a valid ESQL expression containing

your database syntax. PASSTHRU allows you to use database syntax not normally
supported by ESQL.
In Version 2.1, commas can be used as decimal points against an appropriately

configured database.
The behavior of the PASSTHRU function depends on whether it is passed one,

two, or more parameters. The first parameter of the PASSTHRU function must
always be an ESQL expression that either is, or evaluates to, a string. You must use
question marks in the string to denote where any parameter substitution is
required.
If only one other parameter is passed, that parameter evaluates to one of the
following:
v A single scalar value. If this is the case, it is inserted into the first parameter
marker.
v A list of values. If this is the case, the list items are inserted in order into each of
the parameter markers within the string.
If two or more other parameters are passed, each parameter is bound to the
corresponding question mark in the statement string: that is, the first parameter is
bound to the first question mark, the second parameter is bound to the second
question mark, and so on.
The question marks are processed as parameter markers in a prepared SQL

statement by the DBMS. If you pass values to functions in the DBMS you must use
appropriately typed parameter markers. For example, in DB2® a parameter marker
would be cast as follows:
SET OutputRoot.XML.Result.Data[] =
PASSTHRU(’SELECT * FROM user1.stocktable
where time=TIMESTAMP(CAST(? as TIMESTAMP)),’1999-12-31-23.59.59.999999’);
Here are some examples that illustrate different ways of using the PASSTHRU
statement:
SET OutputRoot.XML.Result.Data[] =
PASSTHRU(’SELECT * FROM user1.stocktable’);
PASSTHRU(’DELETE FROM user2.AccountData WHERE AccountId =
?’, InputBody.Data.Account.Id);
SET OutputRoot.XML.Result.Data
= PASSTHRU(’SELECT AccountNum FROM user2.AccountData
WHERE AccountId = ?’, InputBody.Data.Account.Id);
SET OutputRoot.XML.Result.Data[]
= PASSTHRU(’SELECT AccountNum FROM user2.AccountData
WHERE AccountId IN (? , ? , ?)’,
InputBody.Data.Account.Id[]);

PASSTHRU
PASSTHRU(’INSERT INTO user1.stocktable (stock_id, quantity)
values (?, ?)’, InputBody.Transaction.Id,
InputBody.Transaction.Quantity);

PASSTHRU
Using the drag and drop facility with PASSTHRU
If you are using the drag and drop facility to create ESQL and the PASSTHRU
statement contains the whole ESQL statement, you must ensure you include quotes
around the ESQL statement being applied. For example:
PASSTHRU(’INSERT INTO user1.stocktable VALUES (1)’);
However, if a variable is first set and then referenced in the PASSTHRU statement,
quotes are not needed. For example:
SET myVar = ’SELECT * FROM user1.stocktable’;
SET OutputRoot.XML.Data[] = PASSTHRU(myVar);
Considerations when calling stored procedures

If you decide to use the PASSTHRU statement to call stored procedures, the
following considerations must be noted:
v WebSphere MQ Integrator Broker uses Open Database Connectivity (ODBC) to
connect to databases. ODBC Version 1 provides support for Stored Procedure
calls using the following ODBC escape sequence:
call procedure-name [([parameter][,[parameter]]...)]}
Only the escape sequence described above is supported for the input parameters
of the PASSTHRU function in WebSphere MQ Integrator Broker.
v Using the SQL CALL facility, a database Stored Procedure can be called. This
procedure behaves as if a sequence of in-line SQL statements are being executed.
v Stored procedures can exist either:
– Individually (supported by both DB2 and Oracle). This would be written as
follows:
PASSTHRU(’{call proc_insert_comp(?,?)}’,InputBody.Test.Company,InputBody.Test.Price);
– As part of a collective that is accessed using a Package mechanism (supported
by Oracle). This would be written as follows:
PASSTHRU(’{call share_management.add_share(?,?)}’,
InputBody.Test.Company,InputBody.Test.Price);
v When writing Stored Procedures they can be either:
Noncommittal
The procedure logic does not take explicit commit and rollback action
(supported by both DB2 and Oracle).
If a message flow is either committed or rolled back, the database
operations are either committed or rolled back. This is consistent with
the behavior of the Database and Warehouse nodes that have a
transaction property of automatic.
Committal
The procedure logic contains explicit commit and rollback actions
(supported by Oracle).
Even if a message flow rollback occurs, the database operations are
committed. This is consistent with the behavior of the Database and
Warehouse nodes that have a transaction property of commit.
For further information about coordinated transactions, see the
WebSphere MQ Integrator Broker Administration Guide.
Stored Procedure calls, whether committal or noncommittal, will affect any

database operations (and subsequent outcome) if a message flow rollback occurs.

PASSTHRU
Limitations
There are some limitations when using PASSTHRU to call stored procedures. To
illustrate the limitations, please consider the following example:
PASSTHRU(’{call proc_delete_comp(?)}’,InputBody.Test.Company);
1. WebSphere MQ Integrator Broker only supports input parameters.
2. SqlMoreResults cannot be used by WebSphere MQ Integrator Broker to retrieve
result sets.

PROPAGATE
PROPAGATE
PROPAGATE
PROPAGATE
When used in a Compute node, the PROPAGATE statement finalizes the node’s
output message trees (that is, it fixes their header chains) and propagates to the
downstream nodes within the message flow. The PROPAGATE statement then
clears the output message trees and reclaims the memory ready for further use.
The Environment tree is always propagated. The Local Environment, message, and
exception list trees, which are propagated can be either the input or output trees
under the control of the ″Compute Mode″ property as normal. However, it is the
output trees that are finalized and cleared, regardless of which ones are
propagated.
By default the Compute node automatically propagates to its downstream nodes

when its SQL terminates. This can be prevented by having the SQL return FALSE
(see “RETURN” on page 79).
When used in a Database node, the PROPAGATE statement behaves similarly. It

propagates the input message to the downstream nodes within the message flow.
By default the Database node also automatically propagates to its downstream
nodes when its SQL terminates. This can be prevented by having the SQL return
FALSE (see “RETURN” on page 79).
Although it is legal, you are not recommended to use the PROPAGATE statement
within Filter nodes. It currently has no effect within these nodes but this might not
be true of future releases.
The ESQL in this example produces three messages on the OUT terminal using the
sample message in “Message used in this book” on page 147:
ESQL:
DECLARE i INTEGER;
Set i = 1;
WHILE i <= CARDINALITY(InputRoot.XML.Invoice.Purchases."Item"[]) DO
SET OutputRoot.XML = NULL;
SET OutputRoot.XML.BookSold."Item" = InputRoot.XML.Invoice.Purchases."Item"[i];
PROPAGATE;
SET i = i+1;
END WHILE;
RETURN FALSE;
Figure 8. Example using the PROPAGATE statement

PROPAGATE
<BookSold>
<Item>
<Title Category="Computer" Form="Paperback" Edition="2">The XML Companion </Title>
<ISBN>0201674866</ISBN>
<Author>Neil Bradley</Author>
<Publisher>Addison-Wesley</Publisher>
<PublishDate>October 1999</PublishDate>
<UnitPrice>27.95</UnitPrice>
</Item>
</BookSold>
<BookSold>
<Item>
<Title Category="Computer" Form="Paperback" Edition="2">A Complete Guide to DB2 Universal Database</Title>
<ISBN>1558604820</ISBN>
<Author>Don Chamberlin</Author>
<Publisher>Morgan Kaufmann Publishers</Publisher>
<PublishDate>April 1998</PublishDate>
</Item>
</BookSold>
<BookSold>
<Item>
<Title Category="Computer" Form="Hardcover" Edition="0">JAVA 2 Developers Handbook</Title>
<ISBN>0782121799</ISBN>
<Author>Phillip Heller, Simon Roberts </Author>
<Publisher>Sybex, Inc.</Publisher>
<PublishDate>September 1998</PublishDate> <UnitPrice>59.99</UnitPrice>
</Item>
</BookSold>
Figure 9. Messages produced on OUT terminal by PROPAGATE statement

RETURN
RETURN
RETURN
RETURN
expression
When used in a function, the RETURN statement causes execution of that function
to cease and control to be returned to the calling expression. The ″expression″
(which must be present) is evaluated and acts as the return value of the function. It
is an error for a function to return by running off the list of statements. It is also an
error for the data type of the returned value to be different to that in the function’s
declaration.
When used in a procedure, the RETURN statement causes execution of that

procedure to cease and control to be returned to the calling CALL statement. It is
an error for a RETURN statement used with in a procedure to have an
″expression″.
When used in a Filter, Compute, or Database node’s mainline code, the RETURN
statement causes execution of the node’s ESQL to cease and for control to be
passed to the next node. In these cases, if ″expression″ is present, it must evaluate
to a BOOLEAN value. If ″expression″ is not present, a Filter node assumes a value
of UNKNOWN and propagates to its unknown terminal, but Compute and
Database nodes propagate to their out terminals.
The following table describes the differences between the RETURN statement
when used in the Compute, Filter, and Database nodes.
Table 6. Differences in the use of RETURN with the Compute, Filter, and Database nodes
Return value Result
Compute node:
RETURN (TRUE) Propagate message to OUT
terminal.
(FALSE) Does not propagate.
(UNKNOWN) Does not propagate.
RETURN; – Propagate message to OUT
terminal.
Filter node:
RETURN TRUE Propagate message to TRUE
terminal.
FALSE Propagate message to FALSE
terminal.
UNKNOWN Propagate message to
UNKNOWN terminal.
RETURN; Propagate message to
FAILURE terminal.

RETURN
Table 6. Differences in the use of RETURN with the Compute, Filter, and Database
nodes (continued)
Database node:
RETURN TRUE Propagate message to OUT
terminal.
FALSE Does not propagate.
UNKNOWN Does not propagate.
RETURN; Propagate message to OUT
terminal.
This statement is especially useful when used in Filter nodes because it allows the
filter decision to be made using the full power of ESQL statements, rather than just
an expression.
The following example, which is based on “Message used in this book” on

page 147, illustrates how this statement can be used:
-- Declare variables --
DECLARE a INT;
DECLARE PriceTotal FLOAT;
DECLARE NumItems INT;
-- Initialize values --
SET a = 1;
SET NumItems = 0;
SET PriceTotal = 0.0;
-- Calculate value of order, however if this is a bulk purchase, the --

-- order will need to be handled differently (discount given) so return TRUE --
-- or FALSE depending on the size of the order --
WHILE a <= CARDINALITY(Invoice.Purchases.Item[a] DO
SET NumItems = NumItems + Invoice.Purchases.Item[a].Quantity;
SET PriceTotal = PriceTotal + Invoice.Purchases.Item[a].UnitPrice;
SET a = a + 1;
END;
RETURN PriceTotal/NumItems > 42;
If the average price of items is greater than 42, TRUE is returned; otherwise FALSE
is returned. Thus, a Filter node could route messages describing expensive items
down a different path to messages describing inexpensive items.
See Figure 8 on page 77 for an example of RETURN FALSE to prevent the implicit
PROPAGATE at the end of processing in a Compute node.

SET
SET
SET
SET target –=sourceexpression

TYPE
–NAME
–VALUE
The SET statement evaluates ″sourceexpression″ and assigns the resulting value to
the entity identified by ″target″. The target can either be a declared variable or a
field in a message tree that can be modified, for example Environment,
InputLocalEnvironment, OutputLocalEnvironment, OutputRoot, or
OutputExceptionList.
If the target is a declared variable, the TYPE, NAME and VALUE clauses cannot be
present and the source expression must return a scalar value of a type that is the
same as, or is convertible to, the declared data type of the variable. NULL values
are handled in exactly the same way as any other value.
If the target is a field, it is navigated to (creating the fields if necessary) and its
value is set according to a set of rules, which depend on the presence or absence of
TYPE, NAME, or VALUE clauses, and the data type returned by source expression.
If array indices are used in the field reference, only one instance of a particular
field can be created. If you write a SET statement starting:
SET OutputRoot.XML.Message.Structure[2].Field = ...
at least one instance of "Structure" must already exist in the message. That is, the
only fields in the tree that are created are ones on a direct path from the root to the
field identified by the field reference.
If a TYPE clause is present, the type of the target field is changed to that returned
by source expression. An exception is thrown if the result is not scalar, is not of
type INTEGER, or is NULL.
If a NAME clause is present, the name of the target field is changed to that
returned by source expression. An exception is thrown if the result is not scalar, is
not of type CHARACTER, or is NULL.
If a VALUE clause is present, the value of the target field is changed to that
returned by source expression. An exception is thrown if the result is not scalar.
If you do not use TYPE, NAME, or VALUE clauses (the most common case) the
behavior depends on whether source expression evaluates to a scalar, a field
reference, or a list (various kinds) :
v If it is a scalar, the value of the target field is changed to that returned by source
expression. The exception to this is if the result is NULL, the target field is
detached.
v If it is a field reference, the target field’s value is taken from the source field, any
child fields are detached and the new field is given copies of the source field’s

SET
child fields (and grandchildren and so on). If the field indicated by the source
expression does not exist, the target field is detached.
v If it is a list, any child fields are detached and a new set of child fields are
added according to the type and content of the list.
SET statements are particularly useful in Compute nodes that make a modification
to a message, either changing a field, or adding a new field to the original
message. SET statements are also useful in Filter and Database nodes to set
declared variables or the fields in the Environment tree or Local Environment trees.
You can use statements such as these in a Compute node that modifies a message:
SET OutputRoot.XML.Order.Name = UPPER(InputRoot.XML.Order.Name);
This example simply puts one field in the message into uppercase. The first
statement constructs an output message that is a complete copy of the input
message. The second statement sets the value of the "Order.Name" field (which it
is assumed the message flow writer knows will exist in the input message) to a
new value, as defined by the expression on the right.
If the Order.Name field hadn't existed in the original input message, it won't exist
in the output message as generated by the first statement. The expression on the
right of the second statement will return NULL (because the field referenced inside
the UPPER function call does not exist). Assigning the NULL value to a field has
the effect of deleting it if it already exists, and so the effect is that the second
statement has no effect.
If you wish to assign a NULL value to a field without deleting it, use a statement
like this:
SET OutputRoot.XML.Order.Name VALUE = NULL;

THROW
THROW
THROW EXCEPTION
USER SEVERITY expression CATALOG catalog name

MESSAGE message number ,
VALUES ( expression )
The THROW statement generates a user exception causing the transaction to be

rolled back.
SEVERITY is an optional clause that determines the severity associated with the
exception. The clause can contain any expression which returns a non-NULL,
integer value. If the clause is not provided it defaults to 1.
CATALOG is an optional clause and if this value is not provided it defaults to the
WebSphere MQ Integrator Broker version catalog.
MESSAGE is an optional clause and if this value is not provided it defaults to the
first message number of the block of messages provided for the use of THROW
statements in WebSphere MQ Integrator Broker catalog (2949). If you decide to
enter a message number in the THROW statement, message numbers 2949 - 2999
are available for you to use. Alternatively, you can generate your own catalog.
The optional VALUES field is to enable you to insert data into your message. You
can insert any number of pieces of information, but note that the messages
supplied (2949 - 2999) cater for 8 inserts only. For information about SQLSTATE
and SQLCODE, refer to “Database state functions” on page 101.
Here are some examples of how you might use a THROW statement:
v THROW USER EXCEPTION;
This means that there are no inserts.

v THROW USER EXCEPTION CATALOG ’WMQIv210’ MESSAGE 2949 VALUES(1,2,3,4,5,6,7,8) ;
v THROW USER EXCEPTION CATALOG ’WMQIv210’ MESSAGE 2949 VALUES(’The SQL State: ’,
SQLSTATE, ’The SQL Code: ’, SQLCODE, ’The SQLNATIVEERROR: ’, SQLNATIVEERROR,
’The SQL Error Text: ’, SQLERRORTEXT ) ;
v THROW USER EXCEPTION CATALOG ’WMQIv210’ MESSAGE 2949 ;
v THROW USER EXCEPTION CATALOG ’MyCatalog’ MESSAGE 1234 VALUES(’Hello World’) ;
You can use the THROW statement at any time: you do not have to use database
state indicators.
For more information about how to throw an exception, and details of SQLSTATE,
SQLCODE, SQLNATIVEERROR, and SQLERRORTEXT, see “Database state functions” on
page 101.

UPDATE
UPDATE
The UPDATE statement updates the values of specified rows and columns in a
table in an external database.
UPDATE statement
UPDATE Database. table_name

schema_name. AS correlation_name
SET column_name=expression
WHERE search_condition
Examples
The following example updates the PRICE column of the row in the
STOCKPRICES table whose COMPANY column matched the value given in the
Company field in the message that the Database node is processing.
UPDATE Database.StockPrices AS SP
SET PRICE = Body.Message.StockPrice
WHERE SP.COMPANY =Body.Message.Company
In the following example, the "INV.QUANTITY" in the right hand side of the
assignment refers to the previous value of the column before any updates have
taken place:
UPDATE Database.INVENTORY AS INV
SET QUANTITY = INV.QUANTITY - Body.Message.QuantitySold
WHERE INV.ITEMNUMBER = Body.Message.ItemNumber
The following example shows multiple column updates:

UPDATE Database.table AS T
SET column1 = T.column1+1,
column2 = T.column2+1;
Compare the syntax to the way you assign to multiple fields in a Compute node:
SET field = expression;
The form of the assignment; the column on the left of the assignment must be a
single identifier. It must not be qualified with a table name or correlation name. In
contrast, any column references to the right of the assignment must be qualified
with a table name or correlation name.

page 142.

WHILE
WHILE
WHILE
WHILE predicate DO controlled_statements END WHILE;
The WHILE statement executes a sequence of statements repeatedly as long as the

controlling condition is TRUE.
The WHILE statement takes the following form (note the position of the
semicolons):
WHILE predicate DO
controlled statements
END WHILE;
For example:
DECLARE I INTEGER;
SET I = 1;
WHILE I <= 10 DO
SET I = I + 1;
END WHILE;
The same caveats apply to using the WHILE statement as apply in any language,
that is, it is up to you to ensure that the loop will terminate. UNKNOWN and
FALSE are treated in the same way: therefore if the control predicate evaluates to
UNKNOWN the loop terminates.

WHILE

Chapter 5. ESQL functions
This chapter describes all the ESQL functions. For a definition of a function see
“Functions” on page 4.
Most of the functions described here impose restrictions on the data types of the
arguments that can be passed to the function. If the values passed to the functions
do not match the required data types, errors are generated at node configuration
time whenever possible. Otherwise runtime errors are generated when the function
is evaluated.
The functions described here are divided into the following categories:
v “Numeric functions” on page 90
v “String manipulation functions”
v “Datetime functions” on page 94
v “Field functions” on page 96
v “Database state functions” on page 101
v Miscellaneous functions:
– “CARDINALITY” on page 96
– “CASE” on page 97
– “COALESCE” on page 102
– “LASTMOVE” on page 97
– “NULLIF” on page 102
– “PASSTHRU” on page 102
– “SAMEFIELD” on page 97
– “SELECT” on page 100
– “UUIDASBLOB” on page 102
– “UUIDASCHAR” on page 103
String manipulation functions

The following functions perform manipulations on all string data types (BIT,
BLOB, and CHARACTER). Exceptions to this are UPPER, LOWER, LTRIM, and
RTRIM, which operate only on character strings.
In these descriptions, the term ’singleton’ is used to refer to a single part (bit, byte,
or character) within a string of that type.
LENGTH
LENGTH(source_string)
The LENGTH function returns an integer value giving the number of singletons in
source_string. If the source_string is NULL, the result is the NULL value.
Examples:
LENGTH('Hello World!'); returns 12.
LENGTH(''); returns 0.

LOWER
LOWER, LCASE
LOWER(source_string)
LCASE(source_string)
The LOWER and LCASE functions both return a new character string, which is
identical to source_string, except that it has all uppercase letters replaced with the
corresponding lowercase letters. If source_string is NULL, the result is NULL.
For example:
LOWER(’Mr Smith’) returns ’mr smith’
LOWER(’22 Railway Cuttings’) returns ’22 railway cuttings’
LCASE(’ABCD’) returns ’abcd’
LTRIM
LTRIM(source_string)
LTRIM returns a string value of the same data type and content as source_string
but with any leading default singletons removed.
The LTRIM function is equivalent to TRIM(LEADING FROM source_string).
If source_string is NULL, the result is a NULL value of the same type as

source_string.
The default singleton depends on the data type of source_string:
character ’ ’ (space)
byte X’00’
bit B’0’
OVERLAY
The OVERLAY function returns a new string of the same type as the source and is
identical to source string, except that a given substring in the string, starting from
the specified numeric position and of the given length has been replaced
by source_string2. When the length of the substring is zero, nothing is replaced.
OVERLAY(source_string PLACING source_string2 FROM start_position )
OVERLAY(source_string PLACING source_string2 FROM start_position FOR string_length)
The following example shows how you can use OVERLAY:

OVERLAY (’ABCDEFGHIJ’ PLACING ’1234’ FROM 4 FOR 3) returns ’ABC1234GHIJ’
If any of the parameters are NULL, the result is a NULL value of the same data
type as source_string. If string_length is not specified, string_length is assumed
to be equal to LENGTH(source_string2).
The result of the OVERLAY function is equivalent to:

SUBSTRING(source_string FROM 1 FOR start_position -1 ) || source_string2 ||
SUBSTRING(source_string FROM start_position + string length)
(where || is the concatenation operator).

POSITION
POSITION
POSITION( search_string IN source_string )
The POSITION function returns an integer that gives the position of the first
occurrence of one string (search_string) in a second string (source_string). A
position of 1 corresponds to the first character of source_string.
If the value of search_string has a length of zero, the result is one. If the
search_string cannot be found, the result is 0. If any of the parameters to the
POSITION function are NULL, the result is NULL.
For example:
POSITION(’Village’ IN ’Hursley Village’); returns 9
POSITION(’Town’ IN ’Hursley Village’); returns 0
RTRIM
RTRIM(source_string)
RTRIM returns a string value of the same data type and content as source_string
but with any trailing default singletons removed.
The RTRIM function is equivalent to TRIM(TRAILING FROM source_string).
If source_string is NULL, the result is a NULL value of the same type as

source_string.
The default singleton depends on the data type of source_string:
byte X’00’
bit B’0’
SUBSTRING
SUBSTRING(source_string FROM start_position)
SUBSTRING(source_string FROM start_position FOR string_length)
The SUBSTRING function returns a new string of the same type as the source
string containing one contiguous run of characters extracted from the
source_string as specified by the start_position and length parameters.
If any of the parameters are NULL, the result is a NULL value of the same type as
the source_string. (Note this is not a zero length string).
For example:
SUBSTRING('Hello World!' FROM 7 FOR 4) returns 'Worl'
TRIM
TRIM( trim_specification trim_singleton FROM source_string )
TRIM( trim_specification FROM source_string )
TRIM( trim_singleton FROM source_string )
TRIM( source_string )
The TRIM function returns a new string of the same type source_string, in which
the trim_specification singletons have been removed.
Chapter 5. ESQL functions 89

TRIM
trim_specification is one of LEADING, TRAILING, or BOTH. If
trim_specification is not specified, BOTH is assumed. If trim_singleton is not
specified, a default singleton is assumed. This default depends on the data type of
source_string:
byte X’00’
bit B’0’
TRIM returns a string value of the same data type and content as source_string
but with any leading or trailing singletons that are equal to trim_singleton
removed (depending on the value of trim_specification). If any of the parameters
are NULL, the result is a NULL value of the same type as source_string.
The FROM keyword is not required, and is in fact prohibited if neither a trim
specification, for example LEADING or TRAILING, nor a trim singleton, is
specified.
It is often unnecessary to strip trailing blanks from character strings before

comparison because the rules of character string comparison mean that trailing
blanks are not significant.
The following examples illustrate the behavior of the TRIM function:

TRIM(TRAILING 'b' FROM 'aaabBb') returns 'aaabB'
TRIM(' a ') returns 'a'
TRIM(LEADING FROM ' a ') returns 'a '
TRIM('b' FROM 'bbbaaabbb') returns 'aaa'
UPPER, UCASE
UPPER(source_string)
UCASE(source_string)
The UPPER and UCASE functions both return a new character string, which is
identical to source string, except that it has all lowercase letters replaced with the
corresponding uppercase letters. If the source string is NULL, the result is NULL.
For example:
UPPER(’ABCD’) returns ’ABCD’
UCASE(’abc123) returns ’ABC123’
Numeric functions
ABS
ABS(source_number)
ABSVAL(source_number)
The ABS function returns the absolute value of the source number, that is, a
number with the same magnitude as the source but without a sign. The parameter
must be a numeric value. If the parameter to the ABS function is NULL, the result
is NULL.
For example:
ABS( -3.7 ) returns 3.7
ABS( 3.7 ) returns 3.7
ABS( 1024 ) returns 1024

BITAND
BITAND
BITAND(source_integer_1, source_integer_2, ...)
The BITAND function takes two or more integer values and returns the result of
performing the bitwise AND on the binary representation of the numbers. If any of
the parameters are NULL, the result is NULL.
For example:
BITAND(12, 7) returns 4
1100 12
AND 0111 7
_________
0100 4
BITNOT
BITNOT(source_integer)
The BITNOT function takes an integer value and returns the result of performing
the bitwise complement on the binary representation of the number. If the
parameter is NULL, the result is NULL.
For example:
BITNOT(7) returns -8
00...0111 7
NOT
_________
11...1000 -8
BITOR
BITOR(source_integer_1, source_integer_2, ...)
The BITOR function takes two or more integer values and returns the result of
performing the bitwise OR on the binary representation of the numbers. If any of
For example:
BITOR(12, 7) returns 15
1100 12
OR 0111 7
_________
1111 15

BITXOR
BITXOR
BITXOR(source_integer_1, source_integer_2, ...)
The BITXOR function takes two or more integer values and returns the result of
performing the bitwise XOR on the binary representation of the numbers. If any of
For example:
BITXOR(12, 7) returns 11
1100 12
XOR 0111 7
_________
1011 11
CEIL
CEIL(source_number)
CEILING(source_number)
The CEIL function returns the smallest integer value greater than or equal to
source_number. The parameter can be any numeric data type. If the parameter to
the CEIL function is NULL, the result is a NULL value of the same type as the
parameter.
For example:
CEIL(1) returns 1
CEIL(1.2) returns 2.0
CEIL(-1.2) returns -1.0

FLOOR
FLOOR
FLOOR(source_number)
The FLOOR function returns the largest integer value less than or equal to
source_number. The parameter can be any numeric data type. If the parameter is
NULL, the result is a NULL value of the same type as the parameter.
For example:
FLOOR(1) returns 1
FLOOR(1.2) returns 1.0
FLOOR(-1.2) returns -2.0
MOD
MOD(divided, divider)
The MOD function returns the remainder when the first parameter is divided by
the second parameter. The result is negative only if the first parameter is negative.
Parameters must be integers. The function returns an integer. If any parameter is
NULL, the result is NULL.
For example:
MOD(7, 3) returns 1
MOD(-7, 3) returns -1
MOD(7, -3) returns 1
MOD(6, 3) returns 0
ROUND
ROUND(source_number, precision)
If precision is a positive number, the result of the ROUND function is the source
number rounded to precision places right of the decimal point. If precision is
negative, the result is source_number rounded to the absolute value of precision
places to the left of the decimal point.
source_number can be any built-in numeric data type. precision must be an

integer. The result of the function is INTEGER if the first parameter is INTEGER,
FLOAT if the first parameter is FLOAT, and DECIMAL if the first parameter is
DECIMAL. If any parameter is NULL, the result is NULL.
For example:
ROUND(27.75, 2) returns 27.75
ROUND(27.75, -1) returns 30.0
SQRT
SQRT(source_number)
The SQRT function returns the square root of source_number. The parameter can be
any built-in numeric data type. The result is a FLOAT. If the parameter is NULL,
the result is NULL.

ROUND
For example:
SQRT(4) returns 2E+1
SQRT(2) returns 1.414213562373095E+0
SQRT(-1) throws an exception
TRUNCATE
TRUNCATE(source_number, precision)
If precision is positive, the result of the TRUNCATE function is source_number

truncated to precision places right of the decimal point. If precision is negative,
the result is source_number truncated to the absolute value of precision places to
the left of the decimal point.
source_number can be any built-in numeric data type. precision must evaluate to
an INTEGER. The result is of the same data type as source_number. If any
parameter is NULL, the result is NULL.
For example:
TRUNCATE(27.75, 2) returns 27.75
TRUNCATE(27.75, -1) returns 20.0
Datetime functions
In addition to the functions described here, you can use arithmetic operators to
perform various natural calculations on datetime values. For example, you can use
the – (minus) operator to calculate the difference between two dates as an interval,
or you can add an interval to a timestamp. These are described in “Using
arithmetic operators with datetime functions” on page 11.
EXTRACT
EXTRACT(component FROM datetime_value)
The EXTRACT function extracts individual fields from datetime values and
intervals.
component can be any of the keywords YEAR, MONTH, DAY, HOUR, MINUTE,
and SECOND in the EXTRACT function, but you can only extract a field that is
present in the datetime_value value. Either a parse-time or a runtime error is
generated if the requested field does not exist within the particular data type. For
example:
EXTRACT(YEAR FROM CURRENT_DATE)
EXTRACT(HOUR FROM LOCAL_TIMEZONE)
CURRENT_DATE
CURRENT_DATE
The CURRENT_DATE function returns a DATE value representing the current date
in local time.
Note that, as with all SQL functions that take no parameters, no parentheses are
required or accepted. All calls to CURRENT_DATE within the processing of one
node are guaranteed to return the same value.

CURRENT_TIME
CURRENT_TIME
CURRENT_TIME
The CURRENT_TIME function returns a TIME value representing the current local
time.
required or accepted. All calls to CURRENT_TIME within the processing of one
node are guaranteed to return the same value.
CURRENT_TIMESTAMP
CURRENT_TIMESTAMP
The CURRENT_TIMESTAMP function returns TIMESTAMP value representing the

current date and local time.
required or accepted. All calls to CURRENT_TIMESTAMP within the processing of
one node are guaranteed to return the same value.
CURRENT_GMTDATE
CURRENT_GMTDATE
The CURRENT_GMTDATE function returns a DATE value representing the current

date in the GMT time zone.
required or accepted. All calls to CURRENT_GMTDATE within the processing of
CURRENT_GMTTIME
CURRENT_GMTTIME
The CURRENT_GMTTIME function returns a GMTTIME value representing the

current time in the GMT time zone.
required or accepted. All calls to CURRENT_GMTTIME within the processing of
CURRENT_GMTTIMESTAMP
CURRENT_GMTTIMESTAMP
The CURRENT_GMTTIMESTAMP function returns a GMTTIMESTAMP value

representing the current date and time in the GMT time zone.
required or accepted. All calls to CURRENT_GMTTIMESTAMP within the
processing of one node are guaranteed to return the same value.

LOCAL_TIMEZONE
LOCAL_TIMEZONE
LOCAL_TIMEZONE
The LOCAL_TIMEZONE function returns an interval value that represents the

local time zone displacement from GMT.
required or accepted. The value returned is an interval in hours and minutes
representing the displacement of the current time zone from Greenwich Mean
Time. The sign of the interval is such that a local time could be converted to a time
in GMT by subtracting the result of the LOCAL_TIMEZONE function.
Field functions
BITSTREAM
BITSTREAM(field_reference)
The BITSTREAM function returns a value of type BLOB representing the bitstream
described by the given field and its children. For incoming messages, the
appropriate portion of the incoming bitstream is used. For messages constructed by
Compute nodes, the following algorithm is used to establish the ENCODING,
CCSID, message set, message type, and message format:
v If the addressed field has a previous sibling and this sibling is the root of a
subtree belonging to a parser capable of providing an ENCODING and CCSID,
these values are obtained and used in the generation of the requested bitstream.
Otherwise the broker’s default ENCODING and CCSID (that is, those of its
queue manager) are used.
v Similarly, if the addressed field has a previous sibling and this sibling is the root
of a subtree belonging to a parser capable of providing a message set, message
type, and message format, these values are obtained and used in the generation
of the requested bitstream. Otherwise, the zero length strings are used.
This function is typically used in message warehouse scenarios, where the bit
stream of a message needs to be stored in a database. The function returns the bit
stream of the physical portion of the incoming message, identified by the
parameter. In some cases it does not return the bit stream representing the actual
field identified. For example the following two calls would return the same value:
BITSTREAM(Root.MQMD);
BITSTREAM(Root.MQMD.UserIdentifier);
because they lie in the same portion of the message.
CARDINALITY
CARDINALITY(field_reference [])
Returns the number of fields in the array, specified by the parameter.
For more information about the CARDINALITY function, see page 26.
FIELDNAME
FIELDNAME(source_field_reference)
See “Anonymous field references” on page 27 for examples using FIELDNAME.

FIELDNAME
The FIELDNAME function returns the name of the field identified by
source_field_reference as a character value. If the parameter identifies a
nonexistent field, NULL is returned.
FIELDTYPE
FIELDTYPE(source_field_reference)
The FIELDTYPE function returns the type of the field identified by

source_field_reference as an integer. If the parameter identifies a nonexistent
entity, NULL is returned. Note that this is the type of the field, not the data type of
the field that the parameter identifies.
LASTMOVE
LASTMOVE(source_dynamic_reference)
The LASTMOVE function returns a boolean value indicating whether the last
MOVE function applied to source_dynamic_reference was successful (TRUE) or
not (FALSE).
For information about dynamic references, including an example of how to use this
function, see “Navigating a message tree anonymously using dynamic references”
on page 28.
SAMEFIELD
SAMEFIELD(source_field_reference1, source_field_reference2)
The SAMEFIELD function returns a BOOLEAN value indicating whether two field
references point to the same target. If they do, SAMEFIELD returns TRUE,
otherwise SAMEFIELD returns FALSE.
An example of the use of SAMEFIELD based on “Message used in this book” on

page 147 follows:
DECLARE ref1 REFERENCE TO OutputRoot.XML.Invoice.Purchases.Item[1];
MOVE ref1 NEXTSIBLING;
SET Result = SAMEFIELD(ref1,OutputRoot.XML.Invoice.Purchases.Item[2]);
Here Result is TRUE.
For information about dynamic references, see “Navigating a message tree

Complex functions
CASE
The CASE function returns a value depending on a set of rules defined in WHEN
clauses.
There are two forms, the simple form and the searched forms.

CASE
ELSE NULL
CASE searched-when-clause END
simple-when-clause ELSE result-expression
simple-when-clause:
source value WHEN test_value THEN result-value

NULL
searched-when-clause:
WHEN search-condition THEN result-value

NULL
In the simple form, source_value is compared with each test_value until a match
is found. The result of the CASE function is the value of the corresponding
result_value. The data type of source_value must therefore be comparable to the
data type of each test_value.
The CASE function must have at least one WHEN. The ELSE is optional. The
default ELSE expression is NULL. A CASE expression is delimited by END. Note
that unlike many languages there is no requirement for the test values to be literal
values.
The searched-when clause version is similar but has the additional flexibility of
allowing a number of different values to be tested rather than just one value being
tested against a number of values.
The following example shows a CASE function with simple WHEN clause:
CASE CurrentMonth
WHEN ’01’ THEN ’January’
WHEN ’02’ THEN ’February’
WHEN ’03’ THEN ’March’
WHEN ’04’ THEN ’April’
WHEN ’05’ THEN ’May’
WHEN ’06’ THEN ’June’
ELSE ’Second half of year’
END
The following example shows a CASE function with a searched WHEN clause:
SET Month = SUBSTRING(InputBody.Invoice.InvoiceDate FROM 6 FOR 2)
CASE
WHEN Month = ’01’ THEN ’January’
WHEN Month = ’02’ THEN ’February’
WHEN Month = ’03’ THEN ’March’
WHEN Month = ’04’ THEN ’April’
WHEN Month = ’05’ THEN ’May’
WHEN Month = ’06’ THEN ’June’
ELSE ’Second half of year’
END

CAST
CAST
The CAST function transforms the value of one data type into another data type.
CAST function
CAST (source_expression AS BIT

BLOB
BOOLEAN
CHAR
CHARACTER
DATE
DECIMAL(precision, scale)
FLOAT
GMTTIME
GMTTIMESTAMP
INT
INTEGER
INTERVAL interval qualifier
TIME
TIMESTAMP
)
CCSID ccsid_expression ENCODING encoding_expression
See Chapter 7, “Transforming data from one data type to another (CAST)” on
page 111 for more information about using the CAST function. For a description of
precision, scale, and interval qualifier, see “Numbers” on page 6 and
“INTERVAL” on page 8.

SELECT
SELECT
SELECT
( SELECT SelectClause FROM Source

THE AS Alias
)
WHERE Condition
SelectClause:
Expression
AS Target
ITEM Expression
ColumnFunction ( Expression )
(1)
*
ColumnFunction:
COUNT
MAX
MIN
SUM
Notes:
1 Valid with the COUNT function only.
where:
v ″Source″ is a reference to a field, a field array, or a database table
v ″Alias″ is a temporary variable name
v ″Condition″ is any boolean expression
v ″Target″ is a relative path
The SELECT function combines, filters, and reformats complex message and
database data. It is the normal method of message transformation. ESQL SELECT
can be used to:
v Comprehensively reformat messages
v Access database tables
v Make an output array that is a subset of an input array
v Make an output array that contains just the values of an input array
v Count the number of entries in an array
v Select the minimum or maximum value from a number of entries in an array

SELECT
v Sum the values in an array
ESQL SELECT differs from database SQL SELECT in the following ways:
v ESQL can produce tree structured result data but SQL cannot
v ESQL can accept arrays in SELECT clauses but SQL cannot
v ESQL has THE and ITEM but SQL does not
v ESQL has no SELECT ALL
v ESQL has no SELECT DISTINCT
v ESQL has no GROUP BY or HAVING
v ESQL has no AVG column function
For information about using the SELECT function, see Chapter 8, “Using the
SELECT function” on page 125.
Database state functions

Within a message flow, you can access and update an external database resource
using the available ESQL database functions in the Filter, Database and Compute
nodes. When making calls to an external database, you might get errors. For
example a table does not exist, a database is not available, or attempting an insert
for a key that already exists. When these errors occur an exception is generated.
The default behavior with the property ’ThrowExceptionOnDatabaseError’ is to
stop processing the node and to propagate the message trees to the Failure
terminal, and write the details of the error to the ExceptionList. You can handle a
database error in ESQL by unchecking the ″ThrowExceptionOnDatabaseError″
property box.
The following four database state functions can be used to return the SQLCODE,
SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT of the DBMS call made in
ESQL. These can be used in conditional statements in current node’s ESQL to
handle possible errors. This means that the exceptions cannot be thrown in these
situations and the THROW statement can be used to throw an exception as before
if a certain SQL return code is not expected. See “THROW” on page 83 for a
description of THROW.
This section describes the four functions that can used to capture database return
codes issued as a result of making a database call:
SQLCODE
Returns an INTEGER data type with a default value of 0 (zero).
SQLERRORTEXT
Returns a CHARACTER data type with a default value of ’’ (empty string).
SQLNATIVEERROR
Returns an INTEGER data type with a default value of 0 (zero).
SQLSTATE
Returns a 5 character data type of CHARACTER with a default value of
’00000’ (five zeros as a string).
In order to be able to capture these values, the ThrowExceptionOnDatabaseError

property box must be unchecked in the node performing the database operation.
The following example shows how you might use these four functions:

Database state functions
DECLARE SQLState1 CHARACTER;
DECLARE SQLErrorText1 CHARACTER;
DECLARE SQLCode1 INTEGER;
DECLARE SQLNativeError1 INTEGER;
-- Make a database insert to a table that does not exist --

INSERT INTO Database.DB2ADMIN.NONEXISTENTTABLE (KEY,QMGR,QNAME)
VALUES (45,’REG356’,’my TESTING 2’);
--Retrieve the database return codes --

SET SQLState1 = SQLSTATE;
SET SQLCode1 = SQLCODE;
SET SQLErrorText1 = SQLERRORTEXT;
SET SQLNativeError1 = SQLNATIVEERROR;
--Use the THROW statement to back out the database and issue a user exception--
THROW USER EXCEPTION MESSAGE 2950 VALUES
( ’The SQL State’ , SQLState1 , SQLCode1 , SQLNativeError1 , SQLErrorText1 );
Miscellaneous functions
You can also use the following functions:
COALESCE
COALESCE(source_value1, source_value2...)
The COALESCE function returns the first argument that is not NULL. The
arguments are evaluated in the order in which they are specified, and the result of
the function is the first argument that is not NULL. The result is NULL only if all
the arguments are NULL. The arguments must be compatible.
The COALESCE function can be used to provide a default value for the value of a
field, which might not exist in a message. For example, the expression:
COALESCE(Body.Salary, 0)
would return the value of the Salary field in the message if it existed, or 0 (zero) if
that field did not exist.
NULLIF
The NULLIF function returns a NULL value if the arguments are equal; otherwise,
it returns the value of the first argument. The arguments must be comparable. The
result of using NULLIF(e1,e2) is the same as using the expression:
CASE WHEN e1=e2 THEN NULL ELSE e1 END
Note that when e1=e2 evaluates to unknown (because one or both arguments is
NULL), CASE expressions consider this not true. Therefore, in this situation,
NULLIF returns the value of the first argument.
PASSTHRU
The PASSTHRU statement is identical to the PASSTHRU function, except that it
returns the value returned by the extended SQL. It is described in “PASSTHRU” on
page 73.
UUIDASBLOB
UUIDASBLOB
UUIDASBLOB(source_character_uuid)

UUIDASBLOB
The first form of the UUIDASBLOB function creates a new universally unique
identifier (UUID) and returns it as a BLOB.
The second form converts an existing character UUID in the

dddddddd_dddd_dddd_dddd_dddddddddddd to the BLOB form. An exception is thrown
if the parameter is not of the expected form.
UUIDASCHAR
UUIDASCHAR
UUIDASCHAR(source_blob_uuid)
The first form of the UUIDASCHAR function creates a new universally unique
identifier (UUID) as a CHARACTER value.
The second form converts an existing BLOB UUID to the character form.

Chapter 6. Using SELECT with ROW and LIST constructors
ROW and LIST constructors
The ROW and LIST constructor functions can be used to explicitly generate rows
or lists of values that can be assigned to fields in an output message.
A ROW consists of a sequence of named values. When assigned to a field reference

it results in the creation of that sequence of named values as child fields of the
referenced field.
A LIST consists of a sequence of unnamed values. When assigned to an array field

reference (indicated by [] suffixed to the last element of the reference), each value
is assigned in sequence to an element of the array. A ROW cannot be assigned to
an array field reference; a LIST cannot be assigned to a non-array field reference.
ROW constructor
Example 1
SET OutputRoot.XML.Data = ROW(’granary’ AS bread,
’riesling’ AS wine,
’stilton’ AS cheese);
produces:
<Data>
<bread>granary</bread>
<wine>riesling</wine>
<cheese>stilton</cheese>
</Data>
Example 2
Given the following XML input message body:
<Proof>
<beer>5</beer>
<wine>12</wine>
<gin>40</gin>
</Proof>
the following ESQL:

SET OutputRoot.XML.Data = ROW(InputBody.Proof.beer,
InputBody.Proof.wine AS vin,
(InputBody.Proof.gin * 2) AS special);
produces the following result:

<Data>
<beer>5</beer>
<vin>12</vin>
<special>80</special>
</Data>
Because the values in this case are derived from field references, which already
have names, it is not necessary to explicitly provide a name for each element of the
row, but you might choose to do so.

A LIST constructor
Example 1
Given the following XML message input body:
<Car>
<size>big</size>
<color>red</color>
</Car>
The following ESQL:

SET OutputRoot.XML.Data.Result[] = LIST{InputBody.Car.colour,
’green’,
’blue’};
produces the following results:

<Data>
<Result>red</Result>
<Result>green</Result>
<Result>blue</Result>
</Data>
In the case of a LIST, there is no explicit name associated with each value. The
values are assigned in sequence to elements of the message field array specified as
the target of the assignment. Note that curly braces rather than parentheses are
used to surround the LIST items.
Example 2
Given the following XML input message body:
<Data>
<Field>Keats</Field>
<Field>Shelley</Field>
<Field>Wordsworth</Field>
<Field>Tennyson</Field>
<Field>Byron</Field>
</Data>
the following ESQL:

-- Copy the entire input message to the output message,
-- including the XML message field array as above
SET OutputRoot.XML.Data.Field[] = LIST{’Henri’,’McGough’,’Patten’};
Produces the following output:

<Data>
<Field>Henri</Field>
<Field>McGough</Field>
<Field>Patten</Field>
</Data>
The previous members of the Data.Field[] array have been discarded. Assigning a
new list of values to an already existing message field array causes all the elements
in the existing array to be removed before the new ones are assigned.
ROW and LIST combined

A ROW might validly be an element in a LIST. For example:
SET OutputRoot.XML.Data.Country[] =
LIST{ROW(’UK’ AS name,’pound’ AS currency),
ROW(’US’ AS name, ’dollar’ AS currency),
’default’};

produces the following result:
<Data>
<Country>
<name>UK</name>
<currency>pound</currency>
</Country>
<Country>
<name>US</name>
<currency>dollar</currency>
</Country>
<Country>default</Country>
</Data>
ROW and non-ROW values can be freely mixed within a LIST.
A LIST cannot validly be a member of a ROW. Only named scalar values can be
members of a ROW.
ROW and LIST comparisons

ROWs and LISTs can validly be compared against other ROWs and LISTs.
Example 1
IF ROW(InputBody.Data.*[1],InputBody.Data.*[2]) =
ROW(’Raf’ AS Name,’25’ AS Age) THEN ...
IF LIST{InputBody.Data.Name, InputBody.Data.Age} = LIST{’Raf’,’25’} THEN ...
The following XML input message body would cause both the IF expressions in
both of the above statements to evaluate to TRUE:
<Data>
<Name>Raf</Name>
<Age>25</Age>
</Data>
In the comparison between ROWs, both the name and the value of each element
are compared, whereas in the comparison between LISTs only the value of each
element is compared. In both cases the cardinality and sequential order of the LIST
or ROW operands being compared must be equal in order for the two operands to
be equal. In other words, all the following are false because either the sequential
order or the cardinality of the operands being compared do not match:
ROW(’alpha’ AS A, ’beta’ AS B) =
ROW(’alpha’ AS A, ’beta’ AS B, ’delta’ AS D)
ROW(’alpha’ AS A, ’beta’ AS B) =
ROW(’beta’ AS B,’alpha’ AS A)
LIST{1,2,3} = LIST{1,2,3,4}
LIST{3,2,1} = LIST{1,2,3}
Example 2
Consider the following ESQL:
IF InputBody.Places =
ROW(’Ken’ AS first, ’Bob’ AS second, ’Kate’ AS third) THEN ...
The following XML input message body would cause the above IF expression to
evaluate to TRUE:
<Places>
<first>Ken</first>
<second>Bob</second>
<third>Kate</third>
</Places>
Chapter 6. Using SELECT with ROW and LIST constructors 107

The presence of an explicitly constructed ROW as one of the operands to the
comparison operator results in the other operand also being treated as a ROW.
This should be contrasted with a comparison such as:

IF InputBody.Lottery.FirstDraw = InputBody.Lottery.SecondDraw THEN ...
which compares the value of the FirstDraw and SecondDraw fields, not the names
and values of each of FirstDraw and SecondDraw’s child fields constructed as a
ROW. Thus an XML input message body such as:
<Lottery>
<FirstDraw>wednesday
<ball1>32</ball1>
<ball2>12</ball2>
</FirstDraw>
<SecondDraw>saturday
<ball1>32</ball1>
<ball2>12</ball2>
</SecondDraw>
</Lottery>
would not result in the above IF expression being evaluated as TRUE, because the
values ’wednesday’ and ’saturday’ are being compared, not the names and values
of the ball fields.
Example 3
Consider the following ESQL:
IF InputBody.Cities.City[] = LIST{’Athens’,’Sparta’,’Thebes’} THEN ...
The following XML input message body would cause the IF expression to evaluate
to TRUE:
<Cities>
<City>Athens</City>
<City>Sparta</City>
<City>Thebes</City>
</Cities>
Two message field arrays can be compared together in this way, for example:
IF InputBody.Cities.Mediaeval.City[] =
InputBody.Cities.Modern.City[] THEN ...
IF InputBody.Cities.Mediaeval.*[] = InputBody.Cities.Modern.*[] THEN ...
IF InputBody.Cities.Mediaeval.(XML.Element)[] =
InputBody.Cities.Modern.(XML.Element)[] THEN ...
The following XML input message body would cause the IF expression of the first
and third of the statements above to evaluate to TRUE:
<Cities>
<Mediaeval>1350
<City>London</City>
<City>Paris</City>
</Mediaeval>
<Modern>1990
<City>London</City>
<City>Paris</City>
</Modern>
</Cities>
However the IF expression of the second statement would evaluate to FALSE,

because the *[] indicates that all the children of Mediaeval and Modern are to be

compared, not just the (XML.Element)s. In this case the values 1350 and 1990,
which form nameless children of Mediaeval and Modern, are compared as well as
the values of the City tags.
Note that the IF expression of the third statement above would evaluate to TRUE
with an XML input message body such as:
<Cities>
<Mediaeval>1350
<Location>London</Location>
<Location>Paris</Location>
</Mediaeval>
<Modern>1990
<City>London</City>
<City>Paris</City>
</Modern>
</Cities>
LISTs are composed of unnamed values. It is the values of the child fields of
Mediaeval and Modern that are compared, not their names.
Chapter 6. Using SELECT with ROW and LIST constructors 109


Chapter 7. Transforming data from one data type to another
(CAST)
ESQL deals with data in several different formats. The data types supported are
described in “Data types” on page 5. To transform the value of one data type into
another data type, you must use a CAST function.
For example, CAST functions are used often when dealing with generic XML
messages: all fields in a generic XML message have character values, therefore to
perform arithmetic calculations or datetime comparisons (for example), the string
value of the field must first be cast into a value of the appropriate type. If you
wanted to filter on trade messages where the date of the trade was today, you
could write the following expression:
CAST(Body.Trade.Date AS DATE) = CURRENT_DATE
In this example, the string value of the Date field in the message is converted into
a date value, and compared with the current date.
This chapter describes:

v “The CAST function”
v “Supported casts” on page 112
v “Implicit casts” on page 117
v “Data types of values from external sources” on page 122
The CAST function

CAST function
CAST (source_expression AS BIT

BLOB
BOOLEAN
CHAR
CHARACTER
DATE
DECIMAL(precision, scale)
FLOAT
GMTTIME
GMTTIMESTAMP
INT
INTEGER
INTERVAL interval qualifier
TIME
TIMESTAMP
)
CCSID ccsid_expression ENCODING encoding_expression

CAST specifications
Source expression
A CAST specification returns its first operand (source_expression) as the type
specified by the second operand (the target data type). In all cases, if the source
expression is NULL, the result is NULL. If the evaluated source expression is not
compatible with the target data type, or if the source expression is of the wrong
format, a runtime error is generated.
Not all conversions are supported, see “Supported casts” for a full list of
supported conversions.
CCSID
The CCSID clause allows a code page to be specified. It is used for certain
conversions only.
The CCSID expression can be any expression evaluating to a value of type INT. It
is interpreted according to normal WebSphere MQ Integrator Broker rules for
CCSIDs.
ENCODING
The ENCODING clause allows the encoding to be specified. It is used for certain
conversions only. The ENCODING value can be any expression evaluating to a
value of type INT. It is interpreted according to normal WebSphere MQ Integrator
Broker rules for encoding. Valid values are:
v MQENC_NATIVE (0x00000222L)
v MQENC_INTEGER_NORMAL (0x00000001L)
v MQENC_INTEGER_REVERSED (0x00000002L)
v MQENC_DECIMAL_NORMAL (0x00000010L)
v MQENC_DECIMAL_REVERSED (0x00000020L)
v MQENC_FLOAT_IEEE_NORMAL (0x00000100L)
v MQENC_FLOAT_IEEE_REVERSED (0x00000200L)
v MQENC_FLOAT_S390 (0x00000300L)
Supported casts
A CAST is not supported between every combination of data types. Those that are
supported are listed below, along with the effect of the CAST.
Table 7. Supported CASTs
Source data type Target data type Effect
CHARACTER BOOLEAN The character string is interpreted in the same way that a boolean
literal is interpreted. That is, the character string must be one of the
strings TRUE, FALSE, UNKNOWN (in any case combination).
CHARACTER FLOAT The character string is interpreted in the same way as a floating point
literal is interpreted.
CHARACTER DATE The character string must conform to the rules for a date literal or for
the date string. That is, the character string can be either DATE
'2002-10-05' or 2002-10-05.

Supported casts
Table 7. Supported CASTs (continued)
CHARACTER DECIMAL The character string is interpreted in the same way as an exact
numeric literal is interpreted to form a temporary decimal result with
a scale and precision defined by the format of the string. This is
converted into a decimal of the specified precision and scale, with a
runtime error being generated if the conversion would result in loss of
significant digits.
CHARACTER INTEGER The character string is interpreted in the same way as an integer literal
is interpreted.
CHARACTER INTERVAL The character string must conform to the rules for an interval literal
with the same interval qualifier as specified in the CAST specification,
or it must conform to the rules for an interval string that apply for the
specified interval qualifier.
CHARACTER TIME The character string must conform to the rules for a time literal or for
the time string. That is, the character string can be either TIME
'09:24:15' or 09:24:15.
CHARACTER TIMESTAMP The character string must conform to the rules for a timestamp literal
or for the timestamp string. That is, the character string can be either
TIMESTAMP '2002-10-05 09:24:15' or 2002-10-05 09:24:15.
CHARACTER GMTTIME The character string must conform to the rules for a GMT time literal
or for the time string. That is, the character string can be either
GMTTIME '09:24:15' or 09:24:15.
CHARACTER GMTTIMESTAMP The character string must conform to the rules for a GMT timestamp
literal or for the timestamp string. That is, the character string can be
either GMTTIMESTAMP '2002-10-05 09:24:15' or 2002-10-05 09:24:15.
CHARACTER BIT The character string must conform to the rules for a bit string literal or
to the rules for the contents of the bit string literal. That is, the
character string can be of the form B'bbbbbbb' or bbbbbb (where 'b'
can be either '0' or '1').
If either a CCSID or ENCODING clause is specified, the given

character string is converted into the specified CCSID and encoding
and placed without further conversion into the bit array return value.
If only a CCSID is specified, bigendian encoding is assumed.
If only an encoding is specified, a CCSID of 1208 is assumed.
This function might report conversion errors if the given code page or
encoding are unknown or the given data contains Unicode characters
that cannot be converted to the given code page.
Chapter 7. Transforming data from one data type to another (CAST) 113
Supported casts
CHARACTER BLOB The character string must conform to the rules for a binary string
literal or to the rules for the contents of the binary string literal. That
is, the character string can be of the form X'hhhhhh' or hhhhhh (where
'h' can be any hexadecimal digit characters).
If either a CCSID or ENCODING clause is specified, the given

character string is converted into the specified CCSID and encoding
and placed without further conversion into the byte array return
value.
encoding are unknown or the given data contains Unicode characters
that cannot be converted to the given code page.
BOOLEAN CHARACTER If the source value is TRUE, the result is the character string 'TRUE'. If
the source value is FALSE, the result is the character string 'FALSE'.
Because the UNKNOWN boolean value is the same as the NULL
value for booleans, the result is the NULL character string value if the
source value is UNKNOWN.
FLOAT CHARACTER The result is the shortest character string that conforms to the
definition of an approximate numeric literal and whose mantissa
consists of a single digit that is not '0', followed by a period and an
unsigned integer, and whose interpreted value is the value of the float.
DATE CHARACTER The result is a string conforming to the definition of a date literal,
whose interpreted value is the same as the source date value.
For example:
CAST(DATE '2002-10-05' AS CHAR)
would return:
DATE '2002-10-05'
DECIMAL CHARACTER The result is the shortest character string that conforms to the
definition of an exact numeric literal and whose interpreted value is
the value of the decimal.
INTEGER CHARACTER The result is the shortest character string that conforms to the
definition of an exact numeric literal and whose interpreted value is
the value of the integer.
INTERVAL CHARACTER The result is a string conforming to the definition of an INTERVAL
literal, whose interpreted value is the same as the source interval
value.
For example:
CAST(INTERVAL '4' YEARS AS CHAR)
would return:
INTERVAL '4' YEARS

Supported casts
TIME CHARACTER The result is a string conforming to the definition of a TIME literal,
whose interpreted value is the same as the source time value.
For example:
CAST(TIME '09:24:15' AS CHAR)
would return:
TIME '09:24:15'
TIMESTAMP CHARACTER The result is a string conforming to the definition of a TIMESTAMP
literal, whose interpreted value is the same as the source timestamp
value.
For example:
CAST(TIMESTAMP '2002-10-05 09:24:15' AS CHAR)
would return:
TIMESTAMP '2002-10-05 09:24:15'
GMTTIME CHARACTER The result is a string conforming to the definition of a GMTTIME
literal whose interpreted value is the same as the source value. The
result string will have the form GMTTIME 'hh:mm:ss'.
GMTTIMESTAMP CHARACTER The result is a string conforming to the definition of a
GMTTIMESTAMP literal whose interpreted value is the same as the
source value. The result string will have the form GMTTIMESTAMP
'yyyy-mm-dd hh:mm:ss'.
BIT CHARACTER The result is a string conforming to the definition of a bit string literal
whose interpreted value is the same as the source value. The result
string will have the form B'bbbbbb' (where b is either '0' or '1').
If either a CCSID or ENCODING clause is specified, the given bit

array is assumed to be characters in the specified CCSID and encoding
and is code page converted into the character return value.
encoding are unknown, the data supplied is not an integral number of
characters of the given code page or the given data contains characters
that are not valid in the given code page.
Supported casts
BLOB CHARACTER The result is a string conforming to the definition of a binary string
literal whose interpreted value is the same as the source value. The
result string will have the form X'hhhh' (where h is any hexadecimal
digit character).
If either a CCSID or ENCODING clause is specified, the given byte

array is assumed to be characters in the specified CCSID and encoding
and is code page converted into the character return value.
encoding are unknown, the data supplied is not an integral number of
characters of the given code page or the given data contains characters
that are not valid in the given code page.
TIME GMTTIME The result value is the source value minus the local time zone
displacement (as returned by LOCAL_TIMEZONE). The hours field is
calculated modulo 24.
GMTTIME TIME The result value is source value plus the local time zone displacement
(as returned by LOCAL_TIMEZONE). The hours field is calculated
modulo 24.
GMTTIMESTAMP TIMESTAMP The result value is source value plus the local time zone displacement
(as returned by LOCAL_TIMEZONE).
TIMESTAMP GMTTIMESTAMP The result value is the source value minus the local time zone
displacement (as returned by LOCAL_TIMEZONE).
INTEGER or FLOAT The number is converted, with rounding if necessary.
DECIMAL
FLOAT INTEGER or If the conversion would not lead to loss of leading significant digits,
DECIMAL the conversion will happen with the number being rounded as
necessary. If the conversion would lead to loss of leading significant
digits, a runtime error is generated. Loss of significant digits can occur
when converting an approximate numeric value to an integer, or to a
decimal whose precision is not sufficient.
INTEGER or INTEGER or If the conversion would not lead to loss of leading significant digits,
DECIMAL DECIMAL the conversion will happen with the number being rounded as
necessary. If the conversion would lead to loss of leading significant
digits, a runtime error is generated. Loss of significant digits can occur
when converting (say) a decimal to another decimal with insufficient
precision, or an integer to a decimal with insufficient precision.
INTERVAL INTERVAL Year-month intervals are only convertible to year-month intervals, and
day-second intervals are only convertible to day-second intervals. The
conversion is done by converting the source interval into a scalar in
units of the least significant field of the target interval qualifier. This
value is normalized into an interval with the target interval qualifier.
For example, to convert an interval that has the qualifier MINUTE TO
SECOND into an interval with the qualifier DAY TO HOUR, the
source value is converted into a scalar in units of hours, and this value
is normalized into an interval with qualifier DAY TO HOUR.
INTERVAL INTEGER or If the interval value has a qualifier that has only one field, the result is
DECIMAL an exact numeric with that value. If the interval has a qualifier with
more than one field, such as YEAR TO MONTH, a runtime error is
generated.

Supported casts
INTEGER or INTERVAL If the interval qualifier specified has only one field, the result is an
DECIMAL interval with that qualifier with the field equal to the value of the
exact numeric. Otherwise a runtime error is generated.
TIME TIMESTAMP The result is a value whose date fields are taken from the current date,
and whose time fields are taken from the source time value.
TIMESTAMP TIME The result is a value whose fields consist of the time fields of the
source timestamp value.
TIMESTAMP DATE The result is a value whose fields consist of the date fields of the
source timestamp value.
INTEGER BIT The given integer is converted to a bit array with a maximum of 263
elements.
INTEGER BLOB The given integer is converted to a byte array with a maximum of 263
elements.
BLOB INTEGER The byte array has a maximum of 263 elements and is converted to an
integer. An error is reported if the source is not of the correct length to
match an integer.
BIT INTEGER The bit array has a maximum of 263 elements and is converted to an
integer. An error is reported if the source is not of the correct length to
match an integer.
BLOB BIT The given byte array is converted to a bit array with a maximum of
263 elements.
BIT BLOB The given bit array is converted to a byte array with a maximum of
263 elements. An error is reported if the source is not of a suitable
length to produce a BLOB (that is a multiple of 8).
Refer to “Numbers” on page 6 for information about precision and scale. Refer to
“INTERVAL” on page 8 for information about interval qualifier.
Implicit casts
It is not always necessary to cast values between types. Some casts are done
implicitly. For example, numbers are implicitly cast between the three numeric
types for the purposes of comparison and arithmetic. Character strings are also
implicitly cast to other data types for the purposes of comparison.
There are three situations in which a data value of one type is cast to another type
implicitly. The behavior and restrictions of the implicit cast are the same as
described for the explicit cast function, except where noted in the following
sections.
Implicit CASTs for comparisons

The standard SQL comparison operators >, <, >=, <=, =, <> are supported for
comparing two values in ESQL.
When the data types of the two values are not the same, one of them can be
implicitly cast to the type of the other to allow the comparison to proceed. In the
table below, the vertical axis represents the left hand operand, the horizontal axis
represents the right hand operand.
Implicit casts
An ″L″ means that the right hand operand is cast to the type of the left hand
operand before comparison, an ″R″ means the opposite, an ″X″ means no implicit
casting takes place, and a blank means that comparison between the values of the
two data types is not supported.
Table 8. Implicit CASTs for comparison
ukn bln int float dec char time gtm date ts gts ivl blob bit
ukn
bln X L
int X R R L
float L X L L
dec L R X L
chr R R R R X R R R R R R1 R R
tm L X L
gtm L R X
dt L X R2 R2
ts L L2 X L
2
gts L L R X
1
ivl L X
blb L X
bit L X
Notes:
1. When casting from a character string to an interval, the character string must be of the format ″INTERVAL
’<values>’ <qualifier>″. The format ″<values>″, which is allowable for an explicit CAST, is not allowable here
because no qualifier external to the string is supplied.
2. When casting from a DATE to a TIMESTAMP or GMTTIMESTAMP, the time portion of the TIMESTAMP is set
to all zero values (00:00:00). This is different to the behavior of the explicit cast, which sets the time portion to
the current time.
Numeric types: The comparison operators operate on all three numeric types.
Character strings: You cannot define an alternative collation order that, for
example, collates upper and lowercase characters equally.
When comparing character strings, trailing blanks are not significant so the
comparison 'hello' = 'hello ' returns true.
Datetime values: Datetime values are compared in accordance with the natural
rules of the Gregorian calendar and clock.
You can compare the time zone you are working in with the GMT time zone. The
GMT time zone is converted into a local time zone based on the time zone
difference between your local time zone and the GMT time specified. When you
compare your local time with the GMT time, the comparison is based on the
difference at a given time on a given date.
Conversion is always based on the value of LOCAL_TIMEZONE. This is because

GMT timestamps are converted to local timestamps only if it can be done
unambiguously. Converting a local timestamp to a GMT timestamp has difficulties
around the daylight saving cut-over time, and converting between times and GMT

Implicit casts
times (without date information) has to be done based on the LOCAL_TIMEZONE
value, because you cannot specify which time zone difference to use otherwise.
Booleans: Boolean values can be compared using all or the normal comparison
operators. The TRUE value is defined to be greater than the FALSE value.
Comparing either value to the UNKNOWN boolean value (which is equivalent to
NULL) returns an UNKNOWN result.
Intervals: Intervals are compared by converting the two interval values into
intermediate representations, so that both intervals have the same interval qualifier.
Year-month intervals can be compared only with other year-month intervals, and
day-second intervals can be compared only with other day-second intervals.
For example, if an interval in minutes, such as INTERVAL '120' MINUTE is compared

with an interval in days to seconds, such as INTERVAL '0 02:01:00', the two
intervals are first converted into values that have consistent interval qualifiers,
which can be compared. So, in this example, the first value could be converted into
an interval in days to seconds, which will give INTERVAL '0 02:00:00' which can
be compared with the second value.
Comparing character strings with other types: If a character string is compared

to a value of another type, WebSphere MQ Integrator Broker attempts to cast the
character string into a value of the same data type as the other value.
For example, you could write an expression such as:

'1234' > 4567
The character string on the left would be converted into an integer before the
comparison takes place. This behavior reduces some of the need for explicit CAST
operators when comparing values derived from a generic XML message with
literal values. (For details of explicit casts that are supported, see Table 7 on
page 112.) It is this facility that allows you to write an expression such as:
Body.Trade.Quantity > 5000
In this example, the field reference on the left evaluates to the character string
'1000' and, because this is being compared to an integer, that character string is
converted into an integer before the comparison takes place.
Note that you must still check whether the price field that you want interpreted as
a decimal is greater than a given threshold. You must make sure that the literal
you compare it to is a decimal value and not an integer.
For example:
Body.Trade.Price > 100
would not have the desired effect, because the Price field would be converted into
an integer, and that conversion would fail because the character string contains a
decimal point. However, the following expression will succeed:
Body.Trade.Price > 100.00
Implicit CASTs for arithmetic operations

Normally the arithmetic operators (+, -, *, and /) operate on operands of the same
data type, and return a value of the same data type as the operands. Cases where
it is acceptable for the operands to be of different data types, or where the data
type of the resulting value is different from the type of the operands, are shown in
Table 9 on page 120.
Implicit casts
Table 9. Implicit CASTs for arithmetic operations
Left operand data type Right operand data type Supported Result data type
operators
INTEGER FLOAT +, -, *, / FLOAT1
INTEGER DECIMAL +, -, *, / DECIMAL1
INTEGER INTERVAL * INTERVAL4
FLOAT INTEGER +, -, *, / FLOAT1
FLOAT DECIMAL +, -, *, / FLOAT1
FLOAT INTERVAL * INTERVAL4
DECIMAL INTEGER +, -, *, / DECIMAL1
DECIMAL FLOAT +, -, *, / FLOAT1
DECIMAL INTERVAL * INTERVAL4
TIME TIME - INTERVAL2
TIME GMTTIME - INTERVAL2
TIME INTERVAL +, - TIME3
GMTTIME TIME - INTERVAL2
GMTTIME GMTTIME - INTERVAL2
GMTTIME INTERVAL +, - GMTTIME3
DATE DATE - INTERVAL2
DATE INTERVAL +, - DATE3
TIMESTAMP TIMESTAMP - INTERVAL2
TIMESTAMP GMTTIMESTAMP - INTERVAL2
TIMESTAMP INTERVAL +, - TIMESTAMP3
GMTTIMESTAMP TIMESTAMP - INTERVAL2
GMTTIMESTAMP GMTTIMESTAMP - INTERVAL2
GMTTIMESTAMP INTERVAL +, - GMTTIMESTAMP3
INTERVAL INTEGER *, / INTERVAL4
INTERVAL FLOAT *, / INTERVAL4
INTERVAL DECIMAL *, / INTERVAL4
INTERVAL TIME + TIME3
INTERVAL GMTTIME + GMTTIME3
INTERVAL DATE + DATE3
INTERVAL TIMESTAMP + TIMESTAMP3
INTERVAL GMTTIMESTAMP + GMTTIMESTAMP3

Implicit casts
Table 9. Implicit CASTs for arithmetic operations (continued)
Left operand data type Right operand data type Supported Result data type
operators
Notes:
1. The operand that does not match the data type of the result is cast to the data type of the result before the
operation proceeds. For example, if the left operand to an addition operator is an INTEGER, and the right
operand is a FLOAT, the left operand is cast to a FLOAT before the addition operation is performed.
2. Subtracting a (GMT)TIME value from a (GMT)TIME value, a DATE value from a DATE value, or a
(GMT)TIMESTAMP value from a (GMT)TIMESTAMP value results in an INTERVAL value representing the time
interval between the two operands.
3. Adding or subtracting an INTERVAL from a (GMT)TIME, DATE or (GMT)TIMESTAMP value results in a new
value of the data type of the non-INTERVAL operand, representing the point in time represented by the original
non-INTERVAL plus or minus the length of time represented by the INTERVAL.
4. Multiplying or dividing an INTERVAL by an INTEGER, FLOAT or DECIMAL value results in a new INTERVAL
representing the length of time represented by the original multiplied or divided by the factor represented by
the non-INTERVAL operand. For example, an INTERVAL value 2 hours 16 minutes multiplied by an FLOAT
value of 2.5 results in a new INTERVAL value of 5 hours 40 minutes. The intermediate calculations involved in
multiplying or dividing the original INTERVAL are carried out in the data type of the non-INTERVAL, but the
individual fields of the INTERVAL (such as HOUR, YEAR, etc.) are always integral, so some rounding errors
might occur.
Implicit CASTs for assignment

Values can be assigned to one of three entities
A message field (or equivalent in an exception or destination list)
Support for implicit conversion between the WebSphere MQ Integrator
Broker data types and the message (in its bitstream form) is dependent on
the appropriate parser. For example, the XML parser casts everything as
character strings before inserting them into the WebSphere MQ message.
A field in a database table
WebSphere MQ Integrator Broker converts each of its data types into a
suitable standard SQL C data type, as detailed in Table 10. Conversion
between this standard SQL C data type, and the data types supported by
each DBMS, is dependent on the DBMS. Consult your DBMS
documentation for more details.
Table 10. Conversions from WebSphere MQ Integrator Broker to SQL data types
WebSphere MQ Integrator Broker data type SQL data type
NULL, or unknown or invalid value SQL_NULL_DATA
BOOLEAN SQL_C_BIT
INTEGER SQL_C_LONG
FLOAT SQL_C_DOUBLE
DECIMAL SQL_C_CHAR1
CHARACTER SQL_C_CHAR
TIME SQL_C_TIME
GMTTIME SQL_C_TIME
DATE SQL_C_DATE
TIMESTAMP SQL_C_TIMESTAMP
GMTTIMESTAMP SQL_C_DATE
INTERVAL Not supported2
Implicit casts
Table 10. Conversions from WebSphere MQ Integrator Broker to SQL data
types (continued)
WebSphere MQ Integrator Broker data type SQL data type
BLOB SQL_C_BINARY
BIT Not supported2
Notes:
1. For convenience, DECIMAL values are passed to the DBMS in character form.
2. There is no suitable standard SQL C data type for INTERVAL or BIT. These must be
cast to another data type, such as CHARACTER, if it is necessary to assign them to a
database field.
A scalar variable
When assigning to a scalar variable, if the data type of the value being
assigned and that of the target variable data type are different, an implicit
cast is attempted with exactly the same restrictions and behavior as
specified for the explicit CAST function. The only exception is when the
data type of the variable is INTERVAL or DECIMAL.
In both these cases, the value being assigned is first cast to a CHARACTER
value, an attempt is made to cast the CHARACTER value to an INTERVAL
or DECIMAL. The reason for this is that INTERVAL requires a qualifier
and DECIMAL requires a precision and scale, these must be specified in
the explicit cast, but must be obtained from the character string when
implicitly casting. Therefore a further restriction is that when implicitly
casting to an INTERVAL variable, the character string must be of the form
″INTERVAL ’<values>’ <qualifier>″. The shortened ″<values>″ form that is
acceptable for the explicit cast is not acceptable here.
Data types of values from external sources

There are two external sources from which data can be extracted by ESQL:
v Message fields
v Database columns
The ESQL data type of message fields depends on the type of the message (XML
for example), and the parser used to parse it. The ESQL data type of the value
returned by a database column reference depends on the data type of the column
in the database.
Table 11 shows which ESQL data types the various built-in DBMS data types (for
DB2 (version shipped with the product), SQL Server Version 7.0, Sybase Version
12.0, and Oracle Version 8.1.5) are cast to when they are accessed by WebSphere
MQ Integrator Broker.
Table 11. Implicit CASTS for database data types to WebSphere MQ Integrator Broker types
WebSphere MQ DB2 SQL Server and Sybase Oracle
Integrator Broker
BOOLEAN BIT
INTEGER SMALLINT, INTEGER, INT, SMALLINT, TINYINT
BIGINT
FLOAT REAL, DOUBLE FLOAT, REAL NUMBER()1
DECIMAL DECIMAL DECIMAL, NUMERIC, MONEY, NUMBER(P)1,
SMALLMONEY NUMBER(P,S)1

Table 11. Implicit CASTS for database data types to WebSphere MQ Integrator Broker types (continued)
WebSphere MQ DB2 SQL Server and Sybase Oracle
Integrator Broker
CHARACTER CHAR, VARCHAR, CLOB CHAR, VARCHAR, TEXT CHAR, NCHAR,
VARCHAR2,
NVARCHAR2, ROWID,
UROWID, LONG, CLOB,
TIME TIME
GMTTIME
DATE DATE
TIMESTAMP TIMESTAMP DATETIME, SMALLDATETIME DATE
GMTTIMESTAMP
INTERVAL
BLOB BLOB BINARY, VARBINARY, RAW LONG, RAW BLOB
TIMESTAMP, IMAGE,
UNIQUEIDENTIFIER
BIT
Not supported DATALINK, GRAPHIC, NTEXT, NCHAR, NVARCHAR NCLOB, BFILE
VARGRAPHIC, DBCLOB
Note:
1. If an Oracle database column with NUMBER data type is defined with an explicit precision (P) and scale (S), it
is cast to an ESQL DECIMAL value; otherwise it is cast to a FLOAT.
For example, an ESQL statement like this:
SET OutputRoot.xxx[]
= (SELECT T.department FROM Database.personnel AS T);
where Database.personnel resolves to a TINYINT column in a SQL Server database table, results in a list of
ESQL INTEGER values being assigned to OutputRoot.xxx.
By contrast, an identical query where Database.personnel resolves to a NUMBER() column in an Oracle

database results in a list of ESQL FLOAT values being assigned to OutputRoot.xxx.

Chapter 8. Using the SELECT function
For an introductory summary of what the SELECT function does, see “SELECT” on
page 100.
Using SELECT for simple message transformation

The following example shows the use of the SELECT function for simple message
transformation:
SET OutputRoot.XML.Data.Output[] =
(SELECT R.Field1, R.Field2 FROM InputRoot.XML.Data.Input[] AS R);
Consider what happens when the following message is processed by this ESQL:
<Data>
<Input>
<Field0>Value00</Field0>
</Input>
<Input>
</Input>
</Data>
The result is the following message:

<Data>
<Output>
</Output>
<Output>
</Output>
</Data>
There are two ″Output″ fields, one for each ″Input″ field. This is because, by
default, SELECT creates an item in its result list for each item described by its
FROM list. Within each ″Output″ field, there is a Field for each field named in the
SELECT clause and these are in the order they are specified within the SELECT,
not their order in the incoming message. The ″R″ introduced by the final AS
keyword is known as a correlation name. It is a local variable that represents in
turn each of the fields addressed by the FROM clause. There is no significance to
the name chosen. In summary, this simple transform does two things:
1. It discards unwanted fields (the Field0’s)
2. It guarantees the order of the fields
It might help understanding to see the same transform implemented by a
procedural algorithm:
DECLARE i INTEGER;
DECLARE count INTEGER;
SET i = 1;
SET count = CARDINALITY(InputRoot.XML.Data.Input[]);

SELECT for simple message transformation
WHILE (i <= count) DO
SET OutputRoot.XML.Data.Output[i].Field1 = InputRoot.XML.Data.Input[i].Field1;
SET OutputRoot.XML.Data.Output[i].Field2 = InputRoot.XML.Data.Input[i].Field2;
SET i = i+ 1;
END WHILE;
These examples show the SELECT version of the transform is much more concise.
It also executes faster. The following is a slightly more advanced transformation:
SET OutputRoot.XML.Data.Output[] =
(SELECT R.Field1 AS Field.F1, R.Field2 AS Field.F2
FROMInputRoot.XML.Data.Input[] AS R);
In this transform, there is an AS clause associated with each item in the SELECT
clause. This gives each field in the result an explicit name rather than their names
simply being inherited from the input. These names can be paths (that is, a dot
separated list of names), as shown in the example. Thus, the output message’s
structure can be arbitrarily different from the input message’s. Using the same
input message, the result in this case is:
<Data>
<Output>
<Field>
<F1>Value10</F1>
<F2>Value20</F2>
</Field>
</Output>
<Output>
<Field>
<F1>Value11</F1>
<F2>Value21</F2>
</Field>
</Output>
</Data>
The expressions in the SELECT clause can be of any complexity and there are no
special restrictions. They can include operators, functions, literals, refer to
variables, or to fields not related to the correlation name. The following example
shows more complex expressions being used:
SET OutputRoot.XML.Data.Output[] = (SELECT
’Start’ As Header,
’Prefix:’ || R.Field1 AS Field.F1,
R.Field2 || ’:Suffix’ AS Field.F2,
’End’ AS Trailer
FROM InputRoot.XML.Data.Input[] AS R);
Using the same input message, the result in this case is:
<Data>
<Output>
<Header>Start</Header>
<Field>
<F1>Prefix:Value10</F1>
<F2>Value20:Suffix</F2>
</Field>
<Trailer>End</Trailer>
</Output>
<Output>
<Header>Start</Header>
<Field>
<F1>Prefix:Value11</F1>
<F2>Value21:Suffix</F2>
</Field><Trailer>End</Trailer>
</Output>
</Data>

SELECT for simple message transformation
As shown above, the AS clauses of the SELECT clause contain a path describing
the full name of the field to be created in the result. These paths can also specify
(as is normal for paths) the type of field to be created. The following is an example
transform that specifies the field types. In this case a conversion from XML tagged
data to XML attributes is being performed:
R.Field1.* AS Field.(XML.Attribute)A1,
R.Field2.* AS Field.(XML.Attribute)A2
FROM InputRoot.XML.Data.Input[] AS R);
<Data>
<Output>
<Field A1="Value10" A2="Value20"/>
</Output>
<Output>
<Field A1="Value11" A2="Value21"/>
</Output>
</Data>
Finally, a WHERE clause can be used to eliminate some of the results. In the
following example a WHERE clause is used to remove results in which a specific
criterion is met. Note that an entire result is either included or excluded:
R.Field1 AS Field.F1,
R.Field2 AS Field.F2
FROM InputRoot.XML.Data.Input[] AS R
WHERE R.Field2 = ’Value21’);
<Data>
<Output>
<Field>
<F1>Value11</F1>
<F2>Value21</F2>
</Field>
</Output>
</Data>

SELECT for complex message transformation
Using SELECT for complex message transformation

All the transforms in the section above were ″simple″ in the sense that the message
repeated at one level in its structure. This topic considers a more complex example,
in which a message contains a variable number of invoices. Each of these invoices
contains a variable number of Items. The transform is:
SET OutputRoot.XML.Data.Statement[] =
(SELECT
I.Initial || COALESCE(I.Initial[2], ’’) AS Customer.Initials,
I.Surname AS Customer.Name,
I.Balance AS Customer.Balance,
(SELECT
II.Description AS Desc,
CAST(II.Price AS FLOAT) * 1.6 AS Cost,
II.Quantity AS Qty
FROM I.Item[] AS II
WHERE II.Price > 0.0 ) AS Purchases.Article[],
(SELECT
SUM( CAST(II.Price AS FLOAT) *
CAST(II.Quantity AS FLOAT) *
1.6 )
FROM I.Item[] AS II ) AS Amount,
’Dollars’ AS Amount.(XML.Attribute)Currency
FROM InputRoot.XML.Data.Invoice[] AS I
WHERE I.Surname <> ’Shop’ );

The input message is:
<Data>
<Invoice>
<Initial>J</Initial>
<Initial>R</Initial>
<Initial>R</Initial>
<Surname>Tolkien</Surname>
<Item>
<Description>Milk Chocolate Bar</Description>
<Price>0.30</Price>
</Item>
<Item>
<Description>Milk Chocolate 50g</Description>
<Price>0.31</Price>
</Item>
<Item>
<Description>Weekly Advertiser</Description>
<Price>0.0</Price>
</Item>
<Item>
<Description>Plain Chocolate Bar</Description>
<Price>0.32</Price>
</Item>
<Balance>123.40</Balance>
<Currency>Sterling</Currency>
</Invoice>
<Invoice>
<Initial>C</Initial>
<Surname>Darwin</Surname>
<Item>
<Description>Birds</Description>
<Price>1.23</Price>
</Item>
<Item>
<Description>Reptiles</Description>
<Price>2.34</Price>
</Item>
<Item>
<Description>Plain Chocolate Bar</Description>
<Price>0.32</Price>
</Item>
<Currency>Pesos</Currency>
</Invoice>
<Invoice>
<Initial></Initial>
<Initial></Initial>
<Initial>X</Initial>
<Surname>Shop</Surname>
<Item>
<Description>Floor Polish</Description>
<Price>2.34</Price>
</Item>
<Currency>Dollars</Currency>
</Invoice>
</Data>

The output is:
<Data>
<Statement>
<Customer>
<Initials>JR</Initials>
<Name>Tolkien</Name>
</Customer>
<Purchases>
<Article>
<Desc>Milk Chocolate Bar</Desc>
<Cost>4.8E-1</Cost>
<Qty>1</Qty>
</Article>
<Article>
<Desc>Milk Chocolate 50g</Desc>
<Cost>4.96E-1</Cost>
<Qty>2</Qty>
</Article>
<Article>
<Desc>Plain Chocolate Bar</Desc>
<Qty>3</Qty>
</Article>
</Purchases>
<AmountCurrency="Dollars">3.008E+0</Amount>
</Statement>
<Statement>
<Customer>
<Initials>C</Initials>
<Name>Darwin</Name>
</Customer>
<Purchases>
<Article>
<Desc>Birds</Desc>
<Cost>1.968E+0</Cost>
<Qty>100</Qty>
</Article>
<Article>
<Desc>Reptiles</Desc>
<Cost>3.744E+0</Cost>
<Qty>2</Qty>
</Article>
<Article>
<Desc>Plain Chocolate Bar</Desc>
<Qty>3</Qty>
</Article>
</Purchases>
<Amount Currency="Dollars">2.05824E+2</Amount>
</Statement>
</Data>
What is key about this transform is that it has two SELECT’s nested inside each
other. The outer one operates on the list of Invoices. The inner one operates on the
list of Items. Note particularly that the AS clause associated with the inner SELECT
is expecting an array:
(SELECT
II.Quantity AS Qty
FROM I.Item[] AS II
WHERE II.Price > 0.0 ) AS Purchases.Article[], <== NB: [ ]

This tells the outer select to expect a variable number of Items in each result. Note
also that each SELECT has its own correlation name: ″I″ for the outer select and
″II″ for the inner one. Each SELECT generally uses its own correlation name but
note that the inner SELECT’s FROM clause refers to the outer SELECT’s correlation
name:
(SELECT
II.Quantity AS Qty
FROM I.Item[] AS II <== NB: I.Item
WHERE II.Price > 0.0 ) AS Purchases.Article[],
This tells the inner SELECT to work with the current Invoice’s Items. Both
SELECTs contain WHERE clauses. The outer one uses one criterion to discard
certain Invoices and the inner one uses a different criterion to discard certain
Items. The example also shows the use of COALESCE to prevent missing input
fields causing the corresponding output field to be missing. Finally, it also uses the
column function SUM to add together the value of all Items in each Invoice.
Column functions are discussed in “Column functions”.
Column functions
A column function is a function that takes the values of a single column in all the
selected rows of a table or message and returns a single scalar result.
Suppose that you want to perform a special action on invoices that have a total
order value greater than a certain amount. In order to calculate the total order
value of an Invoice field, you must multiply the Price fields by the Quantity
fields in all of the Items in the message, and total the result. You can do this using
a SELECT expression as follows:
(
SELECT SUM( CAST(I.Price AS DECIMAL) * CAST(I.Quantity AS INTEGER) )
FROM Body.Invoice."Item"[] AS I
)
The example assumes that it is necessary to use CAST expressions to cast the
string values of the fields Price and Quantity into the correct data types. The cast
of the Price field into a decimal produces a decimal value with the "natural" scale
and precision, that is, whatever scale and precision is necessary to represent the
number. These CASTs would not be necessary if the data were already in an
appropriate data type.
The SELECT expression works in a similar way to the quantified predicate, and
works in much the same way that a SELECT works in standard database SQL. The
FROM clause specifies what is being iterated, in this case, all Item fields in
Invoice, and establishes that the current instance of Item can be referred to using
"I". This form of SELECT involves a column function, in this case the SUM
function, so the SELECT is evaluated by adding together the results of evaluating
the expression inside the SUM function for each Item field in the Invoice. As with
standard SQL, NULL values are ignored by column functions, with the exception
of the COUNT column function explained below, and a NULL value is returned by
the column function only if there are no non-NULL values to combine.
The other column functions that are provided are MAX, MIN, and COUNT. The
COUNT function has two forms that work in different ways with regard to
NULLs. In the first form you use it much like the SUM function above, so, for
example:

Column functions
SELECT COUNT(I.Quantity)
This expression returns the number of Item fields for which the Quantity field is
non-NULL. That is, the COUNT function counts non-NULL values, in the same
way that the SUM function adds non-NULL values. The alternative way of using
the COUNT function is as follows:
SELECT COUNT(*)
Using COUNT(*) counts the total number of Item fields, regardless of whether any
of the fields is NULL. The above example is in fact equivalent to using the
CARDINALITY function, as in:
CARDINALITY(Body.Invoice."Item"[])
In all of the examples of SELECT given here, just as in standard SQL, a WHERE
clause could have been specified to provide filtering on the fields.
Use of the ITEM keyword

The following example shows the use of the ITEM keyword that selects one item
and creates a single value.
SET OutputRoot.XML.Test.Result[] =
(SELECT ITEM T.Field1 FROM InputBody.Test.Input[] AS T);
Sending the same trigger message will result in a message on the output queue
that looks like this:
<Test>
<Result>value1</Result>
</Test>
Comparing this message to the one that is produced if the ITEM keyword is
omitted:
<Test>
<Result>
<Field1>value1</Field1>
</Result>
<Result>
</Result>
</Test>
you can see the effect of the ITEM keyword. With the ITEM keyword, the result is
a list of scalar values rather than a list of fields (name-value pairs).
Effects of the THE keyword

The THE keyword converts a list containing one item to the item itself.
The two previous examples both specified a list as the source of the SELECT in the
FROM clause (so the field reference had a [] at the end), and so in general the
SELECT will generate a list of results. Because of this it was necessary to specify a
list as the target of the assignment (thus the "Result[]" as the target of the
assignment). However, often you know that the WHERE clause that you specify as

THE keyword
part of the SELECT will only return TRUE for one item in the list. In this case the
THE keyword can be used to indicate this.
The following example shows the effect of using the THE keyword:
SET OutputRoot.XML.Test.Result =
THE (SELECT T.Field4, T.Structure1 FROM InputBody.Test.Input[]
AS T WHERE T.Field1 = 'value1');
The THE keyword means that the target of the assignment becomes
OutputRoot.XML.Test.Result (the "[]" is no longer necessary, or even allowed). This
results in the following message:
<Test>
<Result>
<Structure1>
</Structure1>
</Result>
</Test>
Using SELECT to return a scalar value

A SELECT with a column function is not the only form of SELECT that can be
used in a scalar expression. You can make a SELECT return a scalar value by
issuing both the THE and ITEM keywords, for example:
1 + THE(SELECT ITEM T.a FROM Body.Test.A[] AS T WHERE T.b = '123')
Multiple items in the FROM clause (Join)

The FROM clause is not restricted to having one item. Specifying multiple items in
the FROM clause produces the usual cartesian product joining effect, in which
there is an item in the result for all combinations of items in the two lists. This is
the same joining effect as standard SQL. Consider the following message:
<Data>
<Descriptions>
<Description>
<CatNo>1000</CatNo>
<Short>Milk Chocolate Bar</Short>
<Long>Milk Chocolate Bar 50g Christmas edition</Long>
</Description>
<Description>
<CatNo>1001</CatNo>
<Short>Chocolate Bar</Short>
<Long>Chocolate Bar Fun pack 500g Christmas edition</Long>
</Description>
<Description>
<CatNo>1002</CatNo>
<Short>Chocolate</Short>
</Description>
</Descriptions>
<Prices>
<Price>
<Number>1000</Number>
<Buying>23</Buying>
<Selling>26</Selling>
</Price>
<Price>
<Buying>24</Buying>

Multiple items in the FROM clause
</Price>
<Price>
<Buying>25</Buying>
</Price>
</Prices>
</Data>
The message has two sections: the first is a list of items in which each item has a
catalogue number and a description. The second is another list referring to the
same items but containing a price for each item. Using the following transform:
SET OutputRoot.XML.Results.Items.Item[] =
(SELECT D.CatNo, D.Short, P.Number, P.Selling
FROM
InputRoot.XML.Data.Descriptions.Description[] As D,
InputRoot.XML.Data.Prices.Price[] As P
);
produces the following output:

<Items>
<Item>
<CatNo>1000</CatNo>
</Item>
<Item>
<CatNo>1000</CatNo>
</Item>
<Item>
<CatNo>1000</CatNo>
</Item>
<Item>
<CatNo>1001</CatNo>
</Item>
<Item>
<CatNo>1001</CatNo>
</Item>
<Item>
<CatNo>1001</CatNo>
</Item>
<Item>
<CatNo>1002</CatNo>
</Item>
<Item>

Multiple items in the FROM clause
<CatNo>1002</CatNo>
</Item>
<Item>
<CatNo>1002</CatNo>
</Item>
</Items>
There are nine results, that is the number of descriptions in the first list (three)
multiplied by the number of prices in the second (three). The results systematically
work through all the combinations of the two lists. You can see this by looking at
the CatNo and Number fields selected from each result:
CatNo 1000 Number 1000
This is a useful because by applying a where clause, which selects only the useful
combinations, the two lists can be intelligently merged. Now consider the
following transform:
SET OutputRoot.XML.Results.SaleItems.Item[] =
(SELECT D.CatNo AS Catalogue, D.Short AS Description, P.Selling AS Price
FROM
InputRoot.XML.Data.Descriptions.Description[] As D,
InputRoot.XML.Data.Prices.Price[] As P
WHERE D.CatNo = P.Number
);
The addition of the where clause causes results to be produced for those items
only that have similar catalogue numbers. The result with this transform is:
<SaleItems>
<Item>
<Catalogue>1000</Catalogue>
<Price>26</Price>
</Item>
<Item>
<Description>Chocolate Bar</Description>
<Price>27</Price>
</Item>
<Item>
<Description>Chocolate</Description>
<Price>28</Price>
</Item>
</SaleItems>
There are just three results, each of which contains data from both of the original
lists. Each Item has a description taken from the first list and a price taken from
the second.

Using join to translate data

A common problem is translation of data from one form to another. For example,
in one message the types of items are known by names and in another message
the items are known by numbers. For example:
Type Name Type Code
Confectionary 2000
NewsPapers 3000
Hardware 4000
Consider the following message:

<Data>
<Items>
<Item>
<Cat>1000</Cat>
<Type>Confectionary</Type>
</Item>
<Item>
<Cat>1001</Cat>
<Description>Daily Newspaper</Description>
<Type>NewsPapers</Type>
</Item>
<Item>
<Cat>1002</Cat>
<Description>Kitchen Sink</Description>
<Type>Hardware</Type>
</Item>
</Items>
<TranslateTable>
<Translate>
<Name>Confectionary</Name>
</Translate>
<Translate>
<Name>NewsPapers</Name>
</Translate>
<Translate>
<Name>Hardware</Name>
</Translate>
</TranslateTable>
</Data>
Again the message has two sections: the first is a list of items in which each item
has a catalogue number and a type. The second is a translate table between
English type names and numeric type codes. Given the following transform:
SET OutputRoot.XML.Result.Items.Item[] =
(SELECT I.Cat, I.Description, T.Number As Type
FROM
InputRoot.XML.Data.Items.Item[] As I,
InputRoot.XML.Data.TranslateTable.Translate[] As T
WHERE I.Type = T.Name
);
The result is:

<Result>
<Items>
<Item>
<Cat>1000</Cat>

<Type>2000</Type>
</Item>
<Item>
<Cat>1001</Cat>
<Description>Daily Newspaper</Description>
<Type>3000</Type>
</Item>
<Item>
<Cat>1002</Cat>
<Description>Kitchen Sink</Description>
<Type>4000</Type>
</Item>
</Items>
</Result>
In the result, each type name has been converted to its corresponding code. Both
the data and the translate table were in the same message tree, however it is not a
requirement that are in the same message tree.
Joining items in the FROM clause

You can join between a list and a non-list, two non-lists, and so on.
OutputRoot.XML.Test.Result1[] =
(SELECT ... FROM InputBody.Test.A[], InputBody.Test.b);
OutputRoot.XML.Test.Result1 =
(SELECT ... FROM InputBody.Test.A, InputBody.Test.b);
Note the location of the "[]" in each case. Any number of items can be specified in
the FROM list, not just one or two. If any of the items specify "[]" to indicate a list
of items, the SELECT generates a list of results (the list might contain only one
item, but the SELECT can potentially return a list of items). The target of the
assignment must specify a list (so must end in "[]" or the THE keyword must be
used if you know that the WHERE clause guarantees only one combination is
matched.
Selecting from a list of scalars

Consider the following sample message:
<Test>
<A>1</A>
<A>2</A>
<A>3</A>
<A>4</A>
<A>5</A>
</Test>
and the following ESQL statements:

SET OutputRoot.XML.Test.A[] =
(SELECT ITEM A from InputBody.Test.A[]
WHERE CAST(A AS INTEGER) BETWEEN 2 AND 4);
This ESQL gives you the following output:

<A>2</A>
<A>3</A>
<A>4</A>

Selecting from a list of scalars

Chapter 9. Querying external databases
Queries against external databases can be done in much the same way as can be
done in, for example, embedded SQL.
In order to include a query against an external database in a Filter or Compute

node, the node must be configured with the connection information for the
database. This consists of an ODBC data source name. It is up to the WebSphere
MQ Integrator Broker or database administrator to ensure that a suitable ODBC
data source has been created on the systems on which the brokers, to which the
message flows are deployed, are running.
The connection to the database is performed using the database user ID and
password supplied on the mqsicreatebroker command that created the individual
broker. The WebSphere MQ Integrator Broker or database administrator must
therefore ensure that user has sufficient database privileges to query the required
database tables. If not, a runtime error is generated by the broker when it attempts
to process a message and attempts to connect to the database for the first time.
While the standard SQL SELECT syntax is supported for queries to an external
database, there are a number of points to be borne in mind. It is necessary to prefix
the name of the table with the keyword "Database" in order to indicate that the
SELECT is to be targeted at the external database, rather than at a repeating
structure in the message.
Therefore the basic form of database SELECT is:

SELECT ...
FROM Database.TABLE1
WHERE ...
If necessary a schema name can be given:

SELECT ...
FROM Database.SCHEMA.TABLE1
WHERE ...
where SCHEMA is the name of the schema in which the table TABLE1 is defined.
References to column names must be qualified with either the table name or the
correlation name defined for the table by the FROM clause. So, where you could
normally execute a query such as:
SELECT column1, column2 FROM table1
it is necessary to write one of the following two forms:

SELECT T.column1, T.column2 FROM Database.table1 AS T
SELECT table1.column1, table1.column2 FROM Database.table1
This is necessary in order to distinguish references to database columns from any

references to fields in a message that might also appear in the SELECT:
SELECT T.column1, T.column2 FROM Database.table1
AS T WHERE T.column3 = Body.Field2

Querying external databases
The standard ‘select all’ SQL option is supported in the SELECT clause. If you use
this option, you must qualify the column names with either the table name or the
correlation name defined for the table. For example:
SELECT T.* FROM Database.Table1 AS T
The following examples illustrate how the results sets of external database queries
are represented in WebSphere MQ Integrator Broker. The results of database
queries are assigned to fields in a message using a Compute node.
For more information on how to use the Database node, see WebSphere MQ
Integrator Broker Using the Control Center.
Examples of external database queries

This section gives examples of how to :
v “Create a database table”
v “Create a table in a case sensitive database system” on page 141
v “Use the ITEM keyword” on page 141
v “Use the WHERE clause” on page 142
Create a database table

Create a message flow consisting of an MQInput node wired to a Compute node,
wired to an MQOutput node. Configure the queue names on the MQInput node
and MQOutput node to point to suitable queues, and set the Message Domain
property on the Defaults tab of the MQInput node property editor to be "XML".
Create a database table called USERTABLE with two char(6) data type columns (or
equivalent), called Column1 and Column2. Insert two rows into the table so that it
looks like this:
Column1 Column2
Row 1 value1 value3
Row 2 value2 value4
Add a database table input to the Compute node by clicking the Add input button
on the properties pane of the node and entering the ODBC Data Source Name and
table name. The user database ID and password specified when you created the
broker are used for accessing the database, therefore you must ensure that this ID
and password pair have appropriate permissions within the DBMS.
You are also recommended to ensure that you include the schema name when you
create a table as the second component of the database table reference (for
example, Database.user1.USERTABLE) in the Compute node ESQL you specify. This
avoids potential confusion that some databases might encounter.
For example, if you create your database table as user ID user1, but specified user
ID user2 when you created the broker, you might find that the broker attempts to
access table user2.USERTABLE, which does not exist, rather than user1.USERTABLE,
which does.
You can vary the names of the fields produced by explicitly listing the columns
that you want to extract. How you do this depends partly on your database

system. Most database systems are not case sensitive with regard to database
names. In other words, even though a column might be called "COLUMN1", you
can refer to it in a SELECT as "column1".
Configure the Compute node using the following ESQL statements:

(SELECT T.Column1, T.Column2 FROM Database.USERTABLE AS T);
To trigger the SELECT, you must send in a trigger message with an XML body that
is of the following form:
<Test>
<Result>
<Column1>value1</Column1>
</Result>
<Result>
</Result>
</Test>
The exact structure of the XML is not important, but the enclosing tag must be
<Test>. If it is not, the ESQL statements results in top-level enclosing tags being
formed, which is not valid XML.
Create a table in a case sensitive database system

If the database system is case sensitive, you must use an alternative approach. This
approach is also necessary if you want to change the name of the generated field
to something different:
(SELECT T.COLUMN1 AS Column1, T.COLUMN2 AS Column2
FROM Database.USERTABLE AS T);
This example produces the same message as the example above.
Use the ITEM keyword

Suppose that the Compute node were configured using the following ESQL
statements:
(SELECT ITEM T.Column1 FROM Database.USERTABLE AS T);
The same trigger message will produce the following message:

<Test>
</Test>
Chapter 9. Querying external databases 141

The following message is produced if the ITEM keyword is omitted:
<Test>
<Result>
</Result>
<Result>
</Result>
</Test>
Comparing these two messages illustrates the effect of the ITEM keyword. The
evaluation of the ESQL expressions happens independently of any information
about the schema of the target message. In the case of generating a generic XML
message, there is no message schema for the message being generated, so the
structure of the message that is generated must be defined entirely by the ESQL.
Use the WHERE clause

This example illustrates the use of the WHERE clause:
SET OutputRoot.XML.Test.Result =
THE (SELECT ITEM T.Column1 FROM Database.USERTABLE AS T
WHERE T.Column2 = ‘value2’);
This produces the following message:

<Test>
<Result>value1 </Result>
</Test>

If an error occurs when accessing an external database, you have two options:
v Let the node throw an exception
v Process the exception within the node itself using ESQL statements
The first option is the default and the result is that the ESQL processing in the
current node is abandoned. The exception is then propagated backwards up the
message flow until an enclosing catch node or the flow’s input node is reached. If
the flow’s input node is reached, any transaction is rolled back.
The second option, which is new to this release, requires an understanding of

database return codes and a logical course of action to take when an error occurs.
To enable this inline database error processing, you must uncheck the Filter,
Database, or Compute node’s ″ThrowExceptionOnDatabaseError″ property . This
causes the node to fill in the four database state indicators with appropriate
information instead of throwing an exception.
The indicators contain information only when an error (not a warning) occurs,
unless you have the ″TreatWarningsAsErrors″ property checked. In the case of
″successful″ and ″success with information″ database operations, the indicators
contain their default ″success″ values, as described in“Database state functions” on
page 101.

The values contained in these indicators can be used in normal ESQL statements to
make decisions about the action to take. These indicators are accessed by using the
SQLCODE , SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT functions.
If you are attempting inline error processing, you must check the state indicators
after each database statement is executed to avoid missing any errors. When
processing the indicators, if you meet an error that you cannot handle inline, you
might wish to raise a new exception either to deal with it ‘upstream’ in a catch
node or to let it through to the input node so that the transaction is rolled back.
This can be accomplished by using the ESQL THROW statement, see “THROW”
on page 83 for a description.
Chapter 9. Querying external databases 143

Part 3. Appendixes

Appendix A. Examples
This appendix shows some longer examples that illustrate text in the body of this
book. These examples include:
v “Message used in this book”
v “Using a trace to view a message structure” on page 149
v “How to manipulate a variable length BLOB message” on page 153
v “Exception lists” on page 157
v “Example of CREATE statement” on page 164
Message used in this book

The following message is used in many of the examples throughout this book:
<Invoice>
<InvoiceNo>300524</InvoiceNo>
<InvoiceDate>2000-12-07</InvoiceDate>
<InvoiceTime>12:40:00</InvoiceTime>
<TillNumber>3</TillNumber>
<Cashier StaffNo="089">Mary</Cashier>
<Customer>
<FirstName>Andrew</FirstName>
<LastName>Smith</LastName>
<Title>Mr</Title>
<DOB>20-01-70</DOB>
<PhoneHome>01962818000</PhoneHome>
<PhoneWork/>
<Billing>
<Address>14 High Street</Address>
<Address>Hursley Village</Address>
<Address>Hampshire</Address>
<PostCode>SO213JR</PostCode>
</Billing>
</Customer>
<Payment>
<CardType>Visa</CardType>
<CardNo>4921682832258418</CardNo>
<CardName>Mr Andrew J. Smith</CardName>
<Valid>1200</Valid>
<Expires>1101</Expires>
</Payment>
<Purchases>
<Item>
<Title Category="Computer" Form="Paperback" Edition="2">The XML Companion
</Title>
<ISBN>0201674866</ISBN>
<Author>Neil Bradley</Author>
<Publisher>Addison-Wesley</Publisher>
<PublishDate>October 1999</PublishDate>
</Item>
<Item>
<Title Category="Computer" Form="Paperback" Edition="2">A Complete Guide
to DB2 Universal Database</Title>
<ISBN>1558604820</ISBN>
<Author>Don Chamberlin</Author>
<Publisher>Morgan Kaufmann Publishers</Publisher>
<PublishDate>April 1998</PublishDate>

Message used for examples
</Item>
<Item>
<Title Category="Computer" Form="Hardcover" Edition="0">JAVA 2 Developers
Handbook</Title>
<ISBN>0782121799</ISBN>
<Author>Philip Heller, Simon Roberts </Author>
<Publisher>Sybex, Inc.</Publisher>
<PublishDate>September 1998</PublishDate>
</Item>
</Purchases>
<StoreRecords/>
<DirectMail/>
<Error/>
</Invoice>

Using a trace to view a message structure

If the tree structure is not clear when you look at messages, you can generate a
trace record, using the WebSphere MQ Integrator Broker Trace node to show it. To
do this you must create, assign, and deploy a simple message flow containing the
nodes listed below, and send a message through the flow:
1. MQInput node
2. Trace node with a Destination property of file, a File Path property set to a
fully-qualified file and path name where the trace record is written, and a
Pattern property of ${Root}
3. MQOutput node
See WebSphere MQ Integrator Broker Using the Control Center for more information
on how to do this.
For example, if you were to receive a simple XML message on a WebSphere MQ®
queue, whose message originated in the message flow from an MQInput node, and
which had an MQRFH2 header like the message below:
<Trade type='buy'
Company='IBM'
Price='200.20'
Date='2000-01-01'
Quantity='1000'/>
Appendix A. Examples 149

You could use the Trace node to produce the following tree representation:
Root
Properties
CreationTime=GMTTIMESTAMP '1999-11-24 13:10:00'
(a GMT timestamp field)
... and other fields ...
MQMD
PutDate=DATE '19991124'
(a date field)
PutTime=GMTTIME '131000'
(a GMTTIME field)
... and other fields ...
MQRFH
mcd
msd='xml'
(a character string field)
.. and other fields ...
XML
Trade
type='buy'
Company='IBM'
Price='200'
Date='2000-01-01'
Quantity='1000'
Figure 10. Tree representation produced by Trace node

Tracing the message environment
To trace any data in the environment tree, use the following pattern:
${Environment};, which produces the following type of output:
(0x1000000)Environment = (
(0x1000000)Variables = (
(0x1000000)MyVariable1 = (
(0x2000000) = ’3’
)
(0x1000000)MyVariable2 = (
(0x2000000) = ’Hello’
)
)
)
To trace particular variables in the variables folder of the environment tree, you
can use the following pattern: ${Environment.Variables.MyVariable1}, which
returns the value only (for example 3).
To trace data in the LocalEnvironment or DestinationList tree, use the following

patterns: ${LocalEnvironment} or ${DestinationList};. The output you get is
similar to the following example, which shows settings for queuename under
MQ.DestinationData and MQDestinationList.DestinationData and for labelname
under RouterList.DestinationData).
(0x1000000)Destination = (
(0x1000000)MQ = (
(0x1000000)DestinationData = (
(0x3000000)queuename = ’MQOUT’
)
)
(0x1000000)MQDestinationList = (
(0x3000000)queuename = ’OLDMQOUT’
)
)
(0x1000000)RouterList = (
(0x3000000)labelname = ’continue’
)
(0x3000000)labelname = ’custdetails’
)
(0x3000000)labelname = ’trade’
)
)
)

The example below includes the addition of the WrittenDestination folder. If the
out terminal of an MQOutput node is connected, the LocalEnvironment is
augmented with information about the action that the output node has performed.
(0x1000000)Destination = (
(0x1000000)MQ = (
(0x3000000)queuename = ’MQOUT’
)
)
(0x1000000)WrittenDestination = (
(0x1000000)MQ = (
(0x3000000)queueName = ’MQOUT’
(0x3000000)queueManagerName = ’MQSI_SAMPLE_QM’
(0x3000000)replyIdentfier = X’414d51204d5153495f53414d504c455f1f442f3b12600100’
(0x3000000)msgId = X’414d51204d5153495f53414d504c455f1f442f3b12600100’
(0x3000000)correlId = X’000000000000000000000000000000000000000000000000’
)
)
)
)
Tracing the exception list

To trace data in the exception list, use the following pattern: ${ExceptionList}.
Tracing the message data

To trace the entire message tree, use the following pattern: ${Root}. This gives you
output similar to Figure 10 on page 150. To trace the message data only, use the
pattern: ${Body}

How to manipulate a variable length BLOB message

This example shows how you can manipulate a variable length BLOB message.
The example is based on a hypothetical situation as follows:
A variable length BLOB message is received and some of the fields need to be
parsed in MRM format in order to route the message to the right queue. Because
the message is in BLOB format and contains imbedded NULLs (’x00’), the message
cannot be defined as null terminated.
This example shows the ESQL needed to:

v Calculate the BLOB message length
v Convert it to hexadecimal format
v Add it to the beginning of the BLOB message
By doing this you can define the message, in MRM format, as an integer length
field followed by the BLOB message.
This example also shows how to convert the BLOB message to CWF format,
process the message and strip off the added length field.
In this example the input record has the following format:

v Version Number - string, 11 characters
v Store Number - string, 10 characters
This field is to be used as an integer to route the message to different queues
depending on customer defined criteria.
v Store Data - variable length binary data
Note: You will need to change the queue manager name within the message flow
to the name of your queue manager.
Define a new message

A new message is defined with the following fields and properties:
v B_LEN INTEGER
v VERSION_NUM - STRING, Length 11
v STORE_NUM - STRING, Length 10
v BIN_BLOB - BINARY, Length Value of B_LEN
Create a message flow

1. Define an input node with the following properties:
v Name: INQUEUE
v Queue Name: INQUEUE
v Message Domain: BLOB
2. Connect the output terminal to a Compute node, which will calculate the
length of the BIN_BLOB and add it to the beginning of the BLOB message in
what is going to be field B_LEN. Define the Compute node with the following
properties:
v Name: Add_length
v ESQL: check the ″Copy message header″ box

3. Code the following ESQL within the Compute node:
DECLARE MSGLEN CHARACTER;
DECLARE NUMBER INTEGER;
DECLARE RESULT INTEGER;
DECLARE REM INTEGER;
--
-- Set MSGLEN to non NULL to avoid errors when concatenating the first time --
SET MSGLEN = ’X’;
--
-- Get the length of the BLOB and substract the length of VERSION_NUM and STORE_NUM (11+10)
--
SET NUMBER = LENGTH("InputRoot"."BLOB"."BLOB")-21;
--
-- convert NUMBER to hexadecimal. The remainder of dividing by 16 is calculated recursively. --
WHILE NUMBER > 15 DO
SET RESULT = NUMBER/16;
SET REM = NUMBER - RESULT*16;
SET MSGLEN =
CASE
WHEN REM < 10 THEN CAST(REM AS CHARACTER) || MSGLEN
WHEN REM = 10 THEN ’A’ || MSGLEN
WHEN REM = 11 THEN ’B’ || MSGLEN
WHEN REM = 12 THEN ’C’ || MSGLEN
WHEN REM = 13 THEN ’D’ || MSGLEN
WHEN REM = 14 THEN ’E’ || MSGLEN
ELSE ’F’ || MSGLEN
END;
SET NUMBER = RESULT;
END WHILE;
SET REM = NUMBER;
SET MSGLEN =
CASE
WHEN REM < 10 THEN CAST(REM AS CHARACTER) || MSGLEN
WHEN REM = 10 THEN ’A’ || MSGLEN
WHEN REM = 11 THEN ’B’ || MSGLEN
WHEN REM = 12 THEN ’C’ || MSGLEN
WHEN REM = 13 THEN ’D’ || MSGLEN
WHEN REM = 14 THEN ’E’ || MSGLEN
ELSE ’F’ || MSGLEN
END;
--
-- Add leading ’0’s up to a length of 9 to be able to cast as BLOB.
-- Remember it started with MSGLEN set to X (length 1)
--
WHILE LENGTH(MSGLEN) < 9 DO
SET MSGLEN = ’0’ || MSGLEN; END WHILE;
--
-- Change to appropriate endian (PLATFORM DEPENDANT)
-- If no endian swapping needed then remember to get rid of the last character as below --
SET MSGLEN = SUBSTRING(MSGLEN FROM 1 FOR 8)
--
SET MSGLEN = SUBSTRING(MSGLEN FROM 7 FOR 2) || SUBSTRING(MSGLEN FROM 5 FOR 2) ||
SUBSTRING(MSGLEN FROM 3 FOR 2) || SUBSTRING(MSGLEN FROM 1 FOR 2);
SET "OutputRoot"."BLOB"."BLOB" = CAST(MSGLEN AS BLOB) || "InputRoot"."BLOB"."BLOB";
4. Connect the out terminal of the Compute node to a ResetContentDescriptor
node and define the ResetContentDescriptor node as follows:
v Set Name to ResetContent_2_MRM
v Set Message Domain to MRM
v Check the Reset Message Domain box
v Set Message Set field to the message set identifier of the new message
defined earlier
v Check the Reset Message Set box

v Set the Message Type to the message identifier of the new message defined
earlier
v Check the Reset Message Type box
v Set Message Format to the CWF identifier
v Check the Reset Message Format box
5. Connect the Compute out terminal to a Filter node and define the filter node
with the following properties:
v Set Name to Route_2_QUEUE
v Set Inputs to the new message defined earlier
v Code the following ESQL:
CAST("Body"."e_STORE_NUM" AS INTEGER) < 151
Note: This is based on the arbitrary assumption that an incoming message

from a Store Number is less than 151 and is routed to a specific queue.
6. Connect the Filter output terminals as follows:
v True terminal to a MessageProcessingNode Type SUBFLOW with the
following properties:
– Set Name to LESS_THAN
– Set queueName to LESS_THAN
v False terminal to a MessageProcessingNode Type SUBFLOW with the
– Set Name to GREATER_THAN
– queueName to GREATER_THAN
v Unknown terminal to a MessageProcessingNode Type SUBFLOW with the
– Set Name to INVALID
– Set queueName to INVALID
v Failure to a MessageProcessingNode Type SUBFLOW with the following
properties:
– Set Name to INVALID
– Set queueName to INVALID
Create subflow
The purpose of this subflow is to output the message in its original form. To do
this, the message has to be converted back to BLOB from MRM and the four bytes
that were added (field B_LEN) are removed.
For this subflow:

1. Create an Input Terminal Node with name InputTerminal1 and connect its out
terminal to a ResetContentDescriptor node.
2. Define the ResetContentDescriptor node properties as follows:
v Set Name to ResetContent_2_BLOB
v Set Message Domain to BLOB
v Check the Reset Message Domain box
3. Connect the ResetContentDescriptor node out terminal to a Compute node.

4. Define the Compute node with the following properties:
v Set Name to Remove_length
v Check the ″Copy message header″ box
v Code the following ESQL:
.-- Remove the 4 bytes length field added previously --
SET "OutputRoot"."BLOB"."BLOB" = SUBSTRING("InputRoot"."BLOB"."BLOB" FROM 5);
This Compute node will remove the four bytes that were added at the
beginning of the BLOB message in order to be able to manipulate it
5. Connect the out terminal of the Compute node to an MQOutput node.
6. Create an MQOutput Node. The properties Queue Manager Name and Queue
Name in the MQOutput node can be promoted so they can be specified in the
main flow, node SUBFLOW.

Example exception lists
Exception lists
Figure 11 illustrates one way in which an exception list can be constructed.
ExceptionList {
RecoverableException = { 1
File = ’f:/build/argo/src/DataFlowEngine/ImbDataFlowNode.cpp’
Line = 538
Function = ’ImbDataFlowNode::createExceptionList’
Type = ’ComIbmComputeNode’
Name = ’0e416632-de00-0000-0080-bdb4d59524d5’
Label = ’mf1.Compute1’
Text = ’Node throwing exception’
Catalog = ’WebSphere MQ Integrator Broker2’
Severity = 3
Number = 2230
File = ’f:/build/argo/src/DataFlowEngine/ImbRdlBinaryExpression.cpp’
Line = 231
Function = ’ImbRdlBinaryExpression::scalarEvaluate’
Name = ’0e416632-de00-0000-0080-bdb4d59524d5’
Text = ’error evaluating expression’
Severity = 2
Number = 2439
Insert = {
Type = 2
Text = ’2’
}
Insert = {
Type = 2
Text = ’30’
}
File = ’f:/build/argo/src/DataFlowEngine/ImbRdlValueOperations.cpp’
Line = 257
Function = ’intDivideInt’
Name = ’0e416632-de00-0000-0080-bdb4d59524d5’
Text = ’Divide by zero calculating ’%1 / %2’’
Severity = 2
Number = 2450
Insert = }
Type = 5
Text = ’100 / 0’
}
}
}
}
}
Figure 11. Exception list structure
Notes:
1. The first exception description 1 is a child of the root. This identifies error
number 2230, indicating an exception has been thrown. The node that has
thrown the exception is also identified (mf1.Compute1).

2. Exception description 2 is a child of the first exception description 1. This
identifies error number 2439.
3. Exception description 3 is a child of the second exception description 2.
This identifies error number 2450 that indicates that the node has attempted to
divide by zero.

ExceptionList = (
(0x1000000)RecoverableException = (
(0x3000000)File = ’F:\build\S000_D\src\DataFlowEngine\ImbComputeNode.cpp’
(0x3000000)Line = 402
(0x3000000)Function = ’ImbComputeNode::evaluate’
(0x3000000)Type = ’ComIbmComputeNode’
(0x3000000)Name = ’acd8f35d-e700-0000-0080-b78796c5e70d’
(0x3000000)Label = ’esql_13485_check_defect.Compute1’
(0x3000000)Text = ’Caught exception and rethrowing’
(0x3000000)Catalog = ’WMQIv210’
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x3000000)File = ’F:\build\S000_D\src\DataFlowEngine\ImbRdl\ImbRdlExternalDb.cpp’
(0x3000000)Line = 278
(0x3000000)Function = ’SqlExternalDbStmt::executeStmt’
(0x3000000)Text = ’The following error occurred execution SQL statement &3. inserts where &4’
(0x3000000)Severity = 3
(0x3000000)Number = 2519
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = ’1’
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = ’1’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’USERDB’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’DELETE FROM DB2ADMIN.STOCK WHERE (STOCK_ID)=(?)’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’500027, ’
)
(0x1000000)DatabaseException = (
(0x3000000)File = ’F:\build\S000_D\src\DataFlowEngine\ImbOdbc.cpp’
(0x3000000)Line = 153
(0x3000000)Function = ’ImbOdbcHandle::checkRcInner’
(0x3000000)Type = ’’
(0x3000000)Name = ’’
(0x3000000)Label = ’’
(0x3000000)Text = ’Root SQL exception’
(0x3000000)Severity = 3
(0x3000000)Number = 2321
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = ’100’
)
)
)
)
)
Figure 12. Database exception list structure

ExceptionList = (
(0x3000000)Line = 402
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x3000000)File = ’F:\build\S000_D\src\DataFlowEngine\ImbRdl\ImbRdlTypeCast.cpp’
(0x3000000)Line = 163
(0x3000000)Function = ’SqlTypeCast::evaluate’
(0x3000000)Type = ’’
(0x3000000)Name = ’’
(0x3000000)Label = ’’
(0x3000000)Text = ’Error casting from %3 to %4’
(0x3000000)Severity = 3
(0x3000000)Number = 2521
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = ’12’
)
(0x1000000)Insert = (
(0x3000000)Type = 2
(0x3000000)Text = ’28’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’CHARACTER’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’INTEGER’
)
(0x1000000)ConversionException = (
(0x3000000)File = ’F:\build\S000_D\src\CommonServices\ImbUtility.cpp’
(0x3000000)Line = 195
(0x3000000)Function = ’imbWcsToInt64’
(0x3000000)Type = ’’
(0x3000000)Name = ’’
(0x3000000)Label = ’’
(0x3000000)Text = ’Invalid characters’
(0x3000000)Severity = 3
(0x3000000)Number = 2595
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’fred’
)
)
)
)
)
Figure 13. Conversion exception list structure

ExceptionList = (
(0x3000000)File = ’F:\build\S000_D\src\DataFlowEngine\ImbMqOutputNode.cpp’
(0x3000000)Line = 1444
(0x3000000)Function = ’ImbMqOutputNode::evaluate’
(0x3000000)Type = ’ComIbmMQOutputNode’
(0x3000000)Name = ’c76eb6cd-e600-0000-0080-b78796c5e70d’
(0x3000000)Label = ’esql_13485_check_defect.OUT’
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)ParserException = (
(0x3000000)File = ’F:\build\S000_D\src\MTI\MTIforBroker\GenXmlParser2\XmlImbParser.cpp’
(0x3000000)Line = 210
(0x3000000)Function = ’XmlImbParser::refreshBitStreamFromElements’
(0x3000000)Type = ’ComIbmMQInputNode’
(0x3000000)Name = ’ce64b6cd-e600-0000-0080-b78796c5e70d’
(0x3000000)Label = ’esql_13485_check_defect.IN’
(0x3000000)Text = ’XML Writing Errors have occurred’
(0x3000000)Severity = 3
(0x3000000)Number = 5010
(0x1000000)ParserException = (
(0x3000000)File = ’F:\build\S000_D\src\MTI\MTIforBroker\GenXmlParser2\XmlImbParser.cpp’
(0x3000000)Line = 551
(0x3000000)Function = ’XmlImbParser::checkForBodyElement’
(0x3000000)Type = ’’
(0x3000000)Name = ’’
(0x3000000)Label = ’’
(0x3000000)Text = ’No valid body of the document could be found.’
(0x3000000)Severity = 3
(0x3000000)Number = 5005
)
)
)
)
Figure 14. Parser exception list structure

ExceptionList = (
(0x3000000)Line = 402
(0x3000000)Severity = 3
(0x3000000)Number = 2230
(0x1000000)UserException = (
(0x3000000)File = ’F:\build\S000_D\src\DataFlowEngine\ImbRdl\ImbRdlThrowExceptionStatements.cpp’
(0x3000000)Line = 148
(0x3000000)Function = ’SqlThrowExceptionStatement::execute’
(0x3000000)Text = ’User Generated SQL ’USER’ exception’
(0x3000000)Severity = 1
(0x3000000)Number = 2949
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’USER’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’Insert1’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’Insert2’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’etc’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’’
)
(0x1000000)Insert = (
(0x3000000)Type = 5
(0x3000000)Text = ’’
)
)
)
)
Figure 15. User exception list structure

Exception handling paths base their decisions on the number of exception
conditions on:
v The message number that identifies the type of exception that has occurred.
v The label that is the known name of the object in which the exception occurred.
Figure 16 illustrates an extract of ESQL that shows how you can set up a Compute
node to use the exception list. The ESQL loops through the exception list to the last
(nested) exception description, and extracts the error number. This error relates to
the original cause of the problem and normally provides the most precise
information. Subsequent action taken by the message flow can be decided by the
error number retrieved in this way.
/* Error number extracted from exception list */

DECLARE Error INTEGER;
/* Current path within the exception list */
DECLARE Path CHARACTER;
/* Start at first child of exception list */

SET Path = ’InputExceptionList.*[1]’;
/* Loop until no more children */

WHILE EVAL( ’FIELDNAME(’ || Path || ’) IS NOT NULL’ ) DO
/* Check if error number is available */

IF EVAL( ’FIELDNAME(’ || Path || ’.Number) IS NOT NULL’ ) THEN
/* Remember only the deepest error number */
SET Error = EVAL( Path || ’.Number’ );
END IF;
/* Step to last child of current element (usually a nested exception list */

SET Path = Path || ’.*[LAST]’;
END WHILE; /* End loop */
Figure 16. Retrieving the exception error code

CREATE statement example
Example of CREATE statement

This example provides sample ESQL and an input message, which produce the
output message at the end of this example.
ESQL:
/* copy headers */
DECLARE i INTEGER;
DECLARE numHeaders INT;
SET i = 1;
SET numHeaders = CARDINALITY(InputRoot.*[]);
WHILE i < numHeaders DO
SET OutputRoot.*[i] = InputRoot.*[i];
SET i = i + 1;
END WHILE;
CREATE FIELD OutputRoot.XML.TestCase.description TYPE NameValue VALUE ’This is my TestCase’ ;

CREATE FIRSTCHILD OF OutputRoot.XML.TestCase Domain(’XML’) NAME ’Identifier’ VALUE InputRoot.XML.TestCase.Identifier;
CREATE LASTCHILD OF OutputRoot.XML.TestCase Domain(’XML’) NAME ’Sport’ VALUE InputRoot.XML.TestCase.Sport;
CREATE LASTCHILD OF OutputRoot.XML.TestCase Domain(’XML’) NAME ’Date’ VALUE InputRoot.XML.TestCase.Date;
CREATE LASTCHILD OF OutputRoot.XML.TestCase Domain(’XML’) NAME ’Type’ VALUE InputRoot.XML.TestCase.Type;
CREATE FIELD OutputRoot.XML.TestCase.Division[1].Number TYPE NameValue VALUE ’Premiership’;
CREATE FIELD OutputRoot.XML.TestCase.Division[1].Result[1].Number TYPE NameValue VALUE ’1’ ;
CREATE FIELD OutputRoot.XML.TestCase.Division[1].Result[1].Home TYPE Name;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[1].Home NAME ’Team’ VALUE ’Liverpool’ ;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[1].Home NAME ’Score’ VALUE ’4’;
CREATE FIELD OutputRoot.XML.TestCase.Division[1].Result[1].Away TYPE Name;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[1].Away NAME ’Team’ VALUE ’Everton’;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[1].Away NAME ’Score’ VALUE ’0’;
CREATE FIELD OutputRoot.XML.TestCase.Division[1].Result[2].Number TYPE NameValue VALUE ’2’;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[2].Home NAME ’Team’ VALUE ’Manchester United’;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[2].Home NAME ’Score’ VALUE ’2’;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[1].Result[2].Away NAME ’Team’ VALUE ’Arsenal’;
CREATE FIELD OutputRoot.XML.TestCase.Division[2].Number TYPE NameValue VALUE ’2’;
CREATE FIELD OutputRoot.XML.TestCase.Division[2].Result[1].Number TYPE NameValue VALUE ’1’;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[2].Result[1].Home NAME ’Team’ VALUE ’Port Vale’;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[2].Result[1].Home NAME ’Score’ VALUE ’9’ ;
CREATE LASTCHILD OF OutputRoot.XML.TestCase.Division[2].Result[1].Away NAME ’Team’ VALUE ’Brentford’;
Figure 17. CREATE statement example (Part 1 of 2)

Input Message:
<TestCase description="This is my TestCase">

<Identifier>ES03B305_T1</Identifier>
<Sport>Football</Sport>
<Date>01/02/2000</Date>
<Type>LEAGUE</Type>
</TestCase>
Output Message:
<TestCase description="This is my TestCase">

<Identifier>ES03B305_T1</Identifier>
<Sport>Football</Sport>
<Date>01/02/2000</Date>
<Type>LEAGUE</Type>
<Division Number="Premiership">
<Result Number="1">
<Home>
<Team>Liverpool</Team>
<Score>4</Score>
</Home>
<Away>
<Team>Everton</Team>
<Score>0</Score>
</Away>
</Result>
<Result Number="2">
<Home>
<Team>Manchester United</Team>
<Score>2</Score>
</Home>
<Away>
<Team>Arsenal</Team>
<Score>3</Score>
</Away>
</Result>
</Division>
<Division Number="2">
<Result Number="1">
<Home>
<Team>Port Vale</Team>
<Score>9</Score>
</Home>
<Away>
<Team>Brentford</Team>
<Score>5</Score>
</Away>
</Result>
<Result Number="2">
<Home>
<Team>Reading</Team>
<Score>6</Score>
</Home>
<Away>
<Team>Stoke City</Team>
<Score>7</Score>
</Away>
</Result>
</Division>
</TestCase>
Figure 17. CREATE statement example (Part 2 of 2)

Appendix B. Using the Compute node to work with messages
The Compute node and the Filter node share a common expression syntax. In its
simplest form, a Compute node provides a way of building up a new message
using a set of assignment statements. The expressions that appear on the right
hand side of the assignment, that is, the source expressions, are expressions of
exactly the same form as can appear in a Filter node. But they are not restricted to
returning single boolean values in the same way that a filter expression is.
A Compute node works by constructing a tree representation of a new message

based on a list of assignment statements. A new message is always constructed,
even if it is an identical copy of the input message, because the message passed to
the node must be preserved in its original form (it is not permissible in a message
flow to modify the information passed back "upstream"). The simplest possible
Compute node simply constructs a new message as an exact copy of the input
message. Such a Compute node would consist of the following statement
Because there are two messages involved in a Compute node, it is not sufficient to
refer to "Root" as can be done in a Filter node where there is only one message.
Instead you must refer to "InputRoot" and "OutputRoot" in a Compute node. You
can also refer to "InputBody" in a Compute node in the same way that you can
refer to "Body" in a Filter node, though you cannot refer to "OutputBody", because
there is no fixed concept of what the "body" of the output message is until the
output message has been fully constructed.
The above example causes a complete copy of the input message to be propagated
to the output terminal of the Compute node because when the right hand side of
an assignment statement consists of a field reference, a complete recursive tree
copy is performed to duplicate the tree representation of the input message. For
more information about how you might use a Compute node see WebSphere MQ
Integrator Broker Using the Control Center.
For the destination list to be migrated from the input to the output, the
InputLocalEnvironment must be copied to the OutputLocalEnvironment in the
ESQL section of the node.
Copying messages between parsers

Compute node expressions can copy part of an input message to an output
message. The results of such a copy depend upon the type of input and output
parsers involved.
Like parsers
Where both the source and target messages have the same folder structure at root
level, a like-parser-copy is performed. For example:
will result in all the children in the MQMD folder of the input message being
copied to the MQMD folder of the output message.
Another example of a tree structure that will support a like-parser-copy is:

Copying messages between parsers
SET OutputRoot.XML.Data.Account = InputRoot.XML.Customer.Bank.Data;
Unlike parsers
Where the source and target messages have different folder structures at root level,
it is not possible to make an exact copy of the message source. Instead, the
unlike-parser-copy views the source message as a set of nested folders terminated
by a leaf name-value pair. For example, copying the following message from XML
to MRM:
<Name3><Name31>Value31</Name31>Value32</Name3>
will produce a name element ″Name3″, and a name-value element called

″Name31″ with the value ″Value31″.
Note: The second XML pcdata (Value32) cannot be represented and is discarded.
The unlike-parser-copy scans the source tree, and copies folders, also known as
name elements, and leaf name-value pairs. Everything else, including elements
flagged as ″special″ by the source parser, will not be copied.
An example of a tree structure resulting in an unlike-parser-copy is:

SET OutputRoot.MRM.Data.Account = InputRoot.XML.Data.Account;
Note: If the algorithm used to make an unlike-parser-copy does not suit your tree
structure, it might be necessary to further qualify the source field to restrict
the amount of tree copied.
Using the Compute node for data conversion

You can use the ESQL within a Compute node to provide data conversion for code
page and encoding of messages. You must set MQMD CCSID and Encoding fields
of the output message, plus the CCSID and Encoding of any headers, to the
required target value.
The following example illustrates what is required for a CWF message to pass
from WebSphere MQ Integrator Broker to IMS™ on OS/390.
1. You have defined the input message in XML and are using an MQRFH2
header. The header must be removed before the message is passed to IMS.
2. The message passed to IMS must have MQIIH header, and must be in the
OS/390 code page. This message is defined in the MRM and has identifier
m_IMS1. The PIC X fields in this message must be defined as logical type
string for EBCDIC <-> ASCII conversion to take place. If they are logical type
binary, no data conversion occurs.
3. The message received from IMS is also defined in the MRM and has identifier
m_IMS2. The PIC X fields in this message must be defined as logical type
string for EBCDIC <-> ASCII conversion to take place. If they are logical type
binary, no data conversion occurs.
4. The reply message must be converted to the Windows NT codepage. The
MQIIH header is retained on this message.
5. You have created a message flow that contains:
a. The outbound flow, MQInput1 —> Compute1 —> MQOutput1.
b. The inbound flow, MQInput2 —> Compute2 —> MQOutput2.

Using Compute node for data conversion
6. You must set up the ESQL in Compute1 (outbound) node as follows (specifying
the MessageSet id that is created for you):
DECLARE I INTEGER;
SET I = 1;
WHILE I < CARDINALITY(InputRoot.*[]) - 1 DO
SET I=I+1;
END WHILE;
SET OutputRoot.MQMD.CodedCharSetId = 500;
SET OutputRoot.MQMD.Encoding = 785;
SET OutputRoot.MQMD.Format = ’MQIMS ’;
SET OutputRoot.MQIIH.StrucId = ’IIH ’;
SET OutputRoot.MQIIH.Version = 1;
SET OutputRoot.MQIIH.StrucLength = 84;
SET OutputRoot.MQIIH.Encoding = 785;
SET OutputRoot.MQIIH.CodedCharSetId = 500;
SET OutputRoot.MQIIH.Format = ’MQIMSVS ’;
SET OutputRoot.MQIIH.Flags = 0;
SET OutputRoot.MQIIH.LTermOverride = ’ ’;
SET OutputRoot.MQIIH.MFSMapName = ’ ’;
SET OutputRoot.MQIIH.ReplyToFormat = ’MQIMSVS ’;
SET OutputRoot.MQIIH.Authenticator = ’ ’;
SET OutputRoot.MQIIH.TranInstanceId = X’00000000000000000000000000000000’;
SET OutputRoot.MQIIH.TranState = ’ ’;
SET OutputRoot.MQIIH.CommitMode = ’0’;
SET OutputRoot.MQIIH.SecurityScope = ’C’;
SET OutputRoot.MQIIH.Reserved = ’ ’;
SET OutputRoot.MRM.e_elen08 = 30;
SET OutputRoot.MRM.e_elen09 = 0;
SET OutputRoot.MRM.e_string08 = InputBody.e_string01;
SET OutputRoot.MRM.e_binary02 = X’31323334353637383940’;
SET OutputRoot.Properties.MessageDomain = ’MRM’;
SET OutputRoot.Properties.MessageSet = ’DHCJOEG072001’;
SET OutputRoot.Properties.MessageType = ’m_IMS1’;
SET OutputRoot.Properties.MessageFormat = ’CWF’;
7. You must set up the ESQL in Compute2 (inbound) node as follows (specifying
the MessageSet id that is created for you):
DECLARE I INTEGER;
SET I = 1;
SET I=I+1;
END WHILE;
SET OutputRoot.MQMD.CodedCharSetId = 437;
SET OutputRoot.MQMD.Encoding = 546;
SET OutputRoot.MQMD.Format = ’MQIMS ’;
SET OutputRoot.MQIIH.CodedCharSetId = 437;
SET OutputRoot.MQIIH.Encoding = 546;
SET OutputRoot.MQIIH.Format = ’ ’;
SET OutputRoot.MRM = InputBody;
SET OutputRoot.Properties.MessageSet = ’DHCJOEG072001’;
SET OutputRoot.Properties.MessageType = ’m_IMS2’;
You do not have to set any specific values for the MQInput1 node properties
because the message and message set are identified in the MQRFH2 header, and
no conversion is required.
You must set values for Domain, set, type and format in the MQInput node for the
inbound message flow (MQInput2). You do not need to set conversion parameters.
Appendix B. Using the Compute node to work with messages 169

Using Compute node for data conversion
Using the Compute node for message transformation

You can use the ESQL within a Compute node to transform a message from one
format to another.
For example, if you want to transform a generic XML message into an MRM
message, you can:
1. Add the MRM message to Output Messages on the basic tab of the Compute
node properties dialog.
2. If you want to retain the headers of the message, select Copy message headers.
3. Select the Use as message body check box. This generates ESQL similar to:
SET OutputRoot.Properties.MessageSet = ’DHOP5F709S001’;
SET OutputRoot.Properties.MessageType = ’test_message’;
Note that it is the message identifier that is required in the MessageType field.
4. Specify the output format of the message (this must be one of CWF, PDF, or
XML). For example:
5. Specify the new message domain (in this transformation, this step is not
necessary because MRM is the default, but you are recommended to include
this for completeness):
6. Create ESQL statements to populate your output message, either manually or
by using drag and drop to generate automatic mappings.
The same principles apply for other message transformations.
Using tagged or delimited messages to convert EBCDIC NL to ASCII

CR LF
This example describes how to change New Line (NL) characters in a text message
to Carriage Return (CR) and Line Feed (LF) character pairs. This is useful in
overcoming problems when messages from an EBCDIC platform (for example,
using CCSID 1047) are converted for an ASCII platform (for example, using CCSID
437). Problems can arise because the EBCDIC NL character hex ’15’ is converted to
the undefined ASCII character hex ’7F’. There is no corresponding code point for
the NL character in the ASCII code page.
Figure 18. Message flow using tagged or delimited messages to substitute CR LF pairs for
NL characters
In this example, the input is treated as BLOB format; this is passed into a
ResetContentDescriptor node to reset the data to an MRM message called ″msg_nl″
(a set of repeating string elements delimited by EBCDIC NL characters); a
Compute node is then used to create an output based on an MRM message

Convert EBCDIC NL to ASCII CR LF
″msg_crlf″ (a set of repeating string elements delimited by CR LF pairs); the
message domain is then changed back to BLOB in another ResetContentDescriptor
node.
The following detailed instructions show how to create the messages and then how
to configure a message flow.
Create the messages using these steps:

Step 1. Create a Message Set called ″myMessageSet″
Step 2. Add a Tagged/Delimiter Format layer to the Message Set, and name this
″TDS″
Step 3. Create an Element ″string1″ of type STRING
Step 4. Create a Message Type called ″t_msg_nl″ and specify
v Type Composition = Ordered Set
v Type Content = Closed
v Identifier = t_msg_nl
then check-in and check-out to pick up the TDS layer.
Step 5. Continue specifying Message Type ″t_msg_nl″:
a. Under the TDS tab for Message Type ″t_msg_nl″ specify:
v Data Element Separation = All Elements Delimited
v Delimiter <U+0085>
(hex ’0085’ is the UTF-16 representation of a NL character)
b. Under the Connection Tab for ″string1″ specify:
v Repeat = Yes
v Min Occurs = 1
v Max Occurs = 50
(The text is assumed to consist of no more than 50 lines.)
c. Add Element ″string1″
d. Under the TDS tab for element ″string1″ specify:
v Repeating Element Delimiter = <U+0085>
Step 6. Create a Message ″msg_nl″ and set Message Type = ″t_msg_nl″
Step 7. Create a Message Type called ″t_msg_crlf″ and specify
v Type Composition = Ordered Set
v Type Content = Closed
v Identifier = t_msg_crlf
then check-in and check-out to pick up the TDS layer.
Step 8. Continue specifying Message Type ″t_msg_crlf″:
a. Under the TDS tab for Message Type ″t_msg_crlf″ specify:
v Data Element Separation = All Elements Delimited
v Delimiter <CR><LF>
(<CR> and <LF> are the mnemonics for the CR and LF characters)
b. Under the Connection Tab for ″string1″ specify:
v Repeat = Yes
v Min Occurs = 1
v Max Occurs = 50
c. Add Element ″string1″
Appendix B. Using the Compute node to work with messages 171

Convert EBCDIC NL to ASCII CR LF
d. Under the TDS tab for element ″string1″ specify:
v Repeating Element Delimiter = <CR><LF>
Step 9. Create a Message ″msg_crlf″ and set Message Type = ″t_msg_crlf″
Now configure a message flow as shown in Figure 18 on page 170 using these
steps:
Step 1. Start with the MQInput1 node:
v Set Message Domain = BLOB
Step 2. Add the ResetContentDescriptor1 node, connected to the out terminal of
MQInput1:
v Set Message Domain = MRM
v Select Reset Message Domain
v Set Message Set = <Your Message Set ID>
v Select Reset Message Set
v Set Message Type = msg_nl
v Select Reset Message Type
v Set Message Format = TDS
v Select Reset Message Format
Step 3. Add the Compute1 node, connected to the out terminal of
ResetContentDescriptor1:
v For Inputs add ″msg_nl″
v For Output Messages add ″msg_crlf″
v Select Use as message body
v Click Copy message headers
Step 4. Create this ESQL for the Compute1 node:
DECLARE I INTEGER;
SET I = 1;
SET I=I+1;
END WHILE; SET OutputRoot.Properties.MessageSet = ’<Your Message Set ID>’;
SET OutputRoot.Properties.MessageType = ’t_msg_crlf’;
-- Enter SQL below this line. SQL above this line might be
regenerated, causing any modifications to be lost.
SET I = 1;
WHILE I <= CARDINALITY("InputBody"."string1"[]) DO
SET "OutputRoot"."MRM"."string1"[I] = "InputBody"."string1"[I];
SET I=I+1;
END WHILE;
Step 5. Add the ResetContentDescriptor2 node, connected to the out terminal of
the Compute1 node:
v Set Message Domain = BLOB
v Select Reset Message Domain
Step 6. Finally, add the MQOutput1 node, connected to the out terminal of
ResetContentDescriptor2 node.

Appendix C. How to read syntax diagrams
This book contains syntax diagrams (sometimes referred to as “railroad”
diagrams).
Each syntax diagram begins with a double right arrow and ends with a right and
left arrow pair. Lines beginning with a single right arrow are continuation lines.
You read a syntax diagram from left to right and from top to bottom, following the
direction of the arrows.
Other conventions used in syntax diagrams are:

Table 12. How to read syntax diagrams
Convention Meaning
You must specify values A, B, and C. Required values are shown on
A B C the main line of a syntax diagram.
You may specify value A. Optional values are shown below the main
line of a syntax diagram.
A
Values A, B, and C are alternatives, one of which you must specify.

A
B
C
Values A, B, and C are alternatives, one of which you might specify.

A
B
C
You might specify one or more of the values A, B, and C. Any

, required separator for multiple or repeated values (in this example,
the comma (,)) is shown on the arrow.

A
B
C

Syntax diagrams
Table 12. How to read syntax diagrams (continued)
Convention Meaning
You might specify value A multiple times. The separator in this
example is optional.
,

A
Values A, B, and C are alternatives, one of which you might specify.

A If you specify none of the values shown, the default A (the value
shown above the main line) is used.
B
C
The syntax fragment Name is shown separately from the main syntax
Name diagram.
Name:
A
B
Punctuation and Specify exactly as shown.

uppercase values
Lowercase values Supply your own text in place of the name variable.
(for example, name)

Appendix D. Notices
This information was developed for products and services offered in the United
States. IBM may not offer the products, services, or features discussed in this
information in other countries. Consult your local IBM representative for
information on the products and services currently available in your area. Any
reference to an IBM product, program, or service is not intended to state or imply
that only that IBM product, program, or service may be used. Any functionally
equivalent product, program, or service that does not infringe any IBM intellectual
property right may be used instead. However, it is the user’s responsibility to
evaluate and verify the operation of any non-IBM product, program, or service.
IBM may have patents or pending patent applications covering subject matter
described in this information. The furnishing of this information does not give you
any license to these patents. You can send license inquiries, in writing, to:
IBM Director of Licensing
IBM Corporation
North Castle Drive
Armonk, NY 10504-1785
U.S.A.
For license inquiries regarding double-byte (DBCS) information, contact the IBM
Intellectual Property Department in your country or send inquiries, in writing, to:
IBM World Trade Asia Corporation
Licensing
2-31 Roppongi 3-chome, Minato-ku
Tokyo 106, Japan
The following paragraph does not apply to the United Kingdom or any other
country where such provisions are inconsistent with local law:
INTERNATIONAL BUSINESS MACHINES CORPORATION PROVIDES THIS
PUBLICATION “AS IS” WITHOUT WARRANTY OF ANY KIND, EITHER
EXPRESS OR IMPLIED, INCLUDING, BUT NOT LIMITED TO, THE IMPLIED
WARRANTIES OF NON-INFRINGEMENT, MERCHANTABILITY, OR FITNESS
FOR A PARTICULAR PURPOSE. Some states do not allow disclaimer of express or
implied warranties in certain transactions, therefore this statement may not apply
to you.
This information could include technical inaccuracies or typographical errors.

Changes are periodically made to the information herein; these changes will be
incorporated in new editions of the information. IBM may make improvements
and/or changes in the product(s) and/or the program(s) described in this
information at any time without notice.
Any references in this information to non-IBM Web sites are provided for
convenience only and do not in any manner serve as an endorsement of those Web
sites. The materials at those Web sites are not part of the materials for this IBM
product and use of those Web sites is at your own risk.
IBM may use or distribute any of the information you supply in any way it
believes appropriate without incurring any obligation to you.

Notices
Licensees of this program who wish to have information about it for the purpose
of enabling: (i) the exchange of information between independently created
programs and other programs (including this one) and (ii) the mutual use of the
information which has been exchanged, should contact:
IBM United Kingdom Laboratories,
Mail Point 151,
Hursley Park,
Winchester,
Hampshire,
England
SO21 2JN.
Such information may be available, subject to appropriate terms and conditions,

including in some cases, payment of a fee.
The licensed program described in this information and all licensed material
available for it are provided by IBM under terms of the IBM Customer Agreement,
IBM International Programming License Agreement, or any equivalent agreement
between us.
Information concerning non-IBM products was obtained from the suppliers of

those products, their published announcements or other publicly available sources.
IBM has not tested those products and cannot confirm the accuracy of
performance, compatibility or any other claims related to non-IBM products.
Questions on the capabilities of non-IBM products should be addressed to the
suppliers of those products.
Trademarks
The following terms are trademarks of International Business Machines
Corporation in the United States, other countries, or both:
AIX AS/400 DB2

Everyplace IBM IBMLink
IMS MQSeries OS/390
SupportPac VSE/ESA WebSphere
z/OS
Windows and Windows NT are trademarks of Microsoft Corporation in the United

States, other countries, or both.
Java and all Java-based trademarks are trademarks of Sun Microsystems, Inc. in the
United States, other countries, or both.
UNIX is a registered trademark of The Open Group in the United States and other
countries.
Other company, product, or service names, may be the trademarks or service

marks of others.

Glossary of terms and abbreviations
This glossary defines WebSphere MQ Integrator check in. The Control Center action that stores a new
Broker terms and abbreviations used in this book. or updated resource in the configuration or message
If you do not find the term you are looking for, repository.
see the index or the IBM Dictionary of Computing,
check out. The Control Center action that extracts and
New York: McGraw-Hill, 1994. locks a resource from the configuration or message
repository for local modification by a user. Resources
This glossary includes terms and definitions from from the two repositories can only be worked on when
the American National Dictionary for Information they are checked out by an authorized user, but can be
Systems, ANSI X3.172-1990, copyright 1990 by the viewed (read only) without being checked out.
American National Standards Institute. Copies
may be ordered from the American National collective. A hyperconnected (totally connected) set of
brokers forming part of a multi-broker network for
Standards Institute, 11 West 42 Street, New York,
publish/subscribe applications.
New York 10036. Definitions are identified by the
symbol (A) after the definition. configuration. In the broker domain, the brokers,
execution groups, message flows and message sets
A assigned to them, topics and access control
specifications.
Access Control List (ACL). The list of principals that
Configuration Manager. A component of WebSphere
have explicit permissions (to publish, to subscribe to,
MQ Integrator Broker that acts as the interface between
and to request persistent delivery of a publication
the configuration repository and an executing set of
message) against a topic in the topic tree. The ACLs
brokers. It provides brokers with their initial
define the implementation of topic-based security.
configuration, and updates them with any subsequent
ACL. Access Control List. changes. It maintains the broker domain configuration.
AMI. Application Messaging Interface. configuration repository. Persistent storage for broker
configuration and topology definition.
Application Messaging Interface (AMI). The
programming interface provided by MQSeries that connector. See message processing node connector.
defines a high level interface to message queuing
content-based filter. An expression that is applied to
services. See also MQI and JMS.
the content of a message to determine how the message
is to be processed.
B
context tag. See element qualifier.
BLOB. Binary Large OBject. A block of bytes of data
Control Center. The graphical interface that provides
(for example, the body of a message) that has no
facilities for defining, configuring, deploying, and
discernible meaning, but is treated as one solid entity
monitoring resources of the WebSphere MQ Integrator
that cannot be interpreted.
Broker network.
broker. See message broker.
broker domain. A collection of brokers that share a

D
common configuration, together with the single
datagram. The simplest form of message that
Configuration Manager that controls them.
MQSeries supports. Also known as send-and-forget. This
type of message does not require a reply. Compare with
C request/reply.
callback function. See implementation function. debugger. A facility on the Message Flows view in the
Control Center that enables message flows to be
category. An optional grouping of messages that are debugged.
related in some way. For example, messages that relate
to a particular application. deploy. Make operational the configuration and
topology of the broker domain.

Glossary
destination list. See local environment. length of a string element might be defined in one
message set but used in several message sets.
distribution list. A list of MQSeries queues to which a
message can be put using a single statement. External Security Manager. A program that provides
security management for users and resources on z/OS.
Document Type Definition (DTD). The rules that
specify the structure for a particular class of SGML or
XML documents. The DTD defines the structure with F
elements, attributes, and notations, and it establishes
constraints for how each element, attribute, and field reference. A sequence of period-separated values
notation can be used within the particular class of that identify a specific field (which might be a
documents. A DTD is analogous to a database schema structure) within a message tree. An example of a field
in that the DTD completely describes the structure for a reference might be something like
particular markup language. Body.Invoice.InvoiceNo.
DTD. Document Type Definition. filter. An expression that is applied to the content of a
message to determine how the message is to be
processed.
E
format. A format defines the internal structure of a
e-business. A term describing the commercial use of message, in terms of the fields and order of those
the Internet and World Wide Web to conduct business fields. A format can be self-defining, in which case the
(short for electronic-business). message is interpreted dynamically when read.
element. A unit of data within a message that has

business meaning, for example, street name G
element qualifier. A tag that is applied to an element graphical user interface (GUI). An interface to a
within a message to enable that element to be treated software product that is graphical rather than textual. It
differently in different contexts. For example, an refers to window-based operational characteristics.
element could be mandatory in one context and
optional in another.
I
environment. User-defined variable information
associated with a message while it is being processed IBM Developer Kit. IBM Developer Kit for the Java™
by a message flow. This information can be created and Platform, a software package that can be used to write,
used by nodes within the message flow. compile, debug, and run Java applets and applications.
ESM. External Security Manager. IBM Runtime Environment. IBM Runtime

Environment for the Java Platform. A subset of the IBM
ESQL. Extended SQL. Developer Kit for the Java Platform, that contains the
core executables and files that constitute the standard
exception list. A list of exceptions that have been Java platform. The IBM Runtime Environment includes
generated during the processing of a message, with the Java Virtual Machine, core classes and supporting
supporting information. files.
execution group. A named grouping of message flows implementation function. Function written by a
that have been assigned to a broker. The broker is third-party developer for a plug-in node or parser. Also
guaranteed to enforce some degree of isolation between known as a callback function.
message flows in distinct execution groups by ensuring
that they execute in separate address spaces, or as input node. A message flow node that represents a
unique processes. source of messages for the message flow.
Extended SQL. A specialized set of SQL functions and installation mode. The installation mode can be Full,
statements based on regular SQL, extended with Custom, or Broker only. The mode defines the
functions and statements unique to WebSphere MQ components of the product installed by the installation
Integrator Broker. process on Windows NT systems.
Extensible Markup Language (XML). A W3C

standard for the representation of data.
external reference. A reference within a message set to

a component that has been defined outside the current
message set. For example, an integer that defines the

Glossary
J message dictionary. A repository for (predefined)

message type specifications.
Java Database Connectivity (JDBC™). An application message domain. The value that determines how the
programming interface that has the same characteristics message is interpreted (parsed). The following domains
as ODBC but is specifically designed for use by Java are recognized:
database applications. v MRM, which identifies messages defined using the
Control Center.
Java Message Service (JMS). An application
v XML, JMSMap, and JMSStream, which identify
programming interface that provides Java language
messages that are self-defining.
functions for handling messages.
v BLOB, which identifies messages that are undefined.
JDBC. Java Database Connectivity. You can also create your own message domains: if you
do so, you must supply your own message parser.
JMS. Java Message Service. See also AMI and MQI.
message flow. A directed graph that represents the set
L of activities performed on a message or event as it
passes through a broker. A message flow consists of a
local environment. Information associated with a set of message processing nodes and message
message while it is being processed by a message flow. processing node connectors.
This information includes:
message flow component. See message flow.
v User-defined variable information that can be created
and used by nodes within the message flow. message parser. A program that interprets the bit
v A user-defined list of internal and external stream of an incoming message and creates an internal
destinations to which a message is sent. These can be representation of the message in a tree structure, and
nodes within a message flow (for example, when that regenerates a bit stream for an outgoing message
using the RouteToLabel and Label nodes) or from the internal representation.
MQSeries queues (when the list is examined by an
MQOutput node to determine the final target for the message processing node. A node in the message
message). flow, representing a well defined processing stage. A
v A list of destinations to which a message has been message processing node can be one of several
sent. This list is created by an output node only if it primitive types or can represent a subflow.
is connected to another node in the message flow.
message processing node connector. An entity that
Also known as destination list in previous MQSeries connects the output terminal of one message processing
Integrator releases. Destination list is valid and can be node to the input terminal of another. A message
used for compatibility. processing node connector represents the flow of
control and data between two message flow nodes.
local error log. A generic term that refers to the logs
to which WebSphere MQ Integrator Broker writes message queue interface (MQI). The programming
records on the local system. interface provided by MQSeries queue managers. The
v On UNIX® systems, this is the syslog. programming interface allows application programs to
v On Windows NT, this is the Event Viewer access message queuing services. See also AMI and
(Application View). JMS.
v On z/OS™ systems, this is the operator console.
message repository. A database holding message
Entries written to this log include records that provide template definitions.
information about events that are not errors, but that
occur normally during operation, for example, message repository manager (MRM). A component of
successful deployment of a configuration. the Configuration Manager that handles message
definition and control. A message defined to the MRM
Also known as system log. has a message domain set to MRM.
message set. A grouping of related messages.

M
message template. A named and managed entity that
message broker. A set of execution processes hosting represents the format of a particular message. Message
one or more message flows. templates represent a business asset of an organization.
messages. Entities exchanged between a broker and its message type. The logical structure of the data within
clients. a message. For example, the number and location of
character strings.
Glossary of terms and abbreviations 179

Glossary
metadata. Data that describes the characteristic of POSIX. Portable Operating System Interface For
stored data. Computer Environments. An IEEE standard for
computer operating systems (for example, AIX® and
MQI. Message queue interface. Solaris).
MQIsdp. WebSphere MQ Integrator Broker SCADA predefined message. A message with a structure that
device protocol. A lightweight publish/subscribe is defined before the message is created or referenced.
protocol flowing over TCP/IP. Compare with self-defining message.
MQRFH. An architected message header that is used primitive. A message processing node that is supplied
to provide metadata for the processing of a message. with the product.
This header is supported by MQSeries
Publish/Subscribe. principal. An individual user ID (for example, a log-in
ID) or a group. A group can contain individual user
MQRFH2. An extended version of MQRFH, providing IDs and other groups, to the level of nesting supported
enhanced function in message processing. by the underlying facility.
MQSeries Everyplace™. A generally available property. One of a set of characteristics that define the
MQSeries product that provides proven MQSeries values and behaviors of objects in the Control Center.
reliability and security in a mobile environment. For example, message processing nodes and deployed
message flows have properties.
MRM. Message Repository Manager.
publication node. An end point of a specific path
multilevel wildcard. A wildcard that can be specified through a message flow to which a client application
in subscriptions to match any number of levels in a subscribes. A publication node has an property,
topic. subscription point. If this is not specified, the
publication node represents the default subscription
N point for the message flow.
node. See message processing node. publish/subscribe. Style of messaging application in

which the providers of information (publishers) are
decoupled from the consumers of that information
O (subscribers) using a broker. Compare with
point-to-point. See also topic.
ODBC. Open Database Connectivity.
publisher. An application that makes information
Open Database Connectivity. A standard application about a specified topic available to a broker in a
programming interface (API) for accessing data in both publish/subscribe system.
relational and non-relational database management
systems. Using this API, database applications can
access data stored in database management systems on Q
a variety of computers even if each database
management system uses a different data storage queue. An MQSeries object. Message queuing
format and programming interface. ODBC is based on applications can put messages on, and get messages
the call level interface (CLI) specification of the from, a queue. A queue is owned and maintained by a
X/Open SQL Access Group. queue manager. Local queues can contain a list of
messages waiting to be processed. Queues of other
output node. A message processing node that types cannot contain messages: they point to other
represents a point at which messages flow out of the queues, or can be used as models for dynamic queues.
message flow.
queue manager. A system program that provides
queuing services to applications. It provides an
P application programming interface (the MQI) so that
programs can access messages on the queues that the
plug-in. An extension to the broker, written by a queue manager owns.
third-party developer, to provide a new message
processing node or message parser in addition to those
supplied with the product. See also implementation R
function and utility function.
retained publication. A published message that is
point-to-point. Style of messaging application in kept at the broker for propagation to clients that
which the sending application knows the destination of subscribe at some point in the future.
the message. Compare with publish/subscribe.

Glossary
request/reply. Type of messaging application in which subscription point. A property of a publication node
a request message is used to request a reply from that differentiates it from other publication nodes on
another application. Compare with datagram. the same message flow and therefore represents a
specific path through the message flow. An unnamed
rule. A rule is a definition of a process, or set of publication node (that is, one without a specific
processes, applied to a message on receipt by the subscription point) is known as the default publication
broker. Rules are defined on a message format basis, so node.
any message of a particular format will be subjected to
the same set of rules. Supervisory, Control, And Data Acquisition. A broad
term, used to describe any form of remote telemetry
system used for gathering data from remote sensor
S devices (for example, flow rate meters on an oil
pipeline) and for the near real time control of remote
SCADA. Supervisory, Control, And Data Acquisition.
equipment (for example, pipeline valves).
self-defining message. A message that defines its
system log. See local error log.
structure within its content. For example, a message
coded in XML is self-defining. Compare with pre-defined
message. T
send and forget. See datagram. terminal. The point at which one node in a message
flow is connected to another node. Terminals enable
setup type. The definition of the type of installation you to control the route that a message takes,
requested on Windows NT systems. This can be one of depending whether the operation performed by a node
Full, Broker only, or Custom. on that message is successful.
shared. All configuration data that is shared by users topic. A character string that describes the nature of
of the Control Center. This data is not operational until the data that is being published in a publish/subscribe
it has been deployed. system.
signature. The definition of the external characteristics topic based subscription. A subscription specified by
of a message processing node. a subscribing application that includes a topic for
filtering of publications.
single-level wildcard. A wildcard that can be
specified in subscriptions to match a single level in a topic security. The use of ACLs applied to one or
topic. more topics to control subscriber access to published
messages.
SQL. Structured Query Language.
topology. In the broker domain, the brokers,
stream. A method of topic partitioning used by
collectives, and connections between them.
MQSeries Publish/Subscribe applications.
transform. A defined way in which a message of one
Structured Query Language. A programming
format is converted into one or more messages of
language that is used to define and manipulate data in
another format.
a relational database. The language used by WebSphere
MQ Integrator Broker, ESQL, is based on SQL, and has
many similar constructs. U
subflow. A sequence of message processing nodes that Uniform Resource Identifier. The generic set of all
can be included within a message flow. A subflow does names and addresses that refer to World Wide Web
not have to include an input node or an output node, resources.
but can do so if required.
Uniform Resource Locator. A specific form of URI
subscriber. An application that requests information that identifies the address of an item on the World
about a specified topic from a publish/subscribe Wide Web. It includes the protocol followed by the
broker. fully qualified domain name (sometimes called the host
name) and the request. The Web server typically maps
subscription. Information held by a broker that
the request portion of the URL to a path and file name.
records the details of a subscriber application, including
Also known as Universal Resource Locator.
the identity of the queue on which that subscriber
wants to receive relevant publications. URI. Uniform Resource Identifier
subscription filter. A predicate that specifies a subset URL. Uniform Resource Locator
of messages to be delivered to a particular subscriber.
Glossary of terms and abbreviations 181

Glossary
User Name Server. The WebSphere MQ Integrator
Broker component that interfaces with operating system
facilities to determine valid users and groups.
utility function. Function provided by WebSphere MQ

Integrator Broker for the benefit of third-party
developers writing plug-in nodes or parsers.
W
warehouse. A persistent, historical datastore for events
(or messages). The Warehouse node within a message
flow supports the recording of information in a
database for subsequent retrieval and processing by
other applications.
wildcard. A character that can be specified in

subscriptions to match a range of topics. See also
multilevel wildcard and single-level wildcard.
wire format. This describes the physical representation

of a message within the bit-stream.
W3C. World Wide Web Consortium. An international

industry consortium set up to develop common
protocols to promote evolution and interoperability of
the World Wide Web.
X
XML. Extensible Markup Language.

Bibliography
This section describes the documentation
available for all current WebSphere MQ Integrator
Broker products. Version 2.1 platform-specific
publications
WebSphere MQ Integrator Broker Each WebSphere MQ Integrator Broker product is
Version 2.1 cross-platform documented in at least one platform-specific
publications publication, in addition to the family books.
WebSphere MQ Integrator Broker for AIX
The WebSphere MQ Integrator Broker
Version 2.1
cross-platform publications are:
WebSphere MQ Integrator Broker for AIX
v WebSphere MQ Integrator Broker Introduction and
Installation Guide, GC34-6178
Planning, GC34-6167
v WebSphere MQ Integrator Broker Using the WebSphere MQ Integrator Broker for HP-UX
Control Center, SC34-6168 Version 2.1
v WebSphere MQ Integrator Broker Messages, WebSphere MQ Integrator Broker for
GC34-6169 HP-UX Installation Guide, GC34-6180
v WebSphere MQ Integrator Broker Programming WebSphere MQ Integrator Broker for Solaris
Guide, SC34-6170 Version 2.1
v WebSphere MQ Integrator Broker Administration WebSphere MQ Integrator Broker for
Guide, SC34-6171 Solaris Installation Guide, GC34-6179
v WebSphere MQ Integrator Broker ESQL Reference, WebSphere MQ Integrator Broker for Windows
SC34-6172 NT and Windows 2000 Version 2.1
v WebSphere MQ Integrator Broker Problem WebSphere MQ Integrator Broker for
Determination Guide, GC34-6173 Windows NT and Windows 2000
v WebSphere MQ Integrator Broker Working with Installation Guide, GC34-6177
Messages, GC34-6174
WebSphere MQ Integrator Broker for z/OS
Version 2.1
These books are all available in hardcopy.
WebSphere MQ Integrator Broker for
You can order publications from the IBMLink ™ z/OS Customization and Administration
Web site at: Guide, SC34-6175
http://www.ibm.com/ibmlink WebSphere MQ Integrator Broker for
z/OS Program Directory, GI10-2557
In the United States, you can also order
publications by dialing 1-800-879-2755. MQSeries Everyplace
In Canada, you can order publications by dialing publications
1-800-IBM-4YOU (1-800-426-4968). If you intend to connect MQSeries Everyplace
applications to message flows that include the
For further information about ordering MQSeries Everyplace message flow nodes, you
publications contact your IBM authorized dealer will find the following publications useful:
or marketing representative.
v MQSeries Everyplace for Multiplatforms Version
1.2 Introduction, GC34-5843
1.2 Programming Guide, SC34-5845
1.2 Programming Reference, SC34-5846

Bibliography
v MQSeries Everyplace for Multiplatforms Version You can install the searchable PDF package as
1.2 Native Client Information, SC34-5880 follows:
v On AIX, invoke install —d and select the
You can find these books on the MQSeries Web documentation fileset. After installation, run the
site (see “MQSeries information available on the command mqsidocs. This launches Acrobat
Internet” on page 185). Translated versions of Reader and opens the PDF package.
these books are also available in some languages
v On HP-UX, invoke swinstall —d and select
from the same Web site.
WMQI-DOCS from the menu. After installation,
run the command mqsidocs. This launches
Softcopy books Acrobat Reader and opens the PDF package.
All the WebSphere MQ Integrator Broker books v On Solaris, invoke pkgadd —d and select
are available in softcopy formats. wmqi-docs from the menu. After installation,
run the command mqsidocs. This launches
Acrobat Reader and opens the PDF package.
Portable Document Format (PDF)
v On Windows NT, select the Online
The WebSphere MQ Integrator Broker library is
Documentation component on a custom
supplied in a searchable PDF package in US
installation, or do a full installation. After
English only on the product CD. (This package
installation, select Start—>Programs—>IBM
excludes the WebSphere MQ Integrator Broker for
WebSphere MQ Integrator 2.1—>Documentation.
z/OS Program Directory.) The searchable PDF
package is also supplied in the DOC directory on Translated books for national language versions of
the product Supplemental CD. The contents of the this product are provided as stand-alone PDFs
DOC directory can be viewed without installing the with the US English PDF package and
product. stand-alone PDFs of US English books on the
product supplemental CD. (Not every book is
Softcopy publications are not provided for the available in every supported language.)
z/OS operating system. If you have WebSphere
MQ Integrator Broker for z/OS Version 2.1, refer PDF files can be viewed and printed using the
to the softcopy publications provided on the CD Adobe Acrobat Reader. To take advantage of the
for Windows NT components. usability enhancements provided in the
searchable library, you will need Adobe Acrobat
The searchable PDF package comprises a set of Reader with Search Version 4.05 on Windows NT,
panels, books, and index files that are linked or Adobe Acrobat Reader with Search Version 4.5
together to provide a stand-alone method of on UNIX systems. You are not recommended to
searching the WebSphere MQ Integrator Broker use this package with Adobe Acrobat Reader with
library. It contains the following usability Search Version 3.xx.
enhancements:
v One searchable index per book If you need to obtain the Adobe Acrobat Reader,
or would like up-to-date information about the
v One searchable index file for the entire
platforms on which the Acrobat Reader is
WebSphere MQ Integrator Broker library
supported, visit the Adobe Systems Inc. web site
v Logical page numbering of PDF files to match at:
printed books http://www.adobe.com/
v Full cross-referencing within books that allows
you to jump to other parts of the same If you want to generate your own searchable
document index files for this collection of PDF files, you will
v Hyperlinked URLs to enable transparent need Adobe Acrobat Catalog 4 (part of Adobe
integration with the Web Acrobat Version 4). If you want to make your
own softcopy annotations in the files, you will
v An expandable list of PDF Bookmarks that
need the full version of Adobe Acrobat Version 4
allows you to jump directly to a specific section
(formerly Adobe Acrobat Exchange Version 3.01).
within a book
v Thumbnail images of all pages in the book to If you cut and paste examples of commands from
allow you to scan for diagrams, tables, and so PDF files to a command line for execution, you
on must check that the content is correct before you

Bibliography
press Enter. Some characters might be corrupted
by local system and font settings.
PDF versions of all current WebSphere MQ

Integrator Broker books are also available from
the MQSeries product family Web site at:
http://www.ibm.com/software/mqseries/
MQSeries information available

on the Internet
The MQSeries product family Web site is at:
http://www.ibm.com/software/mqseries/
By following links from this Web site you can:

v Obtain latest information about the MQSeries
product family.
v Access the MQSeries books in HTML and PDF
formats.
v Obtain information about complementary
offerings by following these links:
– IBM Business Partners
– Partner Offerings (within Related links)
v Download an MQSeries SupportPac.
v Access Redbooks.
Bibliography 185
MQSeries on the Internet

Index
A casting (continued)
intervals 119
CURRENT_TIMESTAMP, datetime
function 95
ABS, numeric function 90 numeric types 118
adding an interval to a datetime supported casts 112
value 11
adding or subtracting two intervals 11
CATALOG 83
CCSID
D
ALL data conversion
in CAST 112
use of keyword 26 using the Compute node 168
CEIL, numeric function 92
anonymous field references 27 data type
CHAR 63
ANY convert to a different type 99
CHARACTER 63
using keyword 27 strings 7
character string
arithmetic operators data types
convert to lower case 88
overview 11 BOOLEAN 10
convert to upper case 90
array indices 25 datetime
extract substring 89
AS 64 DATE 7
locate substring 89
ASCII GMTTIME 8
overlay 88
Carriage Return Line Feed 170 GMTTIMESTAMP 8
trim 88, 89
assignments 81 TIME 7
CHARACTER, string data type 7
ATTACH 50 TIMESTAMP 8
COALESCE function 102
datetime types 7
COLUMN functions 131
DECIMAL 6
commenting your ESQL 45
B comments 45
FLOAT 6
INTEGER 6
basic message structure 21 comparing
INTERVAL 8
BIT 63 character strings with other
numeric 6
bit, string data type 7 types 119
DECIMAL 6
BITAND, numeric function 91 comparison of ESQL statements and IBM
FLOAT 6
BITNOT, numeric function 91 nodes 17
INTEGER 6
BITOR, numeric function 91 comparison operators
overview 5
BITSTREAM function 96 overview 13
REFERENCE 10
BITXOR, numeric function 92 complementary offerings
strings
BLOB 63 IBM Business Partners 185
BIT 7
how to manipulate a variable length Partner Offerings 185
BLOB 7
message 153 COMPOUND 52
CHARACTER 7
BLOB, string data type 7 Compute node
database
Body 23 copying between parsers 167
add rows 70
BOOLEAN 63 correlation names 24
delete rows from a table 64
BOOLEAN, data type 10 for data conversion 168
update values in specified
byte, string data type 7 message transformation 170
columns 84
considerations when calling stored
database node
procedures 75
correlation names 24
C copying between like parsers
Compute node 167
database state indicators
CALL STATEMENT 51 SQLCODE 101
copying between parsers
CARDINALITY function 96 DATE 63
Compute node 167
cardinality in arrays 26 DATE, datetime data type 7
copying between unlike parsers
CASE function 97 dates
Compute node 168
case sensitivity of ESQL syntax 45 using arithmetic operators 11
correlation names
cast datetime data types
nodes that create an output
CCSID 112 using arithmetic operators 11
message 24
source expression 112 datetime functions 94
nodes that do not create an output
CAST 99, 111 CURRENT_DATE 94
message 23
CAST specifications 111 CURRENT_GMTDATE 95
CREATE FIELD 53
casting CURRENT_GMTTIME 95
CREATE FUNCTION 57
booleans 119 CURRENT_GMTTIMESTAMP 95
CREATE PROCEDURE 58
cast expressions 111 CURRENT_TIME 95
CURRENT_DATE, datetime function 94
cast specifications 111 CURRENT_TIMESTAMP 95
CURRENT_GMTDATE, datetime
character strings 118 EXTRACT 94
function 95
data types of values from external extracting fields from datetimes and
CURRENT_GMTTIME, datetime
sources 122 interval 94
function 95
datetime values 118 LOCAL_TIMEZONE 96
CURRENT_TIME datetime function 95
implicit CASTs for assignment 121 DECIMAL 63
implicit CASTs for comparisons 117 DECIMAL, numeric data type 6

DECLARE 63 ESQL overview FLOAT 63
DELETE 64 case sensitivity of ESQL syntax 45 FLOAT, numeric data type 6
DestinationList 24 EVAL 66 FLOOR, numeric function 93
DETACH 65 example FROM 53
DOMAIN 53 create a database table 140 FROM clause
drag and drop for ESQL 44 create a table in a case sensitive used in SELECT 133, 137
dynamic reference database system 141 function
LASTMOVE 97 how to use the ITEM keyword 141 SAMEFIELD 97
MOVE 71 how to use the WHERE clause 142 SELECT 100
SAMEFIELD 97 examples of SELECTs functions
SET NAME 81 effects of the THE keyword 132 BITSTREAM 96
SET TYPE 81 joining items in the FROM CARDINALITY 96
SET VALUE 81 clause 137 CASE 97
use of SET 81 multiple items in the FROM CAST 99
dynamic references 28 clause 133 COALESCE 102
selecting from a list of scalars 137 COLUMN 131
simple message transformation 125, database state
E 128
use of the ITEM keyword 132
SQLCODE 101
datetime functions 94
EBCDIC
using SELECT to return a scalar CURRENT_DATE 94
New Line character 170
value 133 CURRENT_GMTDATE 95
ENCODING
exception CURRENT_GMTTIME 95
in CAST 112
generate 83 CURRENT_GMTTIMESTAMP 95
Environment 24
EXCEPTION 83 CURRENT_TIME 95
ESQL
exception list CURRENT_TIMESTAMP 95
overview 3
using, with Compute node 163 EXTRACT 94
using drag and drop to create 44
exception list, examples of 157 extracting fields from datetimes
ESQL concepts
ExceptionList 24 and intervals 94
accessing message fields 5
expression type checking 5 LOCAL_TIMEZONE 96
arithmetic operators 11
expressions FIELDNAME 96
casting
overview 4 FIELDTYPE 97
Booleans 119
EXTERNAL 58 LASTMOVE 97
cast expressions 111
external databases, querying them using miscellaneous functions
character strings 118
ESQL 139 SQLERRORTEXT 101
data types of values from external
example SQLNATIVEERROR 101
sources 122
create a database table 140 SQLSTATE 101
datetime values 118
create a table in a case sensitive MOVE 71
implicit CASTs for arithmetic
database system 141 NULLIF 102
operations 119
how to use the ITEM numeric functions 90
implicit CASTs for
keyword 141 ABS 90
assignment 121
how to use the WHERE BITAND 91
implicit CASTs for
clause 142 BITNOT 91
comparisons 117
EXTRACT, datetime function 94 BITOR 91
intervals 119
extracting fields from datetimes and BITXOR 92
numeric types 118
intervals 94 CEIL 92
supported casts 112
FLOOR 93
comments 45
MOD 93
comparing
character strings with other F ROUND 93
SQRT 93
types 119 field references
TRUNCATE 94
comparison of ESQL statements and anonymous field names 27
overview 4
IBM nodes 17 array indices 25
PASSTHRU 73
comparison operators 13 checking for duplicates 97
string manipulation 87
data types 5 field types for the XML parser 30
LCASE 88
Boolean 10 MQRFH2 field types 30
LENGTH 87
datetime types 7 repeats
LOWER 88
INTERVAL 8 array indices 25
LTRIM 88
REFERENCE 10 quantified predicate 26
OVERLAY 88
strings 7 syntax 21
POSITION 89
expression type checking 5 field types
RTRIM 89
expressions 4 MQRFH2 headers 30
SUBSTRING 89
functions 4 referencing 30
TRIM 89
logical operators 12 XML 30
UCASE 90
nested statements 4 FIELDNAME function 96
UPPER 90
operators 4, 11 FIELDTYPE function 97
UUIDASBLOB 102
order of processing 16 filter node
UUIDASCHAR 103
statements 3 correlation names 24
FIRSTCHILD 50, 51, 52, 53, 71

G message fields
accessing 5
OutputExceptionList 25
OutputLocalEnvironment 25
generate an exception 83 message flow nodes OutputRoot 24
GMT date, current 95 Compute node OVERLAY, string manipulation
GMT time, current 95 to copy between parsers 167 function 88
GMT timestamp, current 95 message hierarchy
GMTTIME 63 attach portion in new position 50, 51,
GMTTIME, datetime data type 8
GMTTIMESTAMP 63
52
message structure, how to view 149
P
GMTTIMESTAMP, datetime data type 8 PARENT 71
message transformation
PARSE 53
Compute node 170
Partner Offerings 185
message tree
H attach portion in new position 50, 51,
PASSTHRU 73
considerations when calling stored
how to manipulate a variable length 52
procedures 75
BLOB message 153 detach portion without deleting 65
limitations when calling stored
message used for examples 147
procedures 76
message, example 147
PDF (Portable Document Format) 184
I messages
basic structure 21
Portable Document Format (PDF) 184
IBM Business Partners 185 POSITION, string manipulation
how to view a message structure 149
IF 69 function 89
miscellaneous functions
implicit CASTs for assignment 121 precedence
SQLERRORTEXT 101
implicit CASTs for comparisons 117 of operators 16
SQLNATIVEERROR 101
IN 58 predefined functions
SQLSTATE 101
information on the Internet overview 4
MOD, numeric function 93
complementary offerings 185 predicate
MOVE 71
MQSeries family libraries 185 quantified 26
MQRFH2 field types 30
MQSeries products 185 predicates 13
MQSeries Everyplace publications 183
MQSeries SupportPacs 185 EXISTS 15
INOUT 58 PREVIOUSSIBLING 50, 51, 52, 53, 71
InputBody 24 primitives
InputDestinationList 24 N Compute node
InputExceptionList 24 NAME 58, 71 to copy between parsers 167
InputLocalEnvironment 24 nested statements PROPAGATE 77
InputProperties 24 overview 4 Properties 24
InputRoot 24 NEXTSIBLING 50, 51, 52, 53, 71 publications
INSERT 70 node MQSeries Everyplace 183
INT 63 stop processing 79 WebSphere MQ Integrator
INT, numeric data type 6 NULLIF function 102 Broker 183
INTEGER 63 number
INTEGER, numeric data type 6 convert to absolute value 90
INTERVAL 63 find square root 93
round up 93
Q
INTERVAL, data type 8 querying external databases using
ITEM keyword truncate 94
ESQL 139
used in SELECT 132 numeric
data types 6
numeric functions
L ABS 90 R
BITAND 91 Redbooks 185
LASTCHILD 50, 51, 52, 53, 71 BITNOT 91 REFERENCE 28
LASTMOVE 97 BITOR 91 dynamic reference
LCASE, string manipulation function 88 BITXOR 92 SET NAME 81
LENGTH, string manipulation CEIL 92 SET TYPE 81
function 87 FLOOR 93 SET VALUE 81
LIST constructor 106 MOD 93 use of SET 81
LOCAL_TIMEZONE, datetime ROUND 93 REFERENCE TO 63
function 96 SQRT 93 REFERENCE, data type 10
LocalEnvironment 24 TRUNCATE 94 referencing field types 30
logical operators referencing repeating fields 25
overview 12 referring to information in messages 21
LOWER, string manipulation
function 88 O REPEAT 53, 71
repeating fields
LTRIM, string manipulation function 88 operator precedence 16
referencing if you do not know how
operators
many instances there are 26
overview 4, 11
referencing if you know how many
M order of processing 16
OUT 58
instances there are 25
MESSAGE 83 repeats
OutputDestinationList 25
array indices 25
Index 189
repeats (continued)
quantified predicate 26
statements (continued)
SELECT 105
U
RETURN 79 ROW and LIST constructors 105 UCASE, string manipulation function 90
RETURNS 57 SET 81 universally unique identifier (UUID)
Root 23 THROW 83 create as a BLOB 102
ROUND, numeric function 93 UPDATE 84 create as a CHAR 103
ROW and LIST constructors 105 WHILE 85 UPDATE 84
ROW constructor 105 statements and expressions UPPER, string manipulation function 90
rows and lists in the SELECT EVAL 66 use of keyword ALL 26
statement 105 PASSTHRU USER 83
RTRIM, string manipulation function 89 limitations when calling stored UUID
procedures 76 create as a BLOB 102
stop node processing 79 create as a CHAR 103
UUIDASBLOB function 102
S string
convert to lower case 88 UUIDASCHAR function 103
SAMEFIELD 97
convert to upper case 90
samples 147, 167
extract substring 89
scalar variable
declare 63
locate substring 89 V
overlay 88 VALUE 53
scaling intervals 12
trim 88, 89 VALUES 83
SELECT 100
string manipulation variable
effects of the THE keyword 132
LCASE function 88 declare 63
joining items in the FROM
LENGTH function 87
clause 137
LOWER function 88
multiple items in the FROM
clause 133
LTRIM function 88
OVERLAY function 88
W
selecting from a list of scalars 137 WebSphere MQ Integrator Broker on the
POSITION function 89
simple message transformation 125, Internet 185
RTRIM function 89
128 WebSphere MQ Integrator Broker
SUBSTRING function 89
use of the ITEM keyword 132 publications 183
TRIM function 89
using SELECT to return a scalar national language 184
UCASE function 90
value 133 platform–specific 183
UPPER function 90
SELECTs, rows and lists 105 WHERE 64
strings
SET 81 WHILE 85
data type 7
use of SET 81 Windows 2000 xi
SUBSTRING, string manipulation
used in a Compute node 167
function 89
SET NAME 81
subtracting two datetime values 12
SET TYPE 81
SET VALUE 81
supported casts 112 X
supported CASTS 112 XML attributes
SEVERITY 83
SupportPac 185 translating to tags 68
softcopy books 184
syntax XML parser field types 30
SOME
case sensitivity of ESQL 45
using keyword 27
expression type checking 5
source expression
in CAST 112
SQLCODE 101
SQLERRORTEXT 101 T
SQLNATIVEERROR 101 THE keyword
SQLSTATE 101 used in SELECT 132
SQRT, numeric function 93 THROW 83
statements TIME 63
assignments 81 time, current 95
ATTACH 50 TIME, datetime data type 7
CALL STATEMENT 51 times
COMPOUND 52 using arithmetic operators 11
CREATE FIELD 53 TIMESTAMP 63
CREATE FUNCTION 57 timestamp, current 95
CREATE PROCEDURE 58 TIMESTAMP, datetime data type 8
DECLARE 63 timezone, local 96
DELETE 64 trace
DETACH 65 using, to view a message
IF 69 structure 149
INSERT 70 transforming data from one data type to
MOVE 71 another (CAST) 111
overview 3, 4 TRIM, string manipulation function 89
PASSTHRU 73 TRUNCATE, numeric function 94
PROPAGATE 77 TYPE 53, 71
RETURN 79

Sending your comments to IBM
If you especially like or dislike anything about this book, please use one of the
methods listed below to send your comments to IBM.
Feel free to comment on what you regard as specific errors or omissions, and on
the accuracy, organization, subject matter, or completeness of this book.
Please limit your comments to the information in this book and the way in which
the information is presented.
To make comments about the functions of IBM products or systems, talk to your
IBM representative or to your IBM authorized remarketer.
When you send comments to IBM, you grant IBM a nonexclusive right to use or
distribute your comments in any way it believes appropriate, without incurring
any obligation to you.
You can send your comments to IBM in any of the following ways:
v By mail, to this address:
User Technologies Department (MP095)
IBM United Kingdom Laboratories
Hursley Park
WINCHESTER,
Hampshire
SO21 2JN
United Kingdom
v By fax:
– From outside the U.K., after your international access code use
44–1962–816151
– From within the U.K., use 01962–816151
v Electronically, use the appropriate network ID:
– IBM Mail Exchange: GBIBM2Q9 at IBMMAIL
– IBMLink: HURSLEY(IDRCF)
– Internet: [email protected]
Whichever method you use, ensure that you include:

v The publication title and order number
v The topic to which your comment applies
v Your name and address/telephone number/fax number/network ID.


Printed in U.S.A.
SC34-6172-00
Spine information:
WebSphere MQ Integrator Broker WebSphere MQ Integrator Broker ESQL Reference Version 2.1

ESQL

Uploaded by

Copyright:

Available Formats

ESQL

Uploaded by

Document Information

Copyright

Available Formats

Share this document

Share or Embed Document

Sharing Options

Did you find this document useful?

Is this content inappropriate?

Copyright:

Available Formats

ESQL

Uploaded by

Copyright:

Available Formats

WebSphere MQ Integrator Broker

First Edition (June 2002)

Chapter 7. Transforming data from Appendix C. How to read syntax

iv WebSphere MQ Integrator Broker ESQL Reference

© Copyright IBM Corp. 2002 vii

© Copyright IBM Corp. 2002 ix

WebSphere MQ messages can be manipulated, constructed, and reformatted by

This book is divided into parts:

A set of examples, an explanation about how to read syntax diagrams, a glossary

Who this book is for

What you need to know to understand this book

Terms used in this book

© Copyright IBM Corp. 2002 xi

Chapter 2. Messages and ESQL . . . . . . . 21

© Copyright IBM Corp. 2002 1

This chapter introduces the following ESQL concepts:

© Copyright IBM Corp. 2002 3

Operators and expressions

SET OuterRadius = 4.0;

4 WebSphere MQ Integrator Broker ESQL Reference

Accessing message fields

For more information about field references, see “Referring to information in

Chapter 1. What is ESQL 5

Note: If a literal of this form is too large to be represented as an integer, it

6 WebSphere MQ Integrator Broker ESQL Reference

Any number of digits, which must be either 0 or 1, can be specified. The

For information about string functions, see “String manipulation functions” on

Chapter 1. What is ESQL 7

An interval literal is defined by the following syntax:

8 WebSphere MQ Integrator Broker ESQL Reference

A space is used to separate days from the rest of the interval.

Some examples of valid interval values are:

Some examples of invalid interval values are:

Chapter 1. What is ESQL 9

10 WebSphere MQ Integrator Broker ESQL Reference

Using arithmetic operators with datetime functions

Chapter 1. What is ESQL 11

Subtracting two datetime values: Two datetime values can be subtracted to

would return the age of the day in minutes and seconds.

Scaling intervals: An interval value can be multiplied by or divided by an integer

12 WebSphere MQ Integrator Broker ESQL Reference

Table 3. Result of NOT operation in ESQL

Chapter 1. What is ESQL 13

The default form of this operator is known as asymmetric. This requires

Both expressions return the value TRUE.

14 WebSphere MQ Integrator Broker ESQL Reference

For example, the following predicate finds a match for ‘IBM_Corp’.

The EXISTS function returns a boolean value indicating whether a SELECT

Chapter 1. What is ESQL 15

Principle Statement Types

Statement type Description

16 WebSphere MQ Integrator Broker ESQL Reference

Comparison of Filter, Compute, and Database nodes and Database

Chapter 1. What is ESQL 17

18 WebSphere MQ Integrator Broker ESQL Reference

WebSphere MQ Integrator Broker provides a THROW statement to create

There is some special handling of return values:

Chapter 1. What is ESQL 19

20 WebSphere MQ Integrator Broker ESQL Reference

This chapter describes: