Update an iSeries table via a linked server in SQL

Posted on 2009-05-27
Medium Priority
Last Modified: 2013-12-06
I have an iSeries running V5R4 and SQL 2005

The System DSN is set up using the iSeries Access ODBC driver version

I have no problem with queries against a linked server defined as follows...

EXEC master.dbo.sp_addlinkedserver @server = N'AS400', @srvproduct=N'iSeries Access ODBC Driver', @provider=N'MSDASQL', @datasrc=N'ODBC source', @provstr=N'UserID=[i]user[/i];Password=[i]pw[/i]'

I can not get this to work for update. I have tried using ...

update bobtest.WRCI5.BOB.CIADLI
set AINAME = N'test name'
where AICODE = 14  

declare @sql          nvarchar(500)
exec ( 'update OPENQUERY(bobtest,''SELECT * from bob.CIADLI where AICODE = 14'')
    set AINAME = N''test name''')
exec @sql

both get an error The OLE DB provider "MSDASQL" for linked server "bobtest" could not UPDATE table "[MSDASQL]".

I have tried setting up the linked server uing IMBDA400 and IBMDASQL both get the error
Cannot create an instance of OLE DB provider "IBMDASQL" for linked server "bobtest".

Question by:wrc2810

Expert Comment

by:Bob Hoffman
ID: 24488147
I had this same issue, for me the problem was that my iSeries Access driver was not the correct version for the iSeries OS. ie( iSeries Access driver was v4r3 and OS400 was v5r0)

Accepted Solution

stevebowdoin earned 500 total points
ID: 24491972
You may need an index on the file you are updating.  By that i mean you need keys defined in the DDS.  If you dont have this, you can create a logical file and use it in your SQL.
Do a DSPFD xxx.  Where xxx is your file.  Then look for "Access path . . . . . . . . . . . . . . . . :            Keyed".  If it says "Arrival" that may be your problem.  Try DSPDBR xxx to see if there are any LFs already.  Then DSPFD them to find one with "Keyed".    

You will also need a journal on the PF.  After images is all you need.
In the DSPFD look for "Journal images  . . . . . . . . . . . . . . : IMAGES     *BOTH".
If you find "File is currently journaled . . . . . . . . :            No", it is not journaled.
This applies to the physical file only.

I ran into this integrating with UPS Worldship.

Good Luck
LVL 36

Expert Comment

by:Gary Patterson
ID: 24492265
To clarify and expand stevebowdoin's comments, to update through ODBC, you need three things:

1) A unique key.  If the physical isn't keyed, you can solve this by using (or creating) a logical (index) that is, and then using that index in your query.

2) Proper commitment control settings.  If the table is journaled (DSPFD to determine if it is), then any commit mode is allowed.  If the table is not journaled, then you must specify a commit mode of *NONE.


3) Update authority to the table and/or index that you are using.

- Gary Patterson


Author Closing Comment

ID: 31585972
I had an idex built on the table already I knew about that being required. I did not know that the file had to be journaled. As soon as I started journaling on it the update worked.

thank- you

Featured Post

A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

In this tutorial, we’re going to learn how to convert Youtube to mp3 for Free. We'll show you how easy it is to make an mp3 from your video clips so that you can enjoy them offline.
If you try to migrate from Elastix to Issabel, you will face a lot of issues. These problems are inevitable but fortunately, you can fix them. In the guide below, I will explain how I performed the migration while keeping all data and successfully t…
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…

624 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question