![]() |
Copyright © TIBCO Software Inc. All Rights Reserved |
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.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.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 DefinerInvoke 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.
• Type the name of an existing table beside the DT define table option and press Enter to display its definition.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:To select a table, type S in the line command field and press Enter.
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.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.
The following figure shows the right portion of the table definition screen, containing the additional SQLDataType and Default 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.TIBCO Object Service Broker Managing Data for more information on using the Table Definer.
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.For information on creating and managing ODBC data sources, refer to the relevant Microsoft documentation.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. Enter SLK for TIBCO Service Gateway for ODBC and ORS for TIBCO Service Gateway for Oracle. The default is SLK. 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. 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. You can specify either an internal (I) or external (X) dictionary in the Dictionary field. Specifies an external dictionary. If possible, metadata is read from the actual DBMS denoted by the data source. Specifies an internal dictionary. Metadata is read from the TDS tables populated by @SLKEXTRACT beforehand (parameterized by the data sources or Oracle SID).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.
You can use asterisks (*) and question marks (?) as wildcards to search for external DBMS tables and owners.
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.TIBCO Object Service Broker Getting Started for more information about wildcards and standard primary commands.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.
tablename LIKE ‘EM%’ & qualifier LIKE ‘%’ & owner ‘SCOTT’ & (type = ‘TABLE’ or TYPE = ‘VIEW’)
----------------------------------------------------------------- ----------------
• 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.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.
PF Keys Primary Command Displays the Documentation screen, where you can document the table definition. Refer to Appendix A, Documenting TIBCO Object Service Broker SLK Tables for more information. 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. Displays the screen used to specify the search values for a list of:
• Columns in the external table, in a table definition
• 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. 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.
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.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.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.
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.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.
It is optional to define a return value. If the procedure returns a value and no field is mapped for it, the return value is discarded.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 Name The name of the columns in the specified external table. This value can be entered manually or imported pressing PF4 or PF5.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). 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). Denotes that this field is to be used only in WHERE clauses. The expression provided as Default for this field is to be used as the value in INSERT/UPDATE statements (for example, SEQ.NEXTVAL). 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.
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. Specifies if the field is required. Any field can be a required field. A primary key field is required by default. Valid entries: 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.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 informationYou 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.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.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.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.
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. 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) U - Cursor (result set) returned 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 |