Sending Only Inserts, Not Updates, to AS400 via IDM
Novell Cool Solutions: Tip
By Jason Elsberry
Digg This -
Posted: 8 Aug 2007
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:
<rule> <description>SQL</description> <conditions> <and/> </conditions> <actions> <do-set-local-variable name="sql" scope="policy"> <arg-node-set> <token-xml-parse> <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> </token-xml-parse> </arg-node-set> </do-set-local-variable> <do-clone-xpath dest-expression=".." src-expression="$sql"/> </actions> </rule>
For more information on what can be done using native SQL statements, consult the documentation here:
Novell Cool Solutions (corporate web communities) are produced by WebWise Solutions. www.webwiseone.com