Re: DB2/400 and MS SQL server

[Charles Wilt <charles.wilt@xxxxxxxxx>]

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.