A bit of a warning; this article was written the week of 10/18/2021. I started working with the IBM Cloud and Db2 Lite on Cloud in March and in the past 7 months things have changed and continue to change significantly. So, some of the experiences and thoughts in this article may not be valid at the time in which you are reading the article.
Intro
I have, for several years, maintained a copy of Db2 for LUW on my own personal computer in an effort to facilitate my continuing education, and also have a data server available for giving demonstrations during classes I taught or presentations given at conferences. I typically had installed Db2 Express-C or Db2 Developers Edition, installed the sample database, and wrote queries, stored procedures, and created several different schemas and data. There were certain challenges with maintaining this data server on my personal computer. I had to apply Db2 maintenance and perform upgrades, and whenever I bought a new computer I had to go through the entire installation process again. If a seriously messed up the configuration it was sometimes a long a painful process to get things straightened out. There was also the issue of security, which always led to confusion!
In recent times the Db2 “free” offering for personal use has changed somewhat. There is now the Db2 Community Edition, which if I understand correctly is a containerized Db2 or a Db2 data server that you can install on a personal computer directly. There is also the “free” Db2 Lite on Cloud that was brought to my attention by Tanmay Sinha, Vinayak Agrawal, and George Baklarz on the March 14th, 2021, edition of the Db2Night show. The biggest selling point to me was when George Baklarz stated that Db2 Lite on Cloud is a true version of Db2 and works just like if you had it installed on your own computer. More on this later in the article. I decided to rid myself of the burden of maintaining a Db2 data server on my personal computer and explore using Db2 Lite on Cloud, hopefully to the same result!
Creating an IBM Cloud Account
The first step on the road to Db2 Lite on Cloud is to set up an IBM Cloud account. For this you will need an IBM userid, which I already had at the time I set up my IBM Cloud account. You will also need a credit card! Unfortunate, but true. However, setting up an IBM Cloud account is free, and the credit card is on file so they can bill you for any paid services that you sign up for. This to me means that you must be careful when signing up for a trial because you might be suddenly charged for services when the trial expires.
The main page for the IBM Cloud is a dashboard, and from the dashboard there are a myriad of options for resources and services that you can provision on your IBM Cloud account. I personally have not explored any of these resources and services, except for Db2.
Support
There have been quite a few bumps in the road while trying to use my Db2 Lite on Cloud resource. Most issues having to do with security and some having to do with functionality. However, IBM Cloud support has been extremely responsive and dedicated to improving. Overall, I am very pleased with the support I’ve been given although they have not always been able to solve my issues, which in one case resulted in me contacting Db2 development directly. In another instance IBM support gave up on an issue and I simply deleted my Db2 Lite on Cloud resource and started over. Despite these shortcomings, I have to say that this offering is getting better all the time and the responsiveness of the support team has been outstanding!
Provisioning, using, and Destroying a Db2 Lite Service
Provisioning
From the IBM Cloud dashboard, you can navigate to a resource summary, pull up a resource list, or create a new resource, amongst many other activities. From the dashboard or resource list there is the option to create a new resource. Selecting this option brings up quite an extensive list of close to 400 products, including Db2. Selecting the Db2 resource brings up three options: Lite, Standard, and Enterprise, with Lite being the free version. Db2 Lite is deployed on a shared multi-tenant system, and while it is backed up and kept current with maintenance you have no control over these aspects. This means that the user is responsible for backing up their database schemas and all the data, and if you want to use a new feature you may have to wait a few weeks or months for the maintenance or new version to be applied. You will not be able to ask cloud support to restore your data if you accidentally delete it or inadvertently drop some tables.
There are limitations to the Db2 Lite resource. First, there is a limitation of 200MB of storage and 5 simultaneous connections. Additionally, you must renew your Db2 Lite plan every 90 days. Finally, if you do not use the resource for 30 days it will be deleted automatically (make sure to save your data). It typically takes only a few minutes to provision a Db2 Lite resource and that is cool.
Using Db2 Lite on Cloud
Once provisioned, the Db2 Lite resource should appear on the resource list in the Services and Software category on the IBM Cloud dashboard and selecting the resource will navigate to the details of the resource. There are also links to documentation and support which come in quite handy. You then must create service credentials before you can access the Db2 Lite resource or connect to the Db2 Lite resource from any other client, be it cloud based or on a physical machine. From the IBM Cloud resource details page for the Db2 Lite resource there should be a “Service credentials” link. Following this link will enable you to create a credential that will be used by the cloud-based user interface, as well as by external applications to connect and exploit your Db2 Lite resource. The credentials include the connection string, userid, password, and SSL certificate details necessary to enable external processes to connect, as well as allowing cloud access to the resource.
Once service credentials are created, selecting “Go to UI” from the details of the Db2 Lite resource should open in a new window or tab into another dashboard (IBM Db2 Data Management Console subset). This Db2 dashboard enables you to monitor database connections, monitor storage, execute SQL statements, explore database objects, create stored procedures, UDFs, and more.
Using the service credential information, I was able to connect IBM Data Studio and the Db2 Connect Extension to VSCode to the Db2 Lite resource and interact with the resource as if it was a typical Db2 data server to a certain extent (read on for details). The only types of connections allowed are secured connections, so you need to download a certificate from IBM. The easiest way to download the certificate is from the Db2 on Cloud UI, that was described earlier, and select “administration” from the dashboard. Once the certificate is downloaded it must be enabled according to the software you are using to connect to the Db2 Lite resource. For IBM Data Studio this means installing the certificate into a Java keystore and defining a secure connection using that keystore. For the Db2 Connect Extension to VSCode this means installing the IBM Data Server Runtime client and including the path to the certificate in the connection definition within VSCode.
Using Db2 Lite on Cloud is not the same as having your own copy of Db2 Community Edition on your personal computer. There are certain functions for which you do not have authority, one example of which is you cannot create other schemas than your own. You can only create objects under the schema of your userid of your connection credentials. Possibly the biggest issue is the inability to run local system commands, as there is no interface for doing that. This limits functionality to SQL based statements. One major disappointment is that it is not possible to run the db2sampl command used to create the sample database. When I discovered this, I sent an email to George Baklarz of Db2 development and he shared some DDL with me and gave me permission to include it in this article. So, look to the end of the article for the SAMPLE database DDL. The user also has limited or no authority to certain IBM supplied views and table functions so monitoring of statements, connections, tables, etc. is next to impossible. Visual EXPLAIN cannot be run from IBM Data Studio due to authorization issues against the SYSTOOLS schema. As of the writing of this article it appears to be impossible to create EXPLAIN tables and run EXPLAIN although the cloud dashboard offers it as an option!
Deleting the Db2 Lite Resource
If you ever need to start over with your Db2 Lite resource you can simply delete it from the list of resources within your IBM cloud console and, poof, it is gone. If you like then in a few minutes you can get yourself a brand new Db2 Lite resource, which is what I had to do when my account (the service credential userid) got locked and there was apparently no way to unlock it. Nonetheless, being able to allocate and delete a Db2 Lite resource in minutes is convenient and useful!
Summary
While there are some severe limitations within a Db2 Lite on Cloud resource it is extremely useful to have a place to test some design ideas, create some tables, indexes, user-defined functions and stored procedures. If you have a need to execute local system commands, do more intensive monitoring, or run EXPLAIN you are better off installing Db2 Community Edition locally on your own computer.