DB2 data insertion without logging

Data insertion in DB2 generally performs log operations. However, the methods described below enable data insertion in DB2 without logging operations. If you are interested, take a look.

Use the following code in the program:
Insert into a select * from B where ......
There are more than 6 million records inserted into Table a each time. Every time you execute this statement, DB2 returns an error code with the SQLCODE value of-964. Run the db2 sql0964 command to check the cause of the error, DB2 is interpreted as SQL0964C The transaction log for the database is full. then, expand the log file size and the number of log files according to the DB2 instructions. The following command
Db2 update db cfg for MY_DATABASE using LOGFILSIZ 7900
Db2 update db cfg for MY_DATABASE using LOGPRIMARY 30
Db2 update db cfg for MY_DATABASE using LOGSECOND 20
The circular log is used.

The total log file is expanded to about GB, and then the following command is executed:
Db2 "force application all"
Db2stop
Db2start
Db2 connect to MY_DATABASE user db2inst1 uing db2inst1

Finally, the new Stored Procedure containing the insert into... select... statement is called. The execution of this program is completed, but the efficiency is too slow.

Later, I thought about whether to enable DML operations on a table without logging operations. I deleted both table a and Table B, and selected the not logged initially clause when creating the table, after creating the table, I re-import the data to table B, and finally execute the insert... select... SQL stored procedures of the statement, DB2 still returns an error code with the SQLCODE value of-964.

Is there an efficient way to prevent DB2 from performing log operations when executing such methods?) How can this problem be solved?

-------------

Remove logging.
Go to control center -- configuration -- log activity, and configure to not record logs.

-------------

When using cyclic logs, you can consider adjusting the database's commitcount parameter.

-------------

You can also use the not logged initially option to create a table.
However, you must first activate the not logged initially option;
Example in DB2 CLP;
DB2 create table a... not logged initially;
DB2 + C // disable the automatic submission option;
Db2 => alter table a activate not logged initially;
Db2 => Insert into a select * from B;
Db2 => commit;
Since the not logged intially option is enabled before commit, the subsequent SQL statements do not count as logs;
After commit, the not logged intially option is disabled at the same time;
At this time, it is best to perform a backup, because you have no logs for this data operation, which is not conducive to future recovery;

However, this method is not recommended. If you want to Import a large amount of data, you should use Load and Import;
It is also possible to write data in a program. DB2 provides APIs and can refer to API Reference.

Manually analyze the DB2 SQL Execution Plan

Implementation Process of batch execution of DB2 SQL scripts

DB2 Online Export Method

Common db2 stored procedure statements

Usage of the DB2 create server statement