Reproducing the problem:
1. Create 2 tables with similar structure in a tablespace
using a bufferpool spanning multiple partitions and insert data
in one of the tables
db2 -v "create table tmp.tab01 (col01 int, col02 char(10)) in
TS1"
db2 -v "create table tmp.tab02 (col01 int, col02 char(10)) in
TS1"
db2 -v "insert into tmp.tab01
values(1,'abc'),(2,'def'),(3,'ghi'),(4,'jkl'),(5,'mno'),
(6,'pqr'), (7,'stu'), (8,'vwx')"
2. Use the following LOAD command with the
SYSPROC.ADMIN_CMD() procedure
db2 -v "CALL SYSPROC.ADMIN_CMD('LOAD FROM (SELECT * FROM
tmp.tab01) OF CURSOR MESSAGES ON SERVER REPLACE INTO tmp.tab02
NONRECOVERABLE MODE PARTITION_AND_LOAD')"
Result set 1
------------------
ROWS_READ ROWS_SKIPPED ROWS_LOADED ROWS_REJECTED
ROWS_DELETED ROWS_COMMITTED ROWS_PARTITIONED
NUM_AGENTINFO_ENTRIES MSG_RETRIEVAL
MSG_REMOVAL
---------------------- --------------------------
-------------------------- ----------------------------
--------------------------- -------------------------------
---------------------------------
-------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
----------------------------------------------------------------
-----------------------------------------------------
------------------------------------------------------
8 - -
0 - -
8 3
SELECT DBPARTITIONNUM, AGENTTYPE, SQLCODE, MSG FROM
TABLE(SYSPROC.ADMIN_GET_MSGS('32533_31879_USER1')) AS MSG CALL
SYSPROC.ADMIN_REMOVE_MSGS('32533_31879_USER1')
1 record(s) selected.
Result set 2
--------------
DBPARTITIONNUM SQLCODE TABSTATE AGENTTYPE
---------------------------- -----------------
---------------- --------------------
0 0 NORMAL
LOAD
1 0 NORMAL
LOAD
1 0 NORMAL
PARTITIONING
3 record(s) selected.
Return Status = 0
3. Executing the query returned in the MSG_RETRIEVAL
column of result set 1, the DBPARTITIONNUM and AGENTTYPE columns
are not getting populated in the result set and have NULL values
instead of the partition nodes and LOAD agent type respectively
$ db2 -v "SELECT DBPARTITIONNUM, AGENTTYPE, SQLCODE, MSG FROM
TABLE(SYSPROC.ADMIN_GET_MSGS('32533_31879_USER1')) AS MSG"
DBPARTITIONNUM AGENTTYPE SQLCODE MSG
---------------------------- --------------------
----------------
---------------------------------------------------------------
- - 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.
- - SQL3039W The
memory available to LOAD for DATA BUFFER prohibits full LOAD
parallelism. Load parallelism of "4" will be used
- - SQL1193I The
utility is beginning to load data from the SQL statement
"CURSOR".
- - SQL3500W The
utility is beginning the "LOAD" phase at time "11/25/2013
13:16:18.802422".
- - SQL3519W
Begin Load Consistency Point. Input record count = "0".
- - SQL3520W
Load Consistency Point was successful.
- - SQL3110N The
utility has completed processing. "2" rows were read from the
input file.
- - SQL3519W
Begin Load Consistency Point. Input record count = "2".
- - SQL3520W
Load Consistency Point was successful.
- - SQL3515W The
utility has finished the "LOAD" phase at time "11/25/2013
13:16:19.026859".
- - 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.
- - SQL3039W The
memory available to LOAD for DATA BUFFER prohibits full LOAD
parallelism. Load parallelism of "4" will be used
- - SQL1193I The
utility is beginning to load data from the SQL statement
"CURSOR".
- - SQL3500W The
utility is beginning the "LOAD" phase at time "11/25/2013
13:16:18.805902".
- - SQL3519W
Begin Load Consistency Point. Input record count = "0".
- - SQL3520W
Load Consistency Point was successful.
- - SQL3110N The
utility has completed processing. "6" rows were read from the
input file.
- - SQL3519W
Begin Load Consistency Point. Input record count = "6".
- - SQL3520W
Load Consistency Point was successful.
- - SQL3515W The
utility has finished the "LOAD" phase at time "11/25/2013
13:16:19.025516".
- - SQL27903I
"PARTITION" has started on partition "1" at time "11/25/2013
13:16:18.811603".
- - SQL27950I The
type of the input data file is "3".
- - SQL27914I The
mode of operation is "PARTITION".
- - SQL27920I
This utility is using " 1" partitioning keys.
- - SQL27921I
"COL01" Start:"0" Len:"4" Position:"1" Type:"1".
- - SQL27935I
"PARTITION" has ended on partition "1" at time "11/25/2013
13:16:18.973711".
- - SQL27936I
Elapsed time: " 0" hours, "0" minutes, " 0" seconds.
- - SQL27937I
Throughput: "8" records/sec.
- - SQL27939I
Record counts for output partitions: partition number "0".
Record count: "2".
- - SQL27939I
Record counts for output partitions: partition number "1".
Record count: "6".
31 record(s) selected. |
