At the end of November 2023, Amazon made the IBM Db2 database available on AWS, as an engine for RDS. I’ve never used Db2, so I thought it was worthwhile to give it a try.
I was under the impression that Db2 was a legacy database, I’ve only encountered one place in my career that used it. But it seems to be still in use.
Db2 is ranked #8 on the DB-Engines ranking, #5 for relational databases.
Db2 has been around since 1983, and IBM has database products available even before that. Ted Codd (Who came up with the relational model) worked for IBM.
Versions and Deployment Options:
There are three versions of Db2: Community, Standard, and Advanced (The Enterprise Data Warehouse version.
Along with AWS, IBM also has their own IBM Cloud offering. We can also run it in a Docker container.
Versions are available for Linux and Windows, as well as IBM AIX, which is the IBM version of UNIX.
Installation:
You can create an IBM account to get the Community edition of Db2. They do ask for your email, phone, company, and title to create the account. You’ll also be enrolled to get messages from them (Either email or text), although you can unsubscribe from those.
I’m installing on Window 11.
The download was a 1.3 GB ZIP file. After unzipping, run Setup.exe. That brings up a “Launchpad”. Click “Install a Product” on the left, then click the “Install New” button under the Community edition section. I took the default settings. On the admin account page, you’ll need to set a password. On the Notifications page, I did uncheck the option to send notifications. I also unchecked the “Enable operating system security option”.
The defaults create an instance DB2 that uses port 25000.
Once the install has completed, there’s a page with links for getting started, product updates, etc. Go ahead a click “Create sample database” so that we’ll have a db to connect to.
Connecting:
There are IBM Data Studio and Db2 Console tools that can be used to work with Db2.
I like to use the DBeaver query tool. To create a new connection in DBeaver, select the Db2 for LUW (Linux, Unix, Windows) option. Once the connection page comes up, click on the Driver properties tab to get the Db2 driver, if you don’t already have them.
Since we created the sample database, we’ll connect to that.
Host: localhost
Port: 25000
Database: SAMPLE
User: db2admin
Password
Setup:
By default, our admin user won’t have certain permission, like being able to create a schema.
We can use the command line to grant this permission. First we need to run this command on the command line or Powershell:
db2cmd -i -w db2clpsetcp
This will set an environment variable so that we can use the db2 command line tool. We’ll need to do this for each session.
We’ll run this command to start the db2 tool:
db2
We’ll connect to our SAMPLE database:
CONNECT TO SAMPLE
And finally grant out admin user DBADM permissions:
GRANT DBADM ON DATABASE to db2admin
I wasn’t able to create a database in DBeaver. Starting a new command line session where I wasn’t connected to a database allowed me to issue a CREATE DATABASE command.
Using Db2:
Db2 uses schemas as namespaces within a database.
The default install is case sensitive. Like Postgres, any object names get converted to upper case. We can use double quotes to delimit any object names, if needed.
SQL Examples:
Creating a table:
CREATE TABLE Test_Schema.Test_Table ( ID int GENERATED ALWAYS AS IDENTITY PRIMARY KEY, Name varchar(50) NOT NULL UNIQUE, Balance numeric(6,2) NOT NULL, Is_Active boolean NOT NULL, Created_Date TIMESTAMP DEFAULT CURRENT_TIMESTAMP );
We can use GENERATED ALWAYS AS IDENTITY to create an identity columns, which is creating a sequence as well. GENERATED ALWAYS means that the database will set each ID value, the user won’t be able to set those.
For TIMESTAMP, the Db2 version I’m using doesn’t support WITH TIME ZONE. I didn’t see where there is a time zone aware datetime data type available.
Insert records:
INSERT INTO Test_Schema.Test_Table(Name, Balance, Is_Active)
VALUES ('TestRecord1', 50.05, TRUE), ('TestRecord2', 25.00, TRUE);
Db2 wouldn’t run multiple INSERTs at once, but this format worked fine.
Comments:
-- Comment /* Comment */
Select the first record with Name in alphabetical order:
SELECT * FROM Test_Schema.Test_Table ORDER BY Name LIMIT 1;
Get current date/time:
SELECT CURRENT_TIMESTAMP FROM SYSIBM.SYSDUMMY1;
Concatenate strings:
SELECT 'Atlanta' || ' ' || 'Falcons' as team FROM SYSIBM.SYSDUMMY1;
We have to select from a table. We can select from the SYSIBM.SYSDUMMY1 table, like we would use DUAL in Oracle. There isn’t a CONCAT function available.
Convert a string to a datetime:
SELECT CAST('2024-01-01' as TIMESTAMP) FROM SYSIBM.SYSDUMMY1;
Get the month from the current date/time:
SELECT EXTRACT(MONTH FROM CURRENT_TIMESTAMP) as current_month FROM SYSIBM.SYSDUMMY1;
Create view:
CREATE VIEW Test_Schema.vwTestTable AS SELECT ID, Name, Balance, Is_Active, Created_Date FROM Test_Schema.Test_Table;
Get List of tables and views (No INFORMATION_SCHEMA views available):
SELECT TABSCHEMA, TABNAME, CASE WHEN TYPE = 'T' THEN 'TABLE' WHEN TYPE = 'V' THEN 'VIEW' ELSE NULL END as Object_Type FROM syscat.tables WHERE tabschema = 'TEST_SCHEMA' ORDER BY TABSCHEMA, TABNAME;
The SYSCAT schema has system catalog objects.
Drop the table. The view will have to be dropped as well, since it references the table:
DROP VIEW Test_Schema.vwTestTable; DROP TABLE IF EXISTS Test_Schema.Test_Table;
Oddly, IF EXISTS is available for a table but not for a view.
Links:
Db2 Data Types
SQL Rob 