Use the Db2 on Cloud Lite Plan in Hackathons

How to quickly get started for free with Db2.

One of the many services on IBM Cloud that offers a Lite plan is Db2 on Cloud. It is a managed database (DBaaS) without costs (in the Lite plan) and great for testing. In that plan option, it is not meant for any production workload and comes with restrictions. Still, it is a great database option during hackathons. In this blog post, I want to touch on what you need to know to get started, what to consider, and how to connect from Python, Node.js, and Go apps.

Overview: Db2 on Cloud

IBM Db2 on Cloud is the managed version (DBaaS) of IBM's relational database system, Db2. Db2 supports Linux, Unix, and Windows environments, and the cloud offering is based on Linux. 

Db2 on Cloud is configured for transactional workloads, whereas the offering IBM Db2 Warehouse on Cloud suits analytic workloads. In the early days of IBM Cloud (then called Bluemix), the offerings were called "dashDB" and this is the reason you sometimes see references to dashDB. A third offering, IBM Db2 Hosted, provides customers all the features of Db2 in the cloud. As the name implies, you get access to Db2 in a hosted environment, and it is managed by you.

After provisioning, you can access Db2 on Cloud, create and manage objects, load data, and query the database through the browser-based dashboard or by connecting the service instance to apps or external tools. You can see connection details and links to client software for download in the dashboard under CONNECTION INFO.

Using the free Lite plan if Db2 on Cloud and working with restrictions

IBM offers a free "Lite" plan of Db2 on Cloud. It is a great way to learn Db2, test SQL statements, or develop applications. The Lite plan is not designed for production use and is the only service plan with a multi-tenant architecture. It also has a couple other restrictions with an impact on what you can do, but it is a great offering, nonetheless, and I use it for testing SQL features and answering Db2 questions on Stack Overflow. Here are some restrictions you should be aware of.

Reactivation

To continue using the free Db2 service, you'll need to reactivate it every 30 days by clicking a link sent to you. This is mainly to easily identify unused resources and to clean up. If you forget to click the link or are off for vacation, the data is not gone but stays around for 60 days. I just recently ran into this and even my (activated) query history was present.

Regular user

In the Lite plan, you have regular user access to parts of the shared database. You do not have any Administrator privileges. Thus, adding other users or even service IDs—as I discussed in this blog on Db2 security and information security—is not permitted.

Single schema

A big restriction in terms of what you can do with the Lite plan is linked to the multi-tenant architecture. You are not permitted to create any schemas and, therefore, are restricted to creating objects in "your" schema only. 

A schema is a collection of objects. The schema also is a name qualifier—the schema name makes up the first part of a schema-qualified (two-part) identifier. Given a schema HENRIK, I could have two tables with identifiers (names) HENRIK.TABLE1 and HENRIK.TABLE2 or, e.g, FOO.MYTABLE and BAR.MYTABLE. Because of the restriction, creating objects like in the latter example is not possible. 

How do you find "your" schema name? You can either take a look in the Db2 dashboard under EXPLORE > Tables and look for a non-system schema or generate a set of credentials and look for the username. The following screenshot shows the schemas with XD...42 being the assigned username and schema.

List of available Db2 schemas.

Given that my assigned username and schema was something like XD...42, I tried to create a table HENRIK.MYTABLE and ran into the following error:

Db2 error: "IMPLICIT CREATE SCHEMA" privileges missing.

One way to address the schema issue is to leave out the schema part for the table identifier; the other would be to explicitly use the assigned name. By only using MYTABLE as the name, I succeed in creating the table:

Successful execution of "create table."

In my sample statement I used lowercase mytable as identifier. Note that it is automatically converted to MYTABLE and is considered an ordinary identifier. If you use quotes around the string, it is a so-called delimited identifier and it is kept "as-is." Therefor, MYTABLE, "myTable", and "MyTable" all are different identifiers. If you are new to relational database systems, this sometimes leads to confusion and objects not found.

Storage and compliance

The Lite plan includes 200 MB storage capacity. In my experience, it is enough for testing. Because of the multi-tenant architecture, it does not provide HIPAA compliance or support for the European GDPR. As already pointed out, use the Lite plan for testing only and not with (real) production data.

Programming resources

The Db2 dashboard provides links to database client software. It also shows the customized connection string for your service instance and user. You can use that information to configure your apps to connect to Db2. I always recommend the regular Db2 documentation on developing code for accessing and managing data to obtain an overview of what is supported and possible. It has sections on programming in Python, Node.js, Java, and other languages, working with ADO.NET or ODBC, and on application design.

Many of the Db2-related SDKs are provided as open source on GitHub. It includes SDKs for Python, Node.js, Golang, and more. Db2 on Cloud also has a REST API which you could use to perform some tasks within the restrictions of the Lite plan (e.g., execute SQL statements or load data).

If you use Microsoft Visual Studio Code, you can install the Db2 extension and connect to Db2 from within the IDE.

Get started with Db2 on Cloud

Db2 on Cloud is a managed relational database service. Its free Lite plan is a great backend for developing apps and testing, making it well-suited for hackathons. The only thing to keep in mind are the restrictions of the Lite plan and how to work with them. There are Db2 SDKs and code samples for many programming languages, so it is easy to get started. 

If you want to see Db2 on Cloud in action, I recommend the following IBM Cloud solution tutorials. They make use of Db2, provide full end-to-end instructions, and the app code on GitHub is ready for reuse:

• SQL Database for Cloud data: How to use Db2 as database with a Python Flask app.
• Build a database-driven Slackbot: Use Cloud Functions for webhooks to query Db2 from a chatbot built with IBM Watson Assistant.
• Combining serverless and Cloud Foundry for data retrieval and analytics: Use Cloud Functions to retrieve web access statistics and store them in Db2 and a Python Flask app with IBM Cognos Dashboard Embedded for analytics.

If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.