How to make and use your own IBM Db2 inside IBM Cloud using free "lite" plan
IBM provides a cloud-based version of Db2 LUW. It is a full version of Db2 LUW Version 11.5. The Db2 in the cloud can be very powerful and highly available with plenty of CPU and storage. It just depends upon how much you want to pay. You pick the "plan" and you get what you pay for.
The "lite plan" is totally free and easy to use. It is a “full” Db2. It just has limits on storage and only allows one userid.
Ø The "lite" plan allows you to create your Db2 from home and experiment!
Ø That is why I created my Db2 in the IBM cloud.
Ø Fun from home with Db2 in the IBM Cloud! Easy!
>> read this article to make your own Db2 in the IBM Cloud
>> alternatively... you can use MY Db2 in the IBM Cloud... I give my server name, db2 db name, and port. All in this article below
>> some late breaking news. I just learned IBM and AWS now offer a Db2 on Cloud. not free. but still interesting. see bottom of this article for details
First, to be perfectly clear, and to provide proper and fair acknowledgement. I followed the instructions from Dan Luksetich on his excellent "db2expert" website:
https://www.db2expert.com/db2expert/db2-lite-on-cloud/
I followed his documented instructions.
But here in this article, I will attempt to include all the excruciating and obvious details with lots of screen shots to make it easy for YOU to create your own Db2 resource and then use it.
> I figure, better too much detail then too little!
Db2 "lite" plan limitations:
1. You must create an IBM Cloud account (process is described below)
2. You are given a single Db2 instance.
3. You are allowed (measly) 200MB of storage.
4. You are given one and only one userid or credential. You must generate a credential to connect remotely (i.e., from your laptop or home computer) to your Db2 database (in the IBM cloud)
5. You can have up to 15 connections (but all using the same userid)
6. Tables you create will be under the schema of the userid.
7. Performance could be variable because it is essentially a shared server and environment. But my experience has been that performance is fine and zippy.
8. There is no official support from IBM for the "lite" plan.
9. The Db2 instance will eventually expire and become deactivated after 30 or 45 days of inactivity. There is a grace period to re-activate the Db2. Basically, keep using it once a month and it will stay alive and active.
10. There is an IBM cloud provided web-based interface to the Db2. The web-interface is a bit boring but functional. No software required on your computer! Technically, you do not need to generate a userid or credential if all you want to do is use your Db2 via the web interface. Via the web interface you can do all the basics: Run SQL, look at objects. Visual Explain, etc
11. Optionally (and recommended), you can install a popular "database exploration" tool on your computer and use that local tool to access your Db2 resource on the IBM cloud.
important caveat about access to the IBM cloud: You will be required to access to the IBM cloud via the internet. Some "work" environments (VPN) may disallow the ability to ping or connect to the IBM cloud. I know it is a problem in my work environment. BUT, it should not be a problem from a home or personal computer on a regular home network.
links to documentation and IBM Cloud
IBM provides extremely adequate web-based documentation on the IBM Cloud and Db2
https://cloud.ibm.com/docs/Db2onCloud
> read the IBM documentation to answer your "cloud" questions (assuming my instructions miss something)
Do you have questions on how to use Db2 itself? Read the IBM documentation about Db2!
https://www.ibm.com/docs/en/db2/11.5
Lets get started!
How do I create an account with IBM Cloud to get my free "lite" Db2? start here:
You will then be sent to the IBM cloud "registration" page.
You must give them an email and you pick the password. They send you an email with the verification code. You enter the code and all other details.
Here you can see, I am using my personal email of "brian2@spufi.ca".
> And yes, I do own and pay for the amusing domain name of "spufi.ca".
> Google domains provides the service to me and they make it easy!
After the account is created. You will then be prompted to login with your new account email and password!
After you login to the IBM cloud. IBM Cloud will ask you to finish setting up your new account. They will ask for name and address and credit card information. It won't really be used. (They might put a hold of $1 to check it is valid)
And now, you will be in the IBM cloud. It will look like the below.
Click on "create resource"
The easy thing to do is to search for "Db2". And then pick Db2
You will then be asked some details about the Db2 you wish to create.
Important. Pick location of Dallas or London. Only they offer the free "lite" plan for Db2.
• This important trivia about where to find "lite" is cleverly hidden inside the IBM Cloud documentation for free "lite" Db2!! (https://cloud.ibm.com/docs/Db2onCloud?topic=Db2onCloud-free_plan)
After you click OK. You will be redirected to your IBM Cloud list of resources.
• Since we are chatting about resources. The symbol of "3 dot-dashes" on the left panel of the IBM cloud website is your short-cut to this resource list page.
Watch the "status" of your IBM cloud resource. The 'status' must move from "Provision in Progress" to "Active". This may take some minutes. Be patient. Refresh your screen several times. You might want to get a coffee.
When the Db2 resource is "Active" then click on your new Db2 Database name!
You will now pop into the page for your new Db2 database.
Important. The first thing you should do is create a service credential!
• This is poorly documented somewhere... but I am reminding you here, in this article!
After you click on "new credential". You will get a "new" credential!
• Be careful, the IBM Cloud documentation suggests you can create multiple credentials and userids. But the "lite" plan only allows one at a time. So if you create another credential, the previous is deactivated!
After the "new" credential is created, you can expand the credential detail and see the database userid and password. It will also show the database server name and port name.
And yes, the server name is insane and long. I will discuss again later.
Make note of these details for later use by your workstation database client tools.
• user: dpb74213
• password: sQTfgexND6eaz1h5
• server name: 98538591-7217-4024-b027-8baa776ffad1.c3n41cmd0nqnrk39u98g.databases.appdomain.cloud
• MY alternate server name (to be explained later): brian2db.spufi.ca
• port: 30875
Let us go into the web-based interface to your Db2! Click "Manage" on left and then the "Goto UI" button
The web based UI into your "IBM Db2 on cloud" is basic but effective.
you can start with SQL. Click "SQL" from the left side menu options
example SQL you can >
select CURRENT TIMESTAMP , SESSION_USER from sysibm.sysdummy1;
click the "run all" button to execute the SQL statement.
> optionally, you can pick the blue drop down and choose to "run selected". This is useful when you have multiple SQL in the SQL script window.
The IBM Cloud web-based UI has several interesting options beyond SQL.
The "data" exploration button (below the SQL button) lets you explore all the objects in YOUR Db2.
Further down the left, the "about" button on the side will remind you that you are using Db2 on Cloud in "lite" plan!
LET us move beyond the IBM Cloud interface to your Db2.
There are many other database tools that exist and you can have on your workstation/laptop.
Common Database Exploration Tools
· IBM Data Studio (from IBM, no longer being enhanced. It does have specialized functionality related to Db2 not available in other tools)
· DBeaver (open source -> becoming very popular today)
· DBVisualizer (free edition is commonly used)
· AQT (www.querytool.com > costs a bit of money but has some interesting and unique functionality)
· VSCODE with extensions for Db2 or any database.
If you are a serious Db2 user then you might already use IBM Data Studio at work. You might want to get IBM Data Studio for your IBM cloud Db2. You should know how to get it from the IBM website.
If you are a casual home explorer of Db2 then try the popular and open source DBeaver.
Ø The download and install of Dbeaver is trivial and easy. I will not get into details here.
But I will show you how to "configure" it to point to your new Db2 in IBM Cloud!
> The config instructions are the same for IBM Data Studio and DBvisualizer (and VSCODE)
First, download and install the Dbeaver software
After the Dbeaver is running. Click the "add new connection" button in the Database Navigator Window. Search and pick the Db2 for LUW driver.
Now enter the database server name, port number, database name (the database name is always "bludb") and the userid/password.
Do not click "test connection"!
Goto Driver Property tab and enter "sslConnection=true" and then go back and click "test connection"
Once you save the new connection. It will be available in the Database Navigator window.
Now you can double click on the connection name (in my case, "bludb 2") and you will be able to expand and look at your objects. You can also right click and "create new SQL script". Or you can click on "SQL Editor" and pick "create new SQL script".
You can enter any valid SQL and F5 for execute. Or click the orange triangle.
Let's create some sample tables for fun!
The Db2 does have all standard and required Db2 catalog tables and views and indexes.
But there is no application database and tables.
We can easily create a set of tables with data by going back to the website of Dan L and he has helpfully provided the world with the DDL and INSERT for the commonly known IBM sample database for Db2.
grab that SQL script from Dan and paste it into DBeaver or the web-interface and run the script!
https://www.db2expert.com/download/make_sample.sql.txt
• To be honest, I ran the 67 SQL statements via the web-interface. Using a web-interface is a bit faster for long scripts because there is no back and forth between the SQL script window and the database server.
I then "refresh" my web-interface or web-browser and I can see the new tables!
I can now begin to write new SQL and experiment with these tables!
BONUS. make a short and pretty name for your database server name!
I use Google Domains to manage my domain of "spufi.ca". I pay $17 per year for the domain.
It is relatively easy to add a new "cname" record with a nice short name that is easy to remember and type!
So I now have "brian2db.spufi.db" as an alternate name for the ugly server name given to me by IBM!
And now I have updated my Dbeaver! with the new server host name! Much prettier!
I also went to "general" and changed the connection configuration name from "bludb 2" to a more pretty "brian2db". The config name is just a name. But I like it better. see below
LATE BREAKING NEWS
Ember Brooks is a database blogger who posted a recent notification and reminder that IBM and AWS now have a Db2 offerring.
https://datageek.blog/2023/11/27/aws-relational-database-service-for-db2/
The offerring is not free... I have not tried it myself. But it does seem interesting.
The AWS blog has a clear step-by-step
https://aws.amazon.com/blogs/aws/getting-started-with-new-amazon-rds-for-db2/