DB2 LUW – How to Export All Tables from Database Schema to CSV

Unfortunately, it's not possible to take an online backup of a DB2 database if it's in circular logging mode, which is the default for DB2 databases when they are created.

You can check whether your database is using circular logging by issuing:

./db2 get db cfg for dbemp | grep LOGARCH

If both LOGARCHMETH options are switched off...

First log archive method       (LOGARCHMETH1) = OFF
Second log archive method      (LOGARCHMETH2) = OFF

... then you'll need to change your database configuration to use archive logging before backups can be taken. I'm not 100% sure of this but I believe you'll need to restart your database and take an offline backup before you can start taking online backups.

There's an overview of how to do this in IBM's "configuring database logging options" documentation.

Note that if you change from CIRCULAR LOGGING to ARCHIVE LOGGING, you'll need to figure out what to do with your archived log files. Storing them on SAN/NAS/Tape to allow roll-forward recovery can be a really good idea if this data is important to you.

If you want access to all data (ie, all tables in all schemas), you would need to grant dataaccess.

db2 grant dataaccess on database to user winuser1

If you only want winuser1 to access just the 100 tables in the schema you are referring to, then unfortunately, there is no easy way, you would need to grant SELECT on each table. That being said, it can be accomplished through scripting.

You could do the following

db2 -tnx "select distinct 'GRANT ALL ON TABLE '||
    '\"'||rtrim(tabschema)||'\".\"'||rtrim(tabname)||'\" TO USER winuser1;'
    from syscat.tables
    where tabschema = 'myschema' "  >> grants.sql

db2 -tvf grants.sql

This makes use of querying the system catalogs to dynamically generate a script to permission things. This is a lot of how we permission for users we don't want to give dataaccess to.

Here is a good page of the authorities for DB2.