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


Chapter 4 Managing DB2 Data Definitions : Task C  Select a DB2 Table or Stored Procedure

Task C  Select a DB2 Table or Stored Procedure
Complete the necessary fields in the header, location parm, and event rule segments. These fields are described in the following sections.
Header Segment
The following fields are located in the header segment of the DB2 table definition screen.
The table name specified when you invoked the Table Definer. Type a new name to save the definition of the current table under a new name. For more information on tools used to copy objects, refer to the TIBCO Object Service Broker Shareable Tools manual.
Valid entries consist of 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 @ symbol denotes a table supplied by TIBCO Object Service Broker.
The user unit associated with the table. The unit marks a table as belonging to a particular application or to a logical unit such as utilities, accounting, or network control. The default unit for your user ID is specified in your TIBCO Object Service Broker user profile.
Valid entries consist of a character string with a maximum length of 8 characters. These can be specified by your system administrator, for example, ACC.
Identifies a Table (N), stored procedure (P) or result set (R) definition. Valid values: N or P. Note that result set definitions are generated using PF9.
Creator/
Schema
The name of the creator of the DB2 table. You can enter a search pattern in this field and then press PF4 to access a list of valid DB2 creators/schemas and their related tables/procedures. Refer to the ServerID field description for more information.
Note: You can also set Dictionary=I in order to make use of the previously extracted DB2 metadata.
Table/
Procedure
DB2 Location
The Gateway can execute only in a z/OS environment and connect to z/OS DB2; however, through z/OS, the Gateway can access remote DB2 subsystems connected to your local DB2 subsystem.
Type the ID for the Gateway or group of Gateways to use when accessing the table you are defining. This ID identifies a group of Gateways with common characteristics and must match the SERVERID startup parameter specified in the Gateway JCL. The default is DEFAULT. Refer to Gateway Parameters for more information.
Specifies whether the order clause is passed to DB2 in the select statement or ordering is done by TIBCO Object Service Broker. If the DB2 collating sequence is acceptable, specify Y. For release compatibility, the default is N.
If you are using Static SQL, the static cursors available are searched for matching selection, as well as matching the sort clause. If a static cursor that satisfies both criteria is found, DB2 performs the ordering, otherwise TIBCO Object Service Broker does it. Refer to Chapter 3, Using Static SQL for more information.
If using dynamic SQL, the Gateway generates dynamic SQL that includes the Ordered clause. Not used for stored procedure definitions.
Selecting the Foundation of a TIBCO Object Service Broker DB2 Table
To select a DB2 table as the foundation of your TIBCO Object Service Broker DB2 table, you can do one of the following:
Type the name of a creator (schema) in the Creator/Schema field and a DB2 table (procedure) name in the Table/Procedure field, and press Enter.
Type a search pattern (the wildcards * and ? are accepted; blank pattern is considered as *) in the Creator (Schema) and Table (Procedure) fields and press PF4 to access a list of all tables (procedures) pertaining to the creators (schemas) that fit your search patterns.
Sample Screen Using PF4

 
          List of available DB2 tables in e(X)ternal DB2 Dictionary
Creator: SYS*
Table: SQ*
-----------------------------------------------------------------------------
_ SYSIBM
SQTCOLPRIVILEGES
_ SYSIBM
SQTCOLUMNS
_ SYSIBM
SQTFOREIGNKEYS
_ SYSIBM
SQTPRIMARYKEYS
_ SYSIBM
SQTPROCEDURECOLS
_ SYSIBM
SQTPROCEDURES
_ SYSIBM
SQTSPECIALCOLUMNS
_ SYSIBM
SQTSTATISTICS
S=Select
PFKEYS: 3 = SELECT & EXIT ENTER = REFRESH 12 = EXIT

 
Type an S beside the desired table (procedure) and press Enter. A DB2 Table Definition screen similar to the following example displays. The column names for the selected DB2 table (procedure) are listed in the DB2 Column section of the screen.
 

 
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
------------------------------ -------- ----- -Def|--- ---------------- - --
_ CUST_ID CHAR 5 0 N
_ NAME CHAR 30 0 Y
_ ADDR_1 CHAR 30 0 Y
_ ADDR_2 CHAR 30 0 Y
_ CITY CHAR 15 0 Y
_ STATE CHAR 2 0 Y
_ OPEN_$ DECIMAL 9 2 Y
_ PHONE CHAR 10 0 Y
(S=Select P=Parameter K=Key)
PFKEYS: 4=DB2 TBLS 3=END 12=CANCEL 2=DOC 22=DELETE 5=RS_INFO 9=RS_REFRESH

 
DB2 Table Fields
Each column in the DB2 table or view contains information about it in the following fields:
Length of the column as understood by DB2; for a decimal column, it is the precision (calculated in digits).
If the column does not accept null values, whether or not DB2 provides a default value (NOT NULL WITH DEFAULT). Usually set to Y if the DB2 column does not accept null values and a default is supplied. A value of I or J indicates that this column is an IDENTITY column. In this case this field will not be included in INSERT and UPDATE statements.
P – input value
O – output value
B – input/output value
Location Parameter Segment
You can use this section of the table definition screen to define a location parameter for a TIBCO Object Service Broker DB2 table. You use a location parameter to access DB2 data through a peer Gateway associated with another Data Object Broker (remote node). If you do not need to access remote data, use the D line command to delete the parameter. If you always access the DB2 table or view remotely, the node from which you request the access can have either a minimal or a full definition.
Event Rule Segment
Event rules enable you to validate data and automatically trigger other events based on specific update and/or retrieval access to TIBCO Object Service Broker DB2 tables. Event rules are always called when the table is accessed in the type of access specified. All rules applying to a specific access are executed in the order they appear in the scrollable event rule segment of the TIBCO Object Service Broker DB2 Table Definition screen. You define the fields in the event rule segment as follows:
 
V – validation rule. Database updates are prohibited during the validation process. The rule must be a function that returns Y (yes), if the validation was successful, N (no), if the validation was not successful, or a message explaining why it was not successful.
T – trigger rule. There are no restrictions on coding, other than the rule must not be a function, it cannot change the contents of the triggering row, and it cannot use the TRANSFERCALL statement. Nested triggers are possible.
Valid entries for validation rules: W – any write (insert, replace, delete); I – only insert; R – only replace; D – only delete.
Valid entries for trigger rules: W – any write (insert, replace, delete); I – only insert; R – only replace; D – only delete; G – any retrieval.
See Also
TIBCO Object Service Broker Managing Data for more information on location parameters, event rules and minimal table definitions
TIBCO Object Service Broker Parameters for more information about parameters

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