Cool Solutions

Sending Only Inserts, Not Updates, to AS400 via IDM


August 8, 2007 2:41 am






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…


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:

    <do-set-local-variable name="sql" scope="policy">
          <token-text xml:space="preserve"><jdbc:sql xmlns:jdbc="urn:dirxml:jdbc">
          SELECT x from y where z ='</token-text>
          <token-op-attr name="z"/>
          <token-text xml:space="preserve">'</jdbc:sql></token-text>
    <do-clone-xpath dest-expression=".." src-expression="$sql"/>

For more information on what can be done using native SQL statements, consult the documentation here:

0 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 50 votes, average: 0.00 out of 5 (0 votes, average: 0.00 out of 5)
You need to be a registered member to rate this post.

Categories: Uncategorized


Disclaimer: This content is not supported by Micro Focus. 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

  1. By:jessesmith

    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.