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...

A look at local external tables in Db2

Surprising Db2 results or not?

My recent post about Db2 backup to S3 or Object Storage triggered a question. And the answer to it is that I wrote about external tables first in 2017, about 7 years ago. Since then, some features were added or changed for external tables and remote storage support, most recently in Db2 11.5.7 (AFAIK). With my new Db2 playground in place, I thought it was nice to test external tables again.

About BIRD, SQL, IBM granite models, and your business reporting

BIRD-SQL benchmark

Some years ago, when composing SQL queries, I was hoping that those queries would just "fly", performing flawlessly and quickly. Now, I stumbled over something SQL-related that seems to fly: BIRD-bench. It measures Large Language Models' (LLMs) capabilities to generate SQL queries from text input. It is at the core of SQL: You describe the result set you need.

Getting started with Db2 on Cloud Lite

It seems to me that during the COVID-19 pandemic there are more hackathons than usual. Great to see that IBM Cloud is the platform of choice for many solutions. Db2 on Cloud is used as database, often the free Lite plan to get started. That's the reason I published a quick guide on the IBM Cloud blog on how to use Db2 on Cloud Lite plan for hackathons.

Some advanced SQL to analyze COVID-19 data

Learn to write SQL

All of us are impacted by COVID-19. Looking at daily case numbers, the basic reproduction number or mortality rates has become a routine. But what is behind those numbers? How are they computed and how does SQL help? In this post, I want to discuss how some SQL analytics clauses help to dig through the data. When I teach database systems, I always try to show how it applies to life. Here, SQL may not directly help to cure the disease, but SQL is essential to compute and understand the statistics.

25th meeting of German Db2 User Group

Celebrating the 25th DeDUG meeting

Last Friday, the 25th meeting of the German Db2 User Group was held, this time hosted by Fiducia & GAD IT AG in Karlsruhe. As usual, it was both a great networking and informative event. I had the opportunity to catch up with many Db2 users - customers, partner and IBMers. Moreover, similar to a couple of previous meetings, I had the after-lunch talk, this time featuring Db2 on Cloud security topics.

ETL in the cloud using SQL Query and Db2

From COS to Db2 using SQL

The SQL Query service on IBM Cloud allows to process data stored on Cloud Object Storage (COS) by writing SQL queries. So far, results were either shown in the console and / or written back to files on COS. Thus, I was happy to notice a new feature: Query results can now be written back to Db2 tables (on cloud). From my tests both Db2 on Cloud and Db2 Warehouse on Cloud are supported.

After IDUG is before IDUG: Back from Db2 conference in Charlotte, NC

Last week the IDUG NA 2019 was held in Charlotte, NC. It featured 8 parallel tracks dedicated to Db2 for z/OS and Db2 for LUW and emerging technologies. In addition, two tracks for hands-on labs and workshops were offered. Personally, I delivered three presentations, moderated and attended sessions, and was active on Twitter. So what was on? Continue after the snap of Twitter photos from last week.

Twitter photo feed from IDUG NA 2019

Db2: SQL-based explain and printed acccess plan

Vote for this Db2 idea

This Friday in Munich, I gave a talk about the new native JSON functions in Db2 at the German Db2 user group (DeDUG) meeting. To speed up queries and to enforce uniqueness or some structural rules, Db2 functional indexes can be used with the new JSON_VALUE. As usual for some prototyping, I utilized a Jupyter Notebook for my presentation. Now, how to demonstrate that indexes are really used? Show the access plan! But how in a notebook?

More tricks for building chatbots with IBM Watson Assistant

Have you heard? New tips and tricks!

If you are building chatbots with IBM Watson Assistant (Conversation), then you might have already stumbled over my collection of tips and tricks or my command line tool for testing chatbots. Earlier today I updated the tips and tricks and added section on collection projection. If you know SQL and relational algebra, then you know the concept of projection. The same can be applied to JSON data and used within Watson Assistant dialogs.

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.

2017 IDUG Db2 Tech Conference in Lisbon, Portugal

IDUG EMEA 2017 Conference

The EMEA edition of the IDUG Db2 Tech Conference is around the corner, staring the coming Sunday. I am going to talk about Db2 Security on Monday, October 2nd, in session D2 and give a guided tour of the Db2 Catalog on Thursday, October 5th, in session E18. In addition, you can find me on the expert panel for the application development track on Wednesday. What other session do I recommend or plan to visit? Here we go...

Db2 with External Tables: First Tests

External Tables - New in Db2

Db2 Warehouse recently added a new feature named external table. Because I was interested in that feature for a while and I have an instance of Db2 Warehouse on Cloud running in the IBM Cloud datacenter in Frankfurt, Germany, I performed some quick tests. Here is what it is and how I got it to work.

DB2 Quiz for the Resource-Minded & IDUG EMEA 2017

Did you know...?

I hope you had a good start into 2017. I am already up and running and accomplished some important tasks. I submitted my presentation proposals for the IDUG DB2 Tech Conference 2017 in Lisbon, Portugal. The Call for Papers is still open until February 20th. The IDUG Conference is always a great place to learn new stuff and meet great people.

Speaking of learning new stuff. Do you know which DB2 function or procedure produced the following output on my system? As you may notice, I checked some DB2 system processes using SQL. The feature I am using is around since DB2 version 9.7.
If you have a guess, leave a comment or send an email.



MEMBER DB2_PROCESS_NAME  DB2_PROCESS_ID       CPU_USER     CPU_SYSTEM
------ ----------------- -------------------- ------------ ------------
0      db2fmp                           15234            6            7
0      db2vend (PD Vendo                15064            5            3
0      db2ckpwd 0                       15060            0            0
0      db2ckpwd 0                       15061            0            0
0      db2ckpwd 0                       15062            0            0
0      db2sysc 0                        15054           91          128
...





  8 record(s) selected.

Easy to identify: Does the table have a primary key?

Primary Key

Next week I am going to teach students database basics again. One of the topics will be primary keys and how they help enforcing uniqueness and identify each of the stored objects. Recently I stumbled over the question how it is easily possible to tell whether a DB2 or dashDB table has a primary key. The answer, as often, is in the catalog, the database metadata.

For performance reasons almost all database systems use an unique index to implement a primary key. So the key (pun intended) is to look for such an index. Both DB2 for Linux, UNIX, and Windows (LUW) and DB2 for z/OS store information about indexes in a system table SYSIBM.SYSINDEXES. On DB2 for z/OS that table is exposed to the user and documented here. DB2 LUW has catalog views on top and the view to use is named SYSCAT.INDEXES, however querying the table still works:



SELECT COLNAMES
FROM SYSIBM.SYSINDEXES
WHERE TBNAME = 'MYTABLE'
      AND UNIQUERULE = 'P'

The query returns the columns on which the primary key is defined for the table MYTABLE. As can be seen in the documentation, the UNIQUERULE provides information about whether the index is an index with duplicates, an unique index, or it is used to implement a primary key (value P). On DB2 LUW we could write the query utilizing the catalog view SYSCAT.INDEXES. The following query returns the table name and schema as well as the column names for all tables which have a primary key defined:


SELECT TABNAME, TABSCHEMA,COLNAMES
FROM SYSCAT.INDEXES
WHERE UNIQUERULE='P'

So the key to quickly working with primary keys are indexes and their metadata...

Mom, I joined the cloud! (or: Use old stuff with new stuff - DB2 federation)

Everybody is talking about Hybrid Clouds, combining on-premises resources like database systems and ERMs with services in the public or dedicated cloud. Today I am showing you exactly that, how I combined my on-prem DB2 with a cloud-based DB2 that I provisioned via Bluemix. The interesting thing is that really old technology can be used for that purpose: database federation. So relax, sit back, and follow my journey in joining the cloud...

Database Services in the Bluemix Catalog

For my small adventure I used a local DB2 10.5 and a Bluemix-based SQLDB service. The steps I followed are an extended version of what I wrote in 2013 about using three-part names in DB2 to easily access Oracle or DB2 databases. Smilar to the entry I started by enabling my DB2 instance for Federation (FEDERATED is the configuration parameter).
[hloeser@mymachine] db2 update dbm cfg using federated yes
DB20000I  The UPDATE DATABASE MANAGER CONFIGURATION command completed
successfully.

A Cache of Identities and a Sequence of Events

Bits and Bytes of
Sequences

Recently I received an interesting question: DB2 and other database systems have a feature to automatically generate numbers in a specified order. For DB2 this generator is called a sequence. When it is directly used to assign values to a column of a table, the term identity column is used. The next value of a sequence or identity column is derived by incrementing the last value by a specified amount. This works well, but sometimes there is a gap. Why?

MySQL-Style LIMIT and OFFSET in DB2 Queries

I was recently asked whether DB2 support MySQL-style syntax to page through query result sets. The good news is that DB2 supports LIMIT and OFFSET in addition to its own syntax. The only drawback is that the extra syntax supports needs to be enabled before it can be used. In the following I am going to show you how.

MySQL syntax in DB2

To support features offered by other database systems like Oracle, Microsoft SQL Server, Sybase, or MySQL that are incompatible with existing DB2 features, the DB2_COMPATIBILITY_VECTOR registry variable was introduced in DB2 version 9.7. The vector is used to selectively enable features and there are predefined settings named ORA (enable all Oracle features), SYB (Sybase), and MYS (MySQL). With that background knowledge we can directly get started on enabling LIMIT and OFFSET.

DB2, Halloween, and a Time Saver

A roadblock?

Well, right now I am busy with all kinds of things, juggling different database and cloud topics. Last weekend, Europe has switched back from Daylight Saving Time or Summer time to standard time, North America will follow this weekend. During this process, supposedly, you get one additional hour of sleep or time for other stuff. In my case it turned out that I spent the hour changing wall clocks, alarm clocks and watches. Anyway, here is a real time saver for DB2...

DB2 has the function TIMESTAMP_FORMAT (with synomy TO_DATE and TO_TIMESTAMP) to convert string values to timestamps or dates. The expected date and time format can be specified via the usual placeholders, e.g., MM for months or SS for seconds. However, there are some tricky parts to it and I am going to treat you with a solution (this was the "trick & treat" Halloween reference...):

db2 "values to_date('30.10.2015 00:00:00','DD.MM.YYYY HH:MI:SS')"
SQL20448N  "31.10.2015 00:00:00" cannot be interpreted using format string
"DD.MM.YYYY HH:MI:SS" for the TIMESTAMP_FORMAT function.  SQLSTATE=22007

db2 "values timestamp_format('30.10.2015 24:00:00','DD.MM.YYYY HH:MI:SS')"
SQL20448N  "31.10.2015 24:00:00" cannot be interpreted using format string
"DD.MM.YYYY HH:MI:SS" for the TIMESTAMP_FORMAT function.  SQLSTATE=22007

In both cases I am trying to convert midnight on October 31st to an internal timestamp value. Both calls return an error. The reason is that "HH" is the same as "HH12", the 12-hour format. Using "HH24" (24-hour format) everything is ok:

db2 "values timestamp_format('31.10.2015 00:00:00','DD.MM.YYYY HH24:MI:SS')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

db2 "values timestamp_format('31.10.2015 24:00:00','DD.MM.YYYY HH24:MI:SS')"

1                        
--------------------------
2015-10-31-24.00.00.000000

  1 record(s) selected.

If you want to use the HH/HH12 format, you need to specify where to expect the so-called Meridian indicator (AM/PM):

db2 "values timestamp_format('31.10.2015 12:00:00am','DD.MM.YYYY HH:MI:SSAM')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

db2 "values timestamp_format('31.10.2015 12:00:00am','DD.MM.YYYY HH:MI:SSPM')"

1                        
--------------------------
2015-10-31-00.00.00.000000

  1 record(s) selected.

That's all, enjoy the weekend and Halloween...

Altering tables in the SQLDB service on Bluemix

My blog has been quiet for some weeks thanks to some offline vacation and due to some business trips. Last week I first spoke about DB2 at a conference, then coached developers at a Bluemix hackathon. At that Bluemix event I was asked whether it would be possible to change some column definitions for an existing table. It was easy to say "absolutely", but it turned out to be harder than thought because at first I couldn't how to do it. So let's document it... :)

Once you have provisioned a SQLDB database service on Bluemix and launch the administration console, you are greeted by the "Getting Started" screen prominently featuring the following three choiced:

IBM SQL Database: Getting Started

The "Run Queries" button is intended to perform SQL SELECTs, nothing else.