Background:

I am attempting to move a rather large field (VarChar 180) from a very large table to its own table. This is required because an upgrade from my software vendor generates a tablespace overflow on the 8K tablespace as it attempts to add new fields, and my pre-existing custom field turned out to be the one that put it over 8k.

Here are the SQL statements. The first two work fine, but the third fails. It did, however, work correctly in my first test upgrade going from DB2 9.1 to DB2 9.5 last week, but now fails in a new test upgrade from DB2 9.1 to 9.7 FP3a this week.

CREATE TABLE TLORDER_KP_TRACE(
DETAIL_LINE_ID INTEGER NOT NULL,
KP_TRACE_NUMS VARCHAR(180),
PRIMARY KEY (DETAIL_LINE_ID))

INSERT INTO TLORDER_KP_TRACE
SELECT DETAIL_LINE_ID, KP_TRACE_NUMS
FROM TLORDER
WHERE COALESCE(KP_TRACE_NUMS, '') <> ''

ALTER TABLE TLORDER DROP COLUMN KP_TRACE_NUMS

The last statement generates a SQL0727N error with implicit system action type "3", SQLCode -270, SQLSTATE 42977. According to the documentation, that should be due to failed regeneration of a dependent view, but there are no views (nor, indeed, procedures or triggers) that refer to the KP_TRACE_NUMS field. (There is one dependent procedure, but I manually drop that before beginning the upgrade and re-create it after the upgrade is complete to prevent any dependency-related errors during the ugprade, so it does not exist in the DB at the time the above statement is run).

Here are the three associated DB2DIAG log entries:

2011-02-26-10.24.07.493000-480 I161184F522 LEVEL: Severe
PID : 2376 TID : 1812 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : KPTEST
APPHDL : 0-53 APPID: *LOCAL.DB2.110226181503
AUTHID : LYNX
EDUID : 1812 EDUNAME: db2agent (KPTEST)
FUNCTION: DB2 UDB, catalog services, sqlrlDescendInvalidHiearchy, probe:14914
RETCODE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
"unexpected error but state is OK"

2011-02-26-10.24.07.493000-480 I161708F515 LEVEL: Severe
PID : 2376 TID : 1812 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : KPTEST
APPHDL : 0-53 APPID: *LOCAL.DB2.110226181503
AUTHID : LYNX
EDUID : 1812 EDUNAME: db2agent (KPTEST)
FUNCTION: DB2 UDB, catalog services, sqlrlDropDepTriggers, probe:14068
RETCODE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
"unexpected error but state is OK"

2011-02-26-10.24.07.493000-480 I162225F608 LEVEL: Severe
PID : 2376 TID : 1812 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : KPTEST
APPHDL : 0-53 APPID: *LOCAL.DB2.110226181503
AUTHID : LYNX
EDUID : 1812 EDUNAME: db2agent (KPTEST)
FUNCTION: DB2 UDB, catalog services, sqlrlalt, probe:990
MESSAGE : ZRC=0x803100AF=-2144272209=SQLNN_E_BADNEWS
"unexpected error but state is OK"
DATA #1 : String, 33 bytes
Probe that returned non-zero rc:
DATA #2 : unsigned integer, 4 bytes
10255

I found a workaround that got me past this particular error, but now I continue to receive the error in the next stage of my upgrade.

My fix for this step was thus:

Although nothing in the DB2 documentation of the error indicates that the issue is size-related, I set all values to null in the column I need to drop. That did not change the behavior of the DROP COLUMN statement, so I then altered the column's data type to something much shorter (integer, to be exact), and I was then able to drop the column without error.

UPDATE TLORDER SET KP_TRACE_NUMS = NULL
ALTER TABLE TLORDER ALTER COLUMN KP_TRACE_NUMS SET DATA TYPE INTEGER
ALTER TABLE TLORDER DROP COLUMN KP_TRACE_NUMS

I thought I could probably get through my upgrade with this; however, the same error returned in various placed on the next stage of my upgrade, so I am right back where I started with it.