Dynamic Query Inbound for WebSphere JDBC Adapter
srihari.na 270003TQP7 Visits (2225)
Firstly, Why is Custom Event query required?
The custom event query will provide a mechanism for customization during Inbound processing along with filtering and fine grained control to the user. Dynamic Event query (a.k.a Custom or User-defined queries) is feature of WebSphere Adapter for JDBC which facilitates the users to provide their own database queries to be executed during the Inbound polling cycle. Database queries can be of types such as standard SQL query, stored procedures of functions.
Users can set 4 different custom queries as of Adapter version 7.5:
Working of Dynamic Event Query
The design of the JDBC adapter using Dynamic Event Query is described here. Adapter works with 2 event tables referred as Standard event table and custom event table. The standard event table is the one mentioned in the External Service wizard. Triggers are written to create events in the custom event table. Dynamic event query should take care of all the necessary event filtering. Adapter substitutes the first parameter with pollQuantity in the custom event query.
For Assured Once Delivery:
While execution of Inbound, adapter executes the dynamic query for get events resulting the set of events from the custom table. Adapter will insert these events into Standard table and continue with the normal inbound polling using standard queries. Similarly along with standard update and standard delete queries are accompanied by custom update and delete queries to keep both standard and custom table in sync.
For non Assured Once Delivery
Adapter fetches the events from the custom table using custom query for get events and processes them without copying them to standard table. Similarly update and delete calls are applicable only for custom table.
Sample scenario with example scripts
Here a sample configuration for setting up Inbound with Custom Queries
Screenshot below describes particular fields during EMD of inbound which are marked with numbers.
1) Maximum Events in polling period : Describes the number of events processed in one poll cycle from the Standard event table.
2) Event table name : Specifies the name of Standard event table.
3) Event query type for processing events : Identifies whether the Event query type is Standard or User Defined(Dynamic).
4) Custom query : Specify the custom queries for querying events from the Custom event table. (Note: The queries are written for Custom event table)
Sample Standard SQL Custom Queries for your reference:
select * from WBIA
CREATE PROCEDURE GetEvents(IN pollQuantity int)
DYNAMIC RESULT SETS 1
DECLARE cursor1 CURSOR WITH RETURN FOR select * from WBIA
CREATE PROCEDURE UpdateEvent(IN EventId int)
CREATE PROCEDURE Upda
CREATE PROCEDURE DeleteEvent(IN EventId int)
Note: Standard table should follow the same schema of the event table except for Event_Id being a generated by identity. It continues to be a primary key. Trigger should be pointing to the custom event table. Adapter still uses the same standard query with the standard event table and processes pollQuantity number of events in 1 cycle(Please set the custom event query to return pollQuantity number of events). If your data in the event table contains future dated events(Rows 1 to 10) before current dated events (from row 11 onwards), then handle this in your custom event query.