CODEWITHSUNDEEP
loggingdb2
user1340582
user1340582

Reputation: 19699

Turning off logging in IBM DB2 9.7 LUW

I have problems with transaction logs becoming full when deploying to test environment. I have tried increasing log size, but I thought if it would be possible to disable logging all together then that would be even better. At least during the reset.

Apparently there is a command:

alter table table-name not logged initially;

But here one has to specify all tables individually. I am trying to find a way to do this:

1. turn off logging
2. delete all tables in schema
3. create/populate tables in schema
4. turn logging back on

I have points 2. and 3. covered, but not 1. and 4. The last point 4. is not even necessary in our test environment.

Any ideas? Thanks!

Upvotes: 1

Views: 7344

Answers (2)

AngocA
AngocA

Reputation: 7693

As cnZach has written, you have to generate a query from the information that you get. You can do that by querying the catalog, and then create a set of statements.

Following what you said, the statement to disable the logging in a specific table is

alter table table-name not logged initially;

But you have to retrieve all tables, or a set of tables from the database. This can be done by querying the catalog

db2 "select substr(tabschame) || '.' || substr(tabname) \
from syscat.table where type = 'T' and tabschema not like 'SYS%'"

Once you have modified the previous query to your requirements (retrieving the tables that you need), then you modify the query to generate the alter statement for all that tables

db2 "select 'alter table ' || substr(tabschame) || '.' \
|| substr(tabname) || ' not logger initially' \
from syscat.table where type = 'T' and tabschema not like 'SYS%'"

If you want, you can execute directly the db2 output, or just send that to a file and execute it later via "db2 -tvf filename.sql. The other option has a brawback, because there is a problem with the output size; if it is bigger that a limit, it cannot be executed ( | db2 +p -tv)

db2 "select 'alter table ' || substr(tabschame) || '.' \
|| substr(tabname) || ' not logger initially' \
from syscat.table where type = 'T' and tabschema not like 'SYS%'" | db2 +p -tv

Upvotes: 0

cnZach
cnZach

Reputation: 81

too many tables there? Automated script would be helpful to do repeated tasks:

db2 list tables for schema triuser | grep " T " |awk '{print "ALTER TABLE "$1" NOT LOGGED INITIALLY"}'

Upvotes: 1

Related Questions