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

Marco - What do you mean by "Journaled"

Martin

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

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

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

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

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

Hi.

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

4 level naming in update like:
UPDATE  AS400.S1019865.NSCDEVDATA.WSZZP100
SET 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.WSZZP100
SET ZZSSRV = ''2006-02-08 09:00:19'' ')

It does not work.

Any suggestions ?