July 6, 2007 at 7:32 am
...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.
Hope this helps
Mark
July 6, 2007 at 9:19 pm
Yes, I´m working several U/I/D on as400 Tables using linked servers.
You only need to have an activated journal on the as400 table.
Maria Pia Fernandez
SQL Total Consulting
July 7, 2007 at 7:41 pm
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!
October 12, 2007 at 8:07 am
Hi
I need to update AS400 Linked server tables as soon as a table on SQL server 2005 is updated. I have created a trigger for the same which is given below:
Create Trigger testTrigger
on testTable
For UPDATE
AS
DECLARE @count int;
Begin
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
-- select the rows from inserted table
select @count=count(*) from testTable t inner join inserted i on t.id = i.id
if @count > 0
Update LinkedAS400.xyz.abc.as400testtable set var1 = ('X') Where var2=1
End
Go
The update query works fine when executed separately, but from a trigger, it returns with the following error
"Msg 7390, Level 16, State 2, Procedure testTrigger
The requested operation could not be performed because OLE DB provider "IBMDA400" for linked server "LinkedAS400" does not support the required transaction interface."
The MSDTC service is started and the journals have also been turned on on the AS400 side.
What else am I missing here?
October 13, 2007 at 5:03 am
As Rafael stated:
rafael lenartowicz (4/5/2007)
if you're using OPENQUERY you are executing the SQL "inside" of the AS/400. AS400.S1019865.NSCDEVDATA.WSZZP100 doesn't exist in the AS/400 world. Try this :
Select * from OPENQUERY(AS400, 'UPDATE NSCDEVDATA/WSZZP100 SET ZZSSRV = ''2006-02-08 09:00:19'' ')
:: ::
In other words, AS400 is a SQL object, not an AS400/DB2 object.
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.
See some other info on this subject at http://sqlforums.windowsitpro.com/web/forum/messageview.aspx?catid=65&threadid=47240&STARTPAGE=1
My unrelated 2¢ worth: select(1) is more efficient than select(*).
October 2, 2009 at 10:05 am
I´m using the iSeries Access ODBC Driver to connect to AS/400, and it works from .NET using this connection string:
"DataSource=10.254.xxx.xxx;DataBase=S1018689;UserId=yyy;Password=zzz";
I want to create a linked server with this parameters but I don’t quite know where to put mi conecction string parameters in the wizard.
Could any one help me
October 2, 2009 at 10:57 am
I also have created and test an UDl file to connect to AS/400. After creating an testing the connection the result of the connection string is:
Provider=IBMDA400.DataSource.1;Persist Security Info=False;User ID=SQLODBC1;Data Source=10.254.xxx.yyy
I need to create a Linked Server from SQL SERVER 2005 to AS/400 (the above connection)
Can anyone help me?
February 5, 2010 at 2:37 pm
I have struggled with this in the past and have created a step by step document (attached) showing how you can create a linked server to the iSeries (or AS/400).
Note that you must already have iSeries Access installed on your SQL Server box.
Good luck!
/al
Note: Thanks go to JHeil at SQL Server Forums for the tip at
March 10, 2010 at 12:54 am
I know this article is a bit old, but it should be updated. I had the exact same problem. Journaling needs to be turned on for the table you are trying to update, or insert on the AS400.
October 30, 2015 at 2:07 pm
How can i pass the variable instead of hard coded value in the update in where clause ?
Viewing 10 posts - 16 through 24 (of 24 total)
You must be logged in to reply to this topic. Login to reply