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
Basu Hullur
Basu Hullur
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 124
Comments posted to this topic are about the content posted at http://www.sqlservercentral.com/columnists/bHullur/updatingas400linkedservertablesfromsqlserver.asp
marco de pasquale
marco de pasquale
SSC Rookie
SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)SSC Rookie (42 reputation)

Group: General Forum Members
Points: 42 Visits: 2

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
Martin Purdy
SSC Rookie
SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)SSC Rookie (25 reputation)

Group: General Forum Members
Points: 25 Visits: 4

Marco - What do you mean by "Journaled"

Martin


Basu Hullur
Basu Hullur
SSC-Enthusiastic
SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)SSC-Enthusiastic (122 reputation)

Group: General Forum Members
Points: 122 Visits: 124

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
Per Gunnarsson
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 12

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
DavidSimpson
SSCommitted
SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)SSCommitted (1.8K reputation)

Group: General Forum Members
Points: 1826 Visits: 1095

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
Lawrence Smith-270662
Valued Member
Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)Valued Member (52 reputation)

Group: General Forum Members
Points: 52 Visits: 3

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
Vlad-207446
SSC Veteran
SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)SSC Veteran (220 reputation)

Group: General Forum Members
Points: 220 Visits: 322
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
Oleg Oransky
SSC Veteran
SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)SSC Veteran (273 reputation)

Group: General Forum Members
Points: 273 Visits: 272

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
Per Gunnarsson
SSC-Enthusiastic
SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)SSC-Enthusiastic (136 reputation)

Group: General Forum Members
Points: 136 Visits: 12

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 ?


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