Monday, September 28, 2015

Altering tables in the SQLDB service on Bluemix

My blog has been quiet for some weeks thanks to some offline vacation and due to some business trips. Last week I first spoke about DB2 at a conference, then coached developers at a Bluemix hackathon. At that Bluemix event I was asked whether it would be possible to change some column definitions for an existing table. It was easy to say "absolutely", but it turned out to be harder than thought because at first I couldn't how to do it. So let's document it... :)

Once you have provisioned a SQLDB database service on Bluemix and launch the administration console, you are greeted by the "Getting Started" screen prominently featuring the following three choiced:
IBM SQL Database: Getting Started

The "Run Queries" button is intended to perform SQL SELECTs, nothing else.

Trying to issue "ALTER TABLE" statements results in an error. As the title suggests, "Work with Tables" is the option to choose. It takes you to a simple interface as shown below:
IBM SQL Database: Work with Tables
When you select an existing table like "MYTABLE", the GUI will show the column and data type definition or the existing data:
IBM SQL Database: Table Definition
However, there is no option to change the table structure. Selecting the "+" in the "Create, drop, and work with tables" dialogue is to add a new table to the chosen schema, but is THE solution to modify/alter the structure of an existing table using the administration console. The reason is that it brings up a form to enter the SQL DDL statement to create a new table.
IBM SQL Database: Create a new table
That form can be (mis-)used to enter the ALTER TABLE statement like "ALTER TABLE MYTABLE ALTER COLUMN COL2 SET DATA TYPE VARCHAR(40);", hit the "Run DDL" button an be done.
IBM SQL Database: ALTER TABLE completed

It is not the nicest way to alter a table and not really straight-forward. With the SQLDB service evolving, probably there will be GUI enhancements coming in the near future. If you don't want to wait or don't want to hook up another database client to your SQLDB database on Bluemix, the workaround described above lets you quickly modify the table structure.