Updating AS/400 Linked Server Tables from SQL Server

...anyway i got a trigger (insert) as i want to insert in as400 tables the same data ...

Joel;

DON'T!!! Do not put code in a trigger that depends on an external resource! I have seen this same example at a client, and they were complaining about how slow the database was (they were getting SQL query and web site timeouts). Your communication to the external database will not be quick, and the user's update is held up waiting for the external DB2 update to work. If the user's update is held up, then the db connection is held up, the table locking blocks other updates, the user's application is held up, and you end up with a slow system that cannot handle many concurrent updates.

If you were using SQL Server 2005, I would suggest using Broker Services and having the trigger to write an entry to a broker queue. The queue would be setup with a stored proc to perform the DB2 update. The difference would be that the broker queue would work asynchronously, the trigger would be done and the users' update would complete without waiting for the DB2 update.

With SQL Server 2000, you will have to do more work to implement an asynchronous solution. If you have IIS available, you could write a web service that can be called asynchronously from the trigger. But that solution does not provide much failover support. Perhaps best would be to write out the update to another local table, then have a job runnning every minute to pickup updates from this table and update DB2. Or you could have a flag or timestamp on updated record, that the job would look for and use for making the updates to DB2.

Marco, could you please provide an example of the commands you use with the STRJRNPF command? We currently use DTS packages and are trying to eliminate them and execute everything from stored procedures. Thanks!

If you are updating large amounts of data, you may want to use a DTS package or VBScript to call a procedure on the AS400. One test analysis I conducted, deletes always took around 50 minutes to complete; the inserts 6 to 10 minutes. This was with only 85,000 records where a DTS call would take seconds or less.

Note, too, that if your trigger is using variables to create the statement you will need to encapsulate them in a variant string and execute them that way. OpenQuery does not accept variables. I've also never needed to use the parentheses around the update value.