ESQL has a number of statements and
functions for accessing databases:
You can access user databases from Compute,
Database, and Filter nodes.
Note: There is no difference between the database
access capabilities of these nodes; their names are partly historical and
partly based on typical usage.
You can use the data in the databases
to update or create messages; or use the data in the message to update or
create data in the databases.
Note that:
- Any node that uses any of the ESQL database statements or functions must
have its Data Source property set
with the name (that is, the ODBC DSN) of a database. The database must be
accessible, operational, and allow the broker to connect to it.
- All databases accessed from the same node must have the same OBDC functionality
as the database specified on the node’s Data
Source property. This requirement is always satisfied if the databases
are of the same type (for example, DB2 or Oracle), at the same level (for
example, release 8.1 CSD3), and on the same platform. Other database combinations
may or may not have the same OBDC functionality. If a node tries to access
a database that does not have the same OBDC functionality as the database
specified on the node’s Data Source property,
the broker issues an error message.
- All tables referred to in a single SELECT FROM clause must be in the same
database.
You must ensure that suitable ODBC data
sources have been created on the system on which the broker is running. If
you have used the mqsisetdbparms command
to set a user ID and password for a particular database, the broker uses these
values to connect to the database. If you have not set a user ID and password,
the broker uses the default database user ID and password that you supplied
on the mqsicreatebroker command
(as modified by any subsequent mqsichangebroker commands).
On z/OS systems, use the JCL member
BIPSDBP in the customization data set <hlq>.SBIPPROC to
perform the mqsisetdbparms command.
You must also ensure that the database user IDs have
sufficient privileges to perform the operations your flow requires. Otherwise
errors will occur at runtime.
Select the Throw exception on database error property
check box and the Treat warnings as errors property
check box, and set the Transaction property
to Automatic, to provide maximum
flexibility. You can then use the COMMIT and ROLLBACK statements for transaction
control, and create handlers for dealing with errors.