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