How to permanetly turn of transaction logging for specific table in Db2?

Question

In our company we are merging two systems together. Data are required to be moved from Microsoft SQL Server 2016 source database into Db2 v11.5 target database. In production environment this is going to be one time operation and "moving" data are pretty large, few TB combined.

We will move data in two phases:

1. One-to-one copy from source database tables into Db2 "stage" tables (that are going to be dropped after project is finished).
2. Complex transformation of data from "stage" tables into production tables.

Step one is going to be performed with SQL Server Linked Server, which is similar technology to Db2 federated tables.

We would like to reduce the impact on disk for step one on Db2 target site. Specially on transaction logs. On Db2 database LOGARCHMETH1 parameter is set to DISK to enable online backup. When bulk data insert is transferred into "stage" tables it will fill a lot of transaction logs and archive logs after that. We would like to avoid filling up transaction logs.

Db2 has option to turn off transaction logging for table with command:

ALTER TABLE mytable ACTIVATE NOT LOGGED INITIALLY

but the command is required to be executed inside unit of work (transaction). Like we see now we are having trouble to turn-off auto-commit when using Linked Server.

Is there some setting to permanently set table to "not logged" stage to avoid using transaction logs or some similar solution that would reduce usage of transaction logs?

Just a note: We are not using HADR on this Db2 database. Regards

Answer 1

You cannot turn off logging permanently. What you can do to reduce logging:

• use the LOAD utility with the NONRECOVERABLE option to populate staging tables;
• use temporary tables, which are not logged, for staging.