Sending Only Inserts, Not Updates, to AS400 via IDM

Problem

A Forum reader recently asked:

“I am connected to an AS400 system running DB2 with the JDBC driver. The customer would like to effectively use the database table that I am writing to (Publisher channel disabled) as a queue. When I write values to the database, they would like to process the record and remove it from the table.

The problem I am running into is that when I try to write a second record with the same primary key to that table, the driver is sending an UPDATE query for the old record which has been removed. How can I force this to only send INSERT queries?”

And here’s the response from Jason Elsberry…

Solution

I think the easiest way would be this:

1. Make the driver schema-unaware (basically, don’t specify a schema or view/table names).

2. Remove the matching rule.

3. Update the filter so all attributes you’re interested in are present in the event.

4. Intercept all add events on the object to fashion a SQL statement for the table in question. This way, an association won’t be added and you’ll be guaranteed to only get adds and not modifys.

You can create SQL using policy builder or XSLT. XSLT examples are included in the preconfig that ships w/ the driver (JDBCv2.xml or JDBC-IDM3_5-V1.xml). I’ve included an example policy builder example here:

Disclaimer: This content is not supported by Novell. It was contributed by a community member and is published "as is." It seems to have worked for at least one person, and might work for you. But please be sure to test it thoroughly before using it in a production environment.

1 Comment

If you strip the association from ever being set after an insert, all operations will come through the subscriber channel as an add. This will effectively get what you are looking for, which is to always insert a row with all the object’s attributes (that are in the filter).

Specifically, I believe you will need to do the following:
1. Veto any add-association events in the event transform on the publisher channel.
2. Make sure there is no Matching policy on both the subscriber and publisher channels.
3. Make sure create events are vetoed on the publisher channel.