Hello … Just wondering how others are cleaning up the buffer tables.
After one of our Sybase databases was progressively getting slower with response time I found a million and a half records in one of the wM buffer tables.
Come to find out that if you configure a notification operation (insert, update, delete) using a where clause the entries in the buffer table are not being removed when they don’t meet the where clause conditions.
So what happens is that any time a insert happens in the table, the record is copied to the buffer table. Then the adapter comes along and checks the record against the where clause to see if it satisfies the condition.
If it does, it will then publish the data to an event and then delete the record from the buffer table. Otherwise it remains in the buffer table until it is manually deleted.
So I was just wondering what others are doing to clean up these entries?
I think the perception is wrong. It is not required to manually delet the buffer entries. The contents will be deleted automatically when the last buffer record satisfies the condition incorporated in the WHERE clause. On delete operation the adapter will check whether the aweb_rowid is less that the specfied last valus if the condition is satified.
Just as a follow-up … I have brought this issue up with wM support team and here is what I found out.
The standard adapters cleaned out the buffer tables using the highest value returned from aweb_rowid (eg DELETE FROM AWEBB_orders_ora WHERE aweb_rowid <= …)
However their were some issues with this process and this functionality was pulled from the intelligent adapters. So the only way an entry would be removed from the buffer table is if it met the requirements of the where statement.
So therefore if the row does not meet the where condition it just sits in the buffer table until it is manually deleted.
The following are work arounds: You can change the insert/update/delete notifications to basic notification and specify the where clause within the trigger. Or apparently you can use the JDBC adapter instead of the Oracle/Sybase adapter.
The JDBC adapter has the functionality of cleaning out the buffer table after the where clause is met. And finally you can just truncate the buffer tables occasionally. But beware of valid data sitting in the buffer table waiting to be processed will be lost.