DB2, Halloween, and a Time Saver

A roadblock?

Well, right now I am busy with all kinds of things, juggling different database and cloud topics. Last weekend, Europe has switched back from Daylight Saving Time or Summer time to standard time, North America will follow this weekend. During this process, supposedly, you get one additional hour of sleep or time for other stuff. In my case it turned out that I spent the hour changing wall clocks, alarm clocks and watches. Anyway, here is a real time saver for DB2...

DB2 has the function TIMESTAMP_FORMAT (with synomy TO_DATE and TO_TIMESTAMP) to convert string values to timestamps or dates. The expected date and time format can be specified via the usual placeholders, e.g., MM for months or SS for seconds. However, there are some tricky parts to it and I am going to treat you with a solution (this was the "trick & treat" Halloween reference...):

db2 "values to_date('30.10.2015 00:00:00','DD.MM.YYYY HH:MI:SS')"
SQL20448N  "31.10.2015 00:00:00" cannot be interpreted using format string
"DD.MM.YYYY HH:MI:SS" for the TIMESTAMP_FORMAT function.  SQLSTATE=22007

db2 "values timestamp_format('30.10.2015 24:00:00','DD.MM.YYYY HH:MI:SS')"
SQL20448N  "31.10.2015 24:00:00" cannot be interpreted using format string
"DD.MM.YYYY HH:MI:SS" for the TIMESTAMP_FORMAT function.  SQLSTATE=22007

In both cases I am trying to convert midnight on October 31st to an internal timestamp value. Both calls return an error. The reason is that "HH" is the same as "HH12", the 12-hour format. Using "HH24" (24-hour format) everything is ok:

db2 "values timestamp_format('31.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

db2 "values timestamp_format('31.10.2015 24:00:00','DD.MM.YYYY HH24:MI:SS')"

1                        
--------------------------
2015-10-31-24.00.00.000000

  1 record(s) selected.

If you want to use the HH/HH12 format, you need to specify where to expect the so-called Meridian indicator (AM/PM):

db2 "values timestamp_format('31.10.2015 12:00:00am','DD.MM.YYYY HH:MI:SSAM')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

db2 "values timestamp_format('31.10.2015 12:00:00am','DD.MM.YYYY HH:MI:SSPM')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

That's all, enjoy the weekend and Halloween...

Utilizing User-Provided Services to Link Bluemix Apps to External Databases

Recently, I wrote a Bluemix app which only utilized one of the provided runtimes (Python, node.js, Java, ...). The database was external to the Bluemix environment and didn't come out of the many offerings in the "Data and Analytics" catalog. As I wanted to keep my app as generic as possible, I searched for a way of how to link app and database. One solution is to use so-called user-provided service. Here is what I did.

Once I had provisioned my runtime on Bluemix (Python for this example), the app showed up on the dashboard. As can be seen, it is not linked to any service. Because the database is not provided out of the Bluemix catalog, there isn't any entry or reference to the database yet. What can be done now is to create a Cloud Foundry user-provided service using the command-line interface (CLI):
 

App with Runtime only on Bluemix