SQL Existance Checking

Dee Reins

SQL Existance Checking


Our vendor has supplied this SQL. The table VXX009EXS is a 1 row table used for existance checking.
The table VMR257VID is 150,000 or more rows.
I am thinking tha the "SELECT * " will retrieve all the data for the selected rows. I think that this could be improved by only selecting data columns in the index. Others think that the data will not be retrieved because it is a sub select. I also don't think the existance check table "vVXX009EXS" is required.


FROM VXX009EXS_CHK A
WHERE EXISTS ( SELECT *
FROM VMR257VID_FACS B , VXX009EXS_CHK C
WHERE B . SRVC_FACS_ID = :H AND B . SRVC_FACS_TYP_CD = 'S' AND B .
PORT_FACS_ID = :H AND B . VID = :H AND B . VCI = :H AND ( B . AID_VPI
<> :H OR B . AID_VCI <> :H ) AND C . EXS_CHK_CD = A . EXS_CHK_CD ) WITH
UR ;

The ":H" is a host variable.

Please let me know what you think

Thanks
Dee


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
Attachments

  • import1 (4.6k)

Robert Catterall

Re: SQL Existance Checking
(in response to Dee Reins)
Dee,

SELECT * is the conventional coding for a SELECT statement that's part of an
EXISTS predicate. My understanding is that the column specification doesn't
matter. DB2 just checks to see if the number of rows in the result set is
zero or not-zero. I do not think that specifying only index-key columns in
the SELECT in the EXISTS predicate would affect the efficiency of the query.

I see that the SELECT in the EXISTS predicate references the EXS_CHK_CD
column of the 1-row table. Replacing that reference with a host variable
(so that you'd have AND C.EXS_CHK_CD = :H) should make the 1-row table
unnecessary. For the outer SELECT, I'd think that you could just SELECT a
literal from SYSIBM.SYSDUMMY1.

Robert


On Tue, Dec 2, 2008 at 6:15 PM, Dee Reins <[login to unmask email]> wrote:

>
>
> Our vendor has supplied this SQL. The table VXX009EXS is a 1 row table
> used for existance checking.
> The table VMR257VID is 150,000 or more rows.
> I am thinking tha the "SELECT * " will retrieve all the data for the
> selected rows. I think that this could be improved by only selecting data
> columns in the index. Others think that the data will not be retrieved
> because it is a sub select. I also don't think the existance check table
> "vVXX009EXS" is required.
>
>
> FROM VXX009EXS_CHK A
> WHERE EXISTS ( SELECT *
> FROM VMR257VID_FACS B , VXX009EXS_CHK C
> WHERE B . SRVC_FACS_ID = :H AND B . SRVC_FACS_TYP_CD = 'S' AND B .
> PORT_FACS_ID = :H AND B . VID = :H AND B . VCI = :H AND ( B . AID_VPI
> <> :H OR B . AID_VCI <> :H ) AND C . EXS_CHK_CD = A . EXS_CHK_CD ) WITH
> UR ;
>
> The ":H" is a host variable.
>
> Please let me know what you think
>
> Thanks
> Dee
>
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code
> Place, see the latest IDUG conference information and much more. If you
> have not yet signed up for Basic Membership in IDUG, available at no cost,
> click on Member Services at http://www.idug.org/lsms
>
>
> ---------- Forwarded message ----------
> From: Max Scarpa <[login to unmask email]>
> To: [login to unmask email]
> Date: Tue, 2 Dec 2008 13:49:18 +0000
> Subject: Re: [z/OS] - V5 to V8 migration
>
> Did you tell them you were not born in a manger ?
>
> Max Scarpa
>
>
>
> *Adam Baldwin <[login to unmask email]>*
> Sent by: DB2 Data Base Discussion List <[login to unmask email]>
>
> 02/12/08 12.48
> Please respond to
> DB2 Database Discussion list at IDUG <[login to unmask email]>
>
> To
> [login to unmask email] cc
> Subject
> Re: [DB2-L] [z/OS] - V5 to V8 migration
>
>
>
>
> Thanks Max & Isaac.
>
> I've done a 4 to 7 like that myself, and plenty of "normal" 5 to 7s ....
> the
> worry is the leap from 5 to 8!
>
> We may have to end up going for the unload / load route.... but I'd rather
> not.
> And yes, the rest of the operating system will be upgraded too. This is a
> client
> who is outsourcing and their system / software all dates back to 2003 +/-.
>
> Management are wanting the quickest route to a more current platform and
> I'm looking for something with a good degree of safety.
>
> I'll let you know which way we jump.
>
> Cheers, Adam
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at
> http://www.idug.org/lsidug under the Listserv tab. While at the site, you
> can also access the IDUG Online Learning Center, Tech Library and Code
> Place, see the latest IDUG conference information and much more. If you
> have not yet signed up for Basic Membership in IDUG, available at no cost,
> click on Member Services at http://www.idug.org/lsms
>
>
> ------------------------------
>
> *IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA * < http://idug.org/lsNA >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. * If you have
> not yet signed up for Basic Membership in IDUG, available at no cost, click
> on Member Services < http://www.idug.org/lsms > *
>
> ------------------------------
>
> *IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA * < http://idug.org/lsNA >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
> list archives, FAQ, and delivery preferences are at *IDUG.ORG < http://www.idug.org/lsidug >
> * under the Listserv tab. While at the site, you can also access the IDUG
> Online Learning Center, Tech Library and Code Place, see the latest IDUG *conference
> information < http://www.idug.org/lsconf > *, and much more. * If you have
> not yet signed up for Basic Membership in IDUG, available at no cost, click
> on Member Services < http://www.idug.org/lsms > *
>
>


--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Agus Kwee

Re: SQL Existance Checking
(in response to Robert Catterall)
Dee,1. 'SELECT *'  in a subquery that follows WHERE EXISTS does not retrieve any data.2. I agree with you that you canl obtain what you need to know without the use of the 1 row table    table VXX09EXS_CHECK by simply coding the SELECT FROM VMR257VID probably    selecting only 1 column and specifying FETCH FIRST 1 ROW ONLY if all you need to know   is whether  the result table has at least 1 row or no row at all.Regards,Agus KweeThemis Traininghttp://www.themisinc.com----- Original Message -----From: Dee Reins Date: Tuesday, December 2, 2008 7:49 pmSubject: [DB2-L] SQL Existance CheckingTo: [login to unmask email]> > > Our vendor has supplied this SQL. The table VXX009EXS is a 1 > row table used for existance checking. > The table VMR257VID is 150,000 or more rows. > I am thinking tha the "SELECT * " will retrieve all the data > for the selected rows. I think that this could be improved by > only selecting data columns in the index. Others think that > the data will not be retrieved because it is a sub select. I > also don't think the existance check table "vVXX009EXS" is required.> > > FROM VXX009EXS_CHK A > WHERE EXISTS ( SELECT * > FROM VMR257VID_FACS B , VXX009EXS_CHK C > WHERE B . SRVC_FACS_ID = :H AND B . SRVC_FACS_TYP_CD = 'S' AND > B . > PORT_FACS_ID = :H AND B . VID = :H AND B . VCI = :H AND ( B > . AID_VPI > <> :H OR B . AID_VCI <> :H ) AND C . EXS_CHK_CD = A . > EXS_CHK_CD ) WITH > UR ; > > The ":H" is a host variable. > > Please let me know what you think > > Thanks> Dee> > > ______________________________________________________________________> > * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * > http://IDUG.ORG/lsNA *> ______________________________________________________________________> > > > The IDUG DB2-L Listserv is only part of your membership in IDUG. > The DB2-L list archives, FAQ, and delivery preferences are at > http://www.idug.org/lsidug under the Listserv tab. While at the > site, you can also access the IDUG Online Learning Center, Tech > Library and Code Place, see the latest IDUG conference > information and much more. If you have not yet signed up for > Basic Membership in IDUG, available at no cost, click on Member > Services at http://www.idug.org/lsms >

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Vanroose

Re: SQL Existance Checking
(in response to Agus Kwee)
"SELECT *" in the subquery could (and should?) indeed be replaced by "SELECT
1", for readability purposes: that would make it clearer for the reader that
not data columns need to be obtained.

As to the two suggested alternatives (using SYSIBM.SYSDUMMY1 and using FETCH
FIRST 1 ROW ONLY) for the outer query: I see one good reason why your vendor
did not use one of these "standard DB2" solutions: thy are both
DB2-specific, while his solution is portable (to other SQL platforms).

-- Peter Vanroose
ABIS Training & Consulting.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Peter Backlund

Re: SQL Existance Checking
(in response to Peter Vanroose)
Just for fun, in my education I used to tell them to use "select 1 ..." in exists clauses.
One of my students asked "Doesn't that return the first column?" :-(

Since then I am always using "Select 0 ..."

Best regards,

Peter

Peter Vanroose wrote: "SELECT *" in the subquery could (and should?) indeed be replaced by "SELECT 1", for readability purposes: that would make it clearer for the reader that not data columns need to be obtained. As to the two suggested alternatives (using SYSIBM.SYSDUMMY1 and using FETCH FIRST 1 ROW ONLY) for the outer query: I see one good reason why your vendor did not use one of these "standard DB2" solutions: thy are both DB2-specific, while his solution is portable (to other SQL platforms). -- Peter Vanroose ABIS Training & Consulting. ______________________________________________________________________ * IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA * ______________________________________________________________________ The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms
No virus found in this incoming message. Checked by AVG - http://www.avg.com Version: 8.0.176 / Virus Database: 270.9.13/1825 - Release Date: 2008-12-02 20:44
-- Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy Learn more at http://www.idug.org +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Rasundavagen 94 | Country Code (Sweden): 46 | | S-169 57 SOLNA | Skype: BacklundDB2 | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+

IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services

Edward Long

Re: SQL Existance Checking
(in response to Peter Backlund)
In a recent past life, I tested two versions of this test. Having the subselect return an 'x' as in select 'x' consumed about 5% less CPU than select *. The question that subselect answered required multiple joins. It appeared that the result set was in fact being materialized just to say it does or doesn't exist.
So the select 'x' has become a standard part of the toolkit.
ymmv.

Edward Long


--- On Tue, 12/2/08, Robert Catterall <[login to unmask email]> wrote:

> From: Robert Catterall <[login to unmask email]>
> Subject: Re: [DB2-L] SQL Existance Checking
> To: [login to unmask email]
> Date: Tuesday, December 2, 2008, 9:12 PM
> Dee,
>
> SELECT * is the conventional coding for a SELECT statement
> that's part of an
> EXISTS predicate. My understanding is that the column
> specification doesn't
> matter. DB2 just checks to see if the number of rows in
> the result set is
> zero or not-zero. I do not think that specifying only
> index-key columns in
> the SELECT in the EXISTS predicate would affect the
> efficiency of the query.
>
> I see that the SELECT in the EXISTS predicate references
> the EXS_CHK_CD
> column of the 1-row table. Replacing that reference with a
> host variable
> (so that you'd have AND C.EXS_CHK_CD = :H) should make
> the 1-row table
> unnecessary. For the outer SELECT, I'd think that you
> could just SELECT a
> literal from SYSIBM.SYSDUMMY1.
>
> Robert
>
>
> On Tue, Dec 2, 2008 at 6:15 PM, Dee Reins
> <[login to unmask email]> wrote:
>
> >
> >
> > Our vendor has supplied this SQL. The table
> VXX009EXS is a 1 row table
> > used for existance checking.
> > The table VMR257VID is 150,000 or more rows.
> > I am thinking tha the "SELECT * " will
> retrieve all the data for the
> > selected rows. I think that this could be improved
> by only selecting data
> > columns in the index. Others think that the data
> will not be retrieved
> > because it is a sub select. I also don't think
> the existance check table
> > "vVXX009EXS" is required.
> >
> >
> > FROM VXX009EXS_CHK A
> > WHERE EXISTS ( SELECT *
> > FROM VMR257VID_FACS B , VXX009EXS_CHK C
> > WHERE B . SRVC_FACS_ID = :H AND B . SRVC_FACS_TYP_CD
> = 'S' AND B .
> > PORT_FACS_ID = :H AND B . VID = :H AND B . VCI = :H
> AND ( B . AID_VPI
> > <> :H OR B . AID_VCI <> :H ) AND C .
> EXS_CHK_CD = A . EXS_CHK_CD ) WITH
> > UR ;
> >
> > The ":H" is a host variable.
> >
> > Please let me know what you think
> >
> > Thanks
> > Dee
> >
> >
> >
> ______________________________________________________________________
> >
> > * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> >
> ______________________________________________________________________
> >
> >
> >
> > The IDUG DB2-L Listserv is only part of your
> membership in IDUG. The DB2-L
> > list archives, FAQ, and delivery preferences are at
> > http://www.idug.org/lsidug under the Listserv tab.
> While at the site, you
> > can also access the IDUG Online Learning Center, Tech
> Library and Code
> > Place, see the latest IDUG conference information and
> much more. If you
> > have not yet signed up for Basic Membership in IDUG,
> available at no cost,
> > click on Member Services at http://www.idug.org/lsms
> >
> >
> > ---------- Forwarded message ----------
> > From: Max Scarpa <[login to unmask email]>
> > To: [login to unmask email]
> > Date: Tue, 2 Dec 2008 13:49:18 +0000
> > Subject: Re: [z/OS] - V5 to V8 migration
> >
> > Did you tell them you were not born in a manger ?
> >
> > Max Scarpa
> >
> >
> >
> > *Adam Baldwin <[login to unmask email]>*
> > Sent by: DB2 Data Base Discussion List
> <[login to unmask email]>
> >
> > 02/12/08 12.48
> > Please respond to
> > DB2 Database Discussion list at IDUG
> <[login to unmask email]>
> >
> > To
> > [login to unmask email] cc
> > Subject
> > Re: [DB2-L] [z/OS] - V5 to V8 migration
> >
> >
> >
> >
> > Thanks Max & Isaac.
> >
> > I've done a 4 to 7 like that myself, and plenty of
> "normal" 5 to 7s ....
> > the
> > worry is the leap from 5 to 8!
> >
> > We may have to end up going for the unload / load
> route.... but I'd rather
> > not.
> > And yes, the rest of the operating system will be
> upgraded too. This is a
> > client
> > who is outsourcing and their system / software all
> dates back to 2003 +/-.
> >
> > Management are wanting the quickest route to a more
> current platform and
> > I'm looking for something with a good degree of
> safety.
> >
> > I'll let you know which way we jump.
> >
> > Cheers, Adam
> >
> >
> ______________________________________________________________________
> >
> > * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> >
> ______________________________________________________________________
> >
> >
> >
> > The IDUG DB2-L Listserv is only part of your
> membership in IDUG. The DB2-L
> > list archives, FAQ, and delivery preferences are at
> > http://www.idug.org/lsidug under the Listserv tab.
> While at the site, you
> > can also access the IDUG Online Learning Center, Tech
> Library and Code
> > Place, see the latest IDUG conference information and
> much more. If you
> > have not yet signed up for Basic Membership in IDUG,
> available at no cost,
> > click on Member Services at http://www.idug.org/lsms
> >
> >
> > ------------------------------
> >
> > *IDUG 2009 - North America * May 11-15, 2009 * Denver,
> CO, USA * < http://idug.org/lsNA >
> >
> > The IDUG DB2-L Listserv is only part of your
> membership in IDUG. The DB2-L
> > list archives, FAQ, and delivery preferences are at
> *IDUG.ORG < http://www.idug.org/lsidug >
> > * under the Listserv tab. While at the site, you can
> also access the IDUG
> > Online Learning Center, Tech Library and Code Place,
> see the latest IDUG *conference
> > information < http://www.idug.org/lsconf > *, and
> much more. * If you have
> > not yet signed up for Basic Membership in IDUG,
> available at no cost, click
> > on Member Services < http://www.idug.org/lsms > *
> >
> > ------------------------------
> >
> > *IDUG 2009 - North America * May 11-15, 2009 * Denver,
> CO, USA * < http://idug.org/lsNA >
> >
> > The IDUG DB2-L Listserv is only part of your
> membership in IDUG. The DB2-L
> > list archives, FAQ, and delivery preferences are at
> *IDUG.ORG < http://www.idug.org/lsidug >
> > * under the Listserv tab. While at the site, you can
> also access the IDUG
> > Online Learning Center, Tech Library and Code Place,
> see the latest IDUG *conference
> > information < http://www.idug.org/lsconf > *, and
> much more. * If you have
> > not yet signed up for Basic Membership in IDUG,
> available at no cost, click
> > on Member Services < http://www.idug.org/lsms > *
> >
> >
>
>
> --
> Robert Catterall
> Catterall Consulting
> www.catterallconsulting.com
>
> ______________________________________________________________________
>
> * IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
> http://IDUG.ORG/lsNA *
> ______________________________________________________________________
>
>
>
> The IDUG DB2-L Listserv is only part of your membership in
> IDUG. The DB2-L list archives, FAQ, and delivery
> preferences are at http://www.idug.org/lsidug under the
> Listserv tab. While at the site, you can also access the
> IDUG Online Learning Center, Tech Library and Code Place,
> see the latest IDUG conference information and much more.
> If you have not yet signed up for Basic Membership in IDUG,
> available at no cost, click on Member Services at
> http://www.idug.org/lsms

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Paul Ogborne

Re: SQL Existance Checking
(in response to Edward Long)
Hi Dee,

Just a postscript to the other comments.

Sometimes an existence check is justified.
However, I have seen code where the check is done just?prior to an INSERT into the same table.? In this situation I almost always?prefer to see just the INSERT only,?together with a check for SQLCODE?-803 (..duplicate key; showing that the row already exists).
I.E. Apart from the saving of not doing the SELECT it is also?theoretically?possible for another task to INSERT the same?row between the first task's?existence check and INSERT, therein negating the SELECT and potentially causing a referential integrity?issue in the data.

Best regards,
Paul.

-----Original Message-----
From: Dee Reins <[login to unmask email]>
To: [login to unmask email]
Sent: Tue, 2 Dec 2008 23:15
Subject: [DB2-L] SQL Existance Checking





Our vendor has supplied this SQL. The table VXX009EXS is a 1 row table used
for existance checking.
The table VMR257VID is 150,000 or more rows.
I am thinking tha the "SELECT * " will retrieve all the data for the selected
rows. I think that this could be improved by only selecting data columns in
the index. Others think that the data will not be retrieved because it is a
sub select. I also don't think the existance check table "vVXX009EXS" is
required.


FROM VXX009EXS_CHK A
WHERE EXISTS ( SELECT *
FROM VMR257VID_FACS B , VXX009EXS_CHK C
WHERE B . SRVC_FACS_ID = :H AND B . SRVC_FACS_TYP_CD = 'S' AND B .
PORT_FACS_ID = :H AND B . VID = :H AND B . VCI = :H AND ( B . AID_VPI
<> :H OR B . AID_VCI <> :H ) AND C . EXS_CHK_CD = A . EXS_CHK_CD ) WITH
UR ;

The ":H" is a host variable.

Please let me know what you think

Thanks
Dee


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list
archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under
the Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG conference
information and much more. If you have not yet signed up for Basic Membership
in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms




Attached Message




From:

Max Scarpa <[login to unmask email]>



To:

[login to unmask email]



Subject:

Re: [z/OS] - V5 to V8 migration



Date:

Tue, 2 Dec 2008 13:49:18 +0000






Did you tell them you were not born in a manger ?

Max Scarpa







Adam Baldwin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
02/12/08 12.48




Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>













To


[login to unmask email]




cc







Subject


Re: [DB2-L] [z/OS] - V5 to V8 migration






















Thanks Max & Isaac.

I've done a 4 to 7 like that myself, and plenty of "normal" 5 to 7s .... the
worry is the leap from 5 to 8!

We may have to end up going for the unload / load route.... but I'd rather not.
And yes, the rest of the operating system will be upgraded too. This is a client
who is outsourcing and their system / software all dates back to 2003 +/-.

Management are wanting the quickest route to a more current platform and
I'm looking for something with a good degree of safety.

I'll let you know which way we jump.

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. ?The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. ?While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. ?If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms



IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA
The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services



________________________________________________________________________
AOL Email goes Mobile! You can now read your AOL Emails whilst on the move. Sign up for a free AOL Email account with unlimited storage today.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

M. Khalid Khan

Re: SQL Existance Checking
(in response to Paul Ogborne)
Existance checking tables are so 20th century ! Starting with v7 I have
been coding and recommending this:

SELECT literal INTO :hv
FROM MYTABLE
WHERE existance-conditions
FETCH FIRST 1 ROW ONLY

It's a lot simpler, clearer and avoids all processing for the "existance
checking" table.
HTH
Khalid





"Dee Reins"
<[login to unmask email]
T.NET> To
Sent by: "DB2 [login to unmask email]
Data Base cc
Discussion List"
<[login to unmask email] Subject
ORG> [DB2-L] SQL Existance Checking


12/02/2008 05:15
PM


Please respond to
"DB2 Database
Discussion list
at IDUG"
<[login to unmask email]
2-l.org>








Our vendor has supplied this SQL. The table VXX009EXS is a 1 row table
used for existance checking.
The table VMR257VID is 150,000 or more rows.
I am thinking tha the "SELECT * " will retrieve all the data for the
selected rows. I think that this could be improved by only selecting
data columns in the index. Others think that the data will not be
retrieved because it is a sub select. I also don't think the existance
check table "vVXX009EXS" is required.


FROM VXX009EXS_CHK A
WHERE EXISTS ( SELECT *
FROM VMR257VID_FACS B , VXX009EXS_CHK C
WHERE B . SRVC_FACS_ID = :H AND B . SRVC_FACS_TYP_CD = 'S' AND B .
PORT_FACS_ID = :H AND B . VID = :H AND B . VCI = :H AND ( B . AID_VPI
<> :H OR B . AID_VCI <> :H ) AND C . EXS_CHK_CD = A . EXS_CHK_CD ) WITH

UR ;

The ":H" is a host variable.

Please let me know what you think

Thanks
Dee


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

----- Message from "Max Scarpa" <[login to unmask email]> on Tue, 2
Dec 2008 13:49:18 +0000 -----

To: [login to unmask email],

Subject: Re: [z/OS] - V5 to V8
migration



Did you tell them you were not born in a manger ?

Max Scarpa



Adam Baldwin <[login to unmask email]>
Sent by: DB2 Data Base Discussion List
<[login to unmask email]> To
[login to unmask email]
org
02/12/08 12.48 cc

Subject
Please respond to Re: [DB2-L] [z/OS] -
DB2 Database Discussion list at IDUG V5 to V8 migration
<[login to unmask email]>











Thanks Max & Isaac.

I've done a 4 to 7 like that myself, and plenty of "normal" 5 to 7s ....
the
worry is the leap from 5 to 8!

We may have to end up going for the unload / load route.... but I'd rather
not.
And yes, the rest of the operating system will be upgraded too. This is a
client
who is outsourcing and their system / software all dates back to 2003 +/-.

Management are wanting the quickest route to a more current platform and
I'm looking for something with a good degree of safety.

I'll let you know which way we jump.

Cheers, Adam

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms





IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG: under the
Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services






IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA


The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG: under the
Listserv tab. While at the site, you can also access the IDUG Online
Learning Center, Tech Library and Code Place, see the latest IDUG
conference information, and much more. If you have not yet signed up for
Basic Membership in IDUG, available at no cost, click on Member Services





**********

The information contained in this communication is confidential, private, proprietary, or otherwise privileged and is intended only for the use of the addressee. Unauthorized use, disclosure, distribution or copying is strictly prohibited and may be unlawful. If you have received this communication in error, please notify the sender immediately at (312)653-6000 in Illinois; (800)835-8699 in New Mexico; (918)560-3500 in Oklahoma; or (972)766-6900 in Texas.

**********

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Phil Grainger

Re: SQL Existance Checking
(in response to M. Khalid Khan)
In the interests of readability then, shouldn't we advocate

SELECT "DOES ROW EXIST?" FROM ..........

Phil G
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Peter Backlund
Sent: Wed 03/12/2008 12:29
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Existance Checking


Just for fun, in my education I used to tell them to use "select 1 ..." in exists clauses.
One of my students asked "Doesn't that return the first column?" :-(

Since then I am always using "Select 0 ..."

Best regards,

Peter

Peter Vanroose wrote:

"SELECT *" in the subquery could (and should?) indeed be replaced by "SELECT
1", for readability purposes: that would make it clearer for the reader that
not data columns need to be obtained.

As to the two suggested alternatives (using SYSIBM.SYSDUMMY1 and using FETCH
FIRST 1 ROW ONLY) for the outer query: I see one good reason why your vendor
did not use one of these "standard DB2" solutions: thy are both
DB2-specific, while his solution is portable (to other SQL platforms).

-- Peter Vanroose
ABIS Training & Consulting.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

________________________________



No virus found in this incoming message.
Checked by AVG - http://www.avg.com < http://www.avg.com/ >
Version: 8.0.176 / Virus Database: 270.9.13/1825 - Release Date: 2008-12-02 20:44




--

Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado
Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy

Learn more at http://www.idug.org < http://www.idug.org/ >


+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Rasundavagen 94 | Country Code (Sweden): 46 |
| S-169 57 SOLNA | Skype: BacklundDB2 |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

________________________________


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA < http://idug.org/lsNA >

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at IDUG.ORG <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information <http://www.idug.org/lsconf> , and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark Vickers

Re: SQL Existance Checking
(in response to Phil Grainger)
or SELECT "ROW DOES EXIST" FROM ...




"Grainger, Phil" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
12/03/2008 10:04 AM
Please respond to
DB2 Database Discussion list at IDUG <[login to unmask email]>


To
[login to unmask email]
cc

Subject
Re: [DB2-L] SQL Existance Checking






In the interests of readability then, shouldn't we advocate

SELECT "DOES ROW EXIST?" FROM ..........

Phil G
CA

________________________________

From: DB2 Data Base Discussion List on behalf of Peter Backlund
Sent: Wed 03/12/2008 12:29
To: [login to unmask email]
Subject: Re: [DB2-L] SQL Existance Checking


Just for fun, in my education I used to tell them to use "select 1 ..." in
exists clauses.
One of my students asked "Doesn't that return the first column?" :-(

Since then I am always using "Select 0 ..."

Best regards,

Peter

Peter Vanroose wrote:

"SELECT *" in the subquery could (and should?) indeed be
replaced by "SELECT
1", for readability purposes: that would make it clearer
for the reader that
not data columns need to be obtained.

As to the two suggested alternatives (using
SYSIBM.SYSDUMMY1 and using FETCH
FIRST 1 ROW ONLY) for the outer query: I see one good
reason why your vendor
did not use one of these "standard DB2" solutions: thy
are both
DB2-specific, while his solution is portable (to other
SQL platforms).

-- Peter Vanroose
ABIS Training & Consulting.

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 *
http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership
in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms

________________________________



No virus found in this incoming message.
Checked by AVG - http://www.avg.com < http://www.avg.com/ >

Version: 8.0.176 / Virus Database: 270.9.13/1825 -
Release Date: 2008-12-02 20:44




--

Attend IDUG 2009, North America -- 11-15 May in Denver, Colorado
Attend IDUG 2009, Europe -- 5- 9 October in Rome, Italy

Learn more at http://www.idug.org < http://www.idug.org/ >


+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Rasundavagen 94 | Country Code (Sweden): 46 |
| S-169 57 SOLNA | Skype: BacklundDB2 |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+

________________________________


IDUG 2009 - North America * May 11-15, 2009 * Denver, CO, USA <
http://idug.org/lsNA>

The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L
list archives, FAQ, and delivery preferences are at IDUG.ORG <
http://www.idug.org/lsidug> under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information <
http://www.idug.org/lsconf> , and much more. If you have not yet signed up
for Basic Membership in IDUG, available at no cost, click on Member
Services < http://www.idug.org/lsms >


______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The
DB2-L list archives, FAQ, and delivery preferences are at
http://www.idug.org/lsidug under the Listserv tab. While at the site, you
can also access the IDUG Online Learning Center, Tech Library and Code
Place, see the latest IDUG conference information and much more. If you
have not yet signed up for Basic Membership in IDUG, available at no cost,
click on Member Services at http://www.idug.org/lsms





This e-mail (and any attachments) may contain information that is
confidential and/or protected by law. Any review, use, distribution or
disclosure to anyone other than the
intended recipient(s) is strictly prohibited. If you are not the intended
recipient, please contact the sender by reply email and delete all copies
of this message.



______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Chapman

FW: SQL Existance Checking
(in response to Mark Vickers)

_______________________________________________________________________________________

Note: This e-mail is subject to the disclaimer contained at the bottom of this message.
_______________________________________________________________________________________


Hi Dee,

The EXISTS clause returns a Boolean TRUE / FALSE answer and not the entire result set. I usually use "SELECT 1" as the SELECT clause.

Normally DB2 will also stop when it finds the first row that satisfies the criteria. However in this case, there is no join in the subselect between table B and A or C. So I'm not sure what will happen.

You can change the SQL to the following and it will work just as well:
FROM VXX009EXS_CHK A
WHERE EXISTS (
SELECT 1
FROM VMR257VID_FACS B
WHERE B.SRVC_FACS_ID = :H
AND B.SRVC_FACS_TYP_CD = 'S'
AND B.PORT_FACS_ID = :H
AND B.VID = :H
AND B.VCI = :H
AND (B.AID_VPI <> :H
OR B.AID_VCI <> :H))
WITH UR;

David.





_______________________________________________________________________________________

The information transmitted in this message and its attachments (if any) is intended
only for the person or entity to which it is addressed.
The message may contain confidential and/or privileged material. Any review,
retransmission, dissemination or other use of, or taking of any action in reliance
upon this information, by persons or entities other than the intended recipient is
prohibited.

If you have received this in error, please contact the sender and delete this e-mail
and associated material from any computer.

The intended recipient of this e-mail may only use, reproduce, disclose or distribute
the information contained in this e-mail and any attached files, with the permission
of the sender.

This message has been scanned for viruses with Symantec Scan Engine and cleared by
MailMarshal.
_______________________________________________________________________________________

______________________________________________________________________

* IDUG 2009 Denver, CO, USA * May 11-15, 2009 * http://IDUG.ORG/lsNA *
______________________________________________________________________



The IDUG DB2-L Listserv is only part of your membership in IDUG. The DB2-L list archives, FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms