I have a AS400 JDBC notification on a table; 2 records were inserted into that table within the same sub-second.

14.37.14.003000
14.37.14.150000

The trigger picked up the first one and ignored the other completely!!!

Since the trigger didnt even pick it up, I am kind of stuck on how to debug it? I am other notifications running on SQL server where records inserted within the same sub-second gets processed correctly. I am inclining towards it being an DB2 trigger issue?

any ideas/insights!!

Are you using Basic Notification? As you probably know, when using Basic Notification, the DB trigger is not managed by the adapter at all and is completely in your (or your DB programming team) hands.

Nope. It’s a INSERT notification.

Actually, AS400 notifications require journaling to be turned on and the journal logs were quite helpful in debugging about the missing notification.

It seems that the first record was copied into the buffer table and subsequently processed; The second record was not even copied into the buffer table. This actually vindicates webMethods of being the cause of the issue and our AS400 team is looking at it (as I write).

Possibly a PTF patch is what we are missing!! Will keep you all posted!!!

Potentially. It depends on the root cause. It’s possible, though it seems unlikely, that the adapter is creating the DB trigger in a way that causes it to miss records.

Another possibility is this: when the notification is disabled, the DB trigger and buffer table are dropped. Perhaps the notification was disabled for a time? This is why basic notification can be preferred–the DB trigger and buffer table are managed completely outside of the adapter. Another driver to using basic notification is that many times, the DB team doesn’t like handing out create/alter/drop privileges to a service account.

Just food for thought.

Yes… that is true… But this trigger was not dropped in the recent past and it worked well all other record inserts during last month.

It looks like that the AS400 job that created the missing record has a “open file” and “record added” journal entries but no “close file” journal entry. This implies that the transaction was somehow left in lurch by the job. I dont still understand that how come the transaction didnt roll back, but that goes beyond my domain of journal entries on AS400. I hope our AS400 team will come up with an explanation.

I think the issue is either at the job level (wherein it didnt close that particular transaction correctly) or at the trigger level.

Lets see…

We have noticed that there are times when the JDBC Adapter does not cleanly drop the 3 db objects that are created when you create a JDBC adapter notification (i.e. db trigger, sequence and buffer table). In those cases, we’ve had to ask the DBA to explicitly delete these objects and then we have had to recreate the JDBC Adapter notification.

I don’t know why this happens, perhaps a permissions issue, network glitch, etc. But experience has taught that sometimes you are better off manually deleting those 3 objects and recreating the notification.

You’re right. We also at times have to recreate the objects on AS400 while enabling/disabling the adapter notifications on AS400.

But, in this case, this trigger was not dropped in the recent past and it worked well all other record inserts during last month. It was with this particular records that it kind of skipped.

Still a mystery with no resolution yet.

Hi Saurabhm,

Have you identified what is the problem?

When we enable the Notifications it will create Objects in database and respectively when we disable it will delete the Objects from the database

how it works in cluster( 4 IS Servers) when it is in stand by mode if i have disabled the polling in one server and rest all notifications are enabled will it delete the objects from the database?

when the IS Servers are in cluster and when i start enabling the notifications starting from IS1 Server it is taking long time to create a objects( the user has privileages to create,drop the objects) i am not sure why it is taking long time?

How it works in cluster when enabling the notifications in all IS Servers because we see "objects already exists " when we enable the notifications in individual IS Servers which are not in clusters?

Thanks in Advance
Gouse.