SQLServerCentral / Discuss Content Posted by Basu Hullur / Article Discussions / Article Discussions by Author / Updating AS/400 Linked Server Tables from SQL Server / Latest Posts

RE: Updating AS/400 Linked Server Tables from SQL Server

accounts 90832 — Wed, 10 Mar 2010 00:54:10 GMT

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.

RE: Updating AS/400 Linked Server Tables from SQL Server

allan_leake — Fri, 05 Feb 2010 14:37:16 GMT

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!/alNote: Thanks go to JHeil at SQL Server Forums for the tip athttp://www.sqlteam.com/forums/topic.asp?TOPIC_ID=49610

RE: Updating AS/400 Linked Server Tables from SQL Server

forerolui — Fri, 02 Oct 2009 10:57:54 GMT

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.yyyI need to create a Linked Server from SQL SERVER 2005 to AS/400 (the above connection)Can anyone help me?

RE: Updating AS/400 Linked Server Tables from SQL Server

forerolui — Fri, 02 Oct 2009 10:05:23 GMT

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

RE: Updating AS/400 Linked Server Tables from SQL Server

Carol Taylor-423596 — Sat, 13 Oct 2007 05:03:05 GMT

As Rafael stated:[quote][b]rafael lenartowicz (4/5/2007)[/b][hr]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'' '):: ::[/quote]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=1My unrelated 2¢ worth: select(1) is more efficient than select(*).

RE: Updating AS/400 Linked Server Tables from SQL Server

dsc295-513958 — Fri, 12 Oct 2007 08:07:06 GMT

HiI 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 testTriggeron testTableFor UPDATEAS 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 tableselect @count=count(*) from testTable t inner join inserted i on t.id = i.idif @count > 0 Update LinkedAS400.xyz.abc.as400testtable set var1 = ('X') Where var2=1 EndGoThe update query works fine when executed separately, but from a trigger, it returns with the following error"Msg 7390, Level 16, State 2, Procedure testTriggerThe 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?

RE: Updating AS/400 Linked Server Tables from SQL Server

Carol Taylor-423596 — Sat, 07 Jul 2007 19:41:00 GMT

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!

RE: Updating AS/400 Linked Server Tables from SQL Server

Maria Pia — Fri, 06 Jul 2007 21:19:00 GMT

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 FernandezSQL Total Consulting

RE: Updating AS/400 Linked Server Tables from SQL Server

Mark Harr — Fri, 06 Jul 2007 07:32:00 GMT

...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

RE: Updating AS/400 Linked Server Tables from SQL Server

Joel Ferreira — Mon, 23 Apr 2007 09:37:00 GMT

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 18The 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...

RE: Updating AS/400 Linked Server Tables from SQL Server

Per Gunnarsson — Tue, 10 Apr 2007 02:33:00 GMT

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

RE: Updating AS/400 Linked Server Tables from SQL Server

Ninja's_RGR'us — Thu, 05 Apr 2007 08:20:00 GMT

Sure can, the search engines don't care about the post date .

RE: Updating AS/400 Linked Server Tables from SQL Server

rafael lenartowicz — Thu, 05 Apr 2007 06:58:00 GMT

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

RE: Updating AS/400 Linked Server Tables from SQL Server

kl4576 — Wed, 23 Aug 2006 10:48:00 GMT

I have found that either of the following methods work. The second is MUCH faster:1.UPDATE ISERIES.Serial#.Library.FileSET 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

RE: Updating AS/400 Linked Server Tables from SQL Server

Per Gunnarsson — Wed, 17 May 2006 01:44:00 GMT

Hi.

I have problem when updating large tables (> million rows) using traditional

4 level naming in update like:UPDATE AS400.S1019865.NSCDEVDATA.WSZZP100SET ZZSSRV = '2006-02-08 09:00:19'

It works but is very slow.

It seems like all data first is retrieved to SQL-Server, updated at SQL-Server and then finally sent back to the AS/400 and comitted.

Do any one know a working pass-through-method for updating ?

I've tried using openquery like:Select * from OPENQUERY(AS400, 'UPDATE AS400.S1019865.NSCDEVDATA.WSZZP100SET ZZSSRV = ''2006-02-08 09:00:19'' ')

It does not work.

Any suggestions ?

RE: Updating AS/400 Linked Server Tables from SQL Server

Oleg Oransky — Mon, 10 Apr 2006 02:05:00 GMT

We are using Linked Server to update AS/400 tables since a lot. We have "Jornaling" for all updateble AS/400 tables

The only problem we found is to implement DISTRIBUTED TRANSACTION between SQL Server 2000/2005 and AS/400. If anyone else has run into this and solved it ?

Oleg

RE: Updating AS/400 Linked Server Tables from SQL Server

Vlad-207446 — Wed, 05 Apr 2006 12:38:00 GMT

where it is true that Journaling needs to be enabled on the table for it to be updated, it is not correct to say that table is read-only.I have the very same issue with as400 and after some searching had made several discoveries.1. You can update the table in as400 even if Journaling is not turn on , but ony if you are sing ADO recordset object.I do not remember the settings that need to configured for it to work , but I know it's possible.2. the reason you have an issues with updating table in t-SQL is becouse of the SQL server ways of executing the queries.SQL server when executing queries against linked tables and/or ODBC connected tables in as400 DB require the query to be logged and commited in specific way, and it's requires a proper response from query engine , the as400 engine dose not send the proper response back if the table is not Journaled, thus making the SQL server think that query is not successfull becouse the talbe is read-only. I have found no other way to make this work but to enable Journaling on the table.

RE: Updating AS/400 Linked Server Tables from SQL Server

Lawrence Smith-270662 — Wed, 05 Apr 2006 10:13:00 GMT

Journaling is required for a table to be updated/deleted in AS400. Effectively if journaling is off for the table it is read-only through DB2.

One other problem I've run across when updating to the AS400 had to do with the setup of the ODBC driver itself. For the iSeries ODBC driver verision 9.00.00.00 there are settings that can cause diffculty and seem to work for updating or selection, but not both. In the Performance tab, under the Advanced button, these are the settings:

Open All Cursors as updateable --Set ON for update, Off for select

Retrieve extended column information --set ON for update, Off for select

I've had to set up 2 different datasources, one for read, and another for write. If anyone else has run into this and solved it, or if there's a version up that's fixed this I'd love to know.

-Larry Smith

RE: Updating AS/400 Linked Server Tables from SQL Server

DavidSimpson — Wed, 05 Apr 2006 09:54:00 GMT

Journaling is how the AS/400 tracks activity against a table. The journal then can be used in recovering a table by either applying activities or removing them. If a table is not journaled, then you only have the backups to recover from. I guess you could compare this to Simple versus Full recovery mode on SQL Server.

It's been a while since I've worked on the AS/400 so IBM may have changed how this works.

David

RE: Updating AS/400 Linked Server Tables from SQL Server

Per Gunnarsson — Wed, 05 Apr 2006 09:46:00 GMT

Hi.

I use Microsoft OLE DB Provider for DB2 (DB2OLEDB) for linking to AS/400 from SQL-Server 2000

I use OpenQuery for all reading (best performance)

Have no problem with updating the way described in the article, exept for performance on large tables.

Anyone know how to process a pass-through update query ?

That should be fast even in large/huge tables !

/PG

RE: Updating AS/400 Linked Server Tables from SQL Server

Basu Hullur — Wed, 05 Apr 2006 06:43:00 GMT

Hi Martin:

In the scenario described in the article, journaling was not turned on. The AS400 person did not want to turn it on for some reason and I had to work within that constraint. I did read about it and tried my best to get it turned on.

Thanks,

Basu

RE: Updating AS/400 Linked Server Tables from SQL Server

Martin Purdy — Wed, 05 Apr 2006 03:36:00 GMT

Marco - What do you mean by "Journaled"

Martin

RE: Updating AS/400 Linked Server Tables from SQL Server

marco de pasquale — Wed, 05 Apr 2006 03:26:00 GMT

I am using Linked Server to update AS/400 tables since a lot. The only problem I found is that the Table on AS/400 has to be "journaled".

You can achieve this with the STRJRNPF command onto the AS/400

Marco

Updating AS/400 Linked Server Tables from SQL Server

Basu Hullur — Sun, 12 Mar 2006 17:50:00 GMT

Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bHullur/updatingas400linkedservertablesfromsqlserver.asp