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

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

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

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?

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

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

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

Andy

Good to know:

I will update if I find anything notable.

Thanks to all
DBFinder

verify the entry in sysibm.syschecks
select name ,type from sysibm.syschecks where tbname='CLIENT' and tbcreator='DB2ADMIN'

Just a reminder: Don't use DB2 internal catalog tables - SYSIBM.TABCONST is the way to go.

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