首页 / 日志 / 【DB2】关闭表的日志功能
【DB2】关闭表的日志功能
内容导读
互联网集市收集整理的这篇技术教程文章主要介绍了【DB2】关闭表的日志功能，小编现在分享给大家，供广大互联网技能从业者学习和参考。文章包含4163字，纯文字阅读大概需要6分钟。
内容图文
2018.11.19
客户遇到一个问题，在import数据的时候，产生了大量的日志，客户的数据库是HADR模式，通过评估，这几张表是可以允许在备库上不查询的，表中的数据时临时的。
| 方案一：修改脚本，将import修改为load |
| 方案二：修改脚本，不修改导入数据的方式，临时激活表的not logged initially特性，事务级别 |
| 方案三：将相关的表重建，创建表，并激活not logged initially，该方案表将永久修改为not logged initially |
三种方案结果都是一样的，由于不写日志，均会导致备库表无法查询，报错SQL1477N
现在存在一个疑问，假如使用方案二，同时 import 时使用 commitcount，会不会存在问题呢 ? 针对该问题，得到下面结论
alter table t activate not logged initially; import from t.del of del commitcount 10 insert into t;
通过验证 commitcount 10 会导致 activate not logged initially 失效。
所以将 commitcount 10 取消掉，这个参数对于该场景，意义不大，由于表不写日志，一旦事务失败均会导致表不可访问。
编辑m.sql文件 connect to sample; alter table t activate not logged initially; import from t.del of del insert into t; commit; db2 +c -tvf m.sql
中英文
https://www.ibm.com/support/knowledgecenter/zh/SSEPGG_11.1.0/com.ibm.db2.luw.admin.ha.doc/doc/c0006079.html
https://www.ibm.com/support/knowledgecenter/da/SSEPGG_11.1.0/com.ibm.db2.luw.admin.ha.doc/doc/c0006079.html
附录1：
https://www-01.ibm.com/support/docview.wss?uid=swg21215818
Technote (FAQ)
Question
How to temporarily turn logging off for heavy change operations, such as insert, delete, update, and changes in indexes?
Answer
Tables being modified can be altered to activate the ‘NOT LOGGED INITIALLY‘ attribute, which deactivates logging for the current unit of work. Any changes made to the table by an INSERT, DELETE, UPDATE, CREATE INDEX, DROP INDEX, or ALTER TABLE in the same unit of work after the table is altered by this statement are not logged.
At the completion of the current unit of work, the NOT LOGGED INITIALLY attribute is deactivated for the table and all operations that are done on the table in subsequent units of work are logged.
To use this option properly, the application doing the changes should NOT have AUTOCOMMIT enabled. Having AUTOCOMMIT OFF also helps define the scope of the transactions:
- For CLP, you can turn AUTOCOMMIT OFF using the registry variable DB2OPTIONS:
- If you execute a script containing update SQL statements, such as inserts, and DB2OPTIONS is not set, you can execute the script using:
db2 +c -tvf input_script.sql -z output_script.out
Example
To create table T1 with the ‘not logged initially‘ attribute:
db2 create table T1(id int) NOT LOGGED INITIALLY
or, if tables T1 and T2 are already created, create a transaction with all the operations that are not intended to be logged:
db2 alter table T1 activate not logged initially
db2 alter table T2 activate not logged initially
db2 delete from T1 where ...
db2 update T2 set ...
db2 import from data_file.txt of del insert into T2
db2 commit
The updates in T1 and T2 within this unit of work will not be logged.
Notes
1. You can create more than one table with the NOT LOGGED INITIALLY parameter in the same unit of work.
2. Changes to the catalog tables and other user tables are still logged.
Because changes to the table are not logged, you should consider the following when deciding to use the NOT LOGGED INITIALLY table attribute:
* All changes to the table will be flushed out to disk at commit time. This means that the commit may take longer.
* If the NOT LOGGED INITIALLY attribute is activated and an activity occurs that is not logged, the entire unit of work will be rolled back if a statement fails or a ROLLBACK TO SAVEPOINT is executed (SQL1476N).
* You cannot recover these tables when rolling forward. If the rollforward operation encounters a table that was created or altered with the NOT LOGGED INITIALLY option, the table is marked as unavailable. After the database is recovered, any attempt to access the table returns SQL1477N.
- If using stored procedures, the whole stored procedure defines a transaction by default, unless an explicit COMMIT is called within the procedure code. So, the ‘ALTER TABLE ... ACTIVATE NOT LOGGED INITIALLY‘ can be called at the beginning of the procedure, and a COMMIT statement at end of the procedure (or after the procedure has been called) will end the transaction.
- This document and suggestions given to prevent logging activity do not apply to LOAD utility, since LOAD does not do logging.
原文：https://www.cnblogs.com/DBA-Ivan/p/9987476.html
内容总结
以上是互联网集市为您收集整理的【DB2】关闭表的日志功能全部内容，希望文章能够帮你解决【DB2】关闭表的日志功能所遇到的程序开发问题。 如果觉得互联网集市技术教程内容还不错，欢迎将互联网集市网站推荐给程序员好友。
内容备注
版权声明：本文内容由互联网用户自发贡献，该文观点与技术仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 gblab@vip.qq.com 举报，一经查实，本站将立刻删除。
内容手机端
扫描二维码推送至手机访问。