Just need your inputs on the design which I am thinking of for a requirement. I haven’t worked on JDBC polling notifications.
The main requirement is that there are tables on SQL db and any change to those tables(insert, update or delete ) for any field/particular field ( yet to know ) has to be sent to wM. Now, To accomplish this, if we write a JDBC adapter notification will that work? Also, If there are 10 changes which happened to 10 records, does wM receive 10 documents?
The next requirement is that, they are going to send delta changes every week for the same tables…Like whatever changes happen in that week…All records will be sent to wM ( once a week ). How to accomplish this one? Do we need to write any scheduler to pull the changes on a weekly basis?
The main requirement is that there are tables on SQL db and any change to those tables(insert, update or delete ) for any field/particular field ( yet to know ) has to be sent to wM. Now, To accomplish this, if we write a JDBC adapter notification will that work?
Yes, It will work. JDBC Adapter provides template for DML’s.
Also, If there are 10 changes which happened to 10 records, does wM receive 10 documents?
Yes.
The next requirement is that, they are going to send delta changes every week for the same tables…Like whatever changes happen in that week…All records will be sent to wM ( once a week ). How to accomplish this one? Do we need to write any scheduler to pull the changes on a weekly basis?
If it is for the same table then the notification which you are going to write for the 1st requirement is enough for this requirement too.
Even for different tables you have to write a notification only, no need to put any thing in the scheduler.
As you mentioned JDBC notification is polling based you can set the polling interval according to your requirement.
I have a question here…So, for my first req…any change to the record in the table will be sent to wM. That is fine.
But for my final requirement, They are not going to touch/change any records in the table for delta changes…we need to send all changes in a single shot to the destination. Suppose, If there are 100 changes happened during the week(till friday). Now, On every(saturday) they want to send all these changes to wM.How does wM will receive if there is no change on those records? and How can we get 100 records at one go. Please let me know.
I guess you mean to say we have 100 records in destination application, source is going to send those 100 records with no changes in it. (Means first week what was received, same values we are going to receive in the next week)
Even though there is no changes in the record wM will publish the document for that.
Assume we have an update notification service, it will send notification whenever updation happens. It doesn’t bother whether the value has been changed or not.
But still it is possible to check whether the value is changed or not by writing our own logic, but it is tedious. If the amount of record in that table is static (Fixed number) then we can think about it.
More over wM is integration tool not a programming language.
I have come across similar requirements, for that we used to write code in perl or TCL or Java to compare the old record values with new record values, if the values are not same then we update it. Then wM notification picks it up & publish the document.
How can we get 100 records at one go?
wM has the capability to catch even 10000 or more at one go. wM uses its own table to catch those changes in it.
This is what I have suggested the client. They are going to create buffer tables and wM will be polling to that buffer table. The client process will pick all the changes in the main table and sends it to the buffer table. Now, as soon as they are present in the buffer table, wM will receive the data.
But I am still not clear, whether the data received for the records will be 1 by 1? or wM will get all changed data when it polls? For eg: if there are 10 changed records, will wM receive all 10 records in single time when it polls?
Yes, wM will receive all 10 records in single time when it polls, but the notification service will execute 10 times for each record. The records will be processed in First In First Out manner.
“I mean…do we need to change any settings in the adapter notification to accept multiple records at sametime as a list.”
No. The records will be processed in First In First Out manner. By default wM won’t allow to form a list then execute the notification service only one.
Even if we have 100 records in the buffer table, using a single poll wM will receive 100 records, but 100 times notification service will get executed.
The above explained menthod is default behaviour wM.
If your intention to collect those 100 records and form a list then execute the notification service only once, then we need to write our own logic.
Here is the one.
Create a notification service, at this time automatically buffer table will get created. Note this buffer table name.
Once we ENABLE the notification then only the buffer table and other db components will get created in the db. (The buffer table & other db components like Trigger & sequence which are required for the notification will get created by wM). So ENABLE the notification.
Now SUSPEND the notification. If we SUSPEND, all the changes will be captured in the buffer table, but notification service won’t be executed b’coz it is suspended. Don’t ENABLE the notification.
Write another flow service which will fetch the records from the BUFFER table created in the first step & do the work what you want to do. At the end of this flow service delete BUFFER table completely.
By using this logic, service will get executed only once :-). And you can put this service in the scheduler and run whenever you want.
Note: If the amount of records is high, lets say 10000 or more then you might end up with memory out of exception. In that case you have to use large file handling logic in your flow.
Thanks for your response. It really helped. Also, I have a question regarding the MarkID setting in the basicNotification. The requirement is that we do not want to delete the columns in the buffer table but we want to mark them as processed.
What is the default value of the MarkID, I mean with what value does wM updates this field? Its a Char 1 field? Do you have any idea? Is it ‘y’.