Db2: Loading and unloading of data on remote clients with external tables

Unload Db2 data to remote client

Today, I am going to take a look at another variation of external tables. They can be used to load data from a remote Db2 client or unload data to that client - directly from or into a file. I tested it with the Db2 CLP and with a Python script and it was quite easy.

Remote clients

In my previous posts on Db2 External Tables, I showed examples where the files with the external data were located either on local file system available to the Db2 database server or on remote storage. Remote clients now add a third location type, the local file system available to a Db2 client.

The CREATE EXTERNAL TABLE statement has an option REMOTESOURCE. Its default value is LOCAL. It basically means "not remote" and that files are on the database server. Other possible values are "YES, "GZIP", and "LZ4". You can also specify "JDBC", "ODBC", or "OLE-DB" instead of "YES". In all cases it means that the data file is not on the database server, but resides on a different (remote) machine, typically the database client/app server. The values "GZIP" and "LZ4" indicate the compression algorithm to be applied before/after transfering the data. If only "YES" is specified, the data is not compressed on the wire.

Loading and unload data with external tables

In my last post on external tables, I explained named and transient external tables. You can use transient tables, those without a catalog entry, to load data from and unload data to a Db2 client, here shown for the Db2 Command Line Processor (CLP).

db2 "create external table 'cattables.csv' using (remotesource yes nolog true) as select tabname, tabschema from syscat.tables"

The above statement would result in a file on the client with data sourced from the SELECT statement (unload). Similarly, it is possible to load data from a file on the Db2 client and INSERT it into a database table:

db2 "insert into mytable select * from  external 'mydata.csv' using (remotesource yes nolog true)"

The above, would also work when done in an app. The Db2 documentation has pages dedicated to Java and unloading or loading data from remote clients. I tested it with a Python script which I connected to Db2 on Cloud on IBM Cloud and "from outside" to Db2 Community Edition running in a container.

In another script version I used a CREATE EXTERNAL TABLE statement similar to the one above to unload and download the data to the client. No handling of CSV files is necessary in the Python script, all done by the Db2 code.

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