How to read data from database db2 on sql server 2017 ?

Question

Post reply

How to read data from database db2 on sql server 2017 ?

ahmed_elbarbary.2010

Hall of Fame

Points: 3118
More actions
August 7, 2023 at 5:04 am

#4254587

I work on sql server 2017 i need to know what technology will be quickly and fast for read data from db2 database on sql server
without using linked server
so I need which technology or technique i will use to read data from DB2 Database on sql server 2017 database
August 25, 2023 at 10:06 am

This was removed by the editor as SPAM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Emperor100 SSCrazy Points: 2173 More actions · Answer 1

You can use SQL Server Integration services (SSIS) and also there are many other 3rd party tools in the market.

Azure data factory, Informatica, Ab Initio, Jitterbit, Mulesoft, BOOMI etc.

=======================================================================

Steve Jones - SSC Editor SSC Guru Points: 736068 More actions · Answer 2

OPENROWSET will work: https://learn.microsoft.com/en-us/sql/t-sql/functions/openrowset-transact-sql?view=sql-server-ver16

OleDB driver: https://www.microsoft.com/en-US/download/details.aspx?id=100917

Jeff Moden SSC Guru Points: 1004290 More actions · Answer 3

We use the following providers for linked servers and use OPENQUERY, which proved much faster than OPENROWSET (with the disclaimer that check was made years ago). This allows the query to be written in the DB2 language and executed on the DB2 machine. We load up a table from that and we're ready to rock with joins or whatever. I'll recommend that you NEVER do a join to a LinkedServer. 😀

p.s. And, sorry... just realized you specified "No Linked Server" in your post.

This reply was modified 1 year, 2 months ago by Jeff Moden. Reason: just realized you specified "No Linked Server" in your post

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

kajaldigital8700@gmail.com SSC Rookie Points: 43 More actions · Answer 4

To read data from a DB2 database on SQL Server 2017, you can use the Linked Server feature. First, create a linked server using the "sp_addlinkedserver" system stored procedure. Specify the DB2 server details, like server type, provider, and connection string. Then, use four-part naming convention ("linked_server.catalog.schema.table") to access DB2 tables from SQL Server queries. You might need to install the appropriate OLE DB provider for DB2. This approach allows you to fetch data from DB2 within SQL Server environment seamlessly, enabling cross-database querying and integration.

Phil Parkin SSC Guru Points: 246944 More actions · Answer 5

kajaldigital8700@gmail.com wrote:

To read data from a DB2 database on SQL Server 2017, you can use the Linked Server feature. First, create a linked server using the "sp_addlinkedserver" system stored procedure. Specify the DB2 server details, like server type, provider, and connection string. Then, use four-part naming convention ("linked_server.catalog.schema.table") to access DB2 tables from SQL Server queries. You might need to install the appropriate OLE DB provider for DB2. This approach allows you to fetch data from DB2 within SQL Server environment seamlessly, enabling cross-database querying and integration.

Did you read the original post? I suspect not, because the words 'without using linked server' appear in it 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin