Avoiding polling with the JDBC Adapter

Has anyone looked into better options other than the polling built into the JDBC Adapter Notifications?

We are interested in setting up triggers within the database that would monitor for activity, populate a buffer table and notify webMethods to perform a select and delete on the buffer table.

The solution seems a little clunky but still better than the network overhead associated with regular polling for infrequently updated databases–especially when the infrequent activity requires rapid turnaround.

Are there considerations we haven’t taken into account? Has anyone else tried this approach successfully or otherwise?

Hi!

Does you database support code in Java language, like Oracle does??

If so, you can use the webMethods java API to access IS, or you can use ES api to publish document into the broker.

I never did this, but I know someone who did and it works!

Actually the network overhead of pooling is minimal since the JDBC adapter is only invoking an SQL statment or a stored procedure and the actual work is done at the engine side. (the SQL statment has the compiling overhead)… if there is no results then no overhead to consider.

The notification approach is better to maintain the applications loose from each other an in a worst case scenario (if the pooling interval is 15 seconds the default) is having a certain table entry wait for about 14+ seconds!!! The average is much less if you calculate the probability of all the rows being added in the first seconds of a pooling cycle.

Having the application loose means that case the middleware backbone is down the operations on the database side are not affected at all…

If using Java inside the trigger to actualy publish the document to ES or IS then you have to accomodate failures to connect, slow connection times, etc… these can eventually produce locks in the database (case-by-case basis) and increase the complexity of the actual trigger itself.

The side effect of this can be slowing down the entire process…

You also can use the WmDB package to execute whatever SQL you want to poll with (usually SELECT against a VIEW or trigger table). Then just schedule the service. Much more flexibility and work with any database.

I would like to implement a database trigger which upon insert into the atc_message_log table an insert is made into another table which is used for Production Support.

We have a large number of integrations in our brokers and viewing and re-processing documents in the Integration Monitor (ATC v4.1) is quite time consuming.

Does anyone know if a similar solution has been implemented in the past?

You can try the utl_http.request stored procedure in Oracle RDBMS to easily invoke a service on the IS. If you need input to the service, they should be built into the URL like this:

http://my.is.com:5555/invoke/folder.subfolder/service?input1=value1&input2=value2

Remember authentication in the optional username/password fields, or the HTTP invoke will fail.

Have you ever used the utl_http to connect to Wm?

do you have an example?

Regards,

Hermann