Re: DB2/400 and MS SQL server

While that works, the problem is the SQL server pulls back the entire
SYSTABLES and does the WHERE locally on the SQL Server.

An better performing alternative is to use a pass-though query to have
the i process the query like so:
select * from openquery(MYAS400TEST, 'select * from SYSTABLES where
system_table_schema = ''QSYS2''')

HTH,
Charles

On Thu, Apr 15, 2010 at 2:27 PM, Loyd Goodbar <loyd@xxxxxxxxxxxxxx> wrote:

I usually populate SQL Server tables from SQL Server itself. Create a linked
server to DB2. You will need to install iSeries Access, but just the ODBC
drivers. Here is what a linked server setup looks like (SQL Server 2005 with
v5r4 iSeries Access installed):


Linked server: myas400test
Provider: Microsoft OLE DB Provider for ODBC Drivers
Product name: DB2 UDB for iSeries
Data source: MyAs400Test
Provider string: CMT=0;SYSTEM=10.0.0.1
Catalog: <System serial number>

You query like this:
select *
from DB2400.SYSNAME.QSYS2.SYSTABLES
where system_table_schema='QSYS2'
I've found the four-part name must be in upper case.

HTH,
Loyd


On Thu, Apr 15, 2010 at 10:55 AM, Lance Gillespie <LGillespie@xxxxxxxx>wrote:

I am looking for a way populate a SQL Server table with
DB2/400 data.

In a perfect world I would be running this tool on the
Windows box and it would update SQL server whenever the
DB2/400 data changes.  In a less perfect (but still OK)
world, it would update on a schedule.

Currently we ftp the DB2/400 data to the server as CSV
and use the Microsoft import tool.  This works, but lacks
aesthetic appeal.

I would also like to be able to use a TOAD-like tool from
a workstation against DB2/400 tables.

These could be different solutions.

I tried IBM DB2 CONNECT, but could not get it installed
properly before the evaluation period expired.

Vendor solutions welcome.

Thanks,
lance

--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
To post a message email: MIDRANGE-L@xxxxxxxxxxxx
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx
Before posting, please take a moment to review the archives
at http://archive.midrange.com/midrange-l.