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
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:
for more info.
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.)