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

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

AS/400 Connection Details

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

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