I am using DB2 LUW v11.1 with BLU acceleration.
When I run the following code, I got the error "DB21018E A system error occurred. The command line processor could not continue processing."
create table diabetes_v3_2.comm_outpatient_services_t2dm
as (select * from commercial.outpatient_services)
with no data
not logged initially;
update command options using c off;
insert into diabetes_v3_2.comm_outpatient_services_t2dm
select * from commercial.outpatient_services
where enrolid in (
select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment
);
commit;
The following is the error message:
/* Generate cohort data for the cohort after * filtering according to age and continuous * enrollment criteria. */ /* facility header */ /* inpatient admissions */ /* inpatient services */ /* outpatient prescription drugs */ /* outpatient services */ create table diabetes_v3_2.comm_outpatient_services_t2dm as (select * from commercial.outpatient_services) with no data not logged initially
DB20000I The SQL command completed successfully.
update command options using c off
DB20000I The UPDATE COMMAND OPTIONS command completed successfully.
insert into diabetes_v3_2.comm_outpatient_services_t2dm select * from commercial.outpatient_services where enrolid in ( select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment )
DB21018E A system error occurred. The command line processor could not
continue processing.
"commercial.outpatient_services" is a very large table (> 4 billion rows) and "diabetes_v3_2.t2dm_cohort_filter_age_enrollment" is smaller (~ 4 million rows).
=== UPDATE 2017-02-09 11:48 EST===
I check the db2 diagnostic log file: /home/db2inst1/sqllib/db2dump/db2diag.log. The following is the log I think related to the error:
2017-02-08-19.17.05.245747+000 E431913963E727 LEVEL: Warning
PID : 2936 TID : 138686956037888 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-5261 APPID: *LOCAL.db2inst1.170208191704
AUTHID : PANC HOSTNAME: remote.server.host.name
EDUID : 627 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, data management, sqldEndNoLogList, probe:1
MESSAGE : ADM5530W The COMMIT processing of table
"DIABETES_V3_2.COMM_OUTPATIENT_SERVICES_T2DM" that used NOT LOGGED
INITIALLY has been initiated. It is recommended that you take a
backup of this table's table space(s).
2017-02-08-19.17.05.285806+000 E431914691E744 LEVEL: Warning
PID : 2936 TID : 138686956037888 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-5261 APPID: *LOCAL.db2inst1.170208191704
AUTHID : PANC HOSTNAME: remote.server.host.name
EDUID : 627 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, data management, sqldEndNoLogList, probe:1
MESSAGE : ADM5530W The COMMIT processing of table "SYSIBM
.SYN170208191705181821_COMM_OUTPATIENT_SERVICES_T2DM" that used NOT
LOGGED INITIALLY has been initiated. It is recommended that you take
a backup of this table's table space(s).
.................
.................
.................
Skipped some logs
.................
.................
.................
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
:
STOP : Automatic reorg evaluation has finished successfully on database MYDATABASE
2017-02-09-06.26.38.910171+000 E431936585E1146 LEVEL: Info
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
PID : 2936 TID : 138687035729664 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-6071 APPID: *LOCAL.db2inst1.170209054604
AUTHID : DB2INST1 HOSTNAME: remote.server.host.name
EDUID : 575 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, relation data serv, sqlrr_rds_common_post, probe:5245
MESSAGE : ZRC=0x8012006D=-2146303891=SQLR_CA_BUILT
"SQLCA has already been built"
DATA #1 : String, 31 bytes
UCstate terminate bit is raised
DATA #2 : Hexdump, 4 bytes
0x00007E227FA2FD88 : 4301 0000 C...
DATA #3 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : SQLE05A
sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate:
2017-02-09-06.26.38.915527+000 I431937732E706 LEVEL: Error
PID : 2956 TID : 140379364529920 PROC : db2acd 0
INSTANCE: db2inst1 NODE : 000
HOSTNAME: remote.server.host.name
FUNCTION: DB2 UDB, Health Monitor, db2AutoReorgExec, probe:190
DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
sqlerrmc:
sqlerrp : db2Reorg
sqlerrd : (1) 0x8037006D (2) 0x00000000 (3) 0x00000000
(4) 0x00000000 (5) 0x00000000 (6) 0x00000000
sqlwarn : (1) (2) (3) (4) (5) (6)
(7) (8) (9) (10) (11)
sqlstate: 55032
2017-02-09-06.26.38.915697+000 I431938439E404 LEVEL: Event
PID : 2956 TID : 140379364529920 PROC : db2acd 0
INSTANCE: db2inst1 NODE : 000
HOSTNAME: remote.server.host.name
FUNCTION: DB2 UDB, Health Monitor, db2AutoReorgExec, probe:10
STOP : Automatic reorg has completed with errors on table MYDATABASE ."DIABETES_V3_2"."COMM_OUTPATIENT_SERVICES_T2DM"
2017-02-09-06.26.39.385239+000 E431938844E612 LEVEL: Info
PID : 2936 TID : 138686956037888 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-5261 APPID: *LOCAL.db2inst1.170208191704
AUTHID : PANC HOSTNAME: remote.server.host.name
EDUID : 627 EDUNAME: db2agent (MYDATABASE) 0
FUNCTION: DB2 UDB, buffer pool services, sqlbCleanupBeforeTerm, probe:8024
DATA #1 : SQLP_LSN8, PD_TYPE_SQLP_LSN8, 8 bytes
00000000D456386A
DATA #2 : SQLP_LSN8, PD_TYPE_SQLP_LSN8, 8 bytes
00000000D456386A
2017-02-09-06.26.39.514755+000 E431939457E992 LEVEL: Info
PID : 2936 TID : 139705853142784 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
HOSTNAME: remote.server.host.name
EDUID : 591 EDUNAME: db2loggr (MYDATABASE) 0
FUNCTION: DB2 UDB, data protection services, sqlpghck, probe:2820
DATA #1 : <preformatted>
Log stream 0 has been marked consistent.
Head Extent ID: 0
Group Head Extent ID: 0
Startup LSO: 0
Base LSO: 2864382783169
Last Log Record LSO: 2864344546100
Last Log Record LSN: 00000000D4563869
Next LSN: 00000000D456386A
Lowtran LSN: 00000000D456386A
Minbuff LSN: 00000000D456386A
Head LSN: 00000000D456386A
Group Head LSN: 00000000D456386A
Group Minbuff LSN: 00000000D456386A
2017-02-09-06.26.39.521643+000 E431940450E480 LEVEL: Info
PID : 2936 TID : 139705853142784 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
HOSTNAME: remote.server.host.name
EDUID : 591 EDUNAME: db2loggr (MYDATABASE) 0
FUNCTION: DB2 UDB, data protection services, sqlpghck, probe:2889
DATA #1 : <preformatted>
Database has been marked consistent.
Log File Size: 1000000
2017-02-09-06.26.42.281169+000 E431940931E549 LEVEL: Event
FUNCTION: DB2 UDB, data protection services, sqlpghck, probe:2820
DATA #1 : <preformatted>
Log stream 0 has been marked consistent.
Head Extent ID: 0
Group Head Extent ID: 0
Startup LSO: 0
Base LSO: 2864382783169
Last Log Record LSO: 2864344546100
Last Log Record LSN: 00000000D4563869
Next LSN: 00000000D456386A
Lowtran LSN: 00000000D456386A
Minbuff LSN: 00000000D456386A
Head LSN: 00000000D456386A
Group Head LSN: 00000000D456386A
Group Minbuff LSN: 00000000D456386A
2017-02-09-06.26.39.521643+000 E431940450E480 LEVEL: Info
PID : 2936 TID : 139705853142784 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
HOSTNAME: remote.server.host.name
EDUID : 591 EDUNAME: db2loggr (MYDATABASE) 0
FUNCTION: DB2 UDB, data protection services, sqlpghck, probe:2889
DATA #1 : <preformatted>
Database has been marked consistent.
Log File Size: 1000000
2017-02-09-06.26.42.281169+000 E431940931E549 LEVEL: Event
PID : 2936 TID : 138686956037888 PROC : db2sysc 0
INSTANCE: db2inst1 NODE : 000 DB : MYDATABASE
APPHDL : 0-5261 APPID: *LOCAL.db2inst1.170208191704
AUTHID : PANC HOSTNAME: remote.server.host.name
EDUID : 627 EDUNAME: db2agent (idle) 0
FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown, probe:15912
STOP : DATABASE: MYDATABASE : DEACTIVATED: NO
I noticed the error code sqlcode: -1224 in the log, then I checked the meaning of this code by using db2 ? sql1224 which says:
SQL1224N The database manager is not able to accept new requests, has
terminated all requests in progress, or has terminated the
specified request because of an error or a forced interrupt.
But there could be many possibilities that the database manager does not accept new requests. How to further investigate?
=== UPDATE 2017-02-09 20:51 ===
Instead of using INSERT, I used LOAD to populate the table. The following is the script:
create table diabetes_v3_2.comm_outpatient_services_t2dm
as (select * from commercial.outpatient_services)
with no data;
declare load_cursor cursor for
select * from commercial.outpatient_services
where enrolid in (
select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment
);
load from load_cursor of cursor insert into diabetes_v3_2.comm_outpatient_services_t2dm;
However, I still got an error (a different one):
/* Generate cohort data for the cohort after * filtering according to age and continuous * enrollment criteria. */ /* facility header */ /* inpatient admissions */ /* inpatient services */ /* outpatient prescription drugs */ /* outpatient services */ create table diabetes_v3_2.comm_outpatient_services_t2dm as (select * from commercial.outpatient_services) with no data
DB20000I The SQL command completed successfully.
declare load_cursor cursor for select * from commercial.outpatient_services where enrolid in ( select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment )
DB20000I The SQL command completed successfully.
load from load_cursor of cursor insert into diabetes_v3_2.comm_outpatient_services_t2dm
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.
SQL1193I The utility is beginning to load data from the SQL statement "
select * from commercial.outpatient_services where enrolid in ( s...".
SQL3500W The utility is beginning the "ANALYZE" phase at time "02/09/2017
19:22:52.072421".
SQL3519W Begin Load Consistency Point. Input record count = "0".
SQL3520W Load Consistency Point was successful.
SQL3515W The utility has finished the "ANALYZE" phase at time "02/09/2017
20:34:46.664280".
SQL3500W The utility is beginning the "LOAD" phase at time "02/09/2017
20:34:46.665758".
SQL0952N Processing was cancelled due to an interrupt. SQLSTATE=57014
SQL3185W The previous error occurred while processing data from row
"210184038" of the input file.
SQL0952N Processing was cancelled due to an interrupt. SQLSTATE=57014
SQL0952N Processing was cancelled due to an interrupt. SQLSTATE=57014
DB21018E A system error occurred. The command line processor could not
continue processing.
This links to the related entries I found in db2diag.log: https://github.com/panCtrlV/storage/blob/master/db2_load_from_cursor_error_log-hide
As suggested by my group's db expert, I used old fashioned way to create the table, then used "load from cursor" (suggested by @mustaccio) to populate the table from select ....
The following is how the new script looks like and it succeeded this time:
create table diabetes_v3_2.comm_outpatient_services_t2dm (
col1_name col1_type,
col2_name col1_type,
...
);
declare load_cursor cursor for
select * from commercial.outpatient_services
where enrolid in (
select enrolid from diabetes_v3_2.t2dm_cohort_filter_age_enrollment
);
load from load_cursor of cursor insert into diabetes_v3_2.comm_outpatient_services_t2dm;