Is there a way to find the record causing SET INTEGRITY command to fail?

[technocrat]

I am trying to laod 2 tables and set integrtiy on them, but the second
tables fails ( both are related and SET INTEGRITY ion first table
succeeds) saying constraint violation....is there a way to find out
which records are violating....?? may be through try catch a
SQLException... ??? but how??
any suggestions are welcome!

[Gert van der Kooij]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

In article <1144096926.852 768.141440@v46g 2000cwv.googleg roups.com>,
sumant.kalvala@ gmail.com says...[color=blue]
> I am trying to laod 2 tables and set integrtiy on them, but the second
> tables fails ( both are related and SET INTEGRITY ion first table
> succeeds) saying constraint violation....is there a way to find out
> which records are violating....?? may be through try catch a
> SQLException... ??? but how??
> any suggestions are welcome!
>
>[/color]

Maybe the FOR EXCEPTION option can help you. It removes the duplicates
from the table and puts them in the exception table you specify.

[ChrisC]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

I've found this out using SQL in the past. First, you identify which
column(s) the constraint is failing on (it should tell you) and which
foreign table that is referencing (assuming the constraing is a foreign
key).

Then, SET INTEGRITY ... UNCHECKED. Then do a query like:

select * from table1 where col_with_proble m not in (select distinct
parent_column from parent_table);

This should get you all of the rows that have the problem. You can
then take remedial action (deleting rows, adding rows into the parent,
or whatever else is appropriate), and then turn integrity off, and
reset the integrity with checking back on.

Note that if the tables are big, this can take a really long time,
though.

-Chris

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

you solution looks great and ideal for me...but i aint sure yet ...i ll
have to implement that and try...but in the meanwhile,...ca n you give
me an example (source code) of how u did that?
Like how u identify which colmns are failing..? and which table its
refering...i know when SET INTEGRITY fails..it throws something..lile
integrity failedfor "schema.table.f oreigntableFK" but is ther a way i
can find out the table name...rather than catching the exception and do
"STRING" analysis on it....

Any help would be appreciated...
Thanks

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

Thanks gert ...I think I would try this first and then try the sql
version of it...if I could get some more info on it , i would
appreciate it.....

The thing i am wndering is...does for exception ....remove only
duplicates?? or remove rows that are violating the FK
constraint...be cuase, I am not rteally worried about duplicates...bu t I
want to find the rows that are violating the FK Constraint...

Any help is appreciated..!

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

Thanks gert ...I think I would try this first and then try the sql
version of it...if I could get some more info on it , i would
appreciate it.....

The thing i am wndering is...does for exception ....remove only
duplicates?? or remove rows that are violating the FK
constraint...be cuase, I am not rteally worried about duplicates...bu t I
want to find the rows that are violating the FK Constraint...

Any help is appreciated..!

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

Thanks gert ...I think I would try this first and then try the sql
version of it...if I could get some more info on it , i would
appreciate it.....

The thing i am wndering is...does for exception ....remove only
duplicates?? or remove rows that are violating the FK
constraint...be cuase, I am not rteally worried about duplicates...bu t I
want to find the rows that are violating the FK Constraint...

Any help is appreciated..!

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

And another thing,...if i user FOr EXCEPTION....if there are violating
rows....then will the status of the table be "C" at the end of SET
INTEGRITY or "N" ...i mean will it be normal or check pending at the
end of Set Integrity ...for exception.....? ?

[Ian]

Re: Is there a way to find the record causing SET INTEGRITY commandto fail?

technocrat wrote:[color=blue]
> And another thing,...if i user FOr EXCEPTION....if there are violating
> rows....then will the status of the table be "C" at the end of SET
> INTEGRITY or "N" ...i mean will it be normal or check pending at the
> end of Set Integrity ...for exception.....? ?
>[/color]

Why don't you read the doc for SET INTEGRITY. It will answer all of
your questions.

[Gert van der Kooij]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

In article <44343d0e$1_1@n ewsfeed.slurp.n et>, ianbjor@mobilea udio.com
says...[color=blue]
> technocrat wrote:[color=green]
> > And another thing,...if i user FOr EXCEPTION....if there are violating
> > rows....then will the status of the table be "C" at the end of SET
> > INTEGRITY or "N" ...i mean will it be normal or check pending at the
> > end of Set Integrity ...for exception.....? ?
> >[/color]
>
> Why don't you read the doc for SET INTEGRITY. It will answer all of
> your questions.
>[/color]

And to help with that, you can find them at http://tinyurl.com/r7sug

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

hey gert and ian...
I did read thse two docs u both mentioned...I did understand some
stuf..but not really clear...i m trying to implement that and see how
it goes....if i hav eany doubts will get back to you guys ...

thanks again!

[technocrat]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

the for exception table worked like a charm..Thanks a ton everyone!

[Pierre Saint-Jacques]

Re: Is there a way to find the record causing SET INTEGRITY command to fail?

AFAIK, the SET INTEGRITY command allows you to insert in the exception table
either or both of FK violations and column check violations.
The command has a parm that allows you to specify ALL, CHECK, FK (or
FOREIgn, can't remember).
So the exception table will contain what you'll specify.
Depending on the option, after the set, the table could still be in check
pending. If you use ALL, after the set, the table will be in normal state
and the status will show it.
Look in the SYSCA.TABLES, two columns, STATUS and CONST_CHECKED.
Your docs. will show you that in CONST_CHECKED:
The first Y is indicates the table has been checked for Foreign Key
integrity.
The second Y is indicates the table has been checked for Column Check
integrity.
The fifth Y is indicates the table has been checked for Refresh for MQT
integrity. (Don't ask Y the fifth as opposed to 3rd, 4th ???? I don't know).
As well don't ask Y the rest of the 32 values, I don't know. Maybe it's
like in the docs: This page intentionnally left blank!!!!

HTH, Pierre.
--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
"technocrat " <sumant.kalvala @gmail.com> a écrit dans le message de news:
1144269958.3697 10.115940@i40g2 00...legr oups.com...[color=blue]
> And another thing,...if i user FOr EXCEPTION....if there are violating
> rows....then will the status of the table be "C" at the end of SET
> INTEGRITY or "N" ...i mean will it be normal or check pending at the
> end of Set Integrity ...for exception.....? ?
>[/color]