Connecting to dashDB
& DB2 on Cloud
Dean Compher
29 July 2016
Whenever I talk to people about connecting to our relational databases in the cloud like dashDB and DB2 on Cloud the first thing they ask is how to connect to those databases. I say keep in mind is that connecting to dashDB is basically the same as connecting to DB2 in your own data center. When was the last time you actually got to go into the data center and touch the server running your on-premises database? Most peoples’ answer is “NEVER”. They just get the hostname/IP address, Port and database name and connect to it. The biggest difference is that you don’t telnet or SSH into the dashDB box and make a connection. You connect through the dashDB console on the web or through a database connection. However, with DB2 on Cloud you can SSH into the box. In this article I will discuss connectivity and tools you can use to query and administer your data and some of the unique considerations for connecting to cloud databases.
If you are already connecting to a DB2 through an application, or using the command line tools like the CLP or CLPPLUS, or a GUI administration tool like Data Studio, then you only need to configure a new connection to add your connection to dashDB. You get the connection information from the dashDB console. I discuss that later in this article. If you are new to DB2/dashDB or wish to try a different connection method then keep reading as I will describe some of the tools you can use and what you need to download to use them.
· Drivers for Connectivity from Purchased or Developed Applications
· GUI Query and Admin Tools
· Command Line tools.
Drivers for Connectivity
There are a variety of IBM and non-IBM applications that connect to DB2. There are more generic applications like BI and report writer tools such as Cognos and Tableau, ETL tools like Information Server and Talend, and many customer applications. They all need some sort of driver and you can download those drivers directly from the dashDB Console or from the IBM Data Servers Driver site.
If wish to connect an application to dashDB and do not already have the dashDB/DB2 Driver then you can download the driver you want at no charge from the dashDB console or IBM drivers site. From the dashDB console, choose “Connect | Download Tools | Drivers and command-line client”. I’ll provide more details about the console in a later section. These downloads provide the CLI, JDBC and ODBC drivers your application needs to connect to dashDB. You just need to install the drivers and then configure your application to use them and then configure the connectivity to the particular dashDB database. See the dashDB driver package page for installation instructions. These drivers will configure your connectivity to easily use SSL/TSL for secure connections. If you are using DB2 on Cloud you will not have access to the dashDB console and some dashDB users may want additional driver options. In these cases an others, the best client and driver package for you may be one of the IBM Data Server Client Packages found at this link. The only difference I’ve noticed so far is you may need to do some additional SSL configuration for these. Visit the download page to get your free copy.
Many applications will not have a specific option for connecting to dashDB. For those, you should select the “DB2” option. It should be noted that IBM creates one set of drivers for all databases, including Informix, DB2 and dashDB, so don’t worry about not having the dashDB option since it uses the same driver as DB2.
With dashDB, you also get a number of other complimentary tools including InfoSphere Data Architect for data modeling, a few industry data models you can implement to begin building your data warehouse, and the Aginity Workbench which is a lighter weight database administration tool. You can down load them from the dashDB console by clicking “Connect | Download Tools” and clicking through the options presented.
Desk Top GUI Query and Administration Tools
With dashDB and DB2 on Cloud you get complimentary use of IBM Data Studio. This is a great tool for running queries, exploring the tables, schemas, views and other objects in your database and even doing most database administration tasks. You can just down load it, configure it to connect to your on-premises and cloud databases and begin using it.
Command Line Tools
While you can do many things with the GUI tools, there are still reasons for using the DB2 CLPPLUS and Command Line Processor (CLP) with your cloud database. Reasons include, personal preference, the fact that some commands like LOAD QUERY can only be run at the command line, or you want the ability to run DB2 commands in a Windows or Linux shell script. For more information on CLP and CLPPlus see my series of articles on how to use these tools including how to run DB2 commands from a shell script. For dashDB users you should note that you get the CLPPLUS utility with the driver download.
SSL Connection
DB2 and dashDB use the more advanced TSL encryption, but everyone still refers to it as SSL, so I will continue to just say SSL. SSL encrypts the traffic between your client and the dashDB or DB2 on Cloud database server. I highly recommend that you use SSL with cloud databases. With both DB2 on Cloud and dashDB, you can turn off the non SSL connection. For Data Studio, which connects to databases using a JDBC connection, using SSL is quite easy. You just choose the “DB2 for Linux, UNIX and Windows as the database (even for dashDB) and then select the “IBM Data Server Driver for JDBC and SQL J using SSL connectivity Default” as shown here:
It should be noted that I did not download the SSL certificate to get data studio work with the SSL connection. I also had to uncheck the Trust Store box.
If you wish to use CLPPlus to connect to dashDB and downloaded the driver from the dashDB console then you should just be able to connect to dashDB over an SSL connection using these instructions. If you want to use the CLPPlus or CLP from a IBM Data Server Client download to connect to dashDB or a DB2 on Cloud database, then you will need to perform a couple of other steps including installing the GSKit V8, downloading and installing the SSL certificate from the dashDB console and making a few DBM configuration changes using these instructions. If you are using DB2 on Cloud, then you can create your own certificate as discussed in the DB2 SSL Configuration page.
I had one other problem when I tried to make the SSL connection from the CLP in my DB2 10.5.6 database instance on my laptop. I’ve had it installed for years and added a fix pack or two over time. The problem was that the GSKit install was not in my path. So I went into the windows environment variables configuration and added this to my path: “C:\Program Files\IBM\gsk8\bin”. It was throwing an odd error, and took me a while to figure this out, so I recommend verifying your path before trying the connection.
If you use the IBM Data Server Manager (DSM) to monitor and configure your dashDB databases and wish to connect it to dashDB or DB2 on Cloud using SSL, then see this great article by Mark Latondre.
dashDB Console
The dashDB Console gives you many things. If you are running an application that connects to dashDB, the console is where you go to get the connection information including credentials that allow you to connect. As noted earlier, it is also where you go for downloading drivers, clients and the dashDB SSL certificate. Further, if you just need to explore your tables, load data from your desk top to do some administration, and run some SQL, then you may not even need a client on your desktop since the console allows you to do these things.
When you launch the console you will see the main menu that includes the Home, Tables, Load and other menu items on the left side of the panel as shown here:
The first one that you will most likely review is the “Connect” tab to get information you need for connecting your applications to your database. To get this click “Connect | Connection Information” and you will be presented with a screen similar to this:
I’ve redacted my user id and password, but this shows you enough to give you an idea about how to get your connect information and credentials. Please note that in the window above, you can select either the connection with SSL or the connection without SSL and you can download your SSL certificate here if you need it. For most uses of dashDB, I highly recommend that you turn off the non-SSL connection to make sure your communication with dashDB is encrypted. If you are the administrator you can click “Settings | dashDB Settings” and scroll to the bottom of the page. If you click the “Required” button, access through the non-SSL port (50000) will be removed.
If you do not have the URL to get to the dashDB console, then you should be able to access it through your Bluemix account. From Bluemix, you can launch dashDB and then proceed to the console and get these credentials.
At this point you should have the basics of what you need to connect any application to dashDB and DB2 Connect. As I noted earlier you can explore and create tables, load data and run SQL from the console. The high level tabs for these options are obvious choices from the console main menu. One handy tool that I will point out here is under the “Load | Load from Desktop” option is that if you have a file with the column names already in it, dashDB can actually create the table for you based on the column headings and data types it sees in the file and then loads the data. This is really handy when someone hands you a file and wants it in the database.
***
If you have any additional advice for connecting to cloud databases, please add them to my Facebook Page or my db2Dean and Friends Community and share your thoughts about them.