Results 1 to 5 of 5
  1. #1
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1

    Unanswered: Error droping a column in table.

    good afternoon,
    I have DB2 V8.2 AIX 5.3.

    The following is the output of db2look
    CREATE TABLE "pepito"."GEORGI" (
    "CO_ELEMENTO" CHAR(7) NOT NULL WITH DEFAULT '' ,
    "NU_PERIODO_VALIDEZ" SMALLINT NOT NULL WITH DEFAULT 0 ,
    "CO_PROV" CHAR(2) NOT NULL WITH DEFAULT '' ,
    "CO_EPOB" CHAR(5) NOT NULL WITH DEFAULT '' ,
    "DS_FLAG_3" CHAR(1) ,
    "DS_FLAG_4" CHAR(1) ,
    "HD_P_EPOB_DS_NOMBRE_1" "DB2TX "."DB2TEXTH" ,
    "HD_N_EPOB_DS_NOMBRE_1" "DB2TX "."DB2TEXTH" )
    DATA CAPTURE CHANGES
    IN "KJEPOBTS" INDEX IN "KJEPOBIX" ;

    The table has an index of the field you want to delete
    CREATE INDEX "DB2TX "."GEORGI_001" ON "pepito"."GEORGI"
    ("HD_N_EPOB_DS_NOMBRE_1" ASC)

    My problem is this:
    I'm deleting a column in a table and I get the following error:

    db2 "ALTER TABLE NGESCAL.GEORGI DROP COLUMN HD_N_EPOB_DS_NOMBRE_1"
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0270N Function not supported (Reason code = "100"). SQLSTATE=42997

    can someone tell me it will not let me delete the table field?

    Thank you very much and best regards.
    DBA DB2 for LUW

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    That is because the ALTER TABLE .. DROP COLUMN is not supported in V8.2.
    It was added later (I forget which version).


    Andy

  3. #3
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Are you sure this is v8? I don't think you can drop a column in v8. RC=100 is not in the v8 manual. In v9, RC=100 means:

    100

    Dropping a column or altering nullability is not allowed on any
    table with data capture on.

  4. #4
    Join Date
    Dec 2002
    Location
    Madrid - Spain
    Posts
    422
    Provided Answers: 1
    Excuse me the version is 9.7 Fixpack 6.
    Greetings
    DBA DB2 for LUW

  5. #5
    Join Date
    Nov 2009
    Posts
    21
    Provided Answers: 1
    Hi

    The error message relates to DATA CAPTURE CHANGES.

    Please do the steps below it should run fine

    ALTER TABLE NGESCAL.GEORGI DATA CAPTURE NONE;

    ALTER TABLE NGESCAL.GEORGI DROP COLUMN HD_N_EPOB_DS_NOMBRE_1;

    ALTER TABLE NGESCAL.GEORGI DATA CAPTURE CHANGES;

    It should work fine.

    Regards

    Surgeon

Posting Permissions

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