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


Chapter 4 Managing TIBCO Object Service Broker Data Definitions : Defining an SLK Table

Defining an SLK Table
 
Task A Extract the external DBMS table definition
Overview
You can create an SLK table definition using one of two methods:
The Gateway method assumes that you are reading metadata from the external DBMS online. The extraction method can be used to retrieve pre-loaded metadata from a shadow dictionary in TIBCO Object Service Broker.
Using the Gateway Method
To use the Gateway method to define an SLK table, you must have a Gateway running. Specifying X (external) in the Dictionary field in the Table Definer indicates that the external DBMS (denoted by the data source or Oracle SID field) must be accessed by the Gateway (denoted by the Serverid field) to obtain table definition information.
Even if you run your Table Definer on z/OS, the respective Gateway must run on a supported system and attach to your z/OS Data Object Broker.
Using the Extraction Method
The extraction method can be used to pre-load SLK table definition information from external dictionaries and store it in TIBCO Object Service Broker TDS tables. You can then define SLK tables without having a Gateway running.
Specifying I (internal) in the Dictionary field means that you are able to view and map only to definitions contained in the shadow dictionary stored in TIBCO Object Service Broker.
If you use the extraction method, the data definitions are static; therefore, whenever changes are made to new or existing table definitions in the external DBMS, use the @SLKEXTRACT rule to load more up to date information. Refer to Task B, Extract SLK dictionary data for the steps required to reload the table definitions.
Task B Invoke the Table Definer
Overview
Invoke the Table Definer from the workbench using the DT define table option or the DT command. You can modify an existing table definition or define a new SLK table.
Accessing Existing Tables
To display the definition of an existing SLK table from the workbench, do one of the following:
DT SLKDEMO<Enter>
Move the cursor to the DT define table option and press Enter. This displays the Object Manager screen, listing existing tables in your TIBCO Object Service Broker database. Scroll through this list to see which table you require. Use the SELECT command to filter the list to show only tables of type SLK:
SELECT TYPE=’SLK’
To select a table, type S in the line command field and press Enter.
To define a new table:
1.
Type the name of a new SLK table beside the DT define table option or type DT and the table name in the command field and press Enter.
This displays a TDS table definition template.
2.
A default table definition screen appears, similar to the one shown below. If your screen display is set to 80 columns rather than 120, press PF11 to view the right-hand portion of the screen.
Default Table Definition Screen (left portion)

 
COMMAND==> TABLE DEFINITION
Table: ODBCTBL Type: SLK Unit: HURON
DBSource: ServerType: SLK
ServerId: Proc/ProcResSet: N (P/R/N)
TableName: ServerOrders: Y (Y/N)
Qualifier: ImpliedUpdates: N (Y/N)
Owner: Dictionary: X (X/I)
 
Location Parm Default Src Sourcename Event Rule Typ Acc
---------------- ---------------- - ---------------- ---------------- - -
_ LOCATION _
|--- External Field --------------|---- Metadata Field --------------------
ExternalName Vrt Name Typ Syn Len Dec Ord Req
------------------------------ - ---------------- - -- ----- -- - -
_
_
_
_
_
(P - parameter, K - key, S - select, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=TABLES 5=COLUMNS 6=QUALIFIERS 9=OWNERS

 
Default Table Definition Screen (right portion)
The following figure shows the right portion of the table definition screen, containing the additional SQLDataType and Default fields.

 
COMMAND==> TABLE DEFINITION
Table: ODBCTBL Type: SLK Unit: HURON
DBSource: ServerType: SLK
ServerId: Proc/ProcResSet: N (P/R/N)
TableName: ServerOrders: Y (Y/N)
Qualifier: ImpliedUpdates: N (Y/N)
Owner: Dictionary: X (X/I)
 
Location Parm Default Src Sourcename Event Rule Typ Acc
---------------- ---------------- - ---------------- ---------------- - -
_ LOCATION _
|--- External Field -------------- ----------------------------------------
ExternalName Vrt Default SQLDataType
------------------------------ - ---------------- ---------------
_
_
_
_
_
(P - parameter, K - key, S - select, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=TABLES 5=COLUMNS 6=QUALIFIERS 9=OWNERS
Right edge of Window

 
Table Definition Screen Fields
Displays the table name specified when you invoked the Table Definer. You can type in a new name to save the definition of the current table under a different name.
A character string 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 (_). A table name starting with an at (@) symbol denotes a table supplied by TIBCO Object Service Broker.
Displays the user unit associated with the table. You must use the Core screen to modify this field. Refer to Task F, Add control information for more information on the Core screen.
See Also
TIBCO Object Service Broker Managing Data for more information on using the Table Definer.
Task C Specify the server parameters
Table Definition Screen Fields
These fields appear in the upper portion of the default table definition screen:
If SERVERTYPE is SLK, enter the name of the data source you want the Gateway to use to access the external DBMS. The ODBC data source is configured using the ODBC Administrator.
If SERVERTYPE is ORS, enter the Oracle SID you want the Gateway to use to access the Oracle DBMS. Oracle SID is the symbolic name used by Oracle products to identify an Oracle database.
Note  This field is renamed to Oracle SID if you set the Serverid field to ORS.
You must specify the SERVERID of a Gateway to be associated with the table that you are defining. You can specify the SERVERID of a Gateway that is running (available) or you can specify the SERVERID of a Gateway that you plan to run during the data access session. A valid entry is an alphanumeric value up to eight characters long.
You can view or use metadata information (qualifiers, owners, tables, and table columns) from the actual DBMS only if you specify the SERVERID of a running Gateway. If the SERVERID you specify does not denote a running Gateway, you can still define your SLK table manually.
It is also possible to use extracted metadata to define an SLK table. Refer to the dictionary field (below) for information on specifying the dictionary parameter.
TableName
or
Procedure
Note  This field is renamed to Procedure if you set the Proc/ProcResSet field to either P or R.
Enter the value of the qualifier (or catalog) that qualifies the table to which you are mapping. The qualifier can be entered directly into the field or selected from a list pressing PF6.
Enter the value of the owner (or schema) that qualifies the table to which you are mapping. The owner can be entered directly or selected from a list pressing PF9.
Specifies that the definition being created/edited maps a stored procedure in the external DBMS. The name of the procedure in the external DBMS is required; specify the name in the Procedure field. Optionally, the stored procedure can create result sets (cursors) and/or result counts (numbers of rows affected by INSERT/UPDATE/DELETE requests).
When you set the Proc/ProcResSet field to P, two auxiliary fields, @HANDLE@ and @RESULT@, are generated by the SLK table definer. Do not modify these field definitions. Refer to Adding Fields to a Stored Procedure Mapping for additional field definition requirements.
Specifies that the definition maps a stored procedure result set. You can define result set mappings that are usable for more than one stored procedure, because the name of the procedure is not required. The result set maps a pending cursor, that is, a temporary table that was built and can be fetched.
When you set the Proc/ProcResSet field to R, an auxiliary data parameter, @HANDLE@, is generated by the SLK table definer. Do not modify this parameter definition. Refer to Adding Fields to a Stored Procedure Mapping for additional field definition requirements.
Specify Y if you want ordering performed by the server. If ordering must take place on the client side, specify N. Ordering is performed whenever an order clause is specified in a TIBCO Object Service Broker GET or FORALL request.
In NOBROWSE transactions, a request including a GET or FORALL on the table is treated as an update request, so Fail Safe processing can be triggered if required at synchronization time. This is designed to be used primarily with stored procedures, and applies to regular tables as well.
Specifies an internal dictionary. Metadata is read from the TDS tables populated by @SLKEXTRACT beforehand (parameterized by the data sources or Oracle SID).
Task D Select an external DBMS table
Overview
You can select an external DBMS table as the base for your SLK table. To select a table, type a valid external table name, and optionally a valid qualifier and a valid owner.
Using PF Keys to Generate a List of Tables
If you do not know the table name, you can generate a list of tables using a valid PF key:
With the exception of PF4 (see Building a Table or Stored Procedure List below), the procedure is the same as Step 3: Extract the Desired Information.
See Also
TIBCO Object Service Broker Getting Started for more information about wildcards and standard primary commands.
Building a Table or Stored Procedure List
Pressing PF4 in combination with the specified ODBC data source or Oracle SID produces an additional screen, shown in the following example, where you can specify a filter for the list of tables or stored procedures to be produced. Depending on whether your Proc/ProcResSet is set to R, P or N, a list of tables or stored procedures is produced. The example shows the external TableName, Qualifier, and Owner fields, which you could have already specified.
If the fields are left empty, all tables or procedures in the specified external database appear. To narrow the list of tables to be produced, specify whether to include the following table types (Y/N): Table, System Table, View, Synonym, or Alias.
Specifying Filters for Table Lists

 
DBSource: ORACLE
ServerType: SLK Table: ODBCTBL Proc/ProcResSet: N
ServerId: SLK2 Location: Dictionary: X
Specify the Search Patterns for Table Retrieval in the target Database
TableName EM* | Wildcards accepted, empty =*
Qualifier | No wildcards accepted
Owner Scott | Wildcards accepted, empty = *
|-----------------------------
| Both Qualifier and Owner
| do preserve the case
Table Types (Y/N):
TABLE Y
VIEW Y
SYSTEM TABLE N
ALIAS N
SYNONYM N
<All existing types> N
 
PFKEYS: 12=EXIT Enter=SHOW TABLES

 
In this example, pressing Enter builds a list of tables satisfying the following criteria:
tablename LIKE ‘EM%’ & qualifier LIKE ‘%’ & owner ‘SCOTT’ & (type = ‘TABLE’ or TYPE = ‘VIEW’)
Sample List of Tables Returned

 
DBSource: ORACLE
ServerType: SLK Table: ODBCTBL Proc/ProcResSet: N
ServerId: SLK2 Location: Dictionary: X
Tables in the target Database
TableName, Qualifier, Owner TableType
----------------------------------------------------------------- ----------------
- EM,, SCOTT TABLE
- EM,, SCOTT TABLE
- EMP_SNAPSHOT,, SCOTT TABLE
<S - select E - expand>
PFKEYS: 12=EXIT ENTER=EXPAND 3=SELECT AND MERGE WITH THE TABLE BEING DEFINED

 
Selecting the Table or Stored Procedure
After generating a table or stored-procedure list, you can:
Type E beside the desired table or stored procedure and press Enter.
All the column names for the selected external DBMS table or procedure are listed, as shown in the example below.
Type S beside the table or stored procedure of your choice and press PF3.
The fields for this table are merged with the fields of the SLK table being defined. When the table fields are merged, each external field either replaces the one with the same external name or is added to the list if there is no matching field.
This action is identical to selecting the table columns when PF5 is used at the first table definition screen. This can be performed any number of times and the resulting list can contain fields of more than one table.
Column Names for Sample External DBMS Table
In the ColumnName field as shown in the following screen, column descriptions are produced for tables or views, procedure parameter descriptions are produced for stored procedures, and result set descriptions are produced for stored procedure result sets.

 
DBSource: ORACLE
ServerType: SLK Table: ODBCTBL Proc/ProcResSet: N
ServerId: SLK2 Location: Dictionary: X
Tablename: CUSTOMER
Qualifier:
Owner: SCOTT
 
Table Column Descriptions in the target Database
ColumnName Date Native Name Precision Length Scale
------------------------------ type------------------ ---------- ----------- ---
ID 3 NUMBER 15 8
FIRST NAME 12 VARCHAR2 30 30
LASTNAME 12 VARCHAR2 30 30
USERNAME 12 VARCHAR2 15 15
PASSWORD 12 VARCHAR2 15 15
ADDRESS 0 T_ADDRESS 1 1
STATUS 12 VARCHAR2 8 8
EMAIL 12 VARCHAR2 40 40
Data type is the ODBC-mapped SQL data type code <0 means “unknown type”>

 
Valid PF Keys
PF Keys
Primary Command
Validates the definition information specified in the Table Definition screen. If the definition is valid, you are returned to the workbench.
Displays the screen used to specify the search values used to create a list of tables or stored procedures in the external DBMS. Press Enter to display the list.
Procedure parameters, in a stored procedure mapping where Proc/ProcResSet=P
Result set columns, in a stored procedure mapping where Proc/ProcResSet=R
Displays a list of qualifiers for the specified data source. You can select one qualifier to be copied onto your definition screen.
Displays a list of owners for the specified data source. You can select one owner to be copied onto your definition screen.
Task E Define fields for the SLK table
Overview
A complete table definition or stored procedure mapping includes both the TIBCO Object Service Broker portion and the external DBMS portion, since the TIBCO Object Service Broker field definition is extended by the external DBMS column definition.
When you are finished defining the table or stored procedure, you can press PF3 to save the definition or PF12 to cancel the definition.
Table Definition Screen Fields for a Sample Table

 
COMMAND==> TABLE DEFINITION
Table: ODBCTBL Type: SLK Unit: HURON
DBSource: ORACLE ServerType: SLK
ServerID: SLK2 Proc/ProcResSet: N (P/R/N)
TableName: CUSTOMER ServerOrders: Y (Y/N)
Qualifier: ImpliedUpdates: N (Y/N)
Owner: SCOTT Dictionary: X (X/I)
Location Parm Default Src Sourcename Event Rule Typ Acc
---------------- ---------------- - ---------------- ---------------- - -
_ LOCATION _
|--- External Field --------------|---- Metadata Field --------------------
ExternalName Vrt Name Typ Syn Len Dec Ord Req
------------------------------ - ---------------- - -- ----- -- - -
S _ ENAME ENAME S V 10 0
S _ JOB JOB S V 9 0
S _ MGR MGR Q B 2 0
S _ HIREDATE HIREDATE D B 4 0
S _ DEPTNO DEPTNO Q B 2 0
S _ ID ID Q P 8 0
S _ FIRSTNAME FIRSTNAME S V 30 0
S _ LASTNAME LASTNAME S V 30 0
S _ ADDRESS.STREET _ ADDRESS S V 30 0
(P - parameter, K - key, S - select, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=TABLES 5=COLUMNS 6=QUALIFIERS 9=OWNERS

 
Table Definition Screen Fields for a Sample Stored Procedure Mapping

 
COMMAND==> TABLE DEFINITION
Table: SQLL Type: SLK Unit: DOC
DBSource: sqls ServerType: SLK
ServerId: SLK2 Proc/ProcResSet: P (P/R/N)
Procedure: sstub;1 ServerOrders: Y (Y/N)
Qualifier: pubs ImpliedUpdates: N (Y/N)
Owner: dbo Dictionary: X (X/I)
Location Parm Default Src Sourcename Event Rule Typ Acc
---------------- ---------------- - ---------------- ---------------- - -
_ LOCATION _
|--- External Field -----------------|--- Metadata Field -----------------
ExternalName Vrt Name Typ Syn Len Dec Ord Req
------------------------------ - ---------------- - -- ----- -- - -
K _ @HANDLE@ @HANDLE@ Q B 2 0
K _ @RESULT@ @RESULT@ Q B 2 0
S _ RETURN_VALUE R RETURN_VALUE Q P 6 0
S _ @p1 I @P1 Q B 2 0
S _ @p2 B @P2 Q B 2 0
(S - select, K - key, I - insert, R - replicate, D - delete)
PFKEYS: 3=SAVE 22=DEL 13=PRINT 2=DOC 4=PROCS 5=COLUMNS 6=QUALIFIERS 9=OWNERS

 
When you set the Proc/ProcResSet field to P (procedure), two auxiliary fields, @HANDLE@ and @RESULT@, are generated. When you set the Proc/ProcResSet field to R (result set), an auxiliary parameter, @HANDLE@, is generated by the Table Definer. Do not modify these field definitions.
Selecting Fields
To select fields, type P if the field is to be a data parameter, type K if the field is to be a primary key, and S beside each field you want to select. By default, TIBCO Object Service Broker includes the field in the definition if the selection is left blank. If you want to exclude a field, type D beside the field.
To minimize processing overhead, select TIBCO Object Service Broker primary key fields that are the same as the primary key fields in the external DBMS. The number of fields you can select depends on the sum of all field lengths plus some control information. This sum must be less than or equal to 3915 bytes. For a detailed explanation of the formula used to calculate the total number of bytes of all fields, refer to TIBCO Object Service Broker Programming in Rules.
TIBCO Object Service Broker holds up to 4 data parameters and up to 16 fields in a composite primary key; however, you can use the Table Editor and Table Browser only on tables with eight or less fields in a composite primary key.
Adding Fields to a Table Definition
You can add fields to your table definition using one of the valid PF keys listed, following the same procedures outlined in Task C, Specify the server parameters and Task D, Select an external DBMS table.
To generate a list of columns from a table to which you can map your definition, press PF5. Before the list is generated an additional screen appears, where you can select a table by specifying a Tablename, Qualifier, or Owner.
You can also manually enter fields from the Table Definition screen. If an invalid value is entered, a message is returned.
Sample Screen to Extract Columns Based on Tablename and Owner

 
DBSource: ORACLE
ServerType: SLK Table: ODBCTBL Proc/ProcResSet: N
ServerID: SLK2 Location: Dictionary: X
Specify Table, Qualifier, Owner for Column Retrieval in the target Database
Tablename CUSTOMER | A non-empty value, no wildcards
Qualifier | No wildcards accepted
Owner SCOTT | No wildcards accepted
|--------------------------------
| Both Qualifier and Owner
| do preserve the case
 
 
 
 
 
 
 
 
 
 
PFKEYS: 12=EXIT Enter=SHOW COLUMNS 3=MERGE WITH THE TABLE BEING DEFINED

 
Adding Fields to a Stored Procedure Mapping
Your field definition requirements are determined by whether your stored procedure mapping is for a stored procedure, Proc/ProcResSet=P, or a result set, Proc/ProcResSet=R.
Definitions for a Stored Procedure Mapping
The @HANDLE@ and @RESULT@ columns are generated and marked as primary keys. Your subsequent field definitions should map to the parameters of your stored procedure. All the procedure parameters must be mapped. The following types of parameters can be defined:
A value is passed to the procedure at invocation time or a default value is used. No value is returned by the procedure.
input/output
A value is passed to the procedure at invocation time or a default value is used. A value is returned by the procedure.
Use the VRT field to define the behavior of the parameters. Valid values are:
A return value (one per table definition). This field must follow the @RESULT@ field; if it does not, the definer moves the field to the third position in the definition.
Definitions for a Result Set Mapping
The @HANDLE@ column is generated and marked as a data parameter. Your subsequent field definitions should map the fields of a procedure result set. The VRT field for all those fields is automatically set to S.
External Column Attributes
Note  This name appears as specified and, possibly, in quotes (see the VRT attributes) in the SQL statement generated by the Gateway.
Any string potentially acceptable at runtime can be entered. The Table Definition Screen Fields for a Sample Table section illustrates that a user-type data member can be accessed in Oracle (via both ODBC and Oracle Gateways).
Data Type /Name
This value is returned by the Gateway when you merge fields. This value cannot be changed. The name refers to one of the unified data type names used to denote the variety of type names employed by DBMSs.
Note  The screen produced using one of the valid PF keys displays the true data type names returned by the actual DBMS, while the Table Definition screen displays the unified names. If the field in the external DBMS has configurable length (for example, CHAR) and scale, the length and scale are presented in parentheses after the data type name.
Denotes that this field is virtual as far as the external DBMS is concerned. The Gateway never attempts to modify this field; however, it includes this field in the WHERE clause when it is appropriate. This helps to ensure better selectiveness when rows are updated or deleted. For example, if your external table does not have a primary key, or its primary key is not precisely mapped by your SLK definition (TIBCO Object Service Broker does not require or check this), you can use a virtual field to compensate for possible ambiguities, for example, ORACLE: rowid; Sybase: identity.
Denotes that the name of this field is to be enquoted whenever included in a SQL statement. This is necessary when field names contain special symbols.
Denotes that this field is not to be included in INSERT column lists. You can then have Oracle object members as fields (for example, ADDRESS.STREET).
If you use the merging facility, the SLK Gateway always places the values it suggests that you use in the SYN fields. You can change the suggestions, keeping in mind that not all data types can be coerced into each other.
Metadata Fields
The TIBCO Object Service Broker name for the column. This name must be unique within the SLK table. You can give a field the same name as a field in another table. If you are moving data between two tables, giving the fields the same name simplifies the process.
Valid values include a character string 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 (_). A table name starting with an at (@) symbol denotes a TIBCO Object Service Broker supplied table.
Specifies the TIBCO Object Service Broker semantic data type of the column. You can use any valid TIBCO Object Service Broker semantic type, provided that the semantic type and syntax combination is valid. Valid combinations are described in TIBCO Object Service Broker Programming in Rules. Valid entries:
Specifies the TIBCO Object Service Broker syntax of the column. You can use any valid TIBCO Object Service Broker syntax type, provided that the semantic type and syntax combination is valid. Valid combinations are described in TIBCO Object Service Broker Programming in Rules.
For syntax P only. Specifies the number of digits to the right of the decimal point. The number of decimal places must be smaller than twice the length of the entire field.
Required. Every occurrence in the table must have a value or default for this field. Inserting or editing an occurrence without valid values in required fields causes an exception to be raised.
A wrong TIBCO Object Service Broker syntax can cause a conversion error, since the Gateway must convert each field of each row to the syntax as defined in the SLK Table Definition.
Data Type Translation
Refer to TIBCO Object Service Broker Programming in Rules for an explanation of TIBCO Object Service Broker semantic types and syntax.
Task F Add control information
Location Parameter Information
You can define a location parameter for SLK tables. A location parameter is required only if you need to access an external DBMS through a peer server and external Gateway, both associated with a different Data Object Broker (remote node).
If you always access the SLK table remotely, the node from which you request the access can have either a minimal or a full definition.
Minimal Definition
A minimal table definition consists of the following elements:
The name of the remote node where the full definition is located must be supplied through the use of the Default field, Src field, or Src and Sourcename field.
A minimal definition with a location parameter means you always access data at a remote node. The table type for such a minimal definition must be TDS.
Full Definition
A full table definition that has a location parameter indicates that you can access data at either the local node or at a remote node.
The table type specified in either a full or minimal definition does not have to match the table type of the full definition at the remote node. For example, a full definition of type TDS used to access TDS data on the local node can also be used to access an SLK table with the same name at a remote node.
Specifying Event Rule Information
Use the event rule segment of the Table Definition screen to provide additional controls over access to a particular table. Define event rules based on the type of access performed.
Event rules are always called when the table is accessed according to the access type specified. All rules that apply to a specific access type are executed in the order they appear in the event rule section. They cannot access tables on a remote node.
Types of Event Rules
Three types of event rules can be defined:
Verifies the value of an occurrence when the table is being modified (such as checking the validity of a field value).
Causes additional processing to take place when a table is accessed. For example, a trigger rule can be used to create an audit trail or update other tables.
Event Rule Fields
The event rule information is entered in the scrollable event rule section. To define event rules, complete the following fields:
No database updates are allowed during the validation process. The rule must be a function that returns Y (yes), the validation is successful, N (no), the validation is not successful, or a message explaining why it is not successful.
A trigger rule cannot be a function or change the contents of the triggering row, and cannot use the TRANSFERCALL statement. Nested triggers are permitted.
The rule specified in this entry is invoked to process the result set of the stored procedure (for TIBCO Service Gateway for ODBC only).
Refer to Coding Event Rules for more information about event rules for stored procedures.
Specify the type of access or manipulation to be performed on the data, causing the event to be executed. Valid entries:
W - Any write (insert, replace, delete)
W- Any write (insert, replace, delete)
See Also
TIBCO Object Service Broker Managing Data for more information on location parameters, event rules, and minimal table definitions.

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