Updating AS/400 Linked Server Tables from SQL Server

Question

Post reply

Updating AS/400 Linked Server Tables from SQL Server

Basu Hullur

SSC Veteran

Points: 216
More actions
March 12, 2006 at 5:50 pm

#170924

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

Viewing 15 posts - 1 through 15 (of 24 total)

You must be logged in to reply to this topic. Login to reply

marco de pasquale SSC Journeyman Points: 76 More actions · Answer 1

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

Martin Purdy SSC Rookie Points: 47 More actions · Answer 2

Martin Purdy

SSC Rookie

Points: 47

April 5, 2006 at 3:36 am

#630924

Marco - What do you mean by "Journaled"

Martin

Basu Hullur SSC Veteran Points: 216 More actions · Answer 3

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

Per Gunnarsson SSC Veteran Points: 248 More actions · Answer 4

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

DavidSimpson Hall of Fame Points: 3562 More actions · Answer 5

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

Lawrence Smith-270662 SSC Enthusiast Points: 114 More actions · Answer 6

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

Vlad-207446 Right there with Babe Points: 773 More actions · Answer 7

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.

Oleg Oransky SSChasing Mays Points: 625 More actions · Answer 8

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

Per Gunnarsson SSC Veteran Points: 248 More actions · Answer 9

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 ?

kl4576 Ten Centuries Points: 1270 More actions · Answer 10

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 SSC Veteran Points: 231 More actions · Answer 11

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 SSC Guru Points: 294069 More actions · Answer 12

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

Per Gunnarsson SSC Veteran Points: 248 More actions · Answer 13

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 SSC Enthusiast Points: 117 More actions · Answer 14

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