Moving data out of Db2 LUW database into a file

If you’re a DBA and you want to read some data in your database, you just SELECT something from the database object. But what if you want to take part of your data and send it to your grandmother, so she can display it in her MS Excel? Or maybe someone in your company wants to create beautiful colourful charts using the data?

In both mentioned cases, you need to export the data to a csv file (or any other format of the kind).

Let’s assume we just want to export the list of all tables in the database. It means we want to export the results of:

db2 "SELECT TABNAME FROM SYSCAT.TABLES"

First approach

The simplest way to export the data to a file is this:

db2 "SELECT TABNAME FROM SYSCAT.TABLES" > output-file.txt

It works, but you get a plain text file. It may be sufficient if you just want to take a look at the list later, but not very convenient for any kind of imports or data engineering (no useful structure). The output file looks like this:

TABSCHEMA                          TABNAME
- - - - - - - - - - - - - - - -    - - - - - - - - - - - - - - - -
MAMMALS                            CATS MAMMALS                          
COWS MAMMALS                       DOGS MAMMALS                          
HORSES MAMMALS                     SHEEP

EXPORT tool

What we are really looking for is a file with a structure. It means that the file should contain information about rows and columns, so that the output can be imported as a table. In the most common scenario, the columns are separated by a comma and rows by a newline character — this is a csv file (cvs stands for “comma separated values”).

This is how to EXPORT the data to a csv file:

db2 "EXPORT TO output-file.csv OF DEL SELECT TABNAME FROM SYSCAT.TABLES"

As a result we get a file with following content:

"MAMMALS","CATS"
"MAMMALS","COWS"
"MAMMALS","DOGS"
"MAMMALS","HORSES"
"MAMMALS","SHEEP"

As I mentioned, columns are separated by a comma, rows — by a newline character. Data in such a format can be easily imported anywhere.

csv is not the only format you can use for data exports. If interested, please check the IBM Documentation on the EXPORT command: LINK

The full syntax of the EXPORT tool is the following:

The only disadvantage of the EXPORT tool that I see is that you don’t get headers of your SELECT query (and there is unfortunately no option to include them in the EXPORT output).

In the next article I am gonna show the opposite way: a csv file into a Db2 LUW table ;)

‍