DashDB is IBM’s cloud database. In a similar space as MS Azure or Azure DW RDB services, DashDB additionally offers a query-ready portal, upload and support for text and JSON files, and removes the burden of DBA/maintenance activity of keeping the growing DB well-tuned. While using the text file loading interface is convenient for quick prototypes and demos, being able to connect other applications, and especially data tools such as ETL and Reporting applications (CA, OBI, etc.), is a prominent requirement.
Outlined below are the steps required to set up an ODBC connection and additionally shows how this ODBC connection can be used with SSIS or the SQL Server import/export utility from a SQL Server 2014 instance. The same connection string were also successfully used in a DataStage connection.
Once logged into the DashDB portal, in the top right-hand corner, the familiar settings icon can be found. Clicking on it expands a menu containing the “Connection Info” option.
This link navigates to a page detailing the creation of a connection for ODBC or JDBC on Mac, Linux, Windows or PowerLinux. For a Windows installation, click to download the IBM Data Server drivers package.
On the right-hand side of the screen, IBM also provides the connection information.
Connections can be made with or without SSL. A connection using SSL requires downloading and installing the SSL certificate.
After downloading the executable, run the install package. IBM also provides a “Now get connected..” link on their Connect Info page, which are a set of commands that need to be executed prior to being able to create an ODBC connection.
These commands need to be run via cmd window on the machine which will host the ODBC connection and in the bin directory in the installation directory where the IBM Data Server drivers were installed. Depending on the machine and other software packages, this may be different. On a clean machine, using the default settings, the bin directory is found at C:\Program Files\IBM\IBM DATA SERVER DRIVER\bin. Open a command window, copy and paste the two commands from the website into the cmd window and execute. The successful return message for each command informs you of the entry being made. Now the ODBC connection can be made using the ODBC Administrator.
Simply click Add and select the IBM DBS ODBC DRIVER – IBMDBCL1.
Click Finish and enter a name for your DashDB instance (BLUDB).
Afterwards, configure your ODBC connection by selecting it in the ODBC window and clicking Configure.
In the Data Source tab, enter your User ID and Password.
In the advanced panel set Port, Hostname, DBALIAS and Database to the values provided on the DashDB portal. Also set ConnectTimeout and AutoCommit to the values shown.
Now the ODBC connection should be visible through any ETL tool installed on the configured machine.
Using the Import/Export utility, the ODBC connection is available via the drop-down box.
Opening the Properties… Window, enter the User ID, the password and BLUDB as the Data Source to successfully connect. No other information is needed.
In SSIS, drag an ODBC destination or source connection.
Double-click the connection, and on the Connection Manager task, click New…
Close the pop-up window and the BLUDB database tables should be available for selection. The mappings will display available columns.
Tables can be loaded row by row or via batch using the Data access mode drop down menu.
