DB2事务日志使用详解

DB2事务日志使用详解摘要：我们经常接到客户的电话说数据库日志满了，需要快速清除。对于一些初入门的DB2使用者去维护一个大数据量的系统，这几乎是他们必然会碰到的一个问题。碰到这样的问题，我们可以不厌其烦的一遍遍向客户解释这个问题的原因,也可以给出非常明确的解决方案，但是对于很多客户看来，这似乎是一个比较无奈的解决方案，他们只能承担着这种操作带来的系统中断。因此，对于数据库的设计人员，开发人员和维护人员来讲，非常清楚的了解数据库的日志原理与合理的规划一些操作以避免发生这样的情况是非常重要的！下面，我们就对数据库的日志原理和使用.我们经常接到客户的电话，我的数据库日志满了，有没有什么好办法快速清除?尤其对于一些初入门的DB2使用者去维护一个大数据量的系统，这几乎是他们必然会碰到的一个问题。我们也经常接到客户更紧急的电话，我的数据库不能使用了，因为日志占用太多空间，文件系统满了，就把日志删除了，现在数据库无法使用，这个是生产系统，需要尽快恢复，有什么办法可以让数据库立刻使用？碰到这样的问题，我们可以不厌其烦的一遍遍向客户解释这个问题的原因，也可以给出非常明确的解决方案，但是对于很多客户看来，这似乎是一个比较无奈的解决方案，他们只能承担着这种操作带来的系统中断。因此，对于数据库的设计人员，开发人员和维护人员来讲，非常清楚的了解数据库的日志原理与合理的规划一些操作以避免发生这样的情况是非常重要的！下面，我们就对数据库的日志原理和使用中经常遇到的问题以及其解决方法跟大家分享下。1、DB2数据库的日志原理事务日志记录数据库中所有对象和数据的改变，在早前版本中最大可达256G，其大小为(logprimary+logsecond)*logfilsiz，其中logprimary+logsecond的值小于或等于256，logfilsiz的最大为262144，在9.5版本中，日志最大已经可以达到512G，其中logfilsz的大小更改为524286。DB2数据库的日志分为主日志和辅助日志，其中主日志在第一个连接到达数据库或者数据库被激活后立即分配，而辅助日志在主日志大小不够的时候动态分配。所以需要注意一点，日志所在的文件系统的大小必须大于主日志文件与辅助日志文件的大小之和。DB2数据库有2种日志配置方式，循环日志与归档日志。循环日志：这是数据库默认的日志使用方式，主日志用来记录所有的更改，当事务提交后，日志文件会被重用。当主日志文件达到限制时，辅助日志文件将被使用。这种日志方式可以进行崩溃恢复和版本恢复，不能进行前滚恢复，不支持在线备份。当活动事务的使用空间超过主日志和辅助日志的限制或者日志空间超过磁盘可使用空间，将会得到日志满的错误。

归档日志：启用logarchmetdl、logarchmetd2或打开logretain参数，注意，在9.5版本中，不推荐使用logretain参数，其所有的设置值将被忽略。在数据库归档日志规划时，建议不再使用logretain的方法。日志文件将不会被删除-保持在线或者离线状态。支持前滚恢复和在线备份。疑问：归档日志下，日志一直保留，持续生成新日志，为什么还会出现日志满的错误？归档日志下，其可用的活动日志大小依然受到主日志与辅助日志大小之和的限制，所以，即使在归档日志下，日志满的场景与活动日志下是完全一样的。

2、日志使用中的问题与解决方法在日常使用中，我们遇到最多的问题就是日志满，现在用几个实际的例子来看如何分析和解决日志满的问题，一般的，日志满可以分以下几个场景：A、环境准备，并介绍数据库日志使用大小评估方法：数据库参数设置如下：日志文件大小(4KB)(LOGFILSIZ)=10000主日志文件的数目(LOGPRIMARY)=3辅助日志文件的数目(LOGSECOND)=2日志总大小为200M.创建测试用表:C:\DocumentsandSettings\administrator〉db2"createtabletest_log(colint,col2char(10),col3timestamp,col4varchar(100),col5varchar(100),col6varchar(100),col7varchar(100),col8varchar(100))"DB20000ISQL命令成功完成。创建插入数据的存储过程：C:\DocumentsandSettings\administrator〉db2-td@-vfproc_testlog.sqlcreateprocedureproc_testlog(v1int)begindeclaretimeintdefault0;while(time<v1)doinsertintotest_logvalues(1,'testlog',currenttimestamp,'testlogtestlogte ');settime=time+1;

DB20000ISQL命令成功完成。我们来评估下插入使用日志的情况，以便构造日志满的场景，使用db2pd来查看事务日志的使用。分别打开2个db2cmd会话窗口，在窗口1中我们执行：C:\DocumentsandSettings\administrator>db2+ccallproc_testlog(1)返回状态=0会话2中执行：C:\DocumentsandSettings\administrator〉db2pd-dbsample-transactionsDatabasePartition0--DatabaseSAMPLE--Active--Up0days00:29:20Transactions:AddressAppHandl[nod-index]TranHdlLocksStateTflagTflag2FirstlsnLastlsnLogSpaceSpaceReservedTIDAxRegCntGXID0x7FC21A807[000-00007]27WRITE0x000000000x000000000x0000277188000x0000277188001107000X000000004F1310可以看到这个写操作占用的日志大约为700个字节，在回话1中再重复执行上面的命令，会话2中在看db2pd的输出:C:\DocumentsandSettings\administrator>db2pd-dbsample-transactionsDatabasePartition0--DatabaseSAMPLE--Active--Up0days00:45:55Transactions:AddressAppHandl[nod-index]TranHdlLocksStateTflagTflag2FirstlsnLastlsnLogSpaceSpaceReservedTID

AxRegCntGXID0x7FC21A807[000-00007]28WRITE0x000000000x000000000X000028E385B80X000028E3880615413340X000000004F57101334-700=634,我们可以这样评估，单个事务每执行一次表插入，插入一行占用的日志约为700字节，在一个事务中，插入多条记录，插入一行记录占用的日志约为634字节，当然，实际上当插入多行时，日志的大小会比计算值略大。使用这个方法可以根据业务运行情况来评估需要数据库应该配置的日志大小，也可以评估单个大事务需要的日志空间。根据估算，200M总日志大小，200*1024*1024/635=330781。因此可以一次插入大约33W记录来构造日志满的场景。B、事务日志满场景一：当前未提交的事务太大，超过日志的限制。在会话1中执行：C:\DocumentsandSettingS\administrator>db2commitDB20000ISQL命令成功完成。提交前面未提交的事务。C:\DocumentsandSettingSadministrator>db2+ccallproc_testlog(330000)SQL0964C数据库的事务日志已满。SQLSTATE=57011这时候我打开另外一个session，执行一个不相关的插入操作。C:\DocumentsandSettings\administrator>db2"insertintotestvalues(1112,l,'sdfsdfsdfsdf','sdfsdfsdfsdfsdf','sdfsdfsdffsdfsd')DB21034E该命令被当作SQL语句来处理，因为它是无效的“命令行处理器”命令。在SQL处理期间，它返回：SQL0964C数据库的事务日志已满。SQLSTATE=57011可以看到，当日志满的时候其他的任何记日志的操作都将不能进行，所以整个系统基本处于不可用的状态，除非等事务回滚结束。OK,事务日志满的情况出现，现在我们就根据日志满的日志，来逆向分析是哪个操作导致的该问题，分析步骤如下：

首先，确定哪个应用的事务占用了大量的日志空间:在回话2中执行：C:\DocumentsandSettings\administrator>db2pd-dbsample-transactionsDatabasePartition0--DatabaseSAMPLE--Active--Up0days00:02:27Transactions:AddressAppHandl[nod-index]TranHdlLocksStateTflagTflag2FirstlsnLastlsnLogSpaceSpaceReservedTIDAxRegCntGXID0x7FC21A807 [000-00007]210WRITE0x000000000x000000000X00003D86000C0X000048C4FCD0140145722019554700X000000004F9110可以看到上面红色部分，AppHandl为7的应用的一个事务占用了大量的日志。如果有多个应用占用了大量的日志，我们可以按照下面的方法逐个分析，看每个应用是执行了什么sql导致的占用如此大的日志。然后使用db2pd确定这个日志执行了什么语句导致占用了大量的日志：C:\DocumentsandSettings\administrator>db2pd-dbsample-applicationsDatabasePartition0--DatabaseSAMPLE--Active--Up0days00:02:36Applications:AddressAppHandl[nod-index]NumAgentsCoorEDUIDStatusC-AnchlDC-StmtUIDL-AnchlDL-StmtUIDAppidWorkloadIDWorkloadOccID0x7AED80807 [000-00007]11572UOW-Waiting001851*LOCAL・DB2.08111110072911Applicationhandle为7的应用，对应的L-AnchID为185,L-StmtUID为1。在回话2中继续使用db2pd找到对应的sql语句：db2pd-dbsample-dynamicDynamicSQLStatements:AddressAnchlDStmtUIDNumEnvNumVarNumRefNumExeText0x7EA7D54018511111CALLproc_testlog(?)对应AnchID为185,StmtUID为1的语句，是CALLproc_testlog⑺，通过上面的分析，我们可以找到，是调用存储过程proc_testlog导致占用了大量的日志，从而找出导致日志满的罪魁祸首。解决方案：首先，尽量规避超大事务的操作，对于必须执行的这种大操作，可以考虑是否可以分解成几个事务进行，如果可以，尽量分解为小事务的方式进行;如果业务上不可以分解，是否可以考虑采用不记日志的方式?比如，load代替insert?表针对这个操作，暂时改为不记日志的方式等等。注意：当进行不记日志的操作时，必须非常清楚这样的操作的影响，比如，归档日志下数据库前滚的影响，hadr与复制的数据同步影响，操作失败结果如何等等。其次，总有些我们无法预料的操作发生，可能某个维护人员某天发出一个不适当的命令，删除了大量的数据，导致日志满，整个系统无法运行，如何规避这样的操作带来的系统运行影响呢？可以设置参数：max_log和DB2_FORCE_APP_ON_MAX_LOG注册变量。

max_log此参数指示一个事务可以消耗的主日志空间的百分比。该值是为logprimary配置参数指定的值的百分比。如果该值设置为0,那么对一个事务可以消耗的总的主日志空间的百分比没有限制。我们可以配合设置DB2_FORCE_APP_ON_MAX_LOG注册变量来规定如果应用程序违反了max_log配置，我们对该应用如何处理，DB2_FORCE_APP_ON_MAX_LOG设置为true，则超过max_log的应用回被强制与数据库断开连接，事务将被回滚，并且将返回错误SQL1224N。如果DB2_FORCE_APP_ON_MAX_LOG注册表变量设置为FALSE，则违反了max_log设置的的事务将失败，并返回错误SQL0964N。该应用程序仍然可以提交在工作单元中由先前语句完成的工作，它也可以回滚已完成的工作以撤销该工作单元。通过次设置我们可以保证即使有大事务操作，总有(l-max_log/100)*log_primary+log_second的日志可以用来处理日常交易，从而避免系统中断。注意：由max_log配置参数施加的限制不适用于下列DB2命令：ARCHIVELOG、BACKUPDATABASE、LOAD、REORGTABLE(联机)、RESTOREDATABASE和ROLLFORWARDDATABASEo$db2"rollforwarddbworkflowtoendoflogsandstopoverflowlogpath(/archlog/)"RollforwardStatusInputdatabasealias=workflowNumberofnodeshavereturnedstatus=1Nodenumber=0Rollforwardstatus=notpendingNextlogfiletoberead=Logfilesprocessed=S0001663.LOG-S0001663.LOGLastcommittedtransaction=2011-01-12-18.37.13.000000DB20000ITheROLLFORWARDcommandcompletedsuccessfully.c、事务日志满场景二：某个事务一直未提交，占用的日志不能被重用，导致日志满现在看另外一个场景，我在一个会话中执行了如下命令：C:\DocumentsandSettings\administrator〉db2+ccallproc_testlog(3)SQL0964C数据库的事务日志已满。SQLSTATE=57011

显然，数据库日志已满，于是，根据上面的方法，我找是哪个事务占用了日志。C:\DocumentsandSettings\administrator>db2pd-dbsample-transactionsDatabasePartition0--DatabaseSAMPLE--Active--Up0days00:10:12Transactions:AddressAppHandl[nod-index]TranHdlLocksStateTflagTflag2FirstlsnLastlsnLogSpaceSpaceReservedTIDAxRegCntGXID0x7FC21A8012 [000-00012]27READ0x000000000x000000000x0000000000000x000000000000000X0000000053A9100x7FC2278013[000-00013]30READ0x000000000x000000000x0000000000000x000000000000000x00000000538F100x7FC2348014 [000-00014]40READ0x000000000x000000000x0000000000000x000000000000000x0000000053BE100x7FC2418015[000-00015]50READ0x000000000x000000000x0000000000000x000000000000000x000000005391100x7FC24E8016[000-00016] 60READ0x000000000x000000000x0000000000000x000000000000000x000000005394100x7FC25B8017 [000-00017]74WRITE0x000000000x000000000x0000538A93B70x0000538A94551844080x00000000539A很奇怪，从结果显示，我没有发现任何一个占用大量日志的应用程序，日志的使用显然都非常的小，那为什么日志还会满呢?我们再注意下占用日志的应用，—查看下各自使用的日志文件。C:\DocumentsandSettings\administrator>db2pd-dbsample-logsDatabasePartition0--DatabaseSAMPLE--Active--Up0days00:12:34Logs:CurrentLogNumber4PagesWritten9498Method1ArchiveStatusn/aMethod1NextLogtoArchiven/aMethod1FirstFailuren/aMethod2ArchiveStatusn/aMethod2NextLogtoArchiven/aMethod2FirstFailuren/aAddressStartLSNStateSizePagesFilename0x7FBECBD40x0000537F00000x000000001000010000S0000000.LOG0x7FBECC740x000055F000000x000000001000010000S0000001.LOG0x7FBECD140x0000586100000x000000001000010000S0000002.LOG0x7EABB2F40x00005AD200000x000000001000010000S0000003.LOG0x7EABB3940x00005D4300000x000000001000010000S0000004.LOG分析发现，这个占用日志的应用的日志开始lsn为0x0000538A93B7，结束lsn为0x0000538A9455,正好落在第一日志文件中，因为这个事务一直没有被提交，所以S0000000.LOG一直不能被重用，这样业务在将主日志和辅助日志用完后，无法重新开始使用日志文件，导致出现日志满的错误。同样，使用上面的方法，我们可以查找出这个Applicationshandle为7的一直没有提交的小事务执行的是什么操作。上面的情况模拟方法：在一个回话中执行