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

    Unanswered: How Can I remove a Check Constraint in DB2

    I tried in CC:

    CONNECT TO ECX;
    ALTER TABLE DB2ADMIN.CLIENT DROP CHECK SQL080111061154110 ;
    CONNECT RESET;

    ====The Error =====


    SQL0607N "DROP CONSTRAINT
    " is not defined for system objects.

    Explanation:

    The "<operation>" specified in the SQL statement cannot be
    performed on system objects. One of the following was
    attempted:

    o DROP or ALTER a system owned object such as a system catalog
    table, built-in function or built-in data type

    o COMMENT ON a system owned built-in function.

    o INSERT or DELETE on a system catalog table.

    o UPDATE directly on a system catalog table. Some columns of a
    subset of system catalog tables are updatable. For UPDATE
    operation on these catalog tables, updatable views in SYSSTAT
    schema should be used. For a description of updatable catalog
    views (SYSSTAT views), see the SQL Reference .

    o CREATE or DROP of an index on a system table

    o CREATE of a trigger on a system table

    o A non-updatable system table was identified in the FROM
    clause of a SELECT statement containing a FOR UPDATE clause.
    For a list of updatable system catalogs see the SQL
    Reference .

    o DROP or ALTER of a system table space.

    o DROP or ALTER of a system database partition group.

    o REDISTRIBUTE of the IBMCATGROUP or IBMTEMPGROUP database
    partition group.



    The statement cannot be processed.

    User Response:

    Do not attempt to modify any system objects except for those
    columns of the system catalog tables which are updatable through
    updatable SYSSTAT views. See the SQL Reference for more
    information.

    sqlcode : -607

    sqlstate : 42832

    =========

    I tried on Command Window. Tried Setting Integrity off before dropping.

    Help is needed. - Want to drop Check Constraint in a table.

    Thanks in adv
    DBFinder

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    What DB2 version and OS are you using?

    My guess is that you let DB2 name the constraint for you and it has sysibm
    as the schema. Unfortunately you cannot drop these explicitly.
    The best thing I could suggest is to alter it so that it is not enforced.

    Andy

  3. #3
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    DB2 V8.1.12.99 FP 12 64 bits

    Well it is in db2admin schema and I do not see anything that relates to sys.

    the def is "UCASE_EMAIL"=UCASE( EMAIL ) )

    only system built is UCASE()
    UCASE_EMAIL is the COLUMN name and EMAIL is anothe COLUMN in same table.


    Still Wondring Why

    DBFinder

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    My guess is that the ALTER TABLE statement barfs at the "DB2" in the schema name.

    I just tried it on DB2 V9.5. You may want to try applying the latest FixPak. Something else you should try is to run this on the command line (not in CC). The message has a strange line break - maybe you entered an invalid, non-printable character?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by DBFinder
    DB2 V8.1.12.99 FP 12 64 bits

    Well it is in db2admin schema and I do not see anything that relates to sys.

    the def is "UCASE_EMAIL"=UCASE( EMAIL ) )

    only system built is UCASE()
    UCASE_EMAIL is the COLUMN name and EMAIL is anothe COLUMN in same table.


    Still Wondring Why

    DBFinder
    I assumed it was in sysibm schema because you did not qualify the schema on the constraint and it's name starts with SQL. Maybe if you fully qualified the schema it might work:

    ALTER TABLE DB2ADMIN.CLIENT DROP CHECK db2admin.SQL080111061154110 ;

    Andy

  6. #6
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    C:\SQLLIB\BIN>db2 ALTER TABLE DB2ADMIN.CLIENT DROP CHECK db2admin.SQL08011106115
    4110
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0108N The name "SQL080111061154110" has the wrong number of qualifiers.
    SQLSTATE=42601

    C:\SQLLIB\BIN>



    =====> Did not work

    C:\SQLLIB\BIN>db2 ALTER TABLE DB2ADMIN.CLIENT DROP CHECK SQL080111061154110
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0607N "DROP CONSTRAINT" is not defined for system objects. SQLSTATE=42832

    C:\SQLLIB\BIN>

    ======> Even without qualifier

    PS: I was logged in as db2admin


    The development team is thinking of dropping the table and recreating it withdata exported and imported of course.

    BTW: I tried similar situation on V9.5 seperately and it works !!

    DBFinder

  7. #7
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Quote Originally Posted by DBFinder
    C:\SQLLIB\BIN>

    The development team is thinking of dropping the table and recreating it with data exported and imported of course.

    DBFinder
    This looks like your only course of action until you move to 9.5.

    Andy

  8. #8
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Good to know:

    I will update if I find anything notable.

    Thanks to all
    DBFinder

  9. #9
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    verify the entry in sysibm.syschecks
    select name ,type from sysibm.syschecks where tbname='CLIENT' and tbcreator='DB2ADMIN'
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  10. #10
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Just a reminder: Don't use DB2 internal catalog tables - SYSIBM.TABCONST is the way to go.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  11. #11
    Join Date
    Sep 2008
    Location
    Toronto,Canada
    Posts
    655
    Check out for table DDL. Column was defined as generated always as ucase(EMAIL)

    Altered Col Def as value generation NONE. This alteration droped the constraint.

    DBFinder

Posting Permissions

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