How to systematically export all tables from a DB2 database schema to csv

Question

Is there a way using a DB2 database to systematically export all tables from a schema to separate csv files ? What I mean is to export all the tables in a schema without specifying table names. Maybe somehow getting all table names in a schema, then applying the export on those

• mustaccio: Define "systematically". Is the export command not systematic enough?
• AnarKi: @mustaccio So what i mean is to export all the tables in a schema without specifying table names. Maybe somehow getting all table names in a schema, then applying the export on those ?\

Answer 1

You can easily create a small script that does this for you, or even do a loop on the command line. Assuming you are on a unix/linux platform something like below should do.

#> db2 connect to mydb
#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema = 'DB2INST1'"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

You can replace DB2INST1 with whatever schema you are interested in. If you want tables from several schematas you can use in:

#> for t in $(db2 -x "select rtrim(tabschema) || '.' || rtrim(tabname) from syscat.tables where tabschema in ('DB2INST1', ...)"); do db2 "export to $t.csv of del modified by coldel, select * from $t"; done

There are a lot of options that you can use to format the output from export, here I use modified by coldel but there is much more that you can configure. See:

https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.admin.cmd.doc/doc/r0008303.html

for more info.

Answer 2

With a bit of scripting you can certainly do something like this:

db2 -x list tables for schema your_schema | \
awk '{print "export to "1ドル".csv of del select * from "2ドル"."1ドル}' | \
db2 +p -x

Answer 3

If you want to do that via the SQL inteface, e.g. a JDBC client, you might employ the ADMIN_CMD() system stored procedure and run this compound statement:

begin 
 for r as c1 cursor with hold for 
 select tabschema, tabname from syscat.tables 
 where tabschema = 'YOUR_SCHEMA' 
 do 
 call admin_cmd(
 'export to /some/path/'||rtrim(r.tabname)||
 '.csv of del select * from '||rtrim(r.tabschema)||'.'||rtrim(tabname)); 
 end for; 
end

Note that the files will be created on the database server.

(Cursor declaraion requires with hold, because ADMIN_CMD() commits at the end of every invocation.)

Answer 4

Try this: https://www.sql-workbench.eu/ SQL Workbench/J is a free, DBMS-independent, cross-platform SQL query tool.

Answer 5

Maybe try something like this:

db2move sample export -aw -l lobs -ts USERSPACE1

• Jack Douglas: does db2move produce csv output? ibm.com/support/knowledgecenter/SSEPGG_10.5.0/…