Capturing database state

If an error occurs when the integration node accesses an external database, you can either let the integration node throw an exception during message flow node processing or use ESQL statements to process the exception within the message flow node itself.

About this task

Letting the integration node throw an exception during message flow node processing is the default action; ESQL processing in the current message flow node is abandoned. The exception is then propagated backwards through the message flow until an enclosing catch node, or the input node for this message flow, is reached. If the exception reaches the input node, an active transaction is rolled back.

Using ESQL statements to process the exception within the message flow node itself 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 clear the Filter, Database, or Compute node's Throw Exception On Database Error property. If you clear this property, the message flow node sets the database state indicators SQLCODE, SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT, with appropriate information from the database manager instead of throwing an exception.

The indicators contain information only when an error (not a warning) occurs, unless you have selected the Treat Warnings As Errors property. If a database operation is successful, or returns success with information, the indicators contain their default success values.

You can use the values contained in these indicators in ESQL statements to make decisions about the action to take. You can access these indicators with the SQLCODE, SQLSTATE, SQLNATIVEERROR, and SQLERRORTEXT functions.

If you are attempting inline error processing, check the state indicators after each database statement is executed to ensure that you catch and assess all errors. When processing the indicators, if you meet an error that you cannot handle inline, you can 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, for which you can use the ESQL THROW statement.

You might want to check for the special case in which a SELECT returns no data. This situation is not considered an error and SQLCODE is not set, therefore you must test explicitly for it; see Checking returns to SELECT.

Using ESQL to access database state indicators

The following ESQL example shows how to use the four database state functions, and how to include the error information that is returned in an exception:

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 );

You do not have to throw an exception when you detect a database error; you might prefer to save the error information returned in the local environment tree, and include a Filter node in your message flow that routes the message to error or success subflows according to the values saved.