SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Updating AS/400 Linked Server Tables from SQL Server


Updating AS/400 Linked Server Tables from SQL Server

Author
Message
kl4576
kl4576
Right there with Babe
Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)Right there with Babe (727 reputation)

Group: General Forum Members
Points: 727 Visits: 1413
I have found that either of the following methods work. The second is MUCH faster:
1.
UPDATE ISERIES.Serial#.Library.File
SET field1 = RTRIM(field1) + ' Changed' WHERE field2 = '00248000'

2.
UPDATE OPENQUERY(ISERIES, 'SELECT * FROM Library.File WHERE field2 = ''00248000'' ')
SET field1 = RTRIM(field1) + ' Changed' WHERE field2 = '00248000'

The last WHERE clause is probably unnecessary since it is included in the OPENQUERY SELECT



Ken
rafael lenartowicz
rafael lenartowicz
Valued Member
Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)Valued Member (73 reputation)

Group: General Forum Members
Points: 73 Visits: 271

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'' ')

ooops... I just realized this message was posted 1+ year ago

oh well, maybe somebody else can still use it now


Ninja's_RGR'us
Ninja's_RGR'us
One Orange Chip
One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)One Orange Chip (28K reputation)

Group: General Forum Members
Points: 28705 Visits: 9671
Sure can, the search engines don't care about the post date .
Per Gunnarsson
Per Gunnarsson
SSC Rookie
SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)SSC Rookie (38 reputation)

Group: General Forum Members
Points: 38 Visits: 12

I found a good and fast solution for updating linked AS/400

It is the same solution that Ken LaRoy suggest

UPDATE OPENQUERY(ISERIES, 'SELECT * FROM Library.File WHERE field2 = ''00248000'' ')
SET field1 = RTRIM(field1) + ' Changed' -- WHERE field2 = '00248000'

The last WHERE clause is unnecessary since it is included in the OPENQUERY SELECT

Very fast, even though the AS/400-table has million of rows.

/PG


Joel Ferreira
Joel Ferreira
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 1

i have set a linked server in sql 200 sp4 to as4000 with the provider for db2 (host integration 2006)

im able to see the tables and all basic query's worked fine (i imported all the as400 tables i want with the same name in both servers)

anyway i got a trigger (insert) as i want to insert in as400 tables the same data that i insert in sql table (i could call it replication) but it gives me this error when i type a insert in the sql table.

Server: Msg 7391, Level 16, State 1, Procedure INSERT_WMCS01F, Line 18
The operation could not be performed because the OLE DB provider 'DB2OLEDB' was unable to begin a distributed transaction.
OLE DB error trace [OLE/DB Provider 'DB2OLEDB'' ITransactionJoin::JoinTransaction returned 0x8004d00a].

I would appreciate if you could help me...

PD: MSTDC is active and i configured after have read some things in msdn website, i disabled also the connection pooling as says in another msdn article, and made many other things i still have this problem and dont know whtat is missing to work fine...


Mark Harr
Mark Harr
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1584 Visits: 191

...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
Maria Pia
Maria Pia
Valued Member
Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)Valued Member (63 reputation)

Group: General Forum Members
Points: 63 Visits: 294
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



Carol Taylor-423596
Carol Taylor-423596
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 40
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!
dsc295-513958
dsc295-513958
Forum Newbie
Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)Forum Newbie (3 reputation)

Group: General Forum Members
Points: 3 Visits: 20
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?
Carol Taylor-423596
Carol Taylor-423596
Grasshopper
Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)Grasshopper (17 reputation)

Group: General Forum Members
Points: 17 Visits: 40
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(*).
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search