Db2 catalog: Updated documentation links to database metadata

New Db2 metadata created

In 2017 I wrote the post "Db2 Catalog - A guided tour", a title which I also use for some of my talks at user group meetings and conferences. It contains some now outdated links to the Db2 documentation. Time for an update, so that I can find that important information quicker again. And maybe you too...

Once again: Db2 External Tables

Db2 log files and data on IBM COS

Today, I once again share some notes about Db2 External Tables. Recently, I set up Db2 12.1 Community Edition and had some time to "play" with external tables, multi-tenancy, and catalog tables. As this blog serves me as external memory :), here is what I need to remember...

Db2 12.1 is now available

Since yesterday, November 14th, the new version Db2 12.1 is available. You can download Db2 12.1 from the usual Db2 fix packs by version page. I recommend to check the documentation for Db2 12.1 and to start with the section on what is new. Last, here is the formal announcement from earlier this year.

Three plus one simple (and free) ways to test Db2

Getting started with Db2

Recently, I switched jobs and now want to set up a "playground" on my machine. First on the list is IBM Db2. So, what options are available to anyone to test Db2 and build up some Db2 skills? In this blog, I plan to provide an overview and share what I option I am going with. Why titled I this blog post "three plus one"? Because a possible solution for quickly testing Db2 queries is not really a full Db2 database. So let's start with that one...

How to connect from Python to Db2

One of the recurring questions I have seen is "how to connect from Python to Db2". I have blogged about Python and Db2 a couple times before. Recently, the question has been popping up again more frequently. From my view, the increased security with mandatory SSL/TLS encryption and the use of Python-based Jupyter notebooks for data science and AI / ML projects are the drivers behind that increase. Moreover, there is Db2 on-premises and the Db2 on Cloud / Db2 Warehouse on Cloud plus container-based offerings. Today, I am trying to sort this out and answer that question. Again.... :)

My best practices: How to search Db2 documentation

I often answer technical product questions, for Db2 and IBM Cloud topics. To provide relevant links and to back up my "hunch" after reading a question, I typically search the relevant documentation. But what are efficient way to search in the Db2 documentation? What are good ways to find the relevant parts in the IBM Cloud documentation? Here are my best practices on searching documentation.

JWT token authentication in Db2 runtimes like Python or Node.js

Python script connecting to Db2 with JWT

Some weeks ago I discussed how to configure JWT-based token authentication in Db2. I set up Db2 to accept JWS identity tokens and then connected to my test database using the command line. But how do you connect from a programming language like Python or Node.js? Here is what I needed.

Db2 Security: Configure JSON Web Token (JWT) authentication

Db2 login utilizing a JWT

Since V11.5.4, Db2 allows to consume JWT (JSON Web Token). The business case is to support SSO (single sign-on). You can configure Db2 to accept access tokens issued by external Identity Provider (IDPs), including the IBM solutions or open source projects like gluu. V11.5.5 brought some JWT improvements, e.g., support for multiple labels. On a recent Friday, I set up my Db2 Docker container to accept JWTs, not those issued by an external identity service, but my own.

Db2 11.5 Mod Pack 4 is available

A quick note that Db2 Version 11.5 and its Mod Pack 4 are now available. You can read more about its new features and changes in the "What's new" section of the Db2 docs.

An interesting new security feature is the support for JWT (JSON Web Token) for authentication. It simplifies the integration of SSO (single sign-on) environments.

The Adaptive Workload Manager which we have heard about at past IDUG conferences is now GA. Other enhancements are for log space management and compression and there are several performance improvements.

New performance and security feature in Db2: Authentication cache

New security feature in Db2

The recent release of Db2 11.5.3 (Db2 V11.5 Mod Pack 3) includes a small gem that impacts both performance and security. It is a cache for User ID and Password-based authentication. That feature is great when your system uses LDAP / Active Directory for authentication because lookup results can be cached by Db2. By default, that feature is not active and you need to configure it. In this post, I am going to discuss how to enable and configure it.

Use an API KEY or ACCESS TOKEN to connect to Db2 on IBM Cloud

In the past, I have blogged about how to access a Db2 database on IBM Cloud from your local machine. I cataloged the cloud host and its database. Then, connected to it using the command line processor (CLP) by passing a username and password. Last year, I was delighted to read that Db2 on Cloud and Db2 Warehouse on Cloud now support authentication by either IBMid, API key, or access token. It is called IAM (Identity and Access Management) support. Finally, I had some time to actually use it. Here is what steps I needed to connect from my local machine to Db2 on IBM Cloud using either API key or token.

New Db2 Fix Packs and Mod Packs available

A quick update: Over the past few days, new fix packs and mod packs for Db2 became available. The page "Download DB2 Fix Packs by Version" lists Db2 11.1.4.5 as the most recent fix pack. The related Mod Pack and Fix Pack Updates page in the Db2 Knowledge Center for version 11.1 has details.

Although the overview page lists Db2 11.5 GA as most recent, the page Mod Pack and Fix Pack Updates for 11.5 reveals new container-only Db2 Mod Pack releases.

New! Db2 11.5 is available

Db2 11.5 is available

A quick post to inform you that Db2 11.5 has been released. Check out the What's New for Db2 Version 11.5 GA section in the Db2 Knowledge Center. You can download the GA version from the regular Download Db2 Fix Packs by Version site.


With a new release I typically go over the What's changed documentation to look for changes to registry variables, system views and deprecated functionality.


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

Db2 2 V11.1 Mod Pack4 and Fix Pack 4 is available

New Db2 Mod / Fix Pack

The latest product update for Db2 is out, Mod Pack 4 and Fix Pack 4. You can read about the new features (because it is a Mod Pack) in this Mod Pack and Fix Pack Updates document. Here are some of the focus areas:

• There are several improvements to High Availability when using HADR.
• db2pd has a new option "-barstats" to monitor progress and performance of backup and restore operations.
• ADMIN_MOVE_TABLE has seen some enhancements, including a new ESTIMATE option.
• Application developers benefit from new JSON support that features functions like JSON_QUERY, JSON_TABLE and JSON_EXISTS (Hello, XML developers!).
• Several new data sources can be integrated using the federation capability. This includes SAP HANA, HDFS parquet files and CouchDB.
• A long list of Db2 pureScale improvements for performance, administration and monitoring.

And if you are curious about ongoing product enhancements to Db2 on Cloud, Db2 Warehouse and Db2 Warehouse on Cloud, then check out this related "What's New in..." page.

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

Db2 Node.js and Python drivers and ORM

I am back from the Db2 Aktuell conference with my talk about Db2 app development and IBM Cloud. One of the many questions I received since then is about where to find the Db2 drivers for Node.js and Python. What type of ORM (Object-Relational Mapping) libraries are supported? Let me briefly answer that here and provide some links.

Db2 drivers turning bits into information

Db2 and Node.js or Python

Db2 supports many programming languages for database application development, a good overview is in the Db2 Knowledge Center. Node.js and Python use the open source drivers for IBM Database Servers that are available on GitHub.

• Last year, I blogged about the Python drivers and that IBM actually provides four different ones. The reason is that there are different Python APIs and frameworks, including SQLAlchemy and Django. Both of them feature their own ORM layer.
• For Node.js, a popular framework is Sails with its Waterline ORM. Db2 is a supported database. A popular Node.js API framework is Loopback by the IBM company Strongloop. The Db2 connector can be found in this GitHub repository. Sequelize is a promise-based ORM, the Db2 driver team has started adding support.

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

IBM Cloud and Db2 News - August 2018 Edition

Catch up with news

This Summer I have been away few days here and there. Once back, I tried to catch up with changes for IBM Cloud and its services as well as with Db2. Here are links to what I considered noteworthy (and read so far...):  

• Db2 (for Linux, UNIX, and Windows) recently saw some fix packs. Version 11.1 is now on Mod 3 Fix Pack 3 iFix002. For an overview of the Db2 product enhancements in the different modification packs you can read this page in the Db2 Knowledge Center.
• If you follow Db2 Warehouse on Cloud, Db2 Warehouse and Db2 on Cloud, the related "What's new" page is always worth a visit. There are additional Python modules in the Docker client container for Db2 Warehouse to simplify application development. Moreover, database federation has been enhanced for Db2 on Cloud.
• One of my favorite services on IBM Cloud is Watson Assistant. I have built many chatbots and hence read the release notes with product updates. One new feature is so-called rich responses. They allow to embed multimedia and offer clickable choices. There has also been an improvement to conditional responses that simplifies building more complex dialogs.
• If you are interested in serverless (Function as a Service / FaaS) technology, then this blog about setting up a CI / CD pipeline for OpenWhisk functions (IBM Cloud Functions) might be of interest. Another blog introduces a starterkit to develop IBM Cloud Functions with TypeScript.
• Related to serverless computing is also the announcement by Google and IBM of Knative. Knative is a new open source project that provides the building blocks for serverless platforms to run on top of Kubernetes.
• Another important annoucement, again with Google and others, was the general availability of Istio 1.0. Istio allows to deploy, secure and manage services across Kubernetes and other environments.

That's it for now. Anything that you found interesting and should be included? Then leave a comment.

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

June 2018 Edition: Latest IBM Cloud news and links (Db2 included)

IBM Cloud news

It's been a while since my last news compilation. Here is a collection of news interesting links related to IBM Cloud from the past few weeks:

• I always recommend to regularly go over the "What's new in IBM Cloud" page in the IBM Cloud docs.
• Something announced this week is a major expansion to IBM cloud capabilities and availability zones. It includes the option to run Docker containers on a Kubernetes cluster with worker nodes distributed across different zones.
• Several new IBM Cloud solution tutorials were added and some updated. Check out the one on how to move a VM-based application to containers on Kubernetes and best practices on how to organize users, teams and applications.  I also recommend the tutorial describing how to build a database-driven Slackbot. Last but not least, to analyze traffic to your GitHub repositories read the tutorial on combining serverless technology and Cloud Foundry for data analytics.
• Db2 on Cloud has extended its federation support to non-SSL connections. Several Db2-based data sources can be integrated and queried.
• Do you know that with a recent update to the IBM Cloud CLI (command line interface) the CLI command changed to "ibmcloud"? No worries, the old "bx" is still supported.
• I recommend taking a look at current capabilities of IBM Cloud Key Protect, IBM Cloud App ID and IBM Cloud Activity Tracker. They provide some essential security features and have been enhanced over the past weeks.

With the above you should alreay have enough reading material. But here is another link featuring a cool application built on IBM Cloud: Listen live to Kone elevators around the world.

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

Db2: CTE and CONNECT BY - two kinds of recursion

Writing recursive SQL

Recently, I gave a talk on SQL Recursion. One of the cool features in Db2 that I (re-)discovered for that presentation is that there are two different forms of syntax for recursive queries. One is the Common Table Expression (CTE), another the CONNECT BY syntax. Let me introduce you to the two and show you a sample query in both styles.

If you had to write recursive queries, e.g., to create a bill of material or to find dependencies in data, you would typically use a SELECT statement with a common table expression (CTE). CTEs are easily spotted by starting with the keyword WITH. Some form of temporary table is defined, then the initial data (seed) is specified followed by UNION ALL and the SELECT for the recursive part. A regular fullselect is then applied to the result of that computation.

The CONNECT BY provides another syntax for hiearachical and recursive queries. It was first introduced by Oracle and made its way into Db2 as part of the SQL compatibility features. It allows to write a, more or less, regular SELECT statement followed by a START WITH clause to define the initial data (see) and a CONNECT BY clause to specify the recursion. There are some additional keywords to access data from the prior step, the root ancestor or the (string) path from the root to a node. It is quite powerful and often shorter than a CTE. The only drawback is that you have to enable it in the Db2 compatibility vector.

The following two queries return the same result. It is the organizational structure of the departments in the SAMPLE database. The first query uses CONNECT BY and is directly taken out of the Db2 documentation.

SELECT LEVEL, CAST(SPACE((LEVEL - 1) * 4) || '/' || DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME
FROM DEPARTMENT
     START WITH DEPTNO = 'A00'
     CONNECT BY NOCYCLE PRIOR DEPTNO = ADMRDEPT

The second query is my version of the above with a CTE. As you can see, it is longer. Readability depends on what you are used to.

WITH tdep(level, deptname, deptno) as (
    SELECT 1, CAST( DEPTNAME AS VARCHAR(40)) AS DEPTNAME, deptno
    FROM department 
    WHERE DEPTNO = 'A00'
    UNION ALL
    SELECT t.LEVEL+1, CAST(SPACE(t.LEVEL  * 4) || '/' || d.DEPTNAME
       AS VARCHAR(40)) AS DEPTNAME, d.deptno
    FROM DEPARTMENT d, tdep t
    WHERE d.admrdept=t.deptno and d.deptno<>'A00')
SELECT level, deptname
FROM tdep 

If you only knew one style of recursive query then it is time to try out the other. Both queries above work with the SAMPLE database. If you have feedback, suggestions, or questions about this post, please reach out to me on Twitter (@data_henrik) or LinkedIn.

News on IBM Cloud and Db2 - December 2017 Edition

Another month and a lot of news

In the middle of November I reported about significant changes to Bluemix and IBM's cloud offerings. A month has passed and I want to give you an update to some news I am excited about.

I try to regularly read over the "What's in IBM Cloud" section in the IBM Cloud documentation. There were two significant announcements.

• The new Resource Groups allow simpler management of all what is in your account (a.k.a. "resources"). You can now group apps, services, virtual machines, Kubernetes-based container services and more and easily assign access privileges.
• With the introduction of the Resource Groups also came the IBM Cloud Identity and Access Management. It facilitates fine-grained access control utilizing API keys, service IDs and more.

The two above should keep you busy, but there is more. If you are a Db2 afficionado like I, then you probably have subscribed to the "What's New in IBM Db2 Warehouse on Cloud, IBM Db2 Warehouse, and IBM Db2 on Cloud". Did you know that MySQL and PostgreSQL are now available in the web console as data sources for federation? That makes it easy to access data coming from a LAMP stack.

As you might know, I am using and write about the IBM Watson Conversation service. What I liked from their "Release Notes" is a new beta feature to directly call actions from within a dialog node. IBM Cloud Functions are supported. I put that to a test and wrote a Slack bot backed by Watson Conversation that directly queries a Db2 database. I need to beautify the code and write it down (and submit it to IDUG).

I wrote a tutorial about how to generate, access and analyze application logs on IBM Cloud. You can find it in the Solution Tutorials as part of the IBM Cloud documentation.

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

Use Db2 as Cloud SQL Database with Python

Load Data into IBM Db2 on Cloud

Over the Summer I learned that Python is top in the IEEE programming languages ranking. It is also my favorite language for quickly coding tools, web apps and analyzing data with notebooks (e.g., on IBM Data Science Experience). Did you know that IBM provides four (4) different Db2 drivers for Python? There is a driver with the native Db2 API, one that supports the official Python DBI (database interface), one for the popular SQLAlchemy Python SQL Toolkit, and for the Python-based Django Web Framework. In an older blog I showed you how to use SQLAlchemy with Db2. Today, I am going to demonstrate you how simple it is to create a SQL database-backed web app in the IBM Cloud, utilizing the native Db2 API.