Db2 Warehouse on Cloud - my new "daily driver"

Body

There's a saying amongst car fans to designate the vehicle that they use for general transportation - the daily driver.  While exotic cars may be suited for the race track, or for special occasions, the daily driver is reliable, convenient and straightforward to use.

I had been using Db2 (formerly DB2 for Linux, UNIX, and Windows) for years to maintain business process information and perform ad-hoc queries... about Db2! I need to be able to answer questions like "how many Db2 V11 PMRs were opened in the last 3 months", or "which customers opened Db2 9.7 or Db2 10.1 PMRs between January and April 2017".  I maintain a simple database of Db2 Support information to help me answer such questions, augmented by weekly updates in the form of CSV files.

Since the beginning of 2017, I have moved from using a local installation of Db2 on my Windows laptop to using dashDB for Analytics (now known as Db2 Warehouse on Cloud) as my daily driver. This blog entry describes my simple use case and how dashDB together with DSM let me get my work done very quickly.

For somebody coming from Db2 it is very easy to get started with dashDB. See these instructions for how to sign up for Bluemix and create a dashDB service. 

Once you have set up your dashDB service, loading data is really simple. This tutorial takes you through the steps to load a file from your desktop using the Load utility in the dashDB console.

In my case I want to load data from CSV files. The dashDB load utility makes this very easy by defining a table with column names taken from the first row of the CSV file and by making intelligent default choices for the types of each column. You can specify the format of date columns. A few clicks and your data is loaded into a table in dashDB.

Now that you have the data loaded, you have a couple of choices for running queries. You can run queries directly in the dashDB console from the Run SQL tab:

I prefer to use DSM to run queries because it allows you to save scripts "natively". DSM is a powerful tool for administering and monitoring a variety of data servers, including my old daily driver (Db2) and my new daily driver (dashDB). If you haven't used DSM before:

• You can download DSM here
• Instructions for setting up DSM

Once you have DSM set up, it's easy to configure it to manage your dashDB instance:

• in DSM, select Settings -> Manage Connections and click on Add in the Database Connection tab
• You'll see the Add Database Connection dialog.

• Select dashDB as Data Server Type
• To get the credentials (host name, port number, user ID and password) for your dashDB instance, go to the BlueMix dashboard:
  • Select the entry for dashDB for Analytics in the Services section
  • Select Service Credentials
  • In the list that appears on the right select View Credentials

• The credentials for your dashDB instance appear in JSON format. Enter these credentials in the DSM Add Database Connection dialog and click on Test Connection to validate the connection, then OK to complete it.

You can set up multiple database connections in DSM and use the dropdown list at the top of the DSM window to select which connection you want to use:

Once you have selected your dashDB instance, you can create and save SQL scripts to run against it using the Run SQL tab.

This blog has described how easy it is to set up dashDB, then load data into it and run queries either directly in the dashDB console or in DSM. For somebody who is familiar with Db2, dashDB makes an excellent daily driver - it's convenient, comfortable and it definitely gets the job done.