Data Sources are heterogeneous and time to time you might require to query from a data base while being in another, in this case we will query a DB2 Database from SQL Server Management Studio.
DB2 Connector
First you need to download the DB2 connector to be able to connect to a DB2 Source, in order to do
that go to this URL
Select download and choose “ENU\x64\DB2OLEDBV5_x64.msi” to download and install the DB2
connector.
Configure a Linked Server
You can create a linked server to query a DB2 Data base from
SSMS, in order to create a linked server go to “Server Objects” and expand it
then right click “Linked Servers” and select New Linked Server, here you need to
provide the following information.
Below is an example for a provider string and you should
insert all the relevant values for them.
Data Source = ****; User ID=***;Password=*****;Initial Catalog=***;Provider=DB2OLEDB.1;Persist Security Info=True;Network Address=(Same as Data Source);Network Port=***;Package Collection=WORLD;Default Schema=WORLD;
Then press OK to create a linked server
Querying from Linked Servers.
Sometimes the list of tables won’t be displayed in the
linked server table list, in such scenario we can use an open query statement
to get the list of tables.
SELECT * FROM OPENQUERY([TESTDB2LINKEDSERVER],'select * from
SYSIBM.SYSTABLES');
Once you identify a table name for an example [Country] then you can run a select statement like given below.
Select * from [TESTDB2LINKEDSERVER].[WORLD].[WORLD].[COUNTRY]
First one is the Linked Server Name.
Second one is the Data Base Name (Initial Catalog).
Third one is the Creator Name (more Like Schema).
Fourth one is the Table Name.
Then you can run any T-SQL query for the linked server tables without any issues once the table is properly defined as given above.
Cheers and feel free to hit me up with any questions.
Comments
Post a Comment