Create and query DB2 Linked Server in SSMS

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

https://www.microsoft.com/en-us/download/details.aspx?id=52676

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');

(You should use the name given for "Linked Server" within the [])

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]

Here the
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

Choosing color scheme for Power BI reports

Choosing beautiful colors for an attention seeking Power BI report is always challenging. Sometimes we may have an important message but it might go unseen if proper color scheme is not used, so it is important to create reports which are informational and visually appealing. In this blog our main objective is to get an idea about selecting appropriate color scheme and tools that can be beneficial to do this. What is a color scheme? As you all know not all colors look great together and you can tell stories by just having only two colors, have a look at the following image, there in the middle you can see a circle with same color and same dimension, only thing that changes is the back ground, but depending on the back ground the way you see those circles are totally different. Some small circles appear softer, some appear sharper, and some are brighter also you might notice movements or depth effect too. It is important to choose colors wisely to serve the purp...

Ragged hierarchy in Power BI

Case- I have a data set which has to be visualized in a ragged hierarchy. What is ragged hierarchy A ragged hierarchy is a user defined hierarchy with an uneven number of levels. You can see the above table which has 5 levels of hierarchy. In the first level we only have “World” as a category then moving forward to the next level we have two categories namely “UK” and “USA” likewise the level of hierarchy is drilling down up to five levels with an uneven amount of categories. Creating a hierarchy in Power BI In the above image you can notice that I have right clicked the “level 1” column which has to be the first level of our hierarchy, Once it is right clicked there you will notice “New hierarchy” as the second option then you will have to click that in order to create a new one. As you can notice in the above image once I have created a new hierarchy then a new column is created named “Level 1 Hierarchy” in that I have d...

The new “Sync Slicers” is it better than Drill through Feature in Power BI ?

Power BI is a visualization tool for intelligence analysis. In my personal carrier I have been working with this tool for over a period of half-year and the tool is very user-friendly with engaging UI and an easier learning curve. For the past few months, the arising question is to have a better filtering mechanism throughout the reports with multiple pages in power BI, so that people can precisely view what they want. This has been earlier enabled by the introduction of the drill-through feature which requires some hassle for the end-user to navigate to a specific point which involves multiple key presses and it is not a healthy experience in an end-user perspective. The newly introduced sync slicers seem to be a better alternative than the drill through but how far does it fit and is it really better than the drill through? Let’s dig more on to this, and it's you to decide. First of all, I would like to say what does the sync slicers exactly does. “Sync slicer...

Ajan's BI Blog

Search This Blog