how to check where integrity constraints are being violated

[Database Administrator]

Hello,

I have a database that is bulk loaded via BCP scripts (via talend,
actually). This is to migrate data from a legacy system to our new
system. There are dozens of tables and millions of rows in some of
them. During the load we have referential integrity turned off.

I have discovered today that a small amount of the data violates the
referential integrity constraints, which are restored once the
migration has completed. How can I find out what data violates the
constraints please? Is there some general purpose tool that can do
this? Obviously it would have to be specific to sybase since it would
have to use the sybase data dictionary.

Regards,

Andrew Marlow
http://www.andrewpetermarlow.co.uk

[Database Administrator]

"Andrew" wrote in message
news:e18d1351-5dc8-414e-a888-42a6b94327cf@x5g2000yqk.googlegroups.com...
> Hello,
>
> I have a database that is bulk loaded via BCP scripts (via talend,
> actually). This is to migrate data from a legacy system to our new
> system. There are dozens of tables and millions of rows in some of
> them. During the load we have referential integrity turned off.
>
> I have discovered today that a small amount of the data violates the
> referential integrity constraints, which are restored once the
> migration has completed. How can I find out what data violates the
> constraints please? Is there some general purpose tool that can do
> this? Obviously it would have to be specific to sybase since it would
> have to use the sybase data dictionary.
>
> Regards,
>
> Andrew Marlow
> http://www.andrewpetermarlow.co.uk



Rob Verschoor has a useful chapter on "Finding Missing Data" in his "Tips
...." book: http://www.sypron.nl/main.html#books

His code actually assumes that the columns are non-null. I have written an
ISUG article (2005 Q4) on this and included code for determining FK errors
that includes nullable columns (but the code can run like a dog if you have
nullable FK columns). You can also specify the degree of ANSI checking
(full, partial or "normal" match checking). I have put the source code for
a lot of utility procs at CodeXchange
(http://www.sybase.com/developer/codexchange) but have not successfully
accessed it for quite some time. Sybase has apparently tweaked the site and
not to my advantage. It's a lot of code to load since there are many
subroutines in several files but it provides a lot of flexibility. (Validate
one FK constraint? Validate every FK constraint on a table? Validate every
FK constraint? Which checking level? Choose whichever you like.)

[Database Administrator]

On Jul 7, 7:11*pm, "Carl Kayser" wrote:
> "Andrew" wrote in message
>*I have written an
> ISUG article (2005 Q4) on this and included code for determining FK errors
> that includes nullable columns (but the code can run like a dog if you have
> nullable FK columns). *You can also specify the degree of ANSI checking
> (full, partial or "normal" match checking). *I have put the source codefor
> a lot of utility procs at CodeXchange

I was not able to find it there. Apparently there is a tool in
DBPowersuite (http://www.talussoftware.com/DBPowerSuite) that does the
job. I will give it a try.