Db2 External Tables
In the past, I blogged several times about the CREATE EXTERNAL TABLE and how to use Db2 external table feature. My most recent blog posts on that topic were Loading and unloading of data on remote clients with external tables and Once again: Db2 External Tables where I looked into named versa transient external tables. You will find more links in the mentioned blog posts.
Yesterday, I presented at IDUG EMEA 2025 to an interested audience with great feedback. There was one question that I answered with a “YES, absolutely!”, but realized that my slides and blog posts were not addressing it yet. The question was whether it is possible to combine data from regular Db2 tables with data from external tables. And, yes, absolutely, that is the big benefit and allows bringing in external data for/in many use cases.
Combine Db2 data with external data
In the following, I am going to show how to combine data from a regular table, named “EMP” like employees, with some external address information. All was tested on my Db2 Community Edition.
Setup
I am utilizing the user “db2inst1” and started by updating the Db2 database configuration:
db2 update db cfg using extbl_strict_io yes
mkdir db2inst1
This causes Db2 to store and retrieve files for external tables relative to a base directory, appended with the authorization ID. In my case it is the default directory of the instance user where I needed the subdirectory for data coming in with authorization ID “db2inst1”.
Once connected to my test database, I ran the following to create the EMP table:
create table emp(id int, fname varchar(60),add int)
Next is to insert 8 rows (8, so that “Henrik” is included ;-)
insert into emp values(1,'Aaron',1),(2,'Bertha',1),(3,'Charlie',2),(4,'Dora',1),(5,'Ernst',2),(6,'Frederic',2),(7,'Greta',1),(8,'Henrik',1)
Checking that the data is in:
select * from emp
ID FNAME ADD
----------- ------------------------------------------------------------ -----------
1 Aaron 1
2 Bertha 1
3 Charlie 2
4 Dora 1
5 Ernst 2
6 Frederic 2
7 Greta 1
8 Henrik 1
8 record(s) selected.
The last step is to create an external data file with just two rows:
create external table 'corp_addr.csv' USING (NOLOG TRUE) AS VALUES(1,'Duesseldorf'),(2,'Cologne')
Back in the command shell, I checked the file:
cat db2inst1/corp_addr.csv
1|Duesseldorf
2|Cologne
Run SQL on database and external data combined
There are different ways of running SQL SELECT statements on data stored in the Db2 database together with external file data. Here, I provide some variations.
The following SELECT joins the employee data with the city information and utilizes a transient external table. It’s more complex and harder to code (but I got it to run in the early morning hours!!!).
select e.id,e.fname,a.city from emp e, table(select id,city from external 'corp_addr.csv' (id int,city varchar(20)) using (NOLOG TRUE)) a where e.add=a.id
ID FNAME CITY
----------- ------------------------------------------------------------ --------------------
1 Aaron Duesseldorf
8 Henrik Duesseldorf
7 Greta Duesseldorf
4 Dora Duesseldorf
2 Bertha Duesseldorf
3 Charlie Cologne
6 Frederic Cologne
5 Ernst Cologne
8 record(s) selected.
The more elegant and easier way is to create a named external table first, then use it just like any other table:
create external table addresses (id int,city varchar(20)) USING (FILE_NAME 'corp_addr.csv' NOLOG TRUE)
With the table definition in place and the data still in the external file, what is left is a simple SELECT statement:
select e.id,e.fname,a.city from emp e, addresses a where e.add=a.id"
ID FNAME CITY
----------- ------------------------------------------------------------ --------------------
1 Aaron Duesseldorf
8 Henrik Duesseldorf
7 Greta Duesseldorf
4 Dora Duesseldorf
2 Bertha Duesseldorf
3 Charlie Cologne
6 Frederic Cologne
5 Ernst Cologne
8 record(s) selected.
Conclusions
User group meetings like the IDUG EMEA 2025 are great for networking, sharing experiences, asking questions. The above is yet another example of user-inspired blog content. Thank you for joining my presentation yesterday and inspiring me!
If you have feedback, suggestions, or questions about this post, please reach out to me on Mastodon (@data_henrik@mastodon.social) or LinkedIn.