You can use the Table Browser to browse a defined TIBCO Object Service Broker DB2 table by typing the table name next to the BR browse table option, for example:
In the sample screen below, EMPLOYEE is the name of the TIBCO Object Service Broker DB2 table, and 1998 is the parameter value for the table instance. The DB2 data is presented in the TIBCO Object Service Broker table format:
TIBCO Object Service Broker Managing Data for more information on using the Table Browser and Table Editor.
You can browse and edit a TIBCO Object Service Broker DB2 table in the same way you would browse or edit another TIBCO Object Service Broker table except in the following cases:
−
|
You must use SELECT LIKE instead of SELECT to access fields of this length.
|
•
|
Using the Single Occurrence Editor from the Table Browser begins a dependent transaction in TIBCO Object Service Broker, assigning a new Gateway. Therefore, if you edit an occurrence using the Single Occurrence Editor from the Table Browser it creates a locking conflict in DB2 since the Table Browser transaction already has a shared lock on the DB2 data. You can use the Single Occurrence Editor from the Table Editor, since this does not begin a dependent TIBCO Object Service Broker transaction and requires only one Gateway.
|
The Table Browser does not hold locks on TDS data; however, this is not true for TIBCO Object Service Broker DB2 tables. Locking of DB2 data is determined by the Gateway BIND parameters and the DB2 subsystem, not by TIBCO Object Service Broker.
Accessing DB2 data using the TIBCO Object Service Broker rules language is similar to accessing TIBCO Object Service Broker data. The main difference is in the way DB2 interprets the request. Use the following sections in conjunction with
TIBCO Object Service Broker Programming in Rules, which describes rules statements and coding.
If you issue a TIBCO Object Service Broker EXECUTE statement within a main (parent) transaction, it creates another transaction stream (child transaction), to a maximum of ten streams. The number of streams allowed in a TIBCO Object Service Broker transaction depends on the Execution Environment parameter TRANMAXNUM, which has a default of nine streams. Each transaction stream in TIBCO Object Service Broker accessing DB2 data requires its own Gateway thread.
If you use the default parameter values, you can access at least 16 TIBCO Object Service Broker DB2 tables per transaction; possibly more, depending on the size of the TIBCO Object Service Broker DB2 table definitions. Refer to
Estimating the CTABLESIZE Parameter for more information.
The following sections outline differences encountered while using rules and also point out normal TIBCO Object Service Broker rules behavior that you must consider when building applications.
A FORALL statement is a looping construct that processes a set of occurrences. The body of the loop consists of the statements to be executed for each occurrence satisfying the selection criteria. FORALL statements can be nested provided that they refer to different TIBCO Object Service Broker table names.
Rows are returned to TIBCO Object Service Broker in the order in which DB2 passes them. If you require a different order, you must include an ORDERED clause in your FORALL statement. TIBCO Object Service Broker orders only rows specified in the selection criteria.
For Dynamic SQL, a pool of five cursors is shared for all requests for a sequence of rows (TIBCO Object Service Broker FORALL statements) in a single transaction. Therefore, a transaction can have up to five nested FORALL statements where the resulting set of occurrences from each FORALL is greater than 4 KB. If you are using Static SQL, you can have more than five nested FORALL statements.
The occurrence that matches the selection criteria (based on the primary key and parameters) is updated if you specify a unique DB2 occurrence. If you do
not specify a unique DB2 occurrence, all DB2 rows that match this criteria are updated.
|
•
|
Select parameters and TIBCO Object Service Broker primary keys that, when combined, enable each DB2 occurrence to be uniquely identified, even though the DB2 table does not have unique keys defined. Do not include non-updateable DB2 columns (N in the Upd field) as fields in a TIBCO Object Service Broker DB2 table if you plan to update DB2 data using this definition.
|
|
If you need to insert rows to a DB2 table or view, ensure your DB2 table definition includes all NOT NULL (without default) fields and that these fields are initialized before the INSERT statement.
If you choose DB2 columns as parameters that correspond to a unique key of a DB2 table or view, you can insert only a single row using this definition. This is because the Gateway combines the parameter values with the TIBCO Object Service Broker primary key values, causing an attempt to insert a duplicate row to DB2.
If you edit the occurrence using the Single Occurrence Editor, the first row in the DB2 table or view to match the parameters (if any) and the primary key values appears for editing.
If your DB2 gateway startup parameter specifies USEDB2LIKE, a FORALL…LIKE means that DB2 LIKE statements are generated to maximize efficiencies in retrieval. Only rows that satisfy DB2 LIKE evaluation are returned. Because of differences in evaluation processing, use of DB2 LIKE could return a different number of rows than use of a TIBCO Object Service Broker LIKE. Also, you can use LIKE on all TIBCO Object Service Broker field types. DB2 accepts only LIKE on CHAR, VARCHAR, and graphic types. For more information refer to
Using DB2 LIKE or NOT.
Depending on how a WHERE clause for a parameterized table is coded, the Gateway can generate different SQL for parameterized table accesses. The results of the SQL processing are the same.
A DB2 stored procedure is an executable unit that DB2 callers can have DB2 invoke on their behalf. A DB2 stored procedure as seen by the caller has the following characteristics:
TIBCO Object Service Broker table definitions of type DB2, subtype P, are used to map DB2 stored procedures. When such a definition is created by the TIBCO Object Service Broker table definer, the stored procedure being mapped is fully identified by the Schema/Procedure pair and, if the Service Gateway for DB2 denoted by SERVERID is running and available, the following attributes are fetched from DB2 by the definer:
TIBCO Object Service Broker table definitions of type DB2, subtype R, are used to map DB2 stored procedures' result sets. Such definitions are generated by the definer (the RS_REFRESH PF key) when defining a stored procedure mapping; those pertaining to a particular DB2 stored procedure can be viewed via the RS_INFO key, which displays a list of TIBCO Object Service Broker names for result set mapping, their respective DB2 stored procedure name, and TIBCO Object Service Broker mapping of the latter. If an entry of this list is selected with the S line command, the field structure of the result set is displayed in Browse mode. In order to modify a TIBCO Object Service Broker table definition of type DB2, subtype R, use the table definer directly against it.
The ImplUpd (implied update) attribute indicates whether TIBCO Object Service Broker is expected to consider the fact of invocation of this stored procedure as an update operation, meaning that the transaction in whose context it occurred should be reported to the Data Object Broker as resulting in data changes, which is essential for determining the specifics of commit/rollback/recovery procedures to be used for this transaction. Note that ImplUpd = N improves the application's performance (unless other modifications of data, either implicit or explicit, occur within the boundaries of the same transaction).
In the TIBCO Object Service Broker implementation of DB2 stored procedures support, TIBCO Object Service Broker table definitions of type DB2, subtype P, differ from all other tables of type DB2. For tables of type DB2, subtype P, all tabular access verbs of the rules language (GET, FORALL, INSERT, DELETE, REPLACE) are considered illegal operations, whereas the CALL statement accepts the name of such a table as an executable entity.
Run a DB2 stored procedure with the following CALL statement in either positional or keyword form:
where <proc> is the name of a TIBCO Object Service Broker table of type DB2, subtype P; P1, P2, ..., PN are the names of fields in that table; and
<p1>,<p2>,…,<pn> are values/expressions to be passed as IN and INOUT parameters of the procedure. The OUT parameters must be omitted from the list; if no IN and INOUT parameters are defined, the parentheses (or the WHERE clause) must also be omitted.
Note: Although NULL and zero-length string are considered identical in the context of TIBCO Object Service Broker rules, the DB2 gateway differentiates between them when passing parameter values to DB2-stored procedures. Thus, CALL PROC(NULL) is identical to CALL PROC WHERE P1=NULL and to CALL PROC; the DB2-stored procedure <proc> receives NULL as input value. However, CALL PROC('') or CALL PROC WHERE P1='' must be issued to have a zero-length string passed to the stored procedure.
Upon return, the values set up by the stored procedure are available to the caller via the regular
<table>.<field> notation. If any result sets have been generated, the caller must know the names of the TIBCO Object Service Broker tables (tables of type DB2, subtype R) for mapping them and use the FORALL statements to fetch their contents.
Two auxiliary fields, @HANDLE@ and #RS#, are always included (and marked K – key) in the definition of a TIBCO Object Service Broker stored procedure mapping. They have the following meaning:
The @HANDLE@ auxiliary field is always included (and marked P – data parameter) in the definition of a TIBCO Object Service Broker result set mapping. At the result set fetch time, the caller is expected to supply as input for @HANDLE@ the value assigned to the @HANDLE@ field by the CALL statement.
This example calls a stored procedure and fetches the result set it produces. EMPRSET is a DB2-provided stored procedure sample. The TIBCO Object Service Broker table definer displays it as shown below.
PROC.@HANDLE@ = <non-NULL>
PROC.#RS# = 1
PROC.PDEPTNAME = MANUFACTURING SYSTEMS
PROC.PSQLCODE = 0
PROC.PSQLSTATE = 00000
PROC.PSQLERRMC = <NULL>
The caller can dynamically query the layout of the result set(s) produced by a stored procedure. To do so, after the
CALL PROC(…) statement, issue the following:
where 0 < n <= PROC.#RS# is the number of the result set being queried. The sequence (numbering) of result sets is determined by the procedure. The table @SYSRSCOLS is a subview of @SYSRSCOLUMNS and comprises all columns of a particular result set (identified by the procedure-assigned name CURSOR_NAME) with their attributes as defined by the stored procedure.
When the context of a stored procedure call is no longer needed, you can release the resources used by the context, while the transaction proceeds. After the
CALL PROC(…) statement, issue the following: