Transfer data b/w SQL Server & IBM UDB

I need to transfer some data from SQL Server to IBM UDB on a regular basis. Is there a way to do this through DTS or any other functionality in DQL server.

Thanks!

Yes, you can use either DTS or linked server. When you define a connection in DTS you can use Other DB Connection to use a DSN.

Ok, here’s what I have done:
I crated an ODBC System DSN to my UDB database & tested the connection successfully. Now in the DTS package, I can use Other DB connection to point to the DSN I created, & transfer table/data from UDB to SQL Server. However when I try to transfer table/data from SQL Server to UDB, I am able to create the table, but it is not able to transfer any data. I get an error saying Driver is not capable to do this.
So, it can create a table on UDB, but not transfer any data… what could the problem be?

Also, how can I set up a linked server? Won’t I need OLE DB Provider for DB2 for that?

Thanks for all your help!

Did you try transfer data only from sql to existing udb table? You can create linked server with Microsoft ole db provider for odbc drivers.

I am not sure of what information to enter in the following fields for creating a new linked server after choosing the provider as Microsoft ole db provider for odbc drivers:

Product name
Data source
Provider String
Location
Catalog


Thanks…

Only need odbc dsn as data source.

I just gave the DSN name & the server name and added the server.
When I click on Tables it says, Data source name not found.


Does this DSN need to be on th server or can it reside on the client?

Thanks...

Try query linked server in query analyzer. I always have problem to list tables for non-sql liked server in em so not trust it anymore.

I tried it in EM... & it gave the following error again:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified]

I have the DSN on my client machine...doe sit need to be on the server? also does the server need to have DB2 client?

Yes, should create dsn on sql server. Query linked server with query analyzer instead of em.

Sorry, in my earlier post I meant to say query analyzer.... u tried runningit on QA & got that error.

but my DSN is on my client... let me create it on the server & then try again.

thanks!

Rmiao,
As suggested by you, I created a DSN on the sql server itself & created a linked server. Now I can query the UDB database directly using:
SELECT *
FROM OPENQUERY(Dev, 'SELECT * FROM dsnp.Test')
GO

Now if I want to insert some data into the tables….what syntax do I use? Also, how can I use this linked server to transfer data from any sql database to this new database. Can I use DTS at all?

Thanks a bunch for all your help:)

Something like

INSERT INTO OPENQUERY (Dev, 'SELECT column_to_insert FROM dsnp.Test') values (value_to_insert)

I get the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'MSDASQL' reported an error.
[OLE/DB provider returned message: [IBM][CLI Driver] CLI0150E Driver not capable. SQLSTATE=S1C00]

Then should check db2 odbc driver version to see if it supports those transactions.