DPF环境下表LOAD报错，错误码 sqlcode: -6024
系统是8个节点的DPF环境，表EDW_COD建在0节点，在LOAD数据时很久没有完成
1、load query 查看状态，状态为 load in progress
2、load utilities查看进度，结果如下所示，信息显示还处于load阶段
ID = 2547 Type = LOAD Database Name = EDW Member Number = 0 Description = [LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4)] [10.1.5.114.34153.151113102427] OFFLINE LOAD ASC AUTOMATIC INDEXING INSERT NON-RECOVERABLE EDW .EDW_COD Start Time = 11/13/2015 18:25:01.538274 State = Executing Invocation Type = User Progress Monitoring: Phase Number = 1 Description = SETUP Total Work = 0 bytes Completed Work = 0 bytes Start Time = 11/13/2015 18:25:01.538279 Phase Number [Current] = 2 Description = LOAD Total Work = 3558376 rows Completed Work = 3558376 rows Start Time = 11/13/2015 18:25:01.573520 Phase Number = 3 Description = BUILD Total Work = 2 indexes Completed Work = 0 indexes Start Time = Not Started
3、查看db2diag.log日志，日志显示预分区代理线程db2lpprt启动后就报错了，后续的数据格式化线程db2lfrm还未开始运行。错误由1节点报出，错误代码为-6024，意思是“应用程序连接至节点 节点号，但是未定义表或索引 名称。”后面检查表的DDL，表的确是建在0节点，索引建立时并未指定表空间，默认也会在表所在的表空间，说明表和索引的定义没有问题。
不知道问题到底出在哪里呢？还请各位老师帮忙分析一下，谢谢！
2015-11-13-18.25.01.526871+480 I2096486285A624 LEVEL: Warning PID : 5702106 TID : 48655 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 48655 EDUNAME: db2agent (EDW) 0 FUNCTION: DB2 UDB, database utilities, sqluvtld_route_in, probe:865 DATA #1 : LOADID, PD_TYPE_LOADID, 50 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (-1;-1) DATA #2 : String, 64 bytes Starting LOAD operation (C) (4) (I) [EDW .EDW_COD]. 2015-11-13-18.25.01.535168+480 I2096486910A532 LEVEL: Warning PID : 5702106 TID : 56094 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 56094 EDUNAME: db2lcata 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 82 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Catalog subagent is running. 0, 0 2015-11-13-18.25.01.537807+480 I2096487443A560 LEVEL: Warning PID : 5702106 TID : 50195 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 50195 EDUNAME: db2linit 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 109 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Init SA is running, (status = 1) (action = 5) (op = I). 0, 0 2015-11-13-18.25.01.557171+480 I2096488004A530 LEVEL: Warning PID : 5702106 TID : 50195 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 50195 EDUNAME: db2linit 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 80 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Init SA is exiting iRun(). 0, 0 2015-11-13-18.25.01.564833+480 I2096488535A560 LEVEL: Warning PID : 5702106 TID : 50195 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 50195 EDUNAME: db2linit 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 109 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Init SA is running, (status = 4) (action = 5) (op = I). 0, 0 2015-11-13-18.25.01.565224+480 I2096489096A530 LEVEL: Warning PID : 5702106 TID : 50195 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 50195 EDUNAME: db2linit 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 80 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Init SA is exiting iRun(). 0, 0 2015-11-13-18.25.01.565754+480 I2096489627A539 LEVEL: Warning PID : 5702106 TID : 56094 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 56094 EDUNAME: db2lcata 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 89 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Catalog subagent is exiting iRun(). 0, 0 2015-11-13-18.25.01.570099+480 I2096490167A523 LEVEL: Warning PID : 5702106 TID : 56094 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 56094 EDUNAME: db2lload 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 73 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Load SA is running. 0, 0 2015-11-13-18.25.01.575360+480 I2096490691A527 LEVEL: Warning PID : 5702106 TID : 24104 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 24104 EDUNAME: db2lrid 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 78 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Load CPU parallelism is: 7, 0 2015-11-13-18.25.01.661178+480 I2096491219A594 LEVEL: Warning PID : 5702106 TID : 18464 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 18464 EDUNAME: db2lfrm0 0 FUNCTION: DB2 UDB, database utilities, sqlulPrintPhaseMsg, probe:314 DATA #1 : String, 133 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Starting LOAD phase at 2015-11-13 18:25:01.658701. Table EDW .EDW_COD 2015-11-13-18.25.01.661370+480 I2096491814A542 LEVEL: Warning PID : 5702106 TID : 50195 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19432 APPID: 10.1.5.114.34153.151113102427 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 50195 EDUNAME: db2lpprt 0 FUNCTION: DB2 UDB, database utilities, DIAG_NOTE, probe:0 DATA #1 : String, 92 bytes LOADID: 48655.2015-11-13-18.25.01.526850.0 (9;4) Pre-Partitioning Sub-agent is running. 0, 0 2015-11-16-11.20.58.355015+480 I2096492357A1088 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluCTable::iGetTableInfoForLoadQuery, probe:1944 MESSAGE : ZRC=0x8015006D=-2146107283=SQLU_CA_BUILT "SQLCA has already been built" DATA #1 : String, 0 bytes Object not dumped: Address: 0x0000000000000000 Size: 0 Reason: Address is NULL DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -6024 sqlerrml: 18 sqlerrmc: EDW_COD 1 sqlerrp : SQLUTRGT sqlerrd : (1) 0x8015006D (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000001 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 2015-11-16-11.20.58.356524+480 I2096493446A499 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : , -2146107283, 0, Detected in file:sqluqagt.C, Line:749 2015-11-16-11.20.58.357429+480 I2096493946A503 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : sqluv_rollback , 0, 2, Detected in file:sqluvutl.C, Line:940 2015-11-16-11.22.02.244396+480 I2096494450A1088 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluCTable::iGetTableInfoForLoadQuery, probe:1944 MESSAGE : ZRC=0x8015006D=-2146107283=SQLU_CA_BUILT "SQLCA has already been built" DATA #1 : String, 0 bytes Object not dumped: Address: 0x0000000000000000 Size: 0 Reason: Address is NULL DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -6024 sqlerrml: 18 sqlerrmc: EDW_COD 1 sqlerrp : SQLUTRGT sqlerrd : (1) 0x8015006D (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000001 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 2015-11-16-11.22.02.245746+480 I2096495539A499 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : , -2146107283, 0, Detected in file:sqluqagt.C, Line:749 2015-11-16-11.22.02.246636+480 I2096496039A503 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : sqluv_rollback , 0, 2, Detected in file:sqluvutl.C, Line:940 2015-11-16-11.24.12.328391+480 I2096496543A1088 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluCTable::iGetTableInfoForLoadQuery, probe:1944 MESSAGE : ZRC=0x8015006D=-2146107283=SQLU_CA_BUILT "SQLCA has already been built" DATA #1 : String, 0 bytes Object not dumped: Address: 0x0000000000000000 Size: 0 Reason: Address is NULL DATA #2 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes sqlcaid : SQLCA sqlcabc: 136 sqlcode: -6024 sqlerrml: 18 sqlerrmc: EDW_COD 1 sqlerrp : SQLUTRGT sqlerrd : (1) 0x8015006D (2) 0x00000000 (3) 0x00000000 (4) 0x00000000 (5) 0x00000000 (6) 0x00000001 sqlwarn : (1) (2) (3) (4) (5) (6) (7) (8) (9) (10) (11) sqlstate: 2015-11-16-11.24.12.333292+480 I2096497632A499 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : , -2146107283, 0, Detected in file:sqluqagt.C, Line:749 2015-11-16-11.24.12.334203+480 I2096498132A503 LEVEL: Error PID : 4522056 TID : 23489 PROC : db2sysc 1 INSTANCE: db2inst1 NODE : 001 DB : EDW APPHDL : 1-3605 APPID: *N1.db2inst1.151116031136 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 23489 EDUNAME: db2agent (EDW) 1 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : sqluv_rollback , 0, 2, Detected in file:sqluvutl.C, Line:940 2015-11-16-11.24.35.399502+480 I2096498636A503 LEVEL: Error PID : 5702106 TID : 37491 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-20918 APPID: *N0.db2inst1.151116032432 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 37491 EDUNAME: db2agent (EDW) 0 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : sqluv_rollback , 0, 3, Detected in file:sqluvutl.C, Line:940 2015-11-16-13.59.22.927712+480 I2096499140A503 LEVEL: Error PID : 5702106 TID : 92190 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19922 APPID: *N0.db2inst1.151116055737 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 92190 EDUNAME: db2agent (EDW) 0 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : sqluv_rollback , 0, 3, Detected in file:sqluvutl.C, Line:940 2015-11-16-15.09.00.795503+480 I2096499644A503 LEVEL: Error PID : 5702106 TID : 92190 PROC : db2sysc 0 INSTANCE: db2inst1 NODE : 000 DB : EDW APPHDL : 0-19922 APPID: *N0.db2inst1.151116055737 AUTHID : DB2INST1 HOSTNAME: dw01 EDUID : 92190 EDUNAME: db2agent (EDW) 0 FUNCTION: DB2 UDB, database utilities, sqluReportErrToDiag, probe:0 MESSAGE : sqluv_rollback , 0, 3, Detected in file:sqluvutl.C, Line:940
5同行回答
如果是从DataStage导数据过来，就不太好判断了，因为无法定位是db2这边还是DataStage那边的问题。我觉得有可能是DataStage那边的问题，因为你的load没有报错，而是hang在那里，一般db2这边的问题，load都会报错退出的。
DataStage有一种导数据的方法，是创建一个pipe，然后datastage往pipe里面写数据，db2从pipe读数据，如果datastage异常退出，导致pipe一直没有数据写入，就可能出现你现在的状况。
db2diag.log这一段信息，其实没有什么关键的内容，因为load的时候，即使不出错误也会在db2diag.log里面爆出一堆内容，所以不一定有参考意义。既然load没有退出而是hang在那里，我建议你看看datastage那边是否正常。