referential integrity question - OS/390 DB2 V5

Judi Westcott

referential integrity question - OS/390 DB2 V5
Hello

I was wondering if anyone knows of a way to do the following:

table_1 has primary unique key of col_1, col_2, col_3
table_2 has primary unique key of col_1, col_2, col_3, col_4

I have set up RI to check that there exists a row on table_1 before a row
with the col_1, col_2, col_3 is inserted into table_2. This works fine.

However I also want to check to ensure that if a row exists on table_1 with
value of col_1, col_2, col_3 then there also exists at least 1 row (however
there may be > 1 row) on table_2 with that combination.

This scenario is for a data wharehouse application and table_1 and table_2
are loaded with the LOAD utility (never inserted to or updated). We were
going to set up the LOADs for the tables with the ENFORCE NO clause and
then run CHCKDATA afterwards to check the validity of the loaded data.

When I try to ALTER table_1 to have a foreign key constraint to table_2, we
get a -573 TABLE table_2 DOES NOT HAVE A UNIQUE KEY WITH THE SPECIFIED
COLUMN NAMES. Which of course it doesn't as col_4 is needed to make it
unique.

Currently this is done via application programming. We are trying to
reduce the overhead and time required to verify newly loaded rows by
letting DB2 do the work and eliminating inter-region and application
processing.

TIA for any suggestions.

Regards,
Judi Westcott



Mohammed Nayeem

Re: referential integrity question - OS/390 DB2 V5
(in response to Judi Westcott)
Judy

If you define RI Constrants correclty then first LOAD first parent table then
proceed loading in the child table. In this way you should'nt get any check
status once you finish loading in child table.

Thx
Nayeem



Terry Purcell

Re: referential integrity question - OS/390 DB2 V5
(in response to Mohammed Nayeem)
Judi,

Unfortunately I cannot think of automated V5 method to do this reverse
Foreign Key check.

However, if your current SQL to perform the check looks like:

SELECT COL_1, COL_2, COL_3
FROM TABLE_2 A
WHERE NOT EXISTS
(SELECT 1
FROM TABLE_1 B
WHERE B.COL_1 = A.COL_1
AND B.COL_2 = A.COL_2
AND B.COL_3 = A.COL_3)

Then you can obtain a performance improvement (the greater the duplicates in
table_2, the greater the improvement), you could code the following:

SELECT COL_1, COL_2, COL_3
FROM TABLE_2 A
GROUP BY COL_1, COL_2, COL_3
HAVING COUNT(*) = 0
OR NOT EXISTS
(SELECT 1
FROM TABLE_1 B
WHERE B.COL_1 = A.COL_1
AND B.COL_2 = A.COL_2
AND B.COL_3 = A.COL_3)

This SQL will reduce the number of rows being passed to stage 2 for the
correlated NOT EXISTS check. Your current key structure suggests that there
are indexes to avoid the group by sort. The best improvement I have seen for
this type of query was 80% elapsed time improvement, but the parent table
had 2000 rows and the child table had 1 million rows. If it works for you,
I'll explain why. If it doesn't....no harm done :-)

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Judi Westcott
Sent: Thursday, January 04, 2001 12:12 PM
To: [login to unmask email]
Subject: referential integrity question - OS/390 DB2 V5


Hello

I was wondering if anyone knows of a way to do the following:

table_1 has primary unique key of col_1, col_2, col_3
table_2 has primary unique key of col_1, col_2, col_3, col_4

I have set up RI to check that there exists a row on table_1 before a row
with the col_1, col_2, col_3 is inserted into table_2. This works fine.

However I also want to check to ensure that if a row exists on table_1 with
value of col_1, col_2, col_3 then there also exists at least 1 row (however
there may be > 1 row) on table_2 with that combination.

This scenario is for a data wharehouse application and table_1 and table_2
are loaded with the LOAD utility (never inserted to or updated). We were
going to set up the LOADs for the tables with the ENFORCE NO clause and
then run CHCKDATA afterwards to check the validity of the loaded data.

When I try to ALTER table_1 to have a foreign key constraint to table_2, we
get a -573 TABLE table_2 DOES NOT HAVE A UNIQUE KEY WITH THE SPECIFIED
COLUMN NAMES. Which of course it doesn't as col_4 is needed to make it
unique.

Currently this is done via application programming. We are trying to
reduce the overhead and time required to verify newly loaded rows by
letting DB2 do the work and eliminating inter-region and application
processing.

TIA for any suggestions.

Regards,
Judi Westcott








Terry Purcell

Re: referential integrity question - OS/390 DB2 V5
(in response to Terry Purcell)
Judi,

I realised I sent SQL to perform exactly the same check as the CHECK DATA is
running, since I've used this in sites for application enforced RI.

You may consider elaborating on the application process which is running
slowly, since then the listers can compare with any process they have
implemented.

Regards
Terry

-----Original Message-----
From: Terry Purcell [mailto:[login to unmask email]
Sent: Thursday, January 04, 2001 7:13 PM
To: DB2 Data Base Discussion List
Subject: RE: referential integrity question - OS/390 DB2 V5


Judi,

Unfortunately I cannot think of automated V5 method to do this reverse
Foreign Key check.

However, if your current SQL to perform the check looks like:

SELECT COL_1, COL_2, COL_3
FROM TABLE_2 A
WHERE NOT EXISTS
(SELECT 1
FROM TABLE_1 B
WHERE B.COL_1 = A.COL_1
AND B.COL_2 = A.COL_2
AND B.COL_3 = A.COL_3)

Then you can obtain a performance improvement (the greater the duplicates in
table_2, the greater the improvement), you could code the following:

SELECT COL_1, COL_2, COL_3
FROM TABLE_2 A
GROUP BY COL_1, COL_2, COL_3
HAVING COUNT(*) = 0
OR NOT EXISTS
(SELECT 1
FROM TABLE_1 B
WHERE B.COL_1 = A.COL_1
AND B.COL_2 = A.COL_2
AND B.COL_3 = A.COL_3)

This SQL will reduce the number of rows being passed to stage 2 for the
correlated NOT EXISTS check. Your current key structure suggests that there
are indexes to avoid the group by sort. The best improvement I have seen for
this type of query was 80% elapsed time improvement, but the parent table
had 2000 rows and the child table had 1 million rows. If it works for you,
I'll explain why. If it doesn't....no harm done :-)

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Judi Westcott
Sent: Thursday, January 04, 2001 12:12 PM
To: [login to unmask email]
Subject: referential integrity question - OS/390 DB2 V5


Hello

I was wondering if anyone knows of a way to do the following:

table_1 has primary unique key of col_1, col_2, col_3
table_2 has primary unique key of col_1, col_2, col_3, col_4

I have set up RI to check that there exists a row on table_1 before a row
with the col_1, col_2, col_3 is inserted into table_2. This works fine.

However I also want to check to ensure that if a row exists on table_1 with
value of col_1, col_2, col_3 then there also exists at least 1 row (however
there may be > 1 row) on table_2 with that combination.

This scenario is for a data wharehouse application and table_1 and table_2
are loaded with the LOAD utility (never inserted to or updated). We were
going to set up the LOADs for the tables with the ENFORCE NO clause and
then run CHCKDATA afterwards to check the validity of the loaded data.

When I try to ALTER table_1 to have a foreign key constraint to table_2, we
get a -573 TABLE table_2 DOES NOT HAVE A UNIQUE KEY WITH THE SPECIFIED
COLUMN NAMES. Which of course it doesn't as col_4 is needed to make it
unique.

Currently this is done via application programming. We are trying to
reduce the overhead and time required to verify newly loaded rows by
letting DB2 do the work and eliminating inter-region and application
processing.

TIA for any suggestions.

Regards,
Judi Westcott








Rob Crane

Re: referential Integrity
(in response to Terry Purcell)
Sounds like you are saying that you want All Parent rows (table1) to
always have at least one Child row (table2). You could verify that this
condition exists after your loads are done by doing a LEFT OUTER JOIN
Parent table (table1) to child table (table2). If you have any parent
rows without children B.COL4 will show at the end of the join output as
null.

SELECT A.COL1, A.COL2, A.COL3, B.COL4
FROM TABLE1 A
LEFT OUTER JOIN TABLE2 B
ON A.COL1 = B.COL1 AND
A.COL2 = B.COL2 AND
A.COL3 = B.COL3;

Reverse the order of the tables to ensure that every child row has a
parent, etc.

-Rob
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Judi Westcott
Sent: Thursday, January 04, 2001 12:12 PM
To: [login to unmask email]
Subject: referential integrity question - OS/390 DB2 V5


Hello

I was wondering if anyone knows of a way to do the following:

table_1 has primary unique key of col_1, col_2, col_3
table_2 has primary unique key of col_1, col_2, col_3, col_4

I have set up RI to check that there exists a row on table_1 before a
row
with the col_1, col_2, col_3 is inserted into table_2. This works fine.

However I also want to check to ensure that if a row exists on table_1
with
value of col_1, col_2, col_3 then there also exists at least 1 row
(however
there may be > 1 row) on table_2 with that combination.

This scenario is for a data wharehouse application and table_1 and
table_2
are loaded with the LOAD utility (never inserted to or updated). We
were
going to set up the LOADs for the tables with the ENFORCE NO clause and
then run CHCKDATA afterwards to check the validity of the loaded data.

When I try to ALTER table_1 to have a foreign key constraint to table_2,
we
get a -573 TABLE table_2 DOES NOT HAVE A UNIQUE KEY WITH THE SPECIFIED
COLUMN NAMES. Which of course it doesn't as col_4 is needed to make it
unique.

Currently this is done via application programming. We are trying to
reduce the overhead and time required to verify newly loaded rows by
letting DB2 do the work and eliminating inter-region and application
processing.

TIA for any suggestions.

Regards,
Judi Westcott



Suresh Sane

Re: referential integrity question - OS/390 DB2 V5
(in response to Rob Crane)
Terry,

I am confused by your solution.

1. Original query had table 1 as the parent (col1,2,3) and table 2 as the
child (col1,2,3,4) - you seem to be using these in reverse. No big deal.

2. The construct - group by... having count(*) -> the only reason a
combination of c1, c2, c3 exists was because we found one - how can the
count ever be zero?

3. The number of duplicates on the child is (in my opinion) irrelevant.
Since the exists will check for the presence of a child (dont care how
many), the subselect will return as soon it finds one.

4. I like your idea of GROUP BY but would require a Nested Table Expression
that will invaribaly be materialised - exists should perform better in all
cases.

5. I would suggest this alternative:
select parent.c1, parent.c2, parent.c3, child.c4
from parent LOJ child
on parent.c1 = child.c1 etc up to c3
where child.c4 is NULL

Perhaps you could clarify what you hand mind for me?

Thanks,
Suresh


>From: Terry Purcell <[login to unmask email]>
>Reply-To: [login to unmask email]
>To: [login to unmask email]
>Subject: Re: referential integrity question - OS/390 DB2 V5
>Date: Thu, 4 Jan 2001 19:13:10 -0600
>
>Judi,
>
>Unfortunately I cannot think of automated V5 method to do this reverse
>Foreign Key check.
>
>However, if your current SQL to perform the check looks like:
>
>SELECT COL_1, COL_2, COL_3
>FROM TABLE_2 A
>WHERE NOT EXISTS
>(SELECT 1
> FROM TABLE_1 B
> WHERE B.COL_1 = A.COL_1
> AND B.COL_2 = A.COL_2
> AND B.COL_3 = A.COL_3)
>
>Then you can obtain a performance improvement (the greater the duplicates
>in
>table_2, the greater the improvement), you could code the following:
>
>SELECT COL_1, COL_2, COL_3
>FROM TABLE_2 A
>GROUP BY COL_1, COL_2, COL_3
>HAVING COUNT(*) = 0
>OR NOT EXISTS
>(SELECT 1
> FROM TABLE_1 B
> WHERE B.COL_1 = A.COL_1
> AND B.COL_2 = A.COL_2
> AND B.COL_3 = A.COL_3)
>
>This SQL will reduce the number of rows being passed to stage 2 for the
>correlated NOT EXISTS check. Your current key structure suggests that there
>are indexes to avoid the group by sort. The best improvement I have seen
>for
>this type of query was 80% elapsed time improvement, but the parent table
>had 2000 rows and the child table had 1 million rows. If it works for you,
>I'll explain why. If it doesn't....no harm done :-)
>
>Regards
>Terry Purcell
>Yevich Lawson & Associates
>[login to unmask email]
>WWW.YLASSOC.COM
>WWW.DB2-SYMPOSIUM.COM
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
>Judi Westcott
>Sent: Thursday, January 04, 2001 12:12 PM
>To: [login to unmask email]
>Subject: referential integrity question - OS/390 DB2 V5
>
>
>Hello
>
>I was wondering if anyone knows of a way to do the following:
>
>table_1 has primary unique key of col_1, col_2, col_3
>table_2 has primary unique key of col_1, col_2, col_3, col_4
>
>I have set up RI to check that there exists a row on table_1 before a row
>with the col_1, col_2, col_3 is inserted into table_2. This works fine.
>
>However I also want to check to ensure that if a row exists on table_1 with
>value of col_1, col_2, col_3 then there also exists at least 1 row (however
>there may be > 1 row) on table_2 with that combination.
>
>This scenario is for a data wharehouse application and table_1 and table_2
>are loaded with the LOAD utility (never inserted to or updated). We were
>going to set up the LOADs for the tables with the ENFORCE NO clause and
>then run CHCKDATA afterwards to check the validity of the loaded data.
>
>When I try to ALTER table_1 to have a foreign key constraint to table_2, we
>get a -573 TABLE table_2 DOES NOT HAVE A UNIQUE KEY WITH THE SPECIFIED
>COLUMN NAMES. Which of course it doesn't as col_4 is needed to make it
>unique.
>
>Currently this is done via application programming. We are trying to
>reduce the overhead and time required to verify newly loaded rows by
>letting DB2 do the work and eliminating inter-region and application
>processing.
>
>TIA for any suggestions.
>
>Regards,
>Judi Westcott
>
>
>
>the
>
>
>
>
>
>
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com



Terry Purcell

Re: referential integrity question - OS/390 DB2 V5
(in response to Suresh Sane)
Suresh,

1. Agree, no big deal. What the query does is perform an application
enforced RI, where foreign keys are not explicitly defined. So it does not
solve Judi's problem, but is an interesting query.....

2. True. "OR COUNT(*) = 0" is "not possible" predicate for HAVING clauses;
same as "OR 0=1" is for WHERE clauses.

3. If the child table is the outer table of the query, then there may be
multiple rows for each parent (which is in the subquery). This is very
relevant if the query is the way I coded, but not relevant if it was
actually coded the way Judi wanted it. So I'll explain for the way I coded.

I know you have a good knowledge of SQL, so you will know that repeated
executions of the subquery are avoided due to the correlation-cache;
HOWEVER, I must still pass each row to stage 2 to determine that the value
is in the cache. A GROUP BY can discard duplicates at index manager, if the
relevant index is available (I/O still required though). Therefore if I code
a GROUP BY and the subquery in the HAVING clause, then I should execute it
only for each group (and never have a duplicate found in the cache). Final
problem though, if a subquery in a having does not contain a column
function, then DB2 query rewrite will execute the subquery in the WHERE
clause, before the grouping occurs. That's where the "OR COUNT(*) = 0" comes
in, it changes the HAVING predicate to non-boolean term and places a
dependancy on the HAVING clause being executed as coded.

All this query does then is reduce the number of rows passed to stage 2 for
subquery execution. If the number of duplicates in the outer table is low,
then you may see no improvement; if it is high, you can see a dramatic
improvement.

4. As coded, a NTE is not required, and there is no materialization if an
index is available to avoid the sort.

My apologies for the confusion, since most of your questions were based on
the fact that my query was actually solving the original problem, but
instead solves a different problem. However, it is an interesting solution
for application enforced RI, and I hope I have explained it sufficiently
well.

Regards
Terry Purcell
Yevich Lawson & Associates
[login to unmask email]
WWW.YLASSOC.COM
WWW.DB2-SYMPOSIUM.COM

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Suresh Sane
Sent: Friday, January 05, 2001 4:02 PM
To: [login to unmask email]
Subject: Re: referential integrity question - OS/390 DB2 V5


Terry,

I am confused by your solution.

1. Original query had table 1 as the parent (col1,2,3) and table 2 as the
child (col1,2,3,4) - you seem to be using these in reverse. No big deal.

2. The construct - group by... having count(*) -> the only reason a
combination of c1, c2, c3 exists was because we found one - how can the
count ever be zero?

3. The number of duplicates on the child is (in my opinion) irrelevant.
Since the exists will check for the presence of a child (dont care how
many), the subselect will return as soon it finds one.

4. I like your idea of GROUP BY but would require a Nested Table Expression
that will invaribaly be materialised - exists should perform better in all
cases.

5. I would suggest this alternative:
select parent.c1, parent.c2, parent.c3, child.c4
from parent LOJ child
on parent.c1 = child.c1 etc up to c3
where child.c4 is NULL

Perhaps you could clarify what you hand mind for me?

Thanks,
Suresh


>From: Terry Purcell <[login to unmask email]>
>Reply-To: [login to unmask email]
>To: [login to unmask email]
>Subject: Re: referential integrity question - OS/390 DB2 V5
>Date: Thu, 4 Jan 2001 19:13:10 -0600
>
>Judi,
>
>Unfortunately I cannot think of automated V5 method to do this reverse
>Foreign Key check.
>
>However, if your current SQL to perform the check looks like:
>
>SELECT COL_1, COL_2, COL_3
>FROM TABLE_2 A
>WHERE NOT EXISTS
>(SELECT 1
> FROM TABLE_1 B
> WHERE B.COL_1 = A.COL_1
> AND B.COL_2 = A.COL_2
> AND B.COL_3 = A.COL_3)
>
>Then you can obtain a performance improvement (the greater the duplicates
>in
>table_2, the greater the improvement), you could code the following:
>
>SELECT COL_1, COL_2, COL_3
>FROM TABLE_2 A
>GROUP BY COL_1, COL_2, COL_3
>HAVING COUNT(*) = 0
>OR NOT EXISTS
>(SELECT 1
> FROM TABLE_1 B
> WHERE B.COL_1 = A.COL_1
> AND B.COL_2 = A.COL_2
> AND B.COL_3 = A.COL_3)
>
>This SQL will reduce the number of rows being passed to stage 2 for the
>correlated NOT EXISTS check. Your current key structure suggests that there
>are indexes to avoid the group by sort. The best improvement I have seen
>for
>this type of query was 80% elapsed time improvement, but the parent table
>had 2000 rows and the child table had 1 million rows. If it works for you,
>I'll explain why. If it doesn't....no harm done :-)
>
>Regards
>Terry Purcell
>Yevich Lawson & Associates
>[login to unmask email]
>WWW.YLASSOC.COM
>WWW.DB2-SYMPOSIUM.COM
>



Suresh Sane

Re: referential integrity question - OS/390 DB2 V5
(in response to Terry Purcell)
Terry,

Thanks for the clarification. Makes sense now.

As to Judy's original question - given parent table T1 (pk = c1+c2+c3) and a
child table T2 (pk = c1+c2+c3+c4, fk c1+c2+c3 pointing to T1) and the need
to verify that each parent has at least 1 child: (not verifying RI but
reverese RI):

Do you see anything better than an EXISTS? Would the number of duplicates
in the child be relevant?

BTW - your group by + having count(*) = 0 is really interesting.

Thanks,
Suresh


>From: Terry Purcell <[login to unmask email]>
>Reply-To: [login to unmask email]
>To: [login to unmask email]
>Subject: Re: referential integrity question - OS/390 DB2 V5
>Date: Fri, 5 Jan 2001 17:21:40 -0600
>
>Suresh,
>
>1. Agree, no big deal. What the query does is perform an application
>enforced RI, where foreign keys are not explicitly defined. So it does not
>solve Judi's problem, but is an interesting query.....
>
>2. True. "OR COUNT(*) = 0" is "not possible" predicate for HAVING clauses;
>same as "OR 0=1" is for WHERE clauses.
>
>3. If the child table is the outer table of the query, then there may be
>multiple rows for each parent (which is in the subquery). This is very
>relevant if the query is the way I coded, but not relevant if it was
>actually coded the way Judi wanted it. So I'll explain for the way I coded.
>
>I know you have a good knowledge of SQL, so you will know that repeated
>executions of the subquery are avoided due to the correlation-cache;
>HOWEVER, I must still pass each row to stage 2 to determine that the value
>is in the cache. A GROUP BY can discard duplicates at index manager, if the
>relevant index is available (I/O still required though). Therefore if I
>code
>a GROUP BY and the subquery in the HAVING clause, then I should execute it
>only for each group (and never have a duplicate found in the cache). Final
>problem though, if a subquery in a having does not contain a column
>function, then DB2 query rewrite will execute the subquery in the WHERE
>clause, before the grouping occurs. That's where the "OR COUNT(*) = 0"
>comes
>in, it changes the HAVING predicate to non-boolean term and places a
>dependancy on the HAVING clause being executed as coded.
>
>All this query does then is reduce the number of rows passed to stage 2 for
>subquery execution. If the number of duplicates in the outer table is low,
>then you may see no improvement; if it is high, you can see a dramatic
>improvement.
>
>4. As coded, a NTE is not required, and there is no materialization if an
>index is available to avoid the sort.
>
>My apologies for the confusion, since most of your questions were based on
>the fact that my query was actually solving the original problem, but
>instead solves a different problem. However, it is an interesting solution
>for application enforced RI, and I hope I have explained it sufficiently
>well.
>
>Regards
>Terry Purcell
>Yevich Lawson & Associates
>[login to unmask email]
>WWW.YLASSOC.COM
>WWW.DB2-SYMPOSIUM.COM
>
>-----Original Message-----
>From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
>Suresh Sane
>Sent: Friday, January 05, 2001 4:02 PM
>To: [login to unmask email]
>Subject: Re: referential integrity question - OS/390 DB2 V5
>
>
>Terry,
>
>I am confused by your solution.
>
>1. Original query had table 1 as the parent (col1,2,3) and table 2 as the
>child (col1,2,3,4) - you seem to be using these in reverse. No big deal.
>
>2. The construct - group by... having count(*) -> the only reason a
>combination of c1, c2, c3 exists was because we found one - how can the
>count ever be zero?
>
>3. The number of duplicates on the child is (in my opinion) irrelevant.
>Since the exists will check for the presence of a child (dont care how
>many), the subselect will return as soon it finds one.
>
>4. I like your idea of GROUP BY but would require a Nested Table
>Expression
>that will invaribaly be materialised - exists should perform better in all
>cases.
>
>5. I would suggest this alternative:
> select parent.c1, parent.c2, parent.c3, child.c4
> from parent LOJ child
> on parent.c1 = child.c1 etc up to c3
> where child.c4 is NULL
>
>Perhaps you could clarify what you hand mind for me?
>
>Thanks,
>Suresh
>
>
> >From: Terry Purcell <[login to unmask email]>
> >Reply-To: [login to unmask email]
> >To: [login to unmask email]
> >Subject: Re: referential integrity question - OS/390 DB2 V5
> >Date: Thu, 4 Jan 2001 19:13:10 -0600
> >
> >Judi,
> >
> >Unfortunately I cannot think of automated V5 method to do this reverse
> >Foreign Key check.
> >
> >However, if your current SQL to perform the check looks like:
> >
> >SELECT COL_1, COL_2, COL_3
> >FROM TABLE_2 A
> >WHERE NOT EXISTS
> >(SELECT 1
> > FROM TABLE_1 B
> > WHERE B.COL_1 = A.COL_1
> > AND B.COL_2 = A.COL_2
> > AND B.COL_3 = A.COL_3)
> >
> >Then you can obtain a performance improvement (the greater the duplicates
> >in
> >table_2, the greater the improvement), you could code the following:
> >
> >SELECT COL_1, COL_2, COL_3
> >FROM TABLE_2 A
> >GROUP BY COL_1, COL_2, COL_3
> >HAVING COUNT(*) = 0
> >OR NOT EXISTS
> >(SELECT 1
> > FROM TABLE_1 B
> > WHERE B.COL_1 = A.COL_1
> > AND B.COL_2 = A.COL_2
> > AND B.COL_3 = A.COL_3)
> >
> >This SQL will reduce the number of rows being passed to stage 2 for the
> >correlated NOT EXISTS check. Your current key structure suggests that
>there
> >are indexes to avoid the group by sort. The best improvement I have seen
> >for
> >this type of query was 80% elapsed time improvement, but the parent table
> >had 2000 rows and the child table had 1 million rows. If it works for
>you,
> >I'll explain why. If it doesn't....no harm done :-)
> >
> >Regards
> >Terry Purcell
> >Yevich Lawson & Associates
> >[login to unmask email]
> >WWW.YLASSOC.COM
> >WWW.DB2-SYMPOSIUM.COM
> >
>
>
>
>
>

_________________________________________________________________
Get your FREE download of MSN Explorer at http://explorer.msn.com