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

 To log or not to log

As you can see in the screenshot above, there is a file "quiz.csv" which holds the actual data, and two log files. The CREATE EXTERNAL TABLE statement has many optional parameters. NOLOG by default is "false" which means a log is always written (".log"). By using "NOLOG TRUE" in the list of options, only in the case of errors a log is produced, resulting in ".bad" files.

By default the ".log"/".bad" files are written to the same directory where the file of the external table is written to. The parameter LOGDIR (or ERROR_LOG) controls where the log files are placed. This works well on the local file system, but has only limited usefulness when working with S3 / cloud object storage - as shown below where I tested placing logs to "/tmp".

Files on S3 with prefix "tmp"

Named or transient external table

External tables are either named or transient. When an external table is created based on a select statement, its name can be left out. In that case it is considered a "transient table" and does not have a catalog entry. As consequence, it cannot be referred to in other statements. If you specify a name for the table and it is represented in the database catalog, it is a "named table".

The following is an example for creating such a transient table:

create external table '/database/config/db2inst1/transtab1.csv'  as select tabname,tabschema from syscat.table

Even though metadata about a transient external table is not stored in the catalog, it is represented in the catalog cache. The reason is that Db2 needs metadata to process the statement. You can find the transient table when inspecting the catalog cache using the db2pd command.

db2pd -catalogcache file=catcache.out -database testdb

The following shows two external tables in the db2pd output:

Dump of Db2 catalog cache showing entries of external tables

External tables in comparison

How can you utilize external tables to move data into or out from a database? The Db2 documentation includes a page that compares Ingest, Load, Import, and External tables.

Conclusions

There is a lot to discover about external tables (more coming up?). They are a useful tool to import or export data to the local file system or cloud storage, integrated into the regular "SQL environment".

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