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


Chapter 5 Processing Data : Accessing TIBCO Object Service Broker DB2 Tables

Accessing TIBCO Object Service Broker DB2 Tables
When you access DB2 data from TIBCO Object Service Broker, the following occurs:
TIBCO Object Service Broker requests are translated into SQL statements. The Gateway generates Dynamic SQL to access DB2 data, unless Static SQL is in place for a TIBCO Object Service Broker DB2 table. Existing Static SQL is used by the Table Browser, Table Editor, or a new application. Refer to Chapter 3, Using Static SQL for more information.
DB2 column data types are translated to the TIBCO Object Service Broker field types defined in the TIBCO Object Service Broker DB2 table.
You can access the data using:
These methods are described in the following sections.
Using the TIBCO Object Service Broker Table Browser and Table Editor
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:
 
EMPLOYEE(1998)<Enter>
 
 
Sample Screen
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:

 
BROWSING TABLE : EMPLOYEE(1998)
COMMAND ==>
SCROLL: P
EMPNO LNAME POSITION MGR# DEPTNO SALARY
_ ------ ---------------------- -------------- ------ ------ ---------
_ 22001 DRABEK CUST SUPPORT 56112 30 900.00
_ 22007 ROEDER CUST SUPPORT 56112 30 900.00
_ 30058 HOEGSON PRE-SALES 37219 20 675.00
_ 34111 TERAMURA PRE-SALES 37219 20 710.00
_ 34121 LEES CUST SUPPORT 56112 30 700.00
_ 36162 MORANG JR OPERATOR 44798 80 575.00
_ 41001 CROFTON TECH WRITER 80002 70 675.00
_ 41007 STEVENSON EDUCATOR 80002 60 700.00
_ 41009 SMITH TESTER 79912 50 600.00
_ 44385 SOUZA SALES 37219 10 719.00
_ 44622 SAUNDERS ACCOUNTANT 98895 40 800.00
_ 51111 HRODEK ANALYST 79912 50 710.00
_ 51121 CANNON ANALYST 79912 50 700.00
_ 51162 KIMURA JR PROGRAMMER 79912 50 575.00
_ 61219 WONG SENIOR ANALYST 79912 50 820.00
_ 61385 DHILLON EDUCATOR 80002 60 685.00
_ 61622 SCHULTZ SENIOR ANALYST 79912 50 800.00
PFKEYS: 1=HELP 5=FIND NEXT 9=RECALL 18=EXCLUDE 13=PRINT 3=END 14=EXPAND
At TOP

 
See Also
TIBCO Object Service Broker Managing Data for more information on using the Table Browser and Table Editor.
Exceptions
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:
If a selected column has an N in the Upd field, you cannot edit this field. You can, however, edit other fields in the same table.
If you delete an occurrence using the Table Editor, the first row in the DB2 table to match the parameters (if any) and primary key values is deleted.
If you update an occurrence, all rows that match the parameters (if any) and the primary key values in the DB2 table are updated.
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.
Using Rules
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.
Transaction Processing
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.
 
Ensure your system administrator is aware of the number of Gateway threads required to accommodate all transaction streams accessing DB2 data in a single transaction.
Using TRANSFERCALL or DISPLAY & TRANSFERCALL statements in a rule minimizes Gateway threads and reduces the possibility of DB2 locking contention.
Transaction Limitations
The number of TIBCO Object Service Broker DB2 tables you can access per transaction depends on the POOLSIZE gateway parameter. For details, see Gateway Parameters.
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.
Rules Behavior
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.
Retrieval Processing
A single SQL cursor is used to retrieve data from DB2 for each retrieval statement (GET or FORALL) in your rule.
If your DB2 subsystem is version 2.2 or later, and you are using Static SQL to access DB2 data from TIBCO Object Service Broker, FOR FETCH ONLY is included in each SELECT cursor.
When a TIBCO Object Service Broker transaction runs in browse mode, locks are not taken on the TIBCO Object Service Broker data; however, locks are taken on the DB2 data in accordance with how the Gateway plan is bound and how the DB2 table was created.
GET Statement
A GET statement retrieves the first occurrence in the TIBCO Object Service Broker DB2 table that satisfies the specified selection criteria.
If you are using Static SQL to access DB2 data from TIBCO Object Service Broker, OPTIMIZE FOR 1 ROW is included in each SELECT cursor associated with a GET statement.
A GET...ORDERED statement must retrieve all DB2 data that satisfies the selection criteria and SORT it in the Execution Environment before returning the first occurrence that meets the selection criteria. OPTIMIZE FOR 1 ROW is not included in the SELECT cursor associated with a GET...ORDERED statement.
If your table definition specified DB2 Ordering (Orders=Y), a GET …ORDERED returns only the occurrence that meets the selection criteria from the sorted output, instead of returning all the occurrences to TIBCO Object Service Broker for sorting and selection.
FORALL Statement
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.
If your table definition specified DB2 Ordering (Orders=Y), a FORALL …ORDERED means that DB2 can use its own sorting mechanisms to maximize efficiencies of the DB2 database and indexes. Only the rows that satisfy the Ordered request are returned to TIBCO Object Service Broker instead of all the data.
If you are using Static SQL, the gateway converts Unicode data to EBCDIC from host variables, even for fields defined as syntax UN. If the Unicode data cannot be converted, the FORALL statement fails with a conversion error.
Replace (Update) Processing
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 are using Static SQL, the gateway converts Unicode data to EBCDIC to populate host variables, even for fields defined as syntax UN. If the Unicode data cannot be converted, the REPLACE statement fails with a conversion error.
Insert Processing
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 are using Static SQL, the gateway converts Unicode data to EBCDIC to populate host variables, even for fields defined as syntax UN. If the Unicode data cannot be converted, the INSERT statement fails with a conversion error.
Delete Processing
If you delete an occurrence using the Table Editor, the first row in the DB2 table or view to match the parameters (if any) and the primary key values is deleted.
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.
To clear all the data in a DB2 table, use the $CLRTAB shareable tool.
See Also
TIBCO Object Service Broker Shareable Tools for information on the $CLRTAB tool.
Use of LIKE
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.
DB2 LIKE Statement Format
To use the DB2 LIKE statement format (not to be confused with the TIBCO Object Service Broker LIKE) you must apply the following two settings:
For dynamic SQL, to make the DB2 Gateway formulate DB2 LIKE clauses you must specify USEDB2LIKE in the DB2 gateway start up parameters. Without this specification, the Gateway parses the rows returned.
When generating Static SQL using the @STATICSQL tool, to include LIKE statements in the Static handler, you must sign on to the Execution Environment using USEDB2LIKE(Y). LIKE statements are generated in the static SQL handler. Without this specification, LIKE statements are not included in the static handlers.
WHERE Clause Processing and NULLS
The Gateway translates the following on WHERE clauses referring to NULL fields:
All other operands are discarded.
WHERE clauses containing the operand >= NULL are disregarded because they imply selection on the entire table.
WHERE Clause Processing and Parameterized Tables
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.
The following examples show the differences in the generated SQL:
FORALL TABLE WHERE PARAM=’value’ AND FIELD1=’value’
generates the SQL statement:
SELECT TABLE WHERE PARAMFIELD=’value’
Whereas the following, which is the recommended syntax to use:
FORALL TABLE(‘value’)WHERE FIELD1=’value’
generates the SQL statement:
SELECT TABLE WHERE PARAMFIELD=’value’ AND FIELD=’value’
Invoking DB2 Stored Procedures
This section describes how to invoke DB2 stored procedures.
Overview
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:
Parameter kind (P – IN value, O – OUT value, B – INOUT value).
The Commit on Return attribute indicating if DB2 will COMMIT prior to returning control upon execution of the stored procedure.
The TIBCO Object Service Broker implementation of DB2 stored procedures support allows the following:
The TIBCO Object Service Broker table definer to define a TIBCO Object Service Broker table of type DB2, subtype P mapping the signature of a DB2 stored procedure (uniquely identifying the stored procedure and its parameters to be used for invoking it via DB2).
The TIBCO Object Service Broker table definer to define TIBCO Object Service Broker tables of type DB2, subtype R mapping the result sets produced by a DB2 stored procedure.
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:
#Parms (informational) – number of procedure parameters as stored in DB2's table SYSROUTINES; includes the IN, OUT and INOUT parameters.
#ResultSets (informational) – number of result sets to be returned by the stored procedure as stored in DB2's table SYSROUTINES.
The stored procedure's parameters described in DB2's SYSPARMS table; they are treated similarly to DB2 columns in the case when the definer is used to map regular DB2 tables. The definer enforces fields @HANDLE@ and #RS# as the first two fields of the definition and marks them with the K line command (as primary keys).
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).
Language Provisions
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:
CALL <proc> WHERE P1=<p1> & P2=<p2> & ... & PN = <pn>
 
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.
If the keyword notation is used and NULL is to be passed as the value for a parameter, say PI, you can omit the respective term (& PI = NULL).
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:
@HANDLE@ is an internally-assigned value, available upon return from the procedure, that uniquely identifies the procedure invocation context created for this particular call. This context is kept for subsequent result set fetches until explicitly discarded or implicitly cleaned up at transaction end.
#RS# is the actual number of result sets created by the stored procedure during this particular call. If #RS# is set to 0 by the CALL statement, the procedure call context has already been discarded automatically.
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.
Example
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.
Mapping of PROC:

 
COMMAND==> TABLE DEFINITION
Table: PROC Type: DB2 Unit: VZO10
Dictionary : X (X/I) ServerID: DEFAULT ServerType: DB2 Orders: N ImplUpd: N
Subtype : P (N/P/R) #Parms: 5 #ResultSets: 1 CommitOnReturn: N
Schema : DEVL7083
Procedure : EMPRSETC
DB2 Location:
 
Location Parm Default Src Sourcename ' Event Rule Typ Ac
---------------- ---------------- - ------------- ' ---------------- - -
_ LOCATION ' _
' _
 
| ----------- DB2 Column ---------------------------|-------- OSB Field ------
Name Datatype Len Scale |Num Name Typ Sy
------------------------------ -------- ----- -Def|--- ---------------- - --
K @HANDLE@ SMALLINT 2 0 1 @HANDLE@ B
K #RS# SMALLINT 2 0 2 #RS# B
S PDEPTNO CHAR 3 0 P 3 PDEPTNO V
S PDEPTNAME VARCHAR 36 0 O 4 PDEPTNAME V
S PSQLCODE INTEGER 4 0 O 5 PSQLCODE B
S PSQLSTATE CHAR 5 0 O 6 PSQLSTATE V
S PSQLERRMC VARCHAR 250 0 O 7 PSQLERRMC V
 
(S=Select P=Parameter K=Key)
PFKEYS: 4=DB2 TBLS 3=END 12=CANCEL 2=DOC 22=DELETE 5=RS_INFO 9=RS_REFRESH

 
 
The mapping for the result set, generated under the name PROCC1, is shown below.
Mapping of PROCC1:

 
COMMAND==> TABLE DEFINITION
Table: PROCC1 Type: DB2 Unit: DB2_SP
Dictionary : X (X/I) ServerID: DEFAULT ServerType: DB2 Orders: N ImplUpd: N
Subtype : R (N/P/R)
Schema : DEVL7083
Procedure : EMPRSETC
DB2 Location: CursorName: C1
 
Location Parm Default Src Sourcename ' Event Rule Typ Acc
---------------- ---------------- - ------------- ' ---------------- - -
_ HHH ' _
' _
 
| ----------- DB2 Column ---------------------------|-------- OSB Field -------
Name Datatype Len Scale |Num Name Typ Syn
------------------------------ -------- ----- -Def|--- ---------------- - --
P @HANDLE@ SMALLINT 2 0 1 @HANDLE@ B
S EMPNO CHAR 6 0 1 EMPNO V
S FIRSTNME VARCHAR 12 0 2 FIRSTNME V
S MIDINIT CHAR 1 0 3 MIDINIT V
S LASTNAME VARCHAR 15 0 4 LASTNAME V
S HIREDATE DATE 10 0 5 HIREDATE D B
S SALARY DECIMAL 9 2 6 SALARY P
 
 
(S=Select P=Parameter K=Key)
PFKEYS: 4=DB2 TBLS 3=END 12=CANCEL 2=DOC 22=DELETE 5=RS_INFO 9=RS_REFRESH

 
 
A rule that executes this procedure and fetches its result set could be similar to the following:

 
_ LOCAL T;
_ -----------------------------------------------------
_ -------------------------------------------------+---
_ CALL PROC('D11'); ¦ 1
_ FORALL PROCC1(PROC.@HANDLE@) :                   ¦ 2
_ T = ''; ¦
_ FORALL $$SYSFIELDS('PROCC1') : ¦
_ T = T || PROCC1.($$SYSFIELDS.NAME) || ' '; ¦
_ END; ¦
_ CALL MSGLOG(T); ¦
_ END; ¦
_ -------------------------------------------------¦

 
Note that after the CALL statement, the following values have been assigned:
 
PROC.@HANDLE@ = <non-NULL>
PROC.#RS#      = 1

PROC.PDEPTNAME = MANUFACTURING SYSTEMS
PROC.PSQLCODE = 0
PROC.PSQLSTATE = 00000
PROC.PSQLERRMC = <NULL>
 
Querying the Layout of Result Sets
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:
 
FORALL @SYSRSCOLS(PROC.@HANDLE@,n),
 
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.
Discarding Stored Procedure Context
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:
 
CALL @DB2PROCDISCARD(PROC.@HANDLE@).
 

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