Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved


Chapter 5 Processing Data : Behavior of the Gateway

Behavior of the Gateway
TIBCO Object Service Broker DB2 Requests
This section describes how the Gateway handles TIBCO Object Service Broker requests with respect to:
Synchronization and Recovery
Locking DB2 Data
The Gateway BIND parameters and the DB2 subsystem, not TIBCO Object Service Broker, determine how DB2 data is locked. A DB2 transaction spans the same length of time as a TIBCO Object Service Broker transaction.
When a commit is issued, DB2 data is committed and locks are released. The cursor position is maintained, because the DB2 statement is coded using WITH HOLD (as in EXEC SQL DECLARE HURC01 CURSOR WITH HOLD FOR HURS01). Only at transaction end does the DB2 Gateway close the cursor.
Considerations
A COMMIT request sent to the Gateway or a normal end of TIBCO Object Service Broker transaction results in an SQL COMMIT being sent to DB2.
Processing can continue after a ROLLBACK, however, cursors still open are closed and the application must re-establish the cursor.
The exception COMMITLIMIT does not apply to DB2 tables. Requests to update DB2 data are processed as they are encountered and are not buffered in the intent list.
Updating DB2 Data Only
Transactions that update only DB2 data are recoverable under DB2.
Updating TIBCO Object Service Broker and DB2 Data
TIBCO Object Service Broker provides a method of ensuring data integrity when a TIBCO Object Service Broker transaction updates both DB2 and TIBCO Object Service Broker data in the same transaction. This method is referred to as Fail Safe level‑1 processing.
If you did not request Fail Safe processing, transactions that update both DB2 and TIBCO Object Service Broker data can result in discrepancies if the Gateway or the Data Object Broker abnormally terminate during transaction end processing. Refer to Implementing Fail Safe Processing for more information.
When a ROLLBACK request is sent to the Gateway, an SQL ROLLBACK is sent to DB2 and the TDS Intent List is discarded.
Static SQL
Using Static SQL for your application enables DB2 accesses to be done more quickly using less resources. When your application is complete, you can collect your TIBCO Object Service Broker DB2 access statements and use them to generate Static SQL. To do this, your system administrator must bring up an Execution Environment with the DB2LOG Execution Environment parameter set to Y. Refer to Chapter 3, Using Static SQL for more information.
See Also
TIBCO Object Service Broker Parameters for further information on parameters.
Collecting TIBCO Object Service Broker DB2 Access Statements
When collecting TIBCO Object Service Broker DB2 access statements to generate Static SQL, ensure that the following applies:
If your application includes rules that contain conditions, invoke all statements in your application that access DB2 data. This is done by running the application, perhaps several times, to include all accesses. If an access to a DB2 table is already logged, it is not logged again.
If a DB2 column accepts nulls, and your application is selecting this column for both nulls and values, you must collect access statements for both nulls and values. This is because DB2 requires different SQL statements for nulls than for values.
If you modify only your TIBCO Object Service Broker DB2 table definition, you have to regenerate Static SQL only to pick up any changes. You do not have to recollect the TIBCO Object Service Broker DB2 access statements. Refer to Task C: Generate Static SQL Using the @STATICSQL Tool for more information.
Since the Table Editor and the Table Browser are TIBCO Object Service Broker applications, accesses (SELECTs) made through these applications against TIBCO Object Service Broker DB2 tables are also logged.
Error Handling
Gateway Exceptions
The TIBCO Object Service Broker runtime environment signals system exceptions to enable an application to recover from an error. A three-level hierarchy of exceptions exists. Each exception traps the exceptions that appear below it in the hierarchy. All errors encountered when accessing DB2 data through the Gateway are trapped under one of the following exceptions:
 
 
 
The primary key specified for a DELETE statement does not exist. SQL code +100 raises this exception.
The primary key provided for an INSERT statement already exists. SQL error code -803 raises this exception.
The primary key provided for a REPLACE statement does not exist. SQL code +100 or the SQL error code -803 both raise this exception.
 
 
There is a lock on an occurrence or a table is unavailable. SQL error codes -904, -913, and -30040 all raise this exception.
Permission for the requested action on the TIBCO Object Service Broker object is denied. This also occurs if:
The DB2 authorization ID does not have permission to perform the requested action on the specified object. SQL error codes -551, -552, -553, -554, -555, and -556 all raise this exception.
The external security interface is implemented and one of the checks performed by the HRNSECD2 macro supplied by TIBCO Object Service Broker failed. Refer to Implementing External Security for more information.
The external security interface is implemented and the EXTERNALUSERID parameter is set to GROUP and is greater than eight characters.
A new transaction requested an instance of the Gateway and no Gateway is available. Control is passed back to the rule, so that the rule can try the transaction again. If this exception is raised too often, consider requesting more Gateways or reviewing the amount of work being done in your transactions.
The Gateway made a request to DB2 and DB2 returned an error code that does not map to one of the specific TIBCO Object Service Broker exceptions. The ON SERVERERROR handler can call @SERVERERROR to parse the error message (contained in ENDMSG).
Refer to @SERVERERROR Tool for more information.
A transaction was in progress when the connection to an instance of the Gateway was broken or the Gateway failed. Control is passed back to the rule for transaction cleanup.
SQL error codes -911, -918, -919, -929, ‑30020, -30030, -30041, -30072, and ‑30073 also raise this exception.
See Also
TIBCO Object Service Broker Programming in Rules about exception handling.
@SERVERERROR Tool
Using @SERVERERROR
You must pass @SERVERERROR the contents of RETURN_MESSAGE, which has the following format:
pppADnnnx serverid serveruserid source: Message
The following list describes the variables necessary to pass the RETURN_MESSAGE contents to @SERVERERROR:
The server ID of the Gateway.
The Gateway user ID (IDPREFIX + ###) of the Gateway.
The code portion of the Gateway that trapped the error and returned the message (for example, CSECT, rule, or function).
If a specific message from a specific Gateway has some information that is required to process the error, the table driven approach to the execution of @SERVERERROR causes a rule (specified for that error by the developer using @SERVERERROR) to execute. The error message is interpreted in the @SERVERERROR processing and put into a temporary table until required.
To customize error handling, you must update data in one of the control tables @DB2SQLMSGCNTL or @DB2CAFMSGCNTL. The definition of these tables is owned by TIBCO Object Service Broker and must not be modified. The data you update in them is owned by you.
Table Processing
When the SERVERERROR exception is raised and the @SERVERERROR rule is called by your application, the following steps take place:
1.
@SERVERERROR reads the @SERVERMSGCNTL table and looks up the specific message identifier handlers.
2.
3.
4.
The user-written handler can use other functions and data stored in specific tables to handle specific external error/status code.
Considerations
@SERVERERROR can be called at any time, although it is useful only for parsing TIBCO Object Service Broker DB2 messages generated due to external DB2 errors. The original message can always be retrieved using @SE_MSG when @SERVERERROR has been called. The information parsed by @SERVERERROR has transaction scope.
You can add your own instances in the @SERVERMSGCNTL table, provided that the OWNER specified begins with letters A to Z and the key values in their instance are message identifiers in the form D2nnnx mentioned in Using @SERVERERROR.
See Also
TIBCO Object Service Broker Shareable Tools for more information on the @SERVERERROR tool.
Stored Procedure Processing
In TIBCO Object Service Broker, you CALL a stored procedure table mapping (TIBCO Object Service Broker table of type DB2, subtype P) from a rule. The Gateway sets up a DB2 SQLDA as per the columns in the table definition and issues the following:
EXEC SQL CALL :procedure USING DESCRIPTOR :sqlda
On successful return (SQLCODE +466), the Gateway builds a row from all IN, INOUT and OUT parameters and returns. The SQLDA also contains the number of result sets that the stored procedure has generated. This information is returned in the #RS# field of the table.
If the stored procedure has generated any result sets, the field @HANDLE@ will be set to a non-zero value denoting the context of this particular call. That context remains available until discarded, either explicitly via a @DB2PROCDISCARD call or implicitly at the transaction end time. The caller is expected to fetch a result set through the FORALL statement against the predefined result-set mapping (TIBCO Object Service Broker table of type DB2, subtype R), which is parameterized by the @HANDLE@ available upon the CALL statement. The cursor-name attribute of this table denotes the particular result set to be fetched.
If the stored procedure reports multiple result sets, you can fetch them in any order. However, only one FORALL statement is possible at one time: A current FORALL against a result set is closed—and the respective result set discarded—by a subsequent FORALL against another result set.
When the Gateway receives a transaction end it closes any result sets and issues a SQL COMMIT.

Copyright © TIBCO Software Inc. All Rights Reserved
Copyright © TIBCO Software Inc. All Rights Reserved