0

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

  • Define "systematically". Is the export command not systematic enough? – mustaccio Apr 24 '18 at 12:44
  • @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 ?\ – AnarKi Apr 24 '18 at 12:47
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.

1

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
0

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.)

-1

Maybe try something like this:

db2move sample export -aw -l lobs -ts USERSPACE1

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.