Can DTS Connect to a DB2 Database?

[thermidor]

Can DTS Connect to a DB2 Database? If so, can anyone who's done it explain the steps involved? I've never had to connect to a DB2 data store before so I don't know the first thing about it.

TIA,
Sven

 

[john76]

First of all, yes.

I have an ODBC System DSN pointing to an iSeries DB2 database, then I use that DSN to create a linked server within SQL Server.

After that, you query the DB2 database using a four part name (formated <linked server name>.<database name>.<schema name>.<table name>), or by using OpenQuery (if you want to run a distributed query on the DB2 system, highly recommended!)

Examples:

[green]-- Use the 4-part name[/green]
Select  *
  From  AS400.DB2_RDB.MYLIB.MYTABLE

[green]-- This way sends the query to the DB2 for processing, which means less data to transmit over the network[/green]
Select  *
  From  OpenQuery( Select  *
                     From  MYLIB.MYTABLE
                    Where  MYFIELD = 4
        )

HTH,
John

 

[john76]

correction:
Second query would look like this:

Select  *
  From  OpenQuery([red]AS400,[/red] Select  *
                           From  MYLIB.MYTABLE
                          Where  MYFIELD = 4
        )

John

 

[thermidor]

Thanks for the reply!