JDBC Adapter Notification Buffer Tables

, ,
1

In webMethods , do the Buffer database tables where the JDBC Adapter records Notification data, have to be in and on the same database as the tables the Notification is monitoring? Or can they be on a different database server of a different RDBMS vendor. Thanks.

2

These notification tables are created when you create a notification on a particular tables in a database and they have to be in the same database. I personally had some bad experience of tables getting corrupted when i tried to move them to a different database in a MSSQL 2005 DB.

In the end my notifications were not working and I had to recreate them after running the service deletePollingNotification. I am not sure about the case with Oracle or other DB’s.

Cheers,
Akshith

3

Hi akki,

Can you tell me more about the buffer tables. I am completely new to this concept. My requirement is that we need to send any changes in a table to the trading partner. For that I have suggested the client to use the Buffer table concept and wM will use adapter notification. But What are actually the buffer tables? Who creates them?

I have told the client that they have to create a buffer table similar to main table and send the changes to this table, so that wM will pick as part of polling. Is this correct?

Also, If we are doing a full refresh. Can adapter notification can handle this? Please let me know.

thanks
David.

4

David,

Its been a while i worked on JDBC notifications and hope nothing changed in the recent versions (8.x) of the product.

When ever a JDBC notification is created it creates a corresponding buffer table in the Database where you have set the notification to poll on. These tables are auto created by WM.

No, You will have to create a JDBC (insert\update) Notification on the (actual) table where the data will be sent to and when there is a change in that table a publishable doc with the data will be published to the Broker. In order to receive and process this data you will have to create a trigger on the publishable doc generated by the notification.

I think this can be achieved in two steps,

Write a stored proc on the database to check if data refresh has happened (based on the timestamps of the record entries), if it has then send out the new records as an array.

Create a storedprocedurenotificationwithsignature notification on the above created stored proc. Refer the JDBC Users guide for more information on the configuration.

Hope this helps!

Cheers,
Akshith

5

I usually recommend using Basic Notification. This requires the buffer tables to be created manually. I avoid the other notification types because they automatically manage the create and dropping of the buffer tables and triggers. This sounds like a good thing–having the adapter manage that for you–but it actually is a problem spot. If you inadvertently change the state of the notification–it will drop the DB objects. Which often is NOT what you want it to do.

The key in getting notified about table changes (via normal operations or refresh) is having the right DB triggers in place. The triggers on the “real” tables will write key data to the buffer table. The Basic Notification polls the buffer table for work to do. For refresh, if the triggers fire then you’re set.

To re-emphasize my main point: I recommend that you manage the buffer table and trigger creation/dropping manually. Do not use the Insert/Update/Delete notifications.

6

What data does these buffer tables have? The metadata that needs to be published to the broker or a copy of the actual data itself?

7

HI Sir

   I execute the insert/delete/update notifications successfully. but in the case of basic notification I dnt hve idea how i can create a buffer table and trriger in database instead of automate buffer table and trrigers give me some idea on these and tell me where we are using SEQUENCE in notifications..

Thanks in Advance

8

Did you review the JDBC Adapter User’s Guide for this information?