一例日志空间满带来的insert性能的优化

一例日志空间满带来的insert性能的优化：
症状：
大型数据仓库系统，至少有20个终端连接上去做查询等运维处理。手工调起一个脚本，
该脚本中使用insert...select的方式将一个较大数据量的表的数据抽取部分字段到另一张表中,后系统反应变慢.

--应用报错如下:
Step 2: CMD=[
insert into kf2.MM_test_cdr select 201008, 'AA', A.USR_MOB_NBR,
A.B_NBR, A.LOCAL_CD, A.CELL_CD, A.B_BRND_CD, A.CALL_DT,
A.CALL_CNT, A.CALL_DUR, A.DIR_TYP_CD from ods.to_cdr_AA201008 A with ur
]
DB21034E The command was processed as an SQL statement because
it was not a valid Command Line Processor command. During SQL
processing it returned: SQL0964C The transaction log for the
database is full. SQLSTATE=57011

--查系统诊断日志db2diag.log
2003-01-16-02.53.54.935308 Instance:db2inst1 Node:016
PID:144252(db2agntp (SAMPLE) 16) Appid:*.*
data_protection sqlpgrsp Probe:50 Database:SAMPLE
Log Full -- active log held by appl. handle 787273
End this application by COMMIT, ROLLBACK or FORCE APPLICATION.
:
:

--发现问题
db2 list applications;
--
Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
ETL db2bp 1245 *N0.db2root.101203022137 BITEST 4
ETL db2jccDefaultQ 880 GAC8196B.GB4C.101203012535 BITEST 4
ETL db2bp 1243 *N0.db2root.101203012411 BITEST 1

--解决方法
--使用force停掉应用(不建议用db2stop force停掉数据库,也不要用kill来直接杀进程)
db2 force application(880); --此步骤为异步执行,可以再次用db2 list applications查看是否停掉了应用.

--事务所需日志空间
插入记录 日志记录

--获取日志空间配置信息：
--db2 get db cfg for bidw |grep log
[195/app/etl/_xx]db2 get db cfg for bitest |grep -i 'log file'
Log file size (4KB) (LOGFILSIZ) = 25000
Number of primary log files (LOGPRIMARY) = 40
Number of secondary log files (LOGSECOND) = 2
--计算日志空间大小：
(LOGPRIMARY+LOGSECOND)*(LOGFILSIZ)*(page sizes)=(40+2)*25000*4K ~4.2G

--如果需要修改日志空间配置参数
db2 update db cfg for using LOGPRIMARY 50
db2 update db cfg for using LOGSECOND 20
db2 update db cfg for using LOGFILSIZ 10240 --单位为页大小(默认4K)

--对于事务日志空间不足的情况变通的处理方法:
1.使用load cursor的方式不记录日志
declare my_cursor cursor for select ...;
db2 load from my_cursor of cursor insert into my_target_table nonreoverable;
2.使用导出在倒入的方式
export to xx of del modified by coldel, select * from my_source_table;
load from xx of del modified by coldel, insert into my_target_table;
3.建表或者改表激活not logged initially 特性
:create table my_target_table (col1 type,...) not logged initially ;
eg:create table my_target_table (id int ,col1 varchar(200)) not logged initially ;
:alter table my_target_tale activate not logged initially ;

--事务所需的事务日志空间估算
##Insert Record|| Rollback Delete Record|| Rollback Update Record Log Record Structure
Description||Type||Offset (Bytes)
---------------------------------
Log header||DMSLogRecordHeader||0(6)
Internal||Internal||6(2)
Record Length||unsigned short||8(2)
Free space||unsigned short||10(2)
RID||char[]||12(6)
Record offset||unsigned short||18(2)
Record header and data||variable||20(variable)
Total Length: 20 bytes plus record length

Following are details about the record header and data:
Record header
? 4 bytes
? Record type (unsigned char, 1 byte).
? Reserved (char, 1 byte)
? Record length (unsigned short, 2 bytes)
Record
? Variable length
? Record type (unsigned char, 1 byte).
? Reserved (char, 1 byte)
? The rest of the record is dependent upon the record type and the table descriptor record defined for the table.
? The following fields apply to user data records with record type having the 1 bit set:
o Fixed length (unsigned short, 2 bytes). This is the length of the fixed length section of the data row.
o Formatted record (all of the fixed length columns, followed by the variable length columns).
? The following fields apply to user data records with record type having the 2 bit set:
o Number of columns (unsigned short, 2 bytes). This is the number of columns in the data portion of the data row. See Formatted user data record for table with VALUE COMPRESSION.
Note: the offset array will contain 1 + the number of columns.
o Formatted record (offset array, followed by the data columns).
A user record is specified completely by the following characteristics:
1. Outer record type is 0, or
2. Outer record type is 0x10, or
3. Outer record type has the 0x04 bit set and
1. Inner record type has the 0x01 bit set, or
2. Inner record type has the 0x02 bit set.
Note: Row compression and data capture are not compatible.


??提两个问题
1.使用db2stop直接停数据库,和使用kill杀进程的缺点?
2.对于insert日志条目record部分的计算?
[@more@]

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/23937368/viewspace-1042786/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/23937368/viewspace-1042786/