Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655

    Unanswered: Drop column, and alter nullability is not allowed on any table with data capture on.

    Hi everybody,

    DB2 V 9.5 Fixpack 4 on Win 2K3

    While ALTERing ACC_NUM on many tables in POS databbase, 7 tables were not Altered because of Following Error

    Alter Table ACC_DEPOSIT Alter Column ACC_NUM Set Data Type Varchar(20)
    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
    The reason code "100" expands as

    100
    Drop column, and alter nullability is not allowed on any table with data capture on.
    How can I work around, better if I can avoid using EXPORT-DROP-CREATE-IMPORT approach because these tables are huge and this approach will take too long time. (offline)

    Please help with your ideas, I won't be able to perform this without testing it.

    Thanks
    DBFinder
    Last edited by DBFinder; 06-07-09 at 12:07.

  2. #2
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126

    Thumbs up

    Are you sure you didn't use
    Code:
    ALTER TABLE ... ALTER COLUMN ... SET NOT NULL
    or
    Code:
    ALTER TABLE ... ALTER COLUMN ... DROP NOT NULL
    ? Since these seem to be the only situations where you would receive -290 with reason code 100, at least in DB2 version 9.5 for LUW.
    Just changing the datatype should not give this error.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Alter Table ACC_DEPOSIT Alter Column ACC_NUM Set Data Type Varchar(20)
    I used above statement as seed to build the script for 117 tables.
    Only table name is substituted.

    Please note that error is -270 not -290.

    Also the tables where ALTER COLUMN failed had been created with DATA CAPTURE CHANGES clause.

    Thanks
    DBFinder

  4. #4
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    What was the old datatype? Was it wider than 20 bytes?
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

  5. #5
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Older was CHARACTER[12]

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Can we turn DATA CAPTURE off for this to work and turn it back on after

  7. #7
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Peter Vanroose,

    I have found a way.

    Set data capture to NONE
    ALTER TABLE to change data type to VARCHAR(20)
    Set data capture to CHANGES.


    This seems to work, will you please advise me if this will affect any thing else.
    I mean side affect.

    Thanks,

    DBFinder

  8. #8
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    This would indeed be safe *unless* you plan to use that table for inter-database replication, and table changes are to be captured from the logs. And apparently you do, since otherwise DATA CAPTURE would not have been set to CHANGES on this table.
    Actually, data changes are still captured in the logs, but just for this ALTER TABLE that info could be insufficient.
    No idea whether this will cause problems for replication, though; I've never used it myself.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

Posting Permissions

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