Bluemix Cloud Databases
29 January 2015
You can now very easily deploy IBM databases including DB2, Informix and Cloudant in the IBM cloud and it is free for a few months. This new cloud service is called Bluemix. It is a development platform and lets you develop applications in any language you like and just deploy databases with a click. For the database administrator this is great because you deploy databases quickly without needing any hardware when you want to try something new. Let’s say you want to try out DB2 BLU and experiment with it. With Bluemix, you can just deploy “DashDB” and you are ready to create your tables, load your data and start experimenting. If you want to start dabbling with application development, your Bluemix account lets you do that too and provides lots of languages that you can just start using without having to install any compiler or interpreter yourself. It even lets you develop iOS applications for your iPad or iPhone and connect them to your Bluemix database.
There are tons of things you can do with Bluemix that this short description does not reference at all. Since my site is about database related information, I take a database focused approach to the article. However, Bluemix does much more by allowing you to develop applications in the cloud and then to run them in a production operations environment. For more information, please see the Bluemix Documentation Site. You can read this documentation without registering.
Sign up is easy and you currently get a FREE trial for about a half a year! There are size limits for the free databases, but those limits are not too tiny. See the Bluemix Documentation Site for details. Enrolling is especially easy if you already have an IBM id. This is the ID you use to open PMRs, download fix packs and other things. Just go to the Bleumix start page at http://bluemix.net
Then click the “Sign up for free’ button and then enter your IBM id and an e-mail will be sent to you in a few minutes with a link to complete your registration and the terms for the trial. You are then ready to start using it.
Since Bluemix is intended to be a development and development operations platform it does not use terms like DB2 or Infomix, but those are the underlying technologies. Here is a cross reference of the Bluemix names with the database names. Click on the link to see the amount of free space you get and cost for larger databases.
DB2 with BLU – Generally used for analytics databases
DB2 without BLU – Generally used for transaction (OLTP) oriented databases
Informix with Time Series enabled
Cloudant JSON document store.
Further, Bluemix is optimized to bring many components together in projects to build applications including development tools, preloaded applications and functions that can be called by applications you are building and, of course, databases. These projects are called “spaces”. You can start by either creating a space and then choosing components like a database into them or you can just start configuring a database. I created a space called db2Dean just for grins. Having a space for a set of applications and databases is a great way to experiment with creating applications to use with your database – you can play around with building applications and you don’t have to install any development environment. You can read more about this at the Bluemix Documentation Site.
So to start working with one of the databases, just sign into Bluemix. Then make sure the DASHOARD tab is selected, then select SERVICES on the left pane and the click the ADD SERVICE button. I have circled these items in the picture below:
This will bring you into the catalog with all of the services from which you can choose. They are divided into groups. The databases are split between the Big Data and Data Management Groups. In the table above, I show which databases are in which of these groups. In this example I am going to deploy DB2 by choosing the dashDB button in the Big Data group. This brings up a dialog box that lets me choose some attributes of my database or let them default. I’m going to change the service name to DashDb2Dean and let the rest default including choosing the “Entry” alternative for a free 1GB database. I then clicked the CREATE button and in a few seconds I had a DB2 database ready to go.
The next panel shows the options for your database including reading more about “dashDB”. Here, I looked the “Learn” information then clicked the Launch button. That brings you to the welcome console, part of which I am showing below. You may need to scroll down to see these tools when you are connected to your own dashDB database.
As you can see in the launched screen above dashDB gives you easy tools for loading, viewing/creating and querying tables. By clicking the “Connect Tools” option, you can also connect your own client tools like IBM Data Studio, the DB2 CLP, or even Excel, Cognos, or any other application that can connect to a DB2 database.
Allows you to upload Excel spreadsheet files or delimited files to the Bluemix site and it will create a tables for you and then load the data. This is quite easy for the database novice or even the DBA who has some data in a file and needs it in a database table in a hurry. There are similar tools in the other databases.
Work with Tables
Allows you to view the tables and their attributes defined to your databases as well as to write CREATE commands like CREATE TABLE. You can either type the DDL into the window or paste DDL you wrote elsewhere.
Allows you to create SQL and run it against your tables in the SQL database.
Provides all information you need to connect to your new dashDB database including port and IP Address and your userid and password.
Since many of the readers of db2Dean.com are already used to using their own tools for managing the database and querying the data so I’m going to focus on those, but you should at least look through the tools provided as they may be the easiest to use for a number of situations. As with any DB2 database on a server you just need the IP address, port and database name to connect. One nice thing about the dashDB option is that it comes with a number of sample tables that are preloaded and that is great for if you are just kicking the tires since you can do things like view the tables and select data without loading anything yourself. All of these tables are BLU (column organized), so if you want to see examples of the objects in a BLU database this is a great way to start. You can add your own tables if you like and get rid of the sample tables if you want.
By clicking the “Connect Tools” from the dashDB launched screen, you can get the information you need to connect as you can see here:
Using the connection information above, including user id and password, I was able to connect to my dashDB using IBM Data Studio. You can see some of the connection attributes circled in red in the Administration Explorer view below. In this window, you can also see that I was able to get a list of tables and show that the ORDER_DETAILS table is column organized.
Having your IBM Data Studio connected to your dashDB database, you’re ready to explore your database. As far as I can tell, you can look at anything you like such as DB and DBM configurations, tables, tablespaces, and anything else. However, you will not have authority to change or create some objects due to the nature of dashDB being a fully managed cloud database. For example you will not be able to change some DBM configurations because the instance operation is controlled by the system managers. You will just get an authorization error if you do. Also you can create tables and other objects as long as you create them in the schema that corresponds to your user id. For example, you will notice from the connection information above that my user id is “dash100040” so I can create a table called dash100040.db2dean_table, but I would not be able to create a table called gosales.db2dean_table. You cannot create new schemas either. These restrictions are only as of the writing of this article. New features are being added and restrictions lifted a very fast pace.
If you prefer using the command line to connect to your database then the supported vehicle is the CLPPLUS tool on your DB2 Client or server. You use the same connection parameters shown in the dashDB connection tools. In my case using the connection information shown above, I was able connect using the string “connect dash100040/MQn8XWReCRJ0@22.214.171.124:50000/bludb”.
While the CLP is not supported for running queries, you can use it to catalog your database and bind the CLI packages for your version of the DB2 Client. Again with the connection information above I was able to use the following commands to catalog my DB2 CLP to the database using the following commands:
catalog tcpip node db2dean remote 126.96.36.199 server 50000
catalog database BLUDB at node db2dean authentication server_encrypt
db2 BIND @db2cli.lst BLOCKING ALL GRANT PUBLIC
I want to call your attention to a few things in the set of commands and responses above. You must user the SERVER_ENCRYPT authentication on the catalog database command since that is what the dashDB requires. Also note that the query that worked in the CLPPLUS window did not work in CLP. The error is because the CLP package that is in the db2ubind.lst is not bound to the database. Further you do not have authority to bind that bind list so running queries from the CLP will not be possible, at least for now. However this is a good way to catalog the dashDB database and bind the CLI packages for your version of the DB2 client. The CLP may work if you are using a DB2 10.5.5 on Linux (and other 10.5 fix packs) since those binds have been done for you, but it is still not supported.
While dashDB has some really nice features like having sample data already in place, and R statistical functions built in the other database offerings in Bluemix may be of more interest to you such as the SQL DB that is configured for transactional workloads where dashDB is for analytic workloads. Please note that other levels these two databases have the Optim privacy UDF’s built in so you can obfuscate data in these databases or while loading them using the same algorithms that the full Optim software uses. If your expertise is more in the Informix or Cloudant areas or you want to get to know those areas then deploy those databases instead. Deployment and use of these are very similar to what I have described earlier in this article for dashDB. There are currently free trials so if you want a sandbox to try something out that you can’t do in your own shop, or just want an easy way to get your hands on a database that you can experiment with, then give one or more of these a whirl.
Please note that to when you first launch SQL DB you will not get a user id and password immediately like you do for dashDB. The credentials are assigned when the SQLDB service is bound to an application. You can create an application from one of the boiler plates available and associate the SQLDB service to it (even if your app doesn't do anything with the SQLDB service yet). To do this just click the “+ Add a Service” button and choose one. I chose Web and then PHP. Then once it finishes creating the service, click the “+ Bind a Service” button and then choose your SQL DB. Now you will see a box at the bottom of the screen for SQL DB. In the lower left corner you will see a link called Show Credentials. You can see your user id and password in the information you get by clicking that link. You don’t have to do anything else with the application. You can now go ahead and connect to your SQL DB just as I described for dashDB above. Thank you Sal Ledezma and Frankie Lee for showing me how to find these credentials. For more details on SQL DB please see my Bluemix File to Table Conversion article.
There are also a number of Information Server Components like DataStage available if you want to transform or cleanse your data as it is loaded into your Bluemix database. They are in the Data Management section of the catalog under the DataWorks object. Again if you are a DataStage developer and need a sandbox to run some experiments, then this is a great avenue. There are also several non-IBM databases available to deploy in case you are forced to work with a lessor data store.
I believe that it is definitely worth taking a few minutes to sign up for Blue mix and trying out the tools. If you find something interesting, please take a moment to tell the rest of the community about it on my Facebook Page or the db2Dean and Friends Community.