Inserting Constraints into table

RAGHAVAN Vadakkupattu Sampath

Inserting Constraints into table
Hi Everyone,

My wishes to every member in our discussion list a Happy and prosperous New
Year.

Kindly clarify my doubt.

I have a table ' T1 ' with ' N ' columns. I have loaded the data into table.
Now I want to add check-constraint for some of the columns. Using alter
table statement I tried to add check-constraint, my table went into
check-pending state.

In one of the books, I have read that we can set the constraint for a table
to ' OFF ' position, and then add the check-constraint, then enable the
constraint(ON position). This is to eliminate the Check pending state.

Kindly clarify whether my statement is correct, or is there any other method
available in adding the constraints for a loaded table.

My advance thanks to everyone for your help.

Regards,

Raghavan.V.S



J16 Chennai Janakiraman S

Re: Inserting Constraints into table
(in response to VEERARAGHAVAN Krishnamoorthy)


hi,

you can set the current rules = 'STD' and then try to add the
constraint...The contraint will be added if the data already existing
satisfies the conditions defined in the constraint.

HTH.
janaks.

-----Original Message-----
From: RAGHAVAN Vadakkupattu Sampath [mailto:[login to unmask email]
Sent: Friday, January 03, 2003 11:24 AM
To: [login to unmask email]
Subject: Inserting Constraints into table


Hi Everyone,

My wishes to every member in our discussion list a Happy and prosperous New
Year.

Kindly clarify my doubt.

I have a table ' T1 ' with ' N ' columns. I have loaded the data into table.
Now I want to add check-constraint for some of the columns. Using alter
table statement I tried to add check-constraint, my table went into
check-pending state.

In one of the books, I have read that we can set the constraint for a table
to ' OFF ' position, and then add the check-constraint, then enable the
constraint(ON position). This is to eliminate the Check pending state.

Kindly clarify whether my statement is correct, or is there any other method
available in adding the constraints for a loaded table.

My advance thanks to everyone for your help.

Regards,

Raghavan.V.S





Walter Janißen

Re: Inserting Constraints into table
(in response to RAGHAVAN Vadakkupattu Sampath)
Hi

If you first execute SET CURRENT RULES = 'DB2', then the check constraint
will be checked during the alter. If the check succeeds, the alter is done,
no check pending is set. If the check fails, the alter fails.

From a performance view checking the constraints using the check utility is
better.

HTH



VEERARAGHAVAN Krishnamoorthy

Re: Inserting Constraints into table
(in response to Walter Janißen)
Hi Raghavan,
I hope we have Repair job or check data utility to remove the check pending
status .
FYI ..More Info is available in DB2 UDB V6 Administration guide
Which explains about when chk pending occurs and wht needs to be done

Thanks,
Veera.

-----Original Message-----
From: RAGHAVAN Vadakkupattu Sampath
Sent: Friday, January 03, 2003 11:24 AM
To: [login to unmask email]
Subject: Inserting Constraints into table


Hi Everyone,

My wishes to every member in our discussion list a Happy and prosperous New
Year.

Kindly clarify my doubt.

I have a table ' T1 ' with ' N ' columns. I have loaded the data into table.
Now I want to add check-constraint for some of the columns. Using alter
table statement I tried to add check-constraint, my table went into
check-pending state.

In one of the books, I have read that we can set the constraint for a table
to ' OFF ' position, and then add the check-constraint, then enable the
constraint(ON position). This is to eliminate the Check pending state.

Kindly clarify whether my statement is correct, or is there any other method
available in adding the constraints for a loaded table.

My advance thanks to everyone for your help.

Regards,

Raghavan.V.S








Michael Ebert

Re: Inserting Constraints into table
(in response to J16 Chennai Janakiraman S)
Hello Raghavan,

I think the ability to enable/disable constraints exists only for
non-OS/390 DB2 (and other database systems). On OS/390 DB2, you have to
run the CHECK utility.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




Hi Everyone,

My wishes to every member in our discussion list a Happy and prosperous
New
Year.

Kindly clarify my doubt.

I have a table ' T1 ' with ' N ' columns. I have loaded the data into
table.
Now I want to add check-constraint for some of the columns. Using alter
table statement I tried to add check-constraint, my table went into
check-pending state.

In one of the books, I have read that we can set the constraint for a
table
to ' OFF ' position, and then add the check-constraint, then enable the
constraint(ON position). This is to eliminate the Check pending state.

Kindly clarify whether my statement is correct, or is there any other
method
available in adding the constraints for a loaded table.

My advance thanks to everyone for your help.

Regards,

Raghavan.V.S

David Cohn

Re: Inserting Constraints into table
(in response to Michael Ebert)
Hi Raghavan,
The CURRENT RULES special register controls how DB2 handles adding a
constraint to an already populated table.
If CURRENT RULES is set to DB2 then the constraint is added and the table
is placed in a check pending state. You must then clear the check pending
state, ideally by running the check utility. If you run the check utility
and all rows satisfy the constraint then the check utility clears the check
pending flag.
If CURRENT RULES is set to STD then at the time you add the constraint to
the table, DB2 checks to see that all rows meet the constraint. If the
constraint is satisfied by all rows, it is added. If any row fails to
satisfy the constraint, then it is not added.
Hope this helps,
David Cohn
Senior Systems Advisor
Themis Training
IBM Certified Solutions Expert:
DB2 V7.1 Database Administration
DB2 V7.1 Family Application Development



----- Original Message -----
From: "RAGHAVAN Vadakkupattu Sampath" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, January 02, 2003 9:53 PM
Subject: Inserting Constraints into table


> Hi Everyone,
>
> My wishes to every member in our discussion list a Happy and prosperous
New
> Year.
>
> Kindly clarify my doubt.
>
> I have a table ' T1 ' with ' N ' columns. I have loaded the data into
table.
> Now I want to add check-constraint for some of the columns. Using alter
> table statement I tried to add check-constraint, my table went into
> check-pending state.
>
> In one of the books, I have read that we can set the constraint for a
table
> to ' OFF ' position, and then add the check-constraint, then enable the
> constraint(ON position). This is to eliminate the Check pending state.
>
> Kindly clarify whether my statement is correct, or is there any other
method
> available in adding the constraints for a loaded table.
>
> My advance thanks to everyone for your help.
>
> Regards,
>
> Raghavan.V.S
>
>
>


>