DB2 LOAD导致日志满的另一种原因：delete阶段占用太多日志
正常情况下，db2 load操作只占很少的日志，但目前已知道有两种情况可能会导致日志满，第一种情况是build阶段，创建索引的时候，如果数据库参数LOGINDEXBUILD开启了，那么可能会导致日志满。
今天又发现一种情况，delete阶段也会占很多日志，可能导致日志满，问题非常容易重现，把DB2日志总大小调小，然后往表里LOAD会触发主键冲突的数据，过一会就会看到在delete阶段报SQL0964C
db2inst1@node01:~> seq 1 10000000 > t1.del
db2inst1@node01:~> seq 1 10000000 >> t1.del
db2inst1@node01:~> db2 "create table t1(id int not null primary key)"
DB20000I The SQL command completed successfully.
db2inst1@node01:~> db2 "load from t1.del of del replace into t1"
SQL3501W The table space(s) in which the table resides will not be placed in
backup pending state since forward recovery is disabled for the database.
SQL3109N The utility is beginning to load data from file
"/home/db2inst1/t1.del".
SQL3500W The utility is beginning the "LOAD" phase at time "07/16/2020
11:55:42.420980".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3110N The utility has completed processing. "20000000" rows were read
from the input file.
SQL3519W Begin Load Consistency Point. Input record count = "20000000".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "LOAD" phase at time "07/16/2020
11:56:19.830631".
SQL3500W The utility is beginning the "BUILD" phase at time "07/16/2020
11:56:19.832881".
SQL3213I The indexing mode is "REBUILD".
SQL3515W The utility has finished the "BUILD" phase at time "07/16/2020
11:58:12.131272".
SQL3500W The utility is beginning the "DELETE" phase at time "07/16/2020
11:58:12.196023".
SQL0964C The transaction log for the database is full. SQLSTATE=57011
SQL0964C The transaction log for the database is full. SQLSTATE=57011
db2inst1@node01:~> db2 "load query table t1"
SQL3500W The utility is beginning the "DELETE" phase at time "07/16/2020
11:58:12.196023".
SQL0964C The transaction log for the database is full. SQLSTATE=57011
SQL3532I The Load utility is currently in the "DELETE" phase.
SQL3534I The Load DELETE phase is approximately "0" percent complete.
Number of rows read = 20000000
Number of rows skipped = 0
Number of rows loaded = 20000000
Number of rows rejected = 0
Number of rows deleted = 0
Number of rows committed = 20000000
Number of warnings = 0
Tablestate:
Load Pending
期间的db2diag.log如下：
2020-07-16-11.58.12.198974-240 I8219726E577 LEVEL: Warning
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:315
DATA #1 : String, 100 bytes
LOADID: 23.2020-07-16-11.55.42.309869.0 (3;7)
Starting DELETE phase at 07/16/2020 11:58:12.196023.
2020-07-16-11.58.14.091975-240 E8220304E578 LEVEL: Warning
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data protection services, sqlpWriteToLog, probe:1660
MESSAGE : ADM1822W The active transaction log is being held by dirty pages.
Database performance may be impacted.
2020-07-16-11.58.14.236774-240 E8220883E625 LEVEL: Error
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:2860
MESSAGE : ADM1823E The active log is full and is held by application handle
"0-8". Terminate this application by COMMIT, ROLLBACK or FORCE
APPLICATION.
2020-07-16-11.58.14.237158-240 E8221509E591 LEVEL: Error
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data protection services, sqlpgResSpace, probe:6666
MESSAGE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
"Log File has reached its saturation point"
DIA8309C Log file was full.
2020-07-16-11.58.14.237362-240 I8222101E589 LEVEL: Error
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, data protection services, sqlpWriteLR, probe:6680
MESSAGE : ZRC=0x85100009=-2062548983=SQLP_NOSPACE
"Log File has reached its saturation point"
DIA8309C Log file was full.
2020-07-16-11.58.14.237633-240 I8222691E607 LEVEL: Error
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0
DATA #1 : String, 132 bytes
LOADID: 23.2020-07-16-11.55.42.309869.0 (3;7)
LOAD record delete failed , -2062548983, (nil), Detected in file:sqluldel.C, Line:1122
2020-07-16-11.58.14.240119-240 I8223299E582 LEVEL: Error
PID : 5188 TID : 140591337826048 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : SAMPLE
APPHDL : 0-8 APPID: *LOCAL.db2inst1.200716155324
AUTHID : DB2INST1 HOSTNAME: node01
EDUID : 23 EDUNAME: db2agent (SAMPLE) 0
FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0
DATA #1 : String, 107 bytes
LOADID: 23.2020-07-16-11.55.42.309869.0 (3;7)
, -2146107283, (nil), Detected in file:sqluldel.C, Line:1275
本文地址：https://blog.csdn.net/qingsong3333/article/details/107398364
下一篇： 运维工作台方案详解