Answer: existence checking

Georg Peter

Answer: existence checking
Manish,

you can use EXISTS to test for the existence of a particular row. Here is an
example (originally shown in Craig Mullins great book "DB developers
guide"):

SELECT EMPNO
FROM DSN8610.EMP E
WHERE EXISTS
(SELECT 1
FROM DSN8610.PROJ P
WHERE P.RESPEMP = E.EMPNO);


Hope that helps.............

With kind regards - mit freundlichen Gruessen,
Georg H. Peter c/o
-------------------------------------------------------------------
Datenzentrale Baden-Wuerttemberg
Software Development & Technology Center
Knowledge Center Database Systems
Krailenshaldenstrasse 44, 70469 Stuttgart, Germany, EURurope
e:mail [login to unmask email]
Phone 0049-711-8108-271
PC-Fax 004971189696071
Internet (only in german language):http://www.dzbw.de
----------------------------------------------------------------------
The crystal ball gets cloudier as we proceed into the future.
(heard at the IBM laboratory in Boeblingen, Germany)



-----Ursprüngliche Nachricht-----
Von: Kr Manish [mailto:[login to unmask email]
Gesendet am: Freitag, 3. Januar 2003 09:42
An: [login to unmask email]
Betreff: existence checking

Esteem list,
I am looking for the situation where the "Existence checking" is used in
application program using DB2.
Will appreciate any code/SQL or any article as pointer.

Thanks,
Manish



http://listserv.ylassoc.com. The owners of the list can be reached at
[login to unmask email]



Walter Janißen

Re: existence checking
(in response to Georg Peter)
Manish

I think the best way of doing it (if you are on Version 7)

One quick way of doing it would be

EXEC SQL
SELECT 1 FROM xxx.yyy
WHERE aaa=bbb
FETCH FIRST 1 ROW ONLY
END EXEC



Dave Nance

Re: existence checking
(in response to Walter Janißen)
Manish,
All the responses you've gotten so far will work. You can take a look at DB2mag on the web, Bonnie Baker ran a couple of articles on just this topic last year. Good luck to you.

Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 1/3/03 3:41:37 AM >>>
Esteem list,
I am looking for the situation where the "Existence checking" is used in
application program using DB2.
Will appreciate any code/SQL or any article as pointer.

Thanks,
Manish

======================= DB2-L webpage at http://listserv.ylassoc.com. The owners of the list can

This message, including any attachments, is intended solely for the use
of the named recipient(s) and may contain confidential and/or
privileged information. Any unauthorized review, use, disclosure or
distribution of this communication(s) is expressly prohibited.
If you are not the intended recipient, please contact the sender by
reply e-mail and destroy any and all copies of the original message.
Thank you.



Missy Case

Re: existence checking
(in response to Dave Nance)
I had this same question a year or so back, Terry Purcell was kind enough
to help me out. Here are the results of my test for a 'first true'
existence checking. We have DB2 data queues that I don't care if there are
5 or 5000, I just need to know if it's > 0.

Anyway, hopefully this comes thru in color as I've color coded the
statements & their results in the tables. My results are from Omegamon DB2
detail trace, & this was run using only DYNAMIC SQL, so you see the prepare
cost & the catalog access also.


START TIME INDB2 TIME INDB2 CPU SQL SORTS LOCKS ROWS
10:25:41.612 00:02.47354 0.53943 5 0 30 79912
10:24:51.740 00:02.70532 1.04761 5 1 35 229501
10:24:03.752 00:01.28613 0.83800 5 0 34 79879
12:40:42.374 00:00.00175 0.00144 2 0 5 2

SELECT COUNT(*)
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '56') WITH UR

PLAN # 1 (ESTIMATED COST: 23841 )

TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

SELECT '1'
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS ( SELECT '1'
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '56') ) WITH UR

PLAN # 1 (ESTIMATED COST: 23978 )

TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

PLAN # 2 (ESTIMATED COST: 23980 )

TABLE: SYSDUMMY1 ACCESS TYPE: SEQUENTIAL SCAN
PREFETCH ACTIVITY: SEQUENTIAL

SELECT COUNT( DISTINCT ACCT_NO)
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FD_STRT IN ('12', '34', '56') WITH UR

PLAN # 1 (ESTIMATED COST: 24121 )

TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

TABLE: ACCESS TYPE: SEQUENTIAL SCAN
SORT ACTIVITY: UNIQ(C)

SELECT '1'
FROM SYSIBM.SYSDUMMY1 X ** note the 'x' here
which sets up for the join.
WHERE EXISTS ( SELECT '1'
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '55')
AND X.IBMREQD = X.IBMREQD ) WITH UR ****** This makes it a
non-correlated
****** a 'first true' switch, basically
PLAN # 1 (ESTIMATED COST: 23848 )
TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

PLAN # 2 (ESTIMATED COST: 23850 )
TABLE: SYSDUMMY1 ACCESS TYPE: SEQUENTIAL SCAN
PREFETCH ACTIVITY: SEQUENTIAL

|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA TYPE | ROWS | ROWS | ROWS | ROWS | PAGES |
| | PROCES | LOOKED | QUAL/DM | QUAL/RD | SCAND |
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 79242| 79242| 74829| 0| 10766|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 79219| 0| 74806| 74806| 10756|
|-----------+-----------+-----------+-----------+-----------+-----------|
| WORK | 74806| 74806| 74806| 0| 745|
|-----------+-----------+-----------+-----------+-----------+-----------|
| WORK | 74806| 74806| 74806| 1| 740|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 79203| 0| 74794| 74794| 10744|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 1| 1| 1| 1| 2|
|-----------+-----------+-----------+-----------+-----------+-----------|
| WORK | 1| 1| 1| 1| 1|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 2| 1| 2| 2| 3|
| | | | | | |
|-----------+-----------+-----------+-----------+-----------+-----------|



|-----------+-----+--------------+---------+---------+----------+----------|
| CALL TYPE | CNT | INDB2 TIME | MRETCD | RWS PC | RWS DM | RWS RD |
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.02362 | 0| 670| 335| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:02.44990 | 100| 79242| 74829| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.01689 | 0| 670| 335| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:02.58262 | 0| 154025| 149612| 74806|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:00.10574 | 100| 74806| 74806| 1|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00007 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.01941 | 0| 674| 337| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:01.26646 | 0| 79203| 74794| 74794|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:00.00022 | 100| 2| 2| 2|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00004 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.01846 | 0| 674| 337| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:00.26938 | 100| 2| 2| 2|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|


> MRETCD MAXIMUM SQL RETURN CODE
> RWS PC: NUMBER OF ROWS PROCESSED BY THE DB2 DATA MANAGER.
> RWS DM: NUMBER OF ROWS QUALIFIED BY THE DB2 DATA MANAGER.
> RWS RD: NUMBER OF ROWS QUALIFIED BY THE RELATIONAL DATA SYSTEM.

START TIME PROGNAME INDB2 TIME INDB2 CPU SQL ROWS
12:47:10.959 DB2DYN02 0.17246 0.02008 5 704 prepare
12:47:10.925 DB2DYN02 0.00226 0.00156 2 2 count -= 0
12:46:24.545 DB2DYN02 0.26270 0.03096 5 704 prepare
12:46:24.515 DB2DYN02 0.01981 0.00401 2 2 count = 1


Missy Case
FDR
701-275-6358



Isaac Yassin

Re: existence checking
(in response to Missy Case)
Hi

You probably meant CORRELATED :-)

<snip>
****** This makes it a
non-correlated
****** a 'first true' switch, basically
<snip>

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



Hello

Re: existence checking
(in response to Isaac Yassin)
CAn you explain me why adding a correlated subquery is better in
performance ?


SELECT '1'
FROM SYSIBM.SYSDUMMY1 X ** note the 'x' here
which sets up for the join.
WHERE EXISTS ( SELECT '1'
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '55')
AND X.IBMREQD = X.IBMREQD ) WITH UR ****** This makes it a

instead of (more 'natural')


SELECT '1'
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS ( SELECT '1'
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '56') ) WITH UR



Thanks



<<<<<<<<<<
**I had this same question a year or so back, Terry Purcell was kind enough
to help me out. Here are the results of my test for a 'first true'
existence checking. We have DB2 data queues that I don't care if there are
5 or 5000, I just need to know if it's > 0.

Anyway, hopefully this comes thru in color as I've color coded the
statements & their results in the tables. My results are from Omegamon DB2
detail trace, & this was run using only DYNAMIC SQL, so you see the prepare
cost & the catalog access also.


START TIME INDB2 TIME INDB2 CPU SQL SORTS LOCKS ROWS
10:25:41.612 00:02.47354 0.53943 5 0 30 79912
10:24:51.740 00:02.70532 1.04761 5 1 35 229501
10:24:03.752 00:01.28613 0.83800 5 0 34 79879
12:40:42.374 00:00.00175 0.00144 2 0 5 2

SELECT COUNT(*)
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '56') WITH UR

PLAN # 1 (ESTIMATED COST: 23841 )

TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

SELECT '1'
FROM SYSIBM.SYSDUMMY1
WHERE EXISTS ( SELECT '1'
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '56') ) WITH UR

PLAN # 1 (ESTIMATED COST: 23978 )

TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

PLAN # 2 (ESTIMATED COST: 23980 )

TABLE: SYSDUMMY1 ACCESS TYPE: SEQUENTIAL SCAN
PREFETCH ACTIVITY: SEQUENTIAL

SELECT COUNT( DISTINCT ACCT_NO)
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FD_STRT IN ('12', '34', '56') WITH UR

PLAN # 1 (ESTIMATED COST: 24121 )

TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

TABLE: ACCESS TYPE: SEQUENTIAL SCAN
SORT ACTIVITY: UNIQ(C)

SELECT '1'
FROM SYSIBM.SYSDUMMY1 X ** note the 'x' here
which sets up for the join.
WHERE EXISTS ( SELECT '1'
FROM A.TAB1
WHERE CLIENT_NO = 'XXXX' AND WORK_IND = ' '
AND FRD_STRT IN ('12', '34', '55')
AND X.IBMREQD = X.IBMREQD ) WITH UR ****** This makes it a
non-correlated
****** a 'first true' switch, basically
PLAN # 1 (ESTIMATED COST: 23848 )
TABLE: TAB1 ACCESS TYPE: INDEX
INDEX: TAB1IX1 MATCHING COLS: 1
PREFETCH ACTIVITY: SEQUENTIAL

PLAN # 2 (ESTIMATED COST: 23850 )
TABLE: SYSDUMMY1 ACCESS TYPE: SEQUENTIAL SCAN
PREFETCH ACTIVITY: SEQUENTIAL

|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA TYPE | ROWS | ROWS | ROWS | ROWS | PAGES |
| | PROCES | LOOKED | QUAL/DM | QUAL/RD | SCAND |
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 79242| 79242| 74829| 0| 10766|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 79219| 0| 74806| 74806| 10756|
|-----------+-----------+-----------+-----------+-----------+-----------|
| WORK | 74806| 74806| 74806| 0| 745|
|-----------+-----------+-----------+-----------+-----------+-----------|
| WORK | 74806| 74806| 74806| 1| 740|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 79203| 0| 74794| 74794| 10744|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 1| 1| 1| 1| 2|
|-----------+-----------+-----------+-----------+-----------+-----------|
| WORK | 1| 1| 1| 1| 1|
|-----------+-----------+-----------+-----------+-----------+-----------|
| DATA | 2| 1| 2| 2| 3|
| | | | | | |
|-----------+-----------+-----------+-----------+-----------+-----------|



|-----------+-----+--------------+---------+---------+----------+----------|
| CALL TYPE | CNT | INDB2 TIME | MRETCD | RWS PC | RWS DM | RWS RD |
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.02362 | 0| 670| 335| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:02.44990 | 100| 79242| 74829| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.01689 | 0| 670| 335| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:02.58262 | 0| 154025| 149612| 74806|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:00.10574 | 100| 74806| 74806| 1|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00007 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.01941 | 0| 674| 337| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:01.26646 | 0| 79203| 74794| 74794|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:00.00022 | 100| 2| 2| 2|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00004 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| PREPARE | 1 | 00:00.01846 | 0| 674| 337| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| OPEN CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|
| FETCH | 2 | 00:00.26938 | 100| 2| 2| 2|
|-----------+-----+--------------+---------+---------+----------+----------|
| CLOSE CSR | 1 | 00:00.00001 | 0| 0| 0| 0|
|-----------+-----+--------------+---------+---------+----------+----------|


> MRETCD MAXIMUM SQL RETURN CODE
> RWS PC: NUMBER OF ROWS PROCESSED BY THE DB2 DATA MANAGER.
> RWS DM: NUMBER OF ROWS QUALIFIED BY THE DB2 DATA MANAGER.
> RWS RD: NUMBER OF ROWS QUALIFIED BY THE RELATIONAL DATA SYSTEM.

START TIME PROGNAME INDB2 TIME INDB2 CPU SQL ROWS
12:47:10.959 DB2DYN02 0.17246 0.02008 5 704 prepare
12:47:10.925 DB2DYN02 0.00226 0.00156 2 2 count -= 0
12:46:24.545 DB2DYN02 0.26270 0.03096 5 704 prepare
12:46:24.515 DB2DYN02 0.01981 0.00401 2 2 count = 1



Missy Case

Re: existence checking
(in response to Hello)
I'll try to explain it, quoting my FAVORITE DB2 book - DB2 Developer's
Guide - to try to support the test results.


Mullins explains it like this: 'The subselect portion of a correlated
subquery is reevaluated for each qualifying outer row." So, since there is
only a dummy row on sysdummy, the subselect only is performed once,
henceforth, a 'first true' escape after it is initially performed, and
since sysdummy doesn't have any more rows to force it into the subquery
again........

That makes it cheaper because it only does the subselect once & only uses
the resources it needs to find out 'true or false', not 1 or 10000. It can
have a limited use, but it was perfect for what we needed.

Yes, Isaac, I did mean 'correlated', not NON - good catch!
Thanks.
Missy Case
FDR
701-275-6358



jwkwan2000

Re: existence checking
(in response to Missy Case)
Missy,

I am still not very clear about your explanation. I don't have the
statement with me, but if I remember correctly, the outer statement is
select 1 from sysdummy and the subselect is the select something from the
table where some condition.
If I understand how this select works, DB2 will first select the row from
sysdummy and then evaluate the subselect. Since there is only 1 row in
sysdummy, it will either return 1 or null value after checking the
existence.
I don't see how correlating it will improve the performance. Actually it
should be slower since it has an extra compare to make.

Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390


----- Original Message -----
From: "Missy Case" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, January 06, 2003 12:27 PM
Subject: Re: existence checking


> I'll try to explain it, quoting my FAVORITE DB2 book - DB2 Developer's
> Guide - to try to support the test results.
>
>
> Mullins explains it like this: 'The subselect portion of a correlated
> subquery is reevaluated for each qualifying outer row." So, since there
is
> only a dummy row on sysdummy, the subselect only is performed once,
> henceforth, a 'first true' escape after it is initially performed, and
> since sysdummy doesn't have any more rows to force it into the subquery
> again........
>
> That makes it cheaper because it only does the subselect once & only uses
> the resources it needs to find out 'true or false', not 1 or 10000. It
can
> have a limited use, but it was perfect for what we needed.
>
> Yes, Isaac, I did mean 'correlated', not NON - good catch!
> Thanks.
> Missy Case
> FDR
> 701-275-6358
>
>
>





Isaac Yassin

Re: existence checking
(in response to jwkwan2000)
Hi,

We have beaten the subject many times in the past.
In short - the correlation causes the internal check to stop after the
1st found
Row and not continue to scan for other possible qualifying rows from
The table you check.


BTW - if you have a unique index to check on it's the best .

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of jwkwan2000
Sent: Monday, January 06, 2003 9:26 PM
To: [login to unmask email]
Subject: Re: existence checking


Missy,

I am still not very clear about your explanation. I don't have the
statement with me, but if I remember correctly, the outer statement is
select 1 from sysdummy and the subselect is the select something from
the table where some condition. If I understand how this select works,
DB2 will first select the row from sysdummy and then evaluate the
subselect. Since there is only 1 row in sysdummy, it will either return
1 or null value after checking the existence. I don't see how
correlating it will improve the performance. Actually it should be
slower since it has an extra compare to make.

Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390


----- Original Message -----
From: "Missy Case" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, January 06, 2003 12:27 PM
Subject: Re: existence checking


> I'll try to explain it, quoting my FAVORITE DB2 book - DB2 Developer's

> Guide - to try to support the test results.
>
>
> Mullins explains it like this: 'The subselect portion of a correlated

> subquery is reevaluated for each qualifying outer row." So, since
> there
is
> only a dummy row on sysdummy, the subselect only is performed once,
> henceforth, a 'first true' escape after it is initially performed, and

> since sysdummy doesn't have any more rows to force it into the
> subquery again........
>
> That makes it cheaper because it only does the subselect once & only
> uses the resources it needs to find out 'true or false', not 1 or
> 10000. It
can
> have a limited use, but it was perfect for what we needed.
>
> Yes, Isaac, I did mean 'correlated', not NON - good catch! Thanks.
> Missy Case
> FDR
> 701-275-6358
>
>
>
> visit
the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



jwkwan2000

Re: existence checking
(in response to Isaac Yassin)
Issac,

I have not seen this subject in the past. It seems to me if DB2 is smart
enough to know if the subselect is for existence checking, it should stop
when the first qualifying rows is return regardless whether it is
correlating or not. If it only happens when correlation is specified, then
it is a good candidate for future enhancement.

If there is an unique index on the inner table on those fields, then the
difference should be even insignificant.

Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390


----- Original Message -----
From: "Isaac Yassin" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, January 06, 2003 1:58 PM
Subject: Re: existence checking


> Hi,
>
> We have beaten the subject many times in the past.
> In short - the correlation causes the internal check to stop after the
> 1st found
> Row and not continue to scan for other possible qualifying rows from
> The table you check.
>
>
> BTW - if you have a unique index to check on it's the best .
>
> Isaac Yassin
> DBMS & IT Consultant
> IBM Certified Solution Expert
> DB2 V7.1 Database Administration for OS/390
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
> On Behalf Of jwkwan2000
> Sent: Monday, January 06, 2003 9:26 PM
> To: [login to unmask email]
> Subject: Re: existence checking
>
>
> Missy,
>
> I am still not very clear about your explanation. I don't have the
> statement with me, but if I remember correctly, the outer statement is
> select 1 from sysdummy and the subselect is the select something from
> the table where some condition. If I understand how this select works,
> DB2 will first select the row from sysdummy and then evaluate the
> subselect. Since there is only 1 row in sysdummy, it will either return
> 1 or null value after checking the existence. I don't see how
> correlating it will improve the performance. Actually it should be
> slower since it has an extra compare to make.
>
> Regards,
>
> James Kwan
> IBM Certified Solutions Expert
> - DB2 V7.1 Database Administration for OS/390
>
>
> ----- Original Message -----
> From: "Missy Case" <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Monday, January 06, 2003 12:27 PM
> Subject: Re: existence checking
>
>
> > I'll try to explain it, quoting my FAVORITE DB2 book - DB2 Developer's
>
> > Guide - to try to support the test results.
> >
> >
> > Mullins explains it like this: 'The subselect portion of a correlated
>
> > subquery is reevaluated for each qualifying outer row." So, since
> > there
> is
> > only a dummy row on sysdummy, the subselect only is performed once,
> > henceforth, a 'first true' escape after it is initially performed, and
>
> > since sysdummy doesn't have any more rows to force it into the
> > subquery again........
> >
> > That makes it cheaper because it only does the subselect once & only
> > uses the resources it needs to find out 'true or false', not 1 or
> > 10000. It
> can
> > have a limited use, but it was perfect for what we needed.
> >
> > Yes, Isaac, I did mean 'correlated', not NON - good catch! Thanks.
> > Missy Case
> > FDR
> > 701-275-6358
> >
> >
> >
> > visit
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
>
>
>
> the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
> can
>
>
>





Terry Purcell

Re: existence checking
(in response to jwkwan2000)
James,

Isaac is right, it has been covered a few times, I just cannot recall the
subject lines.

Let me play devil's advocate for a moment. Saying DB2 should do something is
pretty easy. Prioritizing and allocating resources to write the DB2 code,
given the large list of new features/requirements, is not as easy. IBM does
not have an endless supply of developers.

Although I understand IBM is considering this as a future enhancement
(non-OS/390 DB2 already has it).

There are many techniques for existence checking. Usage of non-correlated
subqueries is not a good choice. In V7 there is a better technique for
existence checking, utilizing FETCH FIRST 1 ROW ONLY on a singleton select.

If the result of the subquery is a single row from a unique index, then
non-correlated or correlated should give you similar performance (I haven't
tested this as it isn't of real interest).

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of jwkwan2000
Sent: Monday, January 06, 2003 3:01 PM
To: [login to unmask email]
Subject: Re: existence checking


Issac,

I have not seen this subject in the past. It seems to me if DB2 is smart
enough to know if the subselect is for existence checking, it should stop
when the first qualifying rows is return regardless whether it is
correlating or not. If it only happens when correlation is specified, then
it is a good candidate for future enhancement.

If there is an unique index on the inner table on those fields, then the
difference should be even insignificant.

Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390


----- Original Message -----
From: "Isaac Yassin" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Monday, January 06, 2003 1:58 PM
Subject: Re: existence checking


> Hi,
>
> We have beaten the subject many times in the past.
> In short - the correlation causes the internal check to stop after the
> 1st found
> Row and not continue to scan for other possible qualifying rows from
> The table you check.
>
>
> BTW - if you have a unique index to check on it's the best .
>
> Isaac Yassin
> DBMS & IT Consultant
> IBM Certified Solution Expert
> DB2 V7.1 Database Administration for OS/390
>
>
>
> -----Original Message-----
> From: DB2 Data Base Discussion List [mailto:[login to unmask email]
> On Behalf Of jwkwan2000
> Sent: Monday, January 06, 2003 9:26 PM
> To: [login to unmask email]
> Subject: Re: existence checking
>
>
> Missy,
>
> I am still not very clear about your explanation. I don't have the
> statement with me, but if I remember correctly, the outer statement is
> select 1 from sysdummy and the subselect is the select something from
> the table where some condition. If I understand how this select works,
> DB2 will first select the row from sysdummy and then evaluate the
> subselect. Since there is only 1 row in sysdummy, it will either return
> 1 or null value after checking the existence. I don't see how
> correlating it will improve the performance. Actually it should be
> slower since it has an extra compare to make.
>
> Regards,
>
> James Kwan
> IBM Certified Solutions Expert
> - DB2 V7.1 Database Administration for OS/390
>
>
> ----- Original Message -----
> From: "Missy Case" <[login to unmask email]>
> Newsgroups: bit.listserv.db2-l
> To: <[login to unmask email]>
> Sent: Monday, January 06, 2003 12:27 PM
> Subject: Re: existence checking
>
>
> > I'll try to explain it, quoting my FAVORITE DB2 book - DB2 Developer's
>
> > Guide - to try to support the test results.
> >
> >
> > Mullins explains it like this: 'The subselect portion of a correlated
>
> > subquery is reevaluated for each qualifying outer row." So, since
> > there
> is
> > only a dummy row on sysdummy, the subselect only is performed once,
> > henceforth, a 'first true' escape after it is initially performed, and
>
> > since sysdummy doesn't have any more rows to force it into the
> > subquery again........
> >
> > That makes it cheaper because it only does the subselect once & only
> > uses the resources it needs to find out 'true or false', not 1 or
> > 10000. It
> can
> > have a limited use, but it was perfect for what we needed.
> >
> > Yes, Isaac, I did mean 'correlated', not NON - good catch! Thanks.
> > Missy Case
> > FDR
> > 701-275-6358
> >



Isaac Yassin

Existence check
(in response to Terry Purcell)
Hi,
Let me expand a little bit. (I have cut out the history as it's too
long :-) )
Existence check can be done either on a unique field or a non-unique
one. (e.g. emp_id is unique , emp_name is non-unique) Let's assume we
have indexes on both. For the unique field check you should use the
regular select in all DB2 versions.
For the non-unique:
1. In V7 - use the "fetch 1 first row".
2. in V6 and before - use the correlated sysdummy approach.
About the correlation importance: If you open a trace on the command
you'll see that when you do a non correlated query Then DB2 uses DSNDB07
and scans many rows to find the 2nd occurrence. You will not see it in a
correlated query.
++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++++
NON-CORRELATED
Start Time Progname InDB2 Time InDB2 CPU SQL Sorts Locks
Rows
------------ -------- ------------ --------- ------ ------ ------
-------
10:14:17.957 DSNESM68 00:00.01399 .00131 5 0 7
3
Call Type Stm# Program Count InDB2 Time MRet Rws Pc Rws DM
Rws RD
---------------- ----- -------- ----- ------------ ---- ------ ------
------
PREPARE 116 DSNESM68 1 00:00.00022 0 0 0
0
OPEN CURSOR 190 DSNESM68 1 00:00.00049 0 1 13
13
FETCH 183 DSNESM68 2 00:00.01319 100 2 2
2
CLOSE CURSOR 197 DSNESM68 1 00:00.00009 0 0 0
0
------------ -- ---------------------
---------------------------------------
10:14:18.904 START OPEN CURSOR PGM=DSNESM68 STMT=00190

10:14:18.904 LOCK ACQUIRE DB=DSNDB07 PS=TSTMP07 PSET S
D=CMT
10:14:18.904 LOCK ACQUIRE DB=DSNDB07 DTBS S
D=CMT
10:14:18.904 LOCK ACQUIRE DB=DBZZZZZP PS=D246X0F6 PSET S
D=CMT
10:14:18.904 START INDEX SCAN DB=DBZZZZZP PS=D248X0F8

10:14:18.904 START INSERT SCAN DB=DSNDB07 PS=TSTMP07

10:14:18.904 END OF INDEX SCAN DB=DBZZZZZP PS=D248X0F8

ROWS=0000001 PAGES=0000003

10:14:18.904 END OPEN CURSOR ROWS=0000001 PAGES=0000005

Control=NEXT
(FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
Current=000003 Total Number of SQL Calls=000005

10:14:18.904 START FETCH PGM=DSNESM68 STMT=00183

10:14:18.904 LOCK ACQUIRE DB=DSNDB06 PS=SYSSTR PSET IS
D=CMT
10:14:18.904 LOCK ACQUIRE DB=DSNDB06 PS=D212X0D4 TABL IS
D=CMT
10:14:18.904 START SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR

10:14:18.917 END OF INSERT SCAN DB=DSNDB07 PS=TSTMP07

ROWS=0000000 PAGES=0000000

10:14:18.917 START SEQUENTIAL SCAN DB=DSNDB07 PS=TSTMP07

10:14:18.917 END FETCH ROWS=0000002 PAGES=0000002

Control=NEXT
(FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
Current=000004 Total Number of SQL Calls=000005

10:14:18.936 START FETCH PGM=DSNESM68 STMT=00183

10:14:18.936 END SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR

ROWS=0000001 PAGES=0000002

10:14:18.936 END SEQUENTIAL SCAN DB=DSNDB07 PS=TSTMP07

ROWS=0000001 PAGES=0000000

10:14:18.936 END FETCH ROWS=0000000 PAGES=0000000

Planname=DSNESPCS Connid=TSO Corrid=ISAAC Authid=ISAAC

10:14:18.936 START CLOSE CURSOR PGM=DSNESM68 STMT=00197

10:14:18.936 END CLOSE CURSOR ROWS=0000000 PAGES=0000000

=======================
=========
=======================
=========
CORRELATED QUERY
Planname=DSNESPCS Connid=TSO Corrid=ISAAC Authid=ISAAC

Start Time Progname InDB2 Time InDB2 CPU SQL Sorts Locks
Rows
------------ -------- ------------ --------- ------ ------ ------
-------
10:20:04.949 DSNESM68 00:00.00085 .00081 5 0 5
2
Call Type Stm# Program Count InDB2 Time MRet Rws Pc Rws DM
Rws RD
---------------- ----- -------- ----- ------------ ---- ------ ------
------
PREPARE 116 DSNESM68 1 00:00.00022 0 0 0
0
OPEN CURSOR 190 DSNESM68 1 00:00.00002 0 0 0
0
FETCH 183 DSNESM68 2 00:00.00059 100 2 2
2
CLOSE CURSOR 197 DSNESM68 1 00:00.00002 0 0 0
0
Control=NEXT
(FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
Current=000002 Total Number of SQL Calls=000005

10:20:06.251 START OPEN CURSOR PGM=DSNESM68 STMT=00190

10:20:06.251 END OPEN CURSOR ROWS=0000000 PAGES=0000000

Control=NEXT
(FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
Current=000003 Total Number of SQL Calls=000005

10:20:06.251 START FETCH PGM=DSNESM68 STMT=00183

10:20:06.251 LOCK ACQUIRE DB=DSNDB06 PS=SYSSTR PSET IS
D=CMT
10:20:06.251 LOCK ACQUIRE DB=DSNDB06 PS=D212X0D4 TABL IS
D=CMT
10:20:06.251 START SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR

10:20:06.251 LOCK ACQUIRE DB=DBZZZZZP PS=D246X0F6 PSET S
D=CMT
10:20:06.251 START INDEX SCAN DB=DBZZZZZP PS=D248X0F8

10:20:06.251 END FETCH ROWS=0000002 PAGES=0000005

10:20:06.259 START FETCH PGM=DSNESM68 STMT=00183

10:20:06.259 END SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR

ROWS=0000001 PAGES=0000002

10:20:06.259 END OF INDEX SCAN DB=DBZZZZZP PS=D248X0F8

ROWS=0000001 PAGES=0000003

10:20:06.259 END FETCH ROWS=0000000 PAGES=0000000

10:20:06.259 START CLOSE CURSOR PGM=DSNESM68 STMT=00197

10:20:06.259 END CLOSE CURSOR ROWS=0000000 PAGES=0000000

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



Moyeen Khan

Re: Existence check
(in response to Isaac Yassin)
Isaac;
What is a correlated sysdummy approach?



jwkwan2000

Re: Existence check
(in response to Moyeen Khan)
Issac,

Thanks for the information. Can I ask you few questions?

1) Can you show us the SQL statement?
2) On your second case, does DB2 scan as many rows in DSNDB07 until it find
the 2nd occurrence, or were there only 2 rows satisfied the criteria?

Regards,

James Kwan
IBM Certified Solutions Expert
- DB2 V7.1 Database Administration for OS/390



----- Original Message -----
From: "Isaac Yassin" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Thursday, January 09, 2003 11:20 AM
Subject: Existence check


> Hi,
> Let me expand a little bit. (I have cut out the history as it's too
> long :-) )
> Existence check can be done either on a unique field or a non-unique
> one. (e.g. emp_id is unique , emp_name is non-unique) Let's assume we
> have indexes on both. For the unique field check you should use the
> regular select in all DB2 versions.
> For the non-unique:
> 1. In V7 - use the "fetch 1 first row".
> 2. in V6 and before - use the correlated sysdummy approach.
> About the correlation importance: If you open a trace on the command
> you'll see that when you do a non correlated query Then DB2 uses DSNDB07
> and scans many rows to find the 2nd occurrence. You will not see it in a
> correlated query.
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> +++++
> NON-CORRELATED
> Start Time Progname InDB2 Time InDB2 CPU SQL Sorts Locks
> Rows
> ------------ -------- ------------ --------- ------ ------ ------
> -------
> 10:14:17.957 DSNESM68 00:00.01399 .00131 5 0 7
> 3
> Call Type Stm# Program Count InDB2 Time MRet Rws Pc Rws DM
> Rws RD
> ---------------- ----- -------- ----- ------------ ---- ------ ------
> ------
> PREPARE 116 DSNESM68 1 00:00.00022 0 0 0
> 0
> OPEN CURSOR 190 DSNESM68 1 00:00.00049 0 1 13
> 13
> FETCH 183 DSNESM68 2 00:00.01319 100 2 2
> 2
> CLOSE CURSOR 197 DSNESM68 1 00:00.00009 0 0 0
> 0
> ------------ -- ---------------------
> ---------------------------------------
> 10:14:18.904 START OPEN CURSOR PGM=DSNESM68 STMT=00190
>
> 10:14:18.904 LOCK ACQUIRE DB=DSNDB07 PS=TSTMP07 PSET S
> D=CMT
> 10:14:18.904 LOCK ACQUIRE DB=DSNDB07 DTBS S
> D=CMT
> 10:14:18.904 LOCK ACQUIRE DB=DBZZZZZP PS=D246X0F6 PSET S
> D=CMT
> 10:14:18.904 START INDEX SCAN DB=DBZZZZZP PS=D248X0F8
>
> 10:14:18.904 START INSERT SCAN DB=DSNDB07 PS=TSTMP07
>
> 10:14:18.904 END OF INDEX SCAN DB=DBZZZZZP PS=D248X0F8
>
> ROWS=0000001 PAGES=0000003
>
> 10:14:18.904 END OPEN CURSOR ROWS=0000001 PAGES=0000005
>
> Control=NEXT
> (FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
> Current=000003 Total Number of SQL Calls=000005
>
> 10:14:18.904 START FETCH PGM=DSNESM68 STMT=00183
>
> 10:14:18.904 LOCK ACQUIRE DB=DSNDB06 PS=SYSSTR PSET IS
> D=CMT
> 10:14:18.904 LOCK ACQUIRE DB=DSNDB06 PS=D212X0D4 TABL IS
> D=CMT
> 10:14:18.904 START SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR
>
> 10:14:18.917 END OF INSERT SCAN DB=DSNDB07 PS=TSTMP07
>
> ROWS=0000000 PAGES=0000000
>
> 10:14:18.917 START SEQUENTIAL SCAN DB=DSNDB07 PS=TSTMP07
>
> 10:14:18.917 END FETCH ROWS=0000002 PAGES=0000002
>
> Control=NEXT
> (FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
> Current=000004 Total Number of SQL Calls=000005
>
> 10:14:18.936 START FETCH PGM=DSNESM68 STMT=00183
>
> 10:14:18.936 END SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR
>
> ROWS=0000001 PAGES=0000002
>
> 10:14:18.936 END SEQUENTIAL SCAN DB=DSNDB07 PS=TSTMP07
>
> ROWS=0000001 PAGES=0000000
>
> 10:14:18.936 END FETCH ROWS=0000000 PAGES=0000000
>
> Planname=DSNESPCS Connid=TSO Corrid=ISAAC Authid=ISAAC
>
> 10:14:18.936 START CLOSE CURSOR PGM=DSNESM68 STMT=00197
>
> 10:14:18.936 END CLOSE CURSOR ROWS=0000000 PAGES=0000000
>
>
> =========
>
> =========
> CORRELATED QUERY
> Planname=DSNESPCS Connid=TSO Corrid=ISAAC Authid=ISAAC
>
> Start Time Progname InDB2 Time InDB2 CPU SQL Sorts Locks
> Rows
> ------------ -------- ------------ --------- ------ ------ ------
> -------
> 10:20:04.949 DSNESM68 00:00.00085 .00081 5 0 5
> 2
> Call Type Stm# Program Count InDB2 Time MRet Rws Pc Rws DM
> Rws RD
> ---------------- ----- -------- ----- ------------ ---- ------ ------
> ------
> PREPARE 116 DSNESM68 1 00:00.00022 0 0 0
> 0
> OPEN CURSOR 190 DSNESM68 1 00:00.00002 0 0 0
> 0
> FETCH 183 DSNESM68 2 00:00.00059 100 2 2
> 2
> CLOSE CURSOR 197 DSNESM68 1 00:00.00002 0 0 0
> 0
> Control=NEXT
> (FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
> Current=000002 Total Number of SQL Calls=000005
>
> 10:20:06.251 START OPEN CURSOR PGM=DSNESM68 STMT=00190
>
> 10:20:06.251 END OPEN CURSOR ROWS=0000000 PAGES=0000000
>
> Control=NEXT
> (FIRST/LAST/NEXT/PREV/+nnnn/-nnnn/Snnnn/TOP/BOTTOM/UP/DOWN)
> Current=000003 Total Number of SQL Calls=000005
>
> 10:20:06.251 START FETCH PGM=DSNESM68 STMT=00183
>
> 10:20:06.251 LOCK ACQUIRE DB=DSNDB06 PS=SYSSTR PSET IS
> D=CMT
> 10:20:06.251 LOCK ACQUIRE DB=DSNDB06 PS=D212X0D4 TABL IS
> D=CMT
> 10:20:06.251 START SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR
>
> 10:20:06.251 LOCK ACQUIRE DB=DBZZZZZP PS=D246X0F6 PSET S
> D=CMT
> 10:20:06.251 START INDEX SCAN DB=DBZZZZZP PS=D248X0F8
>
> 10:20:06.251 END FETCH ROWS=0000002 PAGES=0000005
>
> 10:20:06.259 START FETCH PGM=DSNESM68 STMT=00183
>
> 10:20:06.259 END SEQUENTIAL SCAN DB=DSNDB06 PS=SYSSTR
>
> ROWS=0000001 PAGES=0000002
>
> 10:20:06.259 END OF INDEX SCAN DB=DBZZZZZP PS=D248X0F8
>
> ROWS=0000001 PAGES=0000003
>
> 10:20:06.259 END FETCH ROWS=0000000 PAGES=0000000
>
> 10:20:06.259 START CLOSE CURSOR PGM=DSNESM68 STMT=00197
>
> 10:20:06.259 END CLOSE CURSOR ROWS=0000000 PAGES=0000000
>
> Isaac Yassin
> DBMS & IT Consultant
> IBM Certified Solution Expert
> DB2 V7.1 Database Administration for OS/390
>
>
>





Isaac Yassin

Re: Existence check
(in response to jwkwan2000)
Hi,

Select 1 from sysibm.sysdummy1 as x where exists(select 1 from
your.table where <your conditions> and x.ibmreqd=x.ibmreqd)

Used in V6 and before to check existence of <your conditions>
(another way is the cursor approach: "open,fetch,close")

Became obsolote with V7 "fetch 1 first row"

Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Moyeen Khan
Sent: Thursday, January 09, 2003 7:49 PM
To: [login to unmask email]
Subject: Re: Existence check


Isaac;
What is a correlated sysdummy approach?



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Craig Mullins

FW: Existence check
(in response to Isaac Yassin)
Folks:

There is a very good article on dbazine.com that discusses existence
checking along with examples and test results. Check it out at
http://www.dbazine.com/custard1.html

Cheers,
Craig S. Mullins
http://www.CraigSMullins.com
http://www.DBAzine.com
http://www.BMC.com

>------------------------------
>
>Date: Thu, 9 Jan 2003 12:49:06 -0500
>From: Moyeen Khan <[login to unmask email]>
>Subject: Re: Existence check
>
>Isaac;
>What is a correlated sysdummy approach?
>
>------------------------------



Isaac Yassin

Re: Existence check
(in response to Craig Mullins)
Hi,

Not good enough as the distance between 1st and 2nd occurrence (if one
exists at all) was taken into account.


Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Mullins, Craig
Sent: Friday, January 10, 2003 8:05 PM
To: [login to unmask email]
Subject: FW: Existence check


Folks:

There is a very good article on dbazine.com that discusses existence
checking along with examples and test results. Check it out at
http://www.dbazine.com/custard1.html

Cheers,
Craig S. Mullins
http://www.CraigSMullins.com
http://www.DBAzine.com
http://www.BMC.com

>------------------------------
>
>Date: Thu, 9 Jan 2003 12:49:06 -0500
>From: Moyeen Khan <[login to unmask email]>
>Subject: Re: Existence check
>
>Isaac;
>What is a correlated sysdummy approach?
>
>------------------------------



the DB2-L webpage at http://listserv.ylassoc.com. The owners of the list
can



Terry Purcell

Re: Existence check
(in response to Isaac Yassin)
Craig,

While I don't disagree with the article's findings regarding the types of
existence check methods used. The article missed two other major techniques,
DECLARE CURSOR & FETCH (with OPTIMIZE FOR 1 ROW), and singleton SELECT with
FETCH FIRST 1 ROW ONLY (because the article is V6).

The article discovered that there were situations when a correlated subquery
was best, and others when a singleton was best. However, it did not discuss
why there were differences (but who cares other than me?). When you set a
standard you want to give your programmers one answer, not two, as they are
bound to make the incorrect choice at least some of the time (due to
cloning).

Our testing showed that the DECLARE CURSOR & 1 FETCH overcomes the issues
with the performance fluctuations between correlated or singleton. Although
not always the best, it did provide, on average, the best performance,
basically because of the OPTIMIZE FOR 1 ROW (avoidance of sequential
prefetch when undesirable), and the ability to stop after 1 row is retrieved
(just like the correlated). There is more, but I wont go into it.

However, in V7 (as has been stated many times), this issue is completely
resolved. Singleton select with FETCH FIRST 1 ROW only. End of discussion.

For a more up to date discussion of the theory (without pictures - which
would be beneficial), go to this article from the IDUG solutions journal. It
was written in response to many of the misconceptions that had been
propogated by a myriad of misleading articles on this topic.

http://www.idug.org/member/journal/Nov01/articl10.cfm

Regards
Terry Purcell
Yevich Lawson & Assoc Inc (YL&A)
IBM Gold Consultant - DB2
IBM Certified Solutions Expert
DB2 V7 Database Administration OS/390
http://www.ylassoc.com


-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Isaac Yassin
Sent: Saturday, January 11, 2003 3:35 AM
To: [login to unmask email]
Subject: Re: Existence check


Hi,

Not good enough as the distance between 1st and 2nd occurrence (if one
exists at all) was taken into account.


Isaac Yassin
DBMS & IT Consultant
IBM Certified Solution Expert
DB2 V7.1 Database Administration for OS/390



-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]
On Behalf Of Mullins, Craig
Sent: Friday, January 10, 2003 8:05 PM
To: [login to unmask email]
Subject: FW: Existence check


Folks:

There is a very good article on dbazine.com that discusses existence
checking along with examples and test results. Check it out at
http://www.dbazine.com/custard1.html

Cheers,
Craig S. Mullins
http://www.CraigSMullins.com
http://www.DBAzine.com
http://www.BMC.com