Adapter for JDBC 10.3 | webMethods Adapter for JDBC Documentation | webMethods Adapter for JDBC Installation and User’s Documentation | Adapter Notifications | Configuring UpdateNotifications | Creating an UpdateNotification
 
Creating an UpdateNotification
You configure notifications using Designer.
Be sure to review the sections Before Configuring or Managing Notifications and Considerations when Configuring UpdateNotifications before you configure an UpdateNotification.
*To create an UpdateNotification
1. In Designer, right-click the package in which the notification should be contained and select New > Adapter Notification.
2. Select the parent namespace, type a name for the adapter notification, and click Next.
3. Select Adapter for JDBC as the adapter type and click Next.
4. Select the UpdateNotificaton template and click Next.
5. Select the appropriate Adapter Connection Name and click Next.
The name of the publishable document associated with this notification is displayed.
6. Click Finish.
For more information about adapter notifications and publishable documents, see Adapter Notifications. For more details about the Integration Server publishable documents, see the Publish-Subscribe Developer’s Guide for your release.
7. Designer creates the notification, and the editor for the adapter notification appears.
a. You can select the Adapter Settings tab at any time to confirm adapter notification properties such as the Adapter Name, Adapter Connection Name, and Adapter Notification Template, as necessary.
b. In the Publish Document section, you can specify how you want the notification document to be published:
*To publish documents to Broker, select Broker/Local. This is the default option.
*To publish documents to a JMS provider, select JMS Provider, and provide values for the following input fields:
Field
Description/Action
Connection alias name
The name of the JMS connection alias configured on Integration Server.
If the connection alias is a Broker Cluster configured with Multisend Guaranteed policy, you must add the watt property watt.art.notification.jmsSend.usePublicService and set it to true.
Note:
Adapter Runtime does not support LOCAL_TRANSACTION and XA_TRANSACTION type JMS connection alias.
Destination name
The name of the destination from which you want the JMS trigger to receive messages.
Destination type
Whether the destination is a Queue (default) or a Topic.
8. Select the Notification Configure tab and use the following fields:
Field
Description/Action
Base Name
The base name used to generate the Resource Name created by Adapter for JDBC.
Note:
For OS/390 DB2V7.2, the Base Name you create below must be no more than five characters because triggers on OS/390 name cannot be more than eight characters.
Publish Locally
Specifies whether to publish the notification's publishable document to the local Integration Server. By default, this option is not selected, that is, if the Broker is configured to Integration Server, the publishable document is published to the Broker; otherwise the publishable document is published to the local Integration Server. Selecting the Publish Locally option reduces performance problems, if Integration Server is connecting to a remotely located Broker that is in turn triggering a service on the local Integration Server.
Resource Type
Types are buffer table, trigger, and sequence. The base name and resource type determine the following Resource Name.
Resource Name
To ensure uniqueness, the resource name combines the following elements. You do not edit this name.
*Resource prefix (WMB, WMT, and WMS for buffer table, trigger, and sequence respectively)
*The name you typed in the Base Name field
*A suffix, based on a system timestamp
File Record Format
The format of the file record. Optional field used by DB2 for AS/400 V4R5 only.
Database Name
The name of the database where the buffer tables will be created. Optional field used by DB2 for OS/390 only.
Table Space Name
The table space where the buffer tables will be created. Optional field used by DB2 for OS/390 only.
9. Select the Tables tab and use the following fields:
Note:
For AS/400 DB2 V4R5 using a jt400.jar file, the table name for the notification cannot exceed 10 characters; otherwise, an exception will be generated when you try to enable the notification.
Field
Description/Action
Table Alias
The table alias is automatically assigned when you select more than one table in the Table Name field. The default is t1.
Table Name
Select a table. The default for the associated catalog name is current catalog. The default for the associated schema name is current schema. The table name must not contain a period. If the table name does contain a period, Designer will throw an error.
Note:
Informix databases do not allow you to specify a catalog and database name because you can only access the current catalog.
Type
The table type displays automatically based on the table you select.
10. If you are not joining tables, skip this step. Select the Joins tab to specify the columns for joining the tables you just configured.
a. Select the icon to create new left and right columns.
b. Select Left Column and select the first table's joining column.
c. Select the appropriate Operator.
d. Select Right Column and select the next table's joining column.
e. Repeat until you have defined all the joins.
11. Use the SELECT tab to define the columns and fields to be selected as follows:
Note:
When using the Join clause, select only the fields of the monitor table in the Select tab.
a. In the ALL/DISTINCT field, select ALL to include duplicate rows or DISTINCT to suppress duplicate rows. Selecting ALL corresponds to the SQL statement SELECT ALLnamefromtablename. The default value is blank, which corresponds to the SQL statement SELECTnamefromtablename.
b. Select the icon (or the icon to fill in all rows of the table) to create new fields as needed.
c. In the Expression field, select a column or type any valid SQL expression. The corresponding Column Type, JDBC Type, Output Field Type, and Output Field display for each column you select in the Expression field. Use the following fields:
Field
Description/Action
Expression
The column name or SQL expression.
Column Type
The column data type defined in the database table.
JDBC Type
The JDBC type of the corresponding Output Field.
Output Field Type
The data type of the output field. Adapter for JDBC automatically converts database-specific types to Java data types. For a list of JDBC type to Java type mappings, see JDBC Data Type to Java Data Type Mappings.
Output Field
The name of the field containing the output from the SELECT operation. An output field name displays when you select an expression. You can also modify the output field names as required.
Notify On Update
Enable this option to indicate for which of the columns specified in the SELECT tab you want a notification, if the column is updated. Select:
*Yes if you want a notification if this column of data has been updated. Yes is the default value.
*No if you do not want a notification if this column of data has been updated. For example, you configure the following three output fields: MyName, MyNumber, and MyLocation. You want a notification only if the MyLocation output field is updated. In this case, you would select Yes for the MyLocation output field, and select No for the MyName and MyNumber output fields.
Output Value Type
Specifies which output value to retrieve from the database table. By default, the UpdateNotification retrieves the new value from the database table. Select either of the following output value types:
*Old: Retrieves the old value from the database table
*New: Retrieves the new value from the database table
To retrieve both the old and the new values, create two rows and then select an Output Value Type as Old in one row and an Output Value Type as New in the other row. While doing so, ensure that the Output Field is unique for both the old and the new values.
Note:
For Sybase and Microsoft 2000/2005, you cannot retrieve both the old and the new values in the same notification. The notification must retrieve either the old or the new value.
Maximum Row
Specifies the number of rows to be retrieved from the buffer table. This field is useful when you are working with a large number of records and you want to limit the number of documents sent each time the notification polls. Use a value of 0 to indicate no limit on the number of rows retrieved.
Query Time Out
Specify the query time out value in seconds. This value is the amount of time Adapter for JDBC waits for the notification to execute before stopping the SQL operation. The default value is -1. Use the default value to have the notification use the value indicated on the watt.adapter.JDBC.QueryTimeout property as the time out. If you specify a value equal to 0, or if the watt.adapter.JDBC.QueryTimeout property is not set, the notification executes without a time out. If you specify a value greater than 0, the notification executes with the specified value as the time out.
Note:
-1 is the only permissible negative value for this field.
For more information about the watt.adapter.JDBC.QueryTimeout property, see Forcing a Timeout During Long-Running SQL Operations in Services and Notifications.
12. Use the WHEN tab to specify the conditions for selecting information:
Note:
If you use Microsoft SQL Server, Sybase, or V4 AS/400 DB2, do not use the WHEN tab because this feature is not supported. An exception will be generated if you try to use this tab.
a. Select the icon to define new WHEN clause fields.
b. Select the Column field and choose a column from the list.
c. Select a logical operator from the AND/OR field, an Operator, and separators (the left and right parentheses) as needed.
d. Type a fixed value in the Value field. Be sure that it is a valid value, or an exception will be generated at run time.
e. If necessary, use the or icons to change the order of the WHEN clause to ensure the parameters are parsed in the correct order.
f. Repeat until you have specified all WHEN parameters.
13. The information about using the Permissions tab to assign an access control list (ACL) to an element appears in the Properties panel.
14. From the File menu, select Save.
15. You must schedule and enable the notification using Integration Server Administrator before you can use it. For details, see Managing Polling Notifications.