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


Chapter 4 Managing DB2 Data Definitions : Task E  Change the Defaults if Necessary

Task E  Change the Defaults if Necessary
You can modify any of the attributes in the Metadata Field section of the screen. These attributes describe the fields of your TIBCO Object Service Broker DB2 table. Each time you press Enter the screen is validated.
You can change the values of the following attributes:
Default Order in Which the Fields Appear
This attribute is modified by typing alternate numbers in the Num field. The following applies by default:
The primary key must be the first field. The DB2 columns that you choose for the primary key always appear first in the table, regardless of the numbers you type next to them in the Num field.
If all selected fields are numbered and you choose another field and do not assign it a number, it is placed last in the order.
Default Order of Parameters
This attribute is modified by typing numbers in the Num field. The following applies by default:
If all parameters are numbered and you choose another parameter without assigning it a number, it is placed last in the order.
Field Name
Type over the name in the Name field with a new name to uniquely identify the field within the TIBCO Object Service Broker DB2 table. You can use the same name as a field in another table. If you are moving data between this table and another table, giving fields the same names simplifies the process; however, we recommend that you use the same field name as the DB2 column name.
Valid entries: a character string (unique to the TIBCO Object Service Broker DB2 table definition) of up to 16 characters beginning with a letter (A - Z) or a special character (@, $, or #), and continuing with more letters, special characters, digits (0 - 9), or underscore characters (_).
TIBCO Object Service Broker Semantic Type and Syntax
Change the TIBCO Object Service Broker semantic type (Typ field) and syntax (Syn field) of the field. You can specify any valid semantic type and syntax combination that is supported for each DB2 data type. Valid combinations of type and syntax are described in TIBCO Object Service Broker Programming in Rules, and the TIBCO Object Service Broker syntax supported for each DB2 data type is listed in the following table.
Changing the default field syntax of a DB2 column can cause conversion overhead since the Gateway must convert each affected field of each row to the new syntax as defined in the TIBCO Object Service Broker DB2 table definition.
If your DB2 Application Encoding Scheme is EBCDIC, define the DB2 character fields as TIBCO Object Service Broker syntax V.
If your DB2 Application Encoding Scheme is Unicode, define the DB2 character fields as TIBCO Object Service Broker syntax UN. Conversion is performed from the single byte character set ID specified in your DB2 setup (the default being CCSID 1208, UTF‑8) to UTF-16 in TIBCO Object Service Broker.
Define as TIBCO Object Service Broker syntax RD the DB2 fields that have “FOR BIT DATA” set.
Data Conversions Supported
Only specific conversions from DB2 data types to TIBCO Object Service Broker syntax are supported by the Gateway as shown in this table.
TIBCO Object Service Broker                  Syntax -->

DB2 Data Type
Fixed-Length
Character String
Packed
Decimal
Variable-Length
Character String
Raw
Data
LONG VARGRAPHIC
Additional Considerations
If DB2 data contains lowercase characters, define the corresponding TIBCO Object Service Broker field with case-sensitive syntax V.
If a selected DB2 column does not support nulls, it is a required field. For DB2 character fields defined with TIBCO Object Service Broker syntax C, trailing blanks become nulls. Therefore, define the TIBCO Object Service Broker syntax as V to send a field containing only blanks to the Gateway.
Field Length
Change the length in the Len field and, if applicable, the number of digits to the right of the decimal in the Dec field. The data is padded or truncated as necessary.
Occurrence Order
Specify A (ascending) or D (descending) in the Ord field to determine the order of the occurrences.
Server Orders
If Server Orders=Y, DB2 passes data to TIBCO Object Service Broker already ordered and TIBCO Object Service Broker does not re-order the data.
If Server Orders=N, the following occurs:
For a qualified GET or FORALL, all DB2 data that matches the selection criteria must be retrieved and sorted by the Execution Environment.
For an unqualified GET or FORALL, all DB2 data must be retrieved and sorted by the Execution Environment before data is passed back to a rule or a TIBCO Object Service Broker tool.
Sample External Table Definition to Access DB2 Data
After modifying the default TIBCO Object Service Broker field attributes, a screen similar to the following displays:

 
COMMAND==> TABLE DEFINITION
Table : CUSTOMER Type: DB2 Unit: RFS
Dictionary : X (X/I) ServerID: DEFAULT ServerType: DB2 Orders: Y ImplUpd: N
Subtype : N (N/P/R)
Creator : DSN8810
Table : CUST
DB2 Location:
 
Location Parm Default Src Sourcename ' Event Rule Typ Acc
---------------- -------------- --- ------------ ' ---------------- - -
_ LOCATION ' _
' _
 
| ----------- DB2 Column ----------|-------- OSB Field ---------------------------
Name Datatype Len Scale |Num Name Typ Syn Len Dec Req Default
-------------- -------- ----- -Def|--- --------- - -- ----- --Ord- -------------
K CUST_ID CHAR 5 0 N 1 CUST_ID V 5
S NAME CHAR 30 0 Y 2 NAME V 30 A
S ADDR_1 CHAR 30 0 Y 3 ADDR_1 V 30
S ADDR_2 CHAR 30 0 Y 4 ADDR_2 V 30
S CITY CHAR 15 0 Y 5 CITY V 15
S STATE CHAR 2 0 Y 6 STATE V 2
S OPEN_$ DECIMAL 9 2 Y 7 OPEN_$ P 4 2
S PHONE CHAR 10 0 Y 8 PHONE V 10
(S=Select P=Parameter K=Key)
PFKEYS: 4=DB2 TBLS 3=END 12=CANCEL 2=DOC 22=DELETE 5=RS_INFO 9=RS_REFRESH

 
If you are satisfied with your DB2 table definition, press PF3 to save the definition. If you do not want to save your changes, press PF12 to cancel them.
DB2 Stored Procedure Result Set Extract
Press PF9 = RS_REFRESH to generate TIBCO Object Service Broker table definitions mapping the result sets, if any, produced by a stored procedure. The following screen is displayed:

 
Result Set Mapping Construction for Procedure mapped by P
 
Enter valid input to run DB2 Stored Procedure EMPRSETC
Warning: This process executes the Stored Procedure.
Be aware that your Stored Procedure may update data.
 
 
Argument Name Value
PDEPTNAME
PSQLCODE
PSQLSTATE
PSQLERRMC
PFKEYS: ENTER=EXTRACT 3=SAVE & RETURN 12=CANCEL & RETURN

 
 
To describe the result sets of a stored procedure, the stored procedure must be executed. If the stored procedure has input values, you must provide a set of input values that make the procedure return the result set(s). Note that by executing the stored procedure, you may be updating data.
If prompted for input values, specify them and press Enter. You will then be prompted to confirm the execution of the stored procedure for the specified set of input values. Upon confirmation (PF22), the procedure is executed and you will see displayed the values it has returned in its output and input/output parameters. In addition, a screen message will be displayed reporting the number of result sets built.
This process may be repeated any number of times. The table definer stores in a temporary repository the descriptions of the result sets built by this process. You may press PF12 = CANCEL to clean up this repository and return to the Table Define Screen, or PF3 = SAVE & EXIT to generate the persistent OSB table definitions mapping those result sets.
Result Set Table Naming Convention
Under DB2 conventions, any result set returned by a stored procedure has a unique (within the scope of the stored procedure) name (up to 30 bytes long) assigned by the stored procedure. The TIBCO Object Service Broker table definer uses these names and the TIBCO Object Service Broker name of the procedure mapping being defined when generating result set mappings.
Result set tables are named using the following considerations:
If the concatenation of the TIBCO Object Service Broker name and the result set name does not exceed 16 characters, it is used to name the result set mapping.
For example, if the TIBCO Object Service Broker table PROC maps the DB2 stored procedure EMPRSET that returns two result sets named CSR1 and CSR2, the names of TIBCO Object Service Broker tables generated by the definer will be PROCCSR1 and PROCCSR2.
Stored Procedure Definition
In the Subtype field, specify the value P. Enter a schema and procedure name, or use the PF4 key to see a list of stored procedures.
The columns displayed are the parameters of the stored procedure. Two additional TIBCO Object Service Broker fields, @HANDLE@ and #RS#, are added to the definition. All fields are preselected; do not deselect any fields, as this will cause the call to the procedure to fail.
If the stored procedure has result sets available to the calling program, use the SP_INFO and SP_REFRESH PF keys to list and to automatically create table definitions for them.

 
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 Acc
---------------- ---------------- - ------------- ' ---------------- - -
_ LOCATION ' _
' _
 
| ----------- DB2 Column ---------------------------|-------- OSB Field -------
Name Datatype Len Scale |Num Name Typ Syn
------------------------------ -------- ----- -Def|--- ---------------- - --
K @HANDLE@ SMALLINT 2 0 1 @HANDLE@ B
K #RS# SMALLINT 2 0 2 #RS# B
K 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

 
 

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