Connect IBM DB2 with Helical Insight

How to Connect IBM DB2 with Helical Insight?

Helical Insight an Open Source Business Intelligence (BI) tool which supports IBM DB2. In this article we will cover in detail, how you can connect, analyze, share and use other features of Helical Insight with DB2 database.

Following are the steps to create report with Helical Insight using IBM DB2 Database:

  1. Datasource Creation
  2. Metadata Creation
  3. Adhoc Reports

Create Datasource

This process involves establishing a connection between Helical Insight application and a DB2 database.

There are 2 methods available for creating a Data Source in Helical Insight:

A. Front End Approach

B. Back End Approach

Method 1 : Front End Approach

  1. Login to Helical Insight Application. ( For default credentials, click here )
  2. Click on “Home” button and select “Adhoc” from the list.
  3. On entering Adhoc Page, click on “Create” button in Data Sources
  4. Select the “Data Source Type” from the given list. In this case, “Managed Data Source” will be used.
  5. Now, enter the following details as shown in the below image :

Data Source Details:

Datesource Name : DB2 admin  ( user-defined name )
Driver : com.ibm.db2.jcc.DB2Driver   ( IBM DB2 JDBC type 4 driver )
Hostname : 192.168.2.103
Port : 50001
Database : SAMPLE
URL : jdbc:db2://192.168.2.103:50001/SAMPLE
(JDBC URL FORMAT: jdbc:db2://<host>[:<port>]/<database_name> )
Username : db2admin
Password : db2pass

Here, username and password will be the user and password created during IBM DB2 server installation. To view details click here.

Note: If DB2 JDBC type 4 Driver is not present in the dropdown list then it can be added externally. Following are the steps to update driver list :

  • Download latest IBM DB2 JDBC Driver.
  • Once you have downloaded the driver, you need to put that driver in a “Drivers” Folder of Helical Insight Application
C:\Helical Insight\hi-repository\System\Drivers

and then immediately it will start appearing in the drop down list of Datasource. Click here for more details.

6. Now, click on “Test connection” to validate the filled details.

7. If the entered details are correct then “The test connection is established” notification is shown.

8. Click on “Save Datasource” to confirm.

9. Then, next step involves creation of metadata. After saving datasource, from the same page, click on “Metadata” (at the top right )

10. Then, click on “Create”.

Further steps will be covered in “Create Metadata” step below.

Method 2 : Back End Approach

  • Go to “hi-repository” folder where Helical Insight Application is installed and then open the folder.
C:\Helical Insight\hi-repository
  • Create a New Folder with any name say “IBM DB2” inside “hi-repository” folder.
  • Now, open a text-editor like notepad, notepad++ , and so on.
  • Then enter the following details as given below :
<EFWD>
<DataSources>
<Connection id="1" name="DB2 EFWD" type="sql.jdbc">
<Driver>com.ibm.db2.jcc.DB2Driver</Driver>
<Url>jdbc:db2://192.168.2.103:50001/SAMPLE/</Url>
<User>db2admin</User>
<Pass>db2pass</Pass>
</Connection>
</DataSources>
</EFWD>
  • Connection ID : User can assign any ID (numeric only)
  • name : Name of the Datasource to be shown in the Datasource list
  • Url : For creating a URL, use the following DB2 JDBC URL format
JDBC URL FORMAT: jdbc:db2://<host>[:<port>]/<database_name>
  • User: db2admin
  • Password: db2pass

For username and password refer to the below image. Username and password is configured during IBM DB2 server installation.

  • After entering the details, save the file as “filename.efwdNote: Saved file has to be present inside the IBM DB2 folder of “hi-repository“.
  • Now, the DB2 connection has been established. ( if entered details are correct )
  • Then, next step involves creation on metadata.
  • Now, login to Helical Insight Application using any web browser.
  • Click on “Home” button and select “Adhoc” from the list.
  • Then, Click on “Metadata” Tab and then select “Create”.

Further steps will be covered in “Create Metadata” step below.

Create Metadata

For creating a metadata it is mandatory to have established datasource connection which is explained in create datasource.

  1. On clicking “Create” from “Metadata” tab, “Datasource Type” selection page is displayed.
  2. Select the “DataSource Type” from the given list.
  • For Method 1 : Select DataSource Type: Managed Datasource
  • For Method 2 : Select DataSource Type: Plain JDBC Datasource

3.Click on the Datasource Name using which metadata will be created.

  • For Method 1 : Datasource Name “DB2 admin” is used. Any name can be assigned during configuration.
  • For Method 2 : Datasource Name “DB2 EFWD” is used. Any name can be assigned during configuration.

4. Now , a metadata page appears where various operations can be performed such as table/column aliasing, defining joins, creating views and apply table/column/data security conditions.

4. Click on “Save” to create metadata.

5. After clicking “Save” button, a window will appear known as “File Browser”. Using File Browser, a metadata file is saved in the file-repository of Helical Insight.

6. Then, click on “Save Metadata” to confirm.

7. Now, metadata file is ready for creating Adhoc Reports.

Adhoc Reports :

Learn how to create Adhoc Report, click here

For further assistance, kindly contact us on support@helicalinsight.com or post your queries at forum.helicalinsight.com

One clap, two clap, three clap, forty?

By clapping more or less, you can signal to us which stories really stand out.