Update an iSeries table via a linked server in SQL

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

Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Bob HoffmanDeveloperCommented:
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)
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Gary PattersonVP Technology / Senior Consultant Commented:
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

wrc2810Author Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Operating Systems

From novice to tech pro — start learning today.