Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Updating AS/400 Linked Server Tables from SQL Server

By Basu Hullur, (first published: 2006/04/05)

Recently, a project required that after downloading data from AS/400 tables to load a data warehouse, a table in AS/400 be updated with the time stamp to indicate when the load process started and when it completed. These time stamps are then used by the nightly AS/400 process to determine the data for the next load. The data warehouse load process used to be a Java application and the project charter was to eliminate the Java program and move the entire processing to SQL Server 2000 and make use of the linked server to AS/400. The Java program used to read the data one row at a time per table and then either insert or update based on the Add/Update indicator. Obviously it was a slow process.

Here are some details on the new linked server setup:

The AS/400 connection on the SQL server machine is defined as a system DSN named AS400_DEV using the iSeries Access ODBC Driver. Using the DSN, a linked server named AS400 is defined on the SQL Server to connect to AS/400 tables as show below with proper security configuration.

AS/400 Linker Server Defination
AS/400 Linker Server Definition

Now it is easy to refer to AS/400 tables using the four part naming convention and get data from the AS/400 tables. Here is an example:

SELECT
    	CountryNbr = E7CY#,
    	NameCountryShort = E7CYSN,
    	NameCountry = E7CYFN,
    	AddUpdateInd = E7ADUP
FROM
    AS400.S1019865.NSCDEVDATA.WSZZP100
But when you try to update a field in the AS/400 table, with a statement such as

UPDATE  AS400.S1019865.NSCDEVDATA.WSZZP100
SET ZZSSRV = '2006-02-08 09:00:19'

the following error was encountered though the user was given CHANGE authority to that file in AS/400.

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.  
[OLE/DB provider returned message: [IBM][iSeries Access ODBC Driver]
[DB2 UDB]SQL7008 - WSZZP100 in NSCDEVDATA not valid for operation.]
OLE DB error trace [OLE/DB Provider 'MSDASQL' IRowsetChange::SetData returned 0x80004005:   ].

The search on the web returned thousands of results indicating that many people were having issues updating AS/400 tables, but in many cases there were no responses and no complete solution was available. Finally, I ended up on a page that had some useful information. The link to that page is given here. The alternate solution suggested was to use a DTS package with an ODBC connection to AS/400 and executing the SQL statement to get the update done. The following screenshot shows the details of the DTS package:

DTS Package Details
DTS Package Details

The AS/400 connection is defined as other ODBC connection as shown below:

AS/400 Connection Details
AS/400 Connection Details

The Execute SQL task has the update statement as shown below:

Execute SQL task Details
Execute SQL task Details

Here is the actual statement for clarity:

UPDATE  S1019865.NSCDEVDATA.WSZZP100
SET ZZESRV =  CONCAT(CONCAT(CHAR(CURRENT DATE) , ' ' ),CHAR(CURRENT TIME))

Please note that once the connection is set to AS/400 in the execute SQL task, TSQL is no longer valid and the SQL statement needs to conform to the IBM DB2 SQL standards. Here is the link for DB2 Universal Database for iSeries SQL Reference Guide

This DTS can be executed within a stored procedure to update the beginning timestamp and another similar DTS package was created and executed from the stored procedure when the load process was complete.

Apparently there is no valid reason why the AS/400 linked server tables cannot be updated, deleted or inserted from SQL Server. The experts at Microsoft and IBM can work on that problem. It would be interesting to see whether this issue is resolved in SQL Server 2005. Meanwhile, it is always good to have an alternative approach in the tool bag to get the job done.

Basu Hullur
Encore Consulting Services, Inc
bhullur@encore-c.com

Total article views: 42418 | Views in the last 30 days: 10
 
Related Articles
FORUM

Linked Server from SQL SERVER 2005 to AS/400

Linked Server from SQL SERVER 2005 to AS/400

FORUM

importing data from as/400 to sql server 2005

importing data from as/400 to sql server 2005 using

FORUM

Create Linked Server from SQL Server 2005 to AS/400

How to Create Linked Server from SQL Server 2005 to AS/400

FORUM

Linker server to AS/400 DB2 - character translation problems

LinkeD server returning garbage for special characters on AS/400 DB2

FORUM

How do I call query on linked AS/400?

Call query on AS/400 from SQL Server 2005 *SOLVED*

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones