Results 1 to 2 of 2
  1. #1
    Join Date
    Feb 2011
    Posts
    36

    Unanswered: Drop column failure

    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

  2. #2
    Join Date
    Feb 2011
    Posts
    36

    Workaround - but I would still like a better answer

    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.
    Last edited by Brian.Hart; 02-28-11 at 13:08. Reason: Workaround was not fully effective

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •