General SQL question - which SELECT is better?

John McKown

General SQL question - which SELECT is better?
This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.



Jeff L'Italien

Re: General SQL question - which SELECT is better?
(in response to John McKown)
John,

As far as your first two select statements, assuming that col1 is the first key
of one of the indexes on the structure, the access paths should be the same.
You should get either a one column index match with an access type of 'N' or a
multi-index access against the same index. If I were to choose against
hardcoding a value in your select statement versus populating a table with
those values, I would pick the latter due to the fact that one of the hardcoded
values could force a tablespace scan because of distribution values found in
SYSIBM.SYSCOLDIST. This may or may not be too bad, but at least with the
temporary table, the access path should be consistent throughout all
executions.

Regards,
Jeff L'Italien
American Express



From: "McKown, John" <[login to unmask email]>@RYCI.COM> on 11/29/2001 11:26 AM
CST

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: General SQL question - which SELECT is better?


This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.








Tony Moore

Re: General SQL question - which SELECT is better?
(in response to Jeff L'Italien)
John,
My opinion would be your third option, using a subselect. The one and only
reason for that opinion... If the number of values you are comparing can be
"literally any number", you have risk a chance of exceeding the max size of
an SQL statement if your REXX is "hardcoding" the values in the SQL
statement if there is a large number of values.

HTH,
Tony

-----Original Message-----
From: McKown, John [mailto:[login to unmask email]
Sent: Thursday, November 29, 2001 12:26 PM
To: [login to unmask email]
Subject: General SQL question - which SELECT is
better?

This is not specifically DB2 oriented, but a general
question about SQL. I'm
going to write a program which will create another program.
The second
program will contain a SELECT. I can think of two different
ways to code the
SELECT which should give the same results. I wonder which
might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN)
file to get the
values of COL1. My example showed three values, but there
could be literally
any number. This is actually a REXX routine which is
creating another REXX
program based on some input. Or would it be better to have
the first REXX
program create a table containing the values then do
something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first
program and
populated by it from the input file.

Thanks for your thoughts,


----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my
employer.

Unsolicited telephone calls from vendors are NOT appreciated
and tend to
upset my management.

Divine intervention---What happens when God steps in and
does something for
the good of mankind...like ending Ronald Reagan's movie
career.


To change your subscription options or to cancel your
subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can



Dave Nance

Re: General SQL question - which SELECT is better?
(in response to Tony Moore)
Depending on if COL1 is a part of one of the indexes the IN predicates would allow you to have another matching column. If there are too many values, the temp table could hurt you as you would have to read the entire contents of the temp table each time. If you're thinking the temp table may be the way to go, you could try something like
SELECT *
FROM DATABASE A
WHERE EXISTS (SELECT 1 FROM TEMP1 B
WHERE A.COL1 = B.COL1);


>>> [login to unmask email] 11/29/01 12:26PM >>>
This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.




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 communications 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.



John McKown

Re: General SQL question - which SELECT is better?
(in response to Dave Nance)
David,
Thanks. Now I four possibilities. I may just try all four and see what seems
to work best for me. I may need the temp table, because I had not thought
that I might exceed the size allowed for the SELECT if there are too many
values. I don't really expect there to be over 10 or so, but you never know.

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.


> -----Original Message-----
> From: David Nance [SMTP:[login to unmask email]
> Sent: Thursday, November 29, 2001 12:44 PM
> To: [login to unmask email]
> Subject: Re: General SQL question - which SELECT is better?
>
> Depending on if COL1 is a part of one of the indexes the IN predicates
> would allow you to have another matching column. If there are too many
> values, the temp table could hurt you as you would have to read the entire
> contents of the temp table each time. If you're thinking the temp table
> may be the way to go, you could try something like
> SELECT *
> FROM DATABASE A
> WHERE EXISTS (SELECT 1 FROM TEMP1 B
> WHERE A.COL1 = B.COL1);
>
>
> >>> [login to unmask email] 11/29/01 12:26PM >>>
> This is not specifically DB2 oriented, but a general question about SQL.
> I'm
> going to write a program which will create another program. The second
> program will contain a SELECT. I can think of two different ways to code
> the
> SELECT which should give the same results. I wonder which might be better:
>
> SELECT *
> FROM DATABASE
> WHERE COL1 IN ("VAR1"
> ,"VAR2"
> ,"VAR3"
> );
>
> or
>
> SELECT *
> FROM DATABASE
> WHERE COL1 = "VAR1"
> OR COL1 = "VAR2"
> OR COL1 = "VAR3"
> ;
>
> Note that what I'm doing is reading a sequential (SYSIN) file to get the
> values of COL1. My example showed three values, but there could be
> literally
> any number. This is actually a REXX routine which is creating another REXX
> program based on some input. Or would it be better to have the first REXX
> program create a table containing the values then do something like:
>
> SELECT *
> FROM DATABASE
> WHERE COL1 IN (SELECT COL1 FROM TEMP1);
>
> Where TEMP1 is a "temporary" table created by the first program and
> populated by it from the input file.
>
> Thanks for your thoughts,
>
> ----------------------------------------------------------------------
> John McKown
> HealthAxis
>
> All opinions are my own and are not the opinions of my employer.
>
> Unsolicited telephone calls from vendors are NOT appreciated and tend to
> upset my management.
>
> Divine intervention---What happens when God steps in and does something
> for
> the good of mankind...like ending Ronald Reagan's movie career.
>
>
>
>
>
>
> 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
> communications 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.
>
>
>
> http://www.ryci.com/db2-l. The owners of the list can be reached at
> [login to unmask email]



Jobi M B225 Augustine

SQL Question
(in response to John McKown)
Hi List,

Is there any better way to find out the X_CD which has the largest
number of entries in the table TBL.A. We are running with DB2 OS/390 V6.

SELECT *
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL1
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03'AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE1
WHERE COUNTCOL1 =
(SELECT MAX(COUNTCOL2)
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL2
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03' AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE )

Thanks in advance !!

Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-1797 (6-1797)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++



Dave Nance

Re: SQL Question
(in response to Jobi M B225 Augustine)
Jobi,
This ran faster on one of my tables than the method you had used.
SELECT I_BENEFIT_PKG
FROM DB2U85.RS_BNFT_PREASSGN
GROUP BY I_BENEFIT_PKG
HAVING COUNT(*) = (SELECT MAX(X.CNT)
FROM TABLE(SELECT I_BENEFIT_PKG, COUNT(*) AS CNT
FROM DB2U85.RS_BNFT_PREASSGN
GROUP BY I_BENEFIT_PKG) AS X )


>>> [login to unmask email] 11/29/01 03:57PM >>>
Hi List,

Is there any better way to find out the X_CD which has the largest
number of entries in the table TBL.A. We are running with DB2 OS/390 V6.

SELECT *
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL1
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03'AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE1
WHERE COUNTCOL1 =
(SELECT MAX(COUNTCOL2)
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL2
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03' AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE )

Thanks in advance !!

Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-1797 (6-1797)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




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 communications 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.



Terry Purcell

Re: General SQL question - which SELECT is better?
(in response to Dave Nance)
John,

I would have to say I prefer 1 or 2. Actually, OS/390 DB2 will rewrite 2 to
be 1....and non-OS/390 will convert both alternatives and cost both (index
screening or multi-index access). It does not have the single index
"multiple probes" of OS/390 DB2.

(OS/390 answer) By introducing an additional table, you are simply adding
materialization overhead with a non-correlated subquery. If the predicate on
the original table is index matching, then you now also have the overhead of
a table join...if not index matching, then you have the overhead of this IN
predicate being stage 2.

(non-OS/390) The non-correlated subquery will transform to a join with a
DISTINCT clause.....

For 1 or 2, this is simply an indexable or stage 1 predicate (if not
indexed)....with no additional table processing.

The only reason I would go for 3 is if query 1 will exceed the 32K limit of
a SQL statement on OS/390.

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
McKown, John
Sent: Thursday, November 29, 2001 11:26 AM
To: [login to unmask email]
Subject: General SQL question - which SELECT is better?


This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis



Jeff L'Italien

Re: SQL Question
(in response to Terry Purcell)
Jobi,

This can be rewritten as so:

SELECT SUBSTR(CATCOLS,11,{LENGTH OF X_CD}), DECIMAL(SUBSTR(CATCOLS,1,10),10,0)
FROM
(SELECT MAX(COUNTCOL2 || X_CD) AS CATCOLS
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL2
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03' AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE ) AS TEMPTAB

This assumes that X_CD is defined as a CHAR field.

Regards,
Jeff L'Italien
American Express



From: "Augustine, Jobi M B225" <[login to unmask email]>@RYCI.COM> on
11/29/2001 03:57 PM EST

Please respond to "DB2 Data Base Discussion List" <[login to unmask email]>

Sent by: "DB2 Data Base Discussion List" <[login to unmask email]>


To: [login to unmask email]
cc:
Subject: SQL Question


Hi List,

Is there any better way to find out the X_CD which has the largest
number of entries in the table TBL.A. We are running with DB2 OS/390 V6.

SELECT *
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL1
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03'AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE1
WHERE COUNTCOL1 =
(SELECT MAX(COUNTCOL2)
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL2
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03' AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE )

Thanks in advance !!

Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-1797 (6-1797)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
immediately notify the sender by e-mail at the address shown. This e-mail
transmission may contain confidential information. This information is
intended only for the use of the individual(s) or entity to whom it is intended
even if addressed incorrectly. Please delete it from your files if you are not
the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++


visit the







Terry Purcell

Re: SQL Question
(in response to Jeff L'Italien)
Jobi,

If you only want a 1 row result (and assuming X_CD is CHAR(3)), then:

SELECT SUBSTR(MAX_COUNT,11,3) AS X_CD, INTEGER(SUBSTR(MAX_COUNT,1,10)) AS
COUNTCOL1
FROM(
SELECT MAX(DIGITS(COUNTCOL1) CONCAT X_CD) AS MAX_COUNT
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL1
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03'AND C_DIV
= '001'
GROUP BY X_CD) AS A) AS B

Otherwise, query rewrite should be tidying up your SQL to remove any
unwanted materialization.

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
Augustine, Jobi M B225
Sent: Thursday, November 29, 2001 2:57 PM
To: [login to unmask email]
Subject: SQL Question


Hi List,

Is there any better way to find out the X_CD which has the largest
number of entries in the table TBL.A. We are running with DB2 OS/390 V6.

SELECT *
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL1
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03'AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE1
WHERE COUNTCOL1 =
(SELECT MAX(COUNTCOL2)
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL2
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03' AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE )

Thanks in advance !!

Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-1797 (6-1797)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


----------------------------------------------------------------------------
--
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please
immediately notify the sender by e-mail at the address shown. This e-mail
transmission may contain confidential information. This information is
intended only for the use of the individual(s) or entity to whom it is
intended even if addressed incorrectly. Please delete it from your files if
you are not the intended recipient. Thank you for your compliance.©
Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++








Murari Selvakesavan

Re: SQL Question
(in response to Terry Purcell)
Hi Jobi,

How about restructuring the sql as follows, this gives the Database name that has maximum number of tables defined on it.

SELECT DBNAME, COUNT(*)
FROM SYSIBM.SYSTABLES A
WHERE CREATOR = ?
GROUP BY DBNAME
HAVING COUNT(*) =
(SELECT MAX(COUNT) FROM
(SELECT DBNAME, COUNT(*) COUNT
FROM SYSIBM.SYSTABLES
WHERE CREATOR = ?
GROUP BY DBNAME
) B
)

Murari Selvakesavan.
First Health Services Corp.

>>> [login to unmask email] 11/29/01 03:57PM >>>
Hi List,

Is there any better way to find out the X_CD which has the largest
number of entries in the table TBL.A. We are running with DB2 OS/390 V6.

SELECT *
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL1
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03'AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE1
WHERE COUNTCOL1 =
(SELECT MAX(COUNTCOL2)
FROM
(SELECT X_CD, COUNT(*) AS COUNTCOL2
FROM TBL.A WHERE A_NUM = '0222254' AND P_CD = '03' AND C_DIV = '001'

GROUP BY X_CD ) AS X_TABLE )

Thanks in advance !!

Thanks and best regards,

Jobi Augustine
CIGNA Systems - ES IM &T
* Tel (860) 226-5466 (6-5466)
* Fax (860) 226-1797 (6-1797)
* Mail [login to unmask email] <mailto:[login to unmask email]>

Confidential, unpublished property of CIGNA. Do not duplicate or distribute.
Use and distribution limited solely to authorized personnel. Copyright2001
CIGNA.


------------------------------------------------------------------------------
CONFIDENTIALITY NOTICE: If you have received this e-mail in error, please immediately notify the sender by e-mail at the address shown. This e-mail transmission may contain confidential information. This information is intended only for the use of the individual(s) or entity to whom it is intended even if addressed incorrectly. Please delete it from your files if you are not the intended recipient. Thank you for your compliance.© Copyright 2001 CIGNA

++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++




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 communications 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.

david owen

Re: General SQL question - which SELECT is better?
(in response to Murari Selvakesavan)
John,
your first 2 queries will be the same in DB2.

The Query
SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

will be transformed by DB2 into the following query

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

If an Index exists on COL1 and providing that you do not retrieve too much
data from the Table based on these values in the IN list it will be
efficient.
(The percentage of rows varies, but I usually find that < 20 - 25% will
qualify for an index).

The use of a small code Table (temporarily created or not) can provide the
list of values that you require. This is fine when there are a small no of
values.

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1)

The use of this IN non-correlated subquery is now indexable in V6 (or in V5
with APAR PQ23243).(correlated subquery is indexable in V7)

However using an EXISTS subquery is Stage 2 and non-indexable.



Alternatively using a JOIN to the temporary Table could also be used. With
the Temporary Table as the outer Table, the JOIN will provide a good
indexable access for your main Table. The Temporary Table if a Declared
Table can also support an index on COL1

SELECT *
FROM DATABASE D,TEMP1 T
WHERE D.COL1 =T.COL1



The best way is to check the alternatives and see what performs the best.

regards
David Owen
www.do-db2.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
McKown, John
Sent: 29 November 2001 17:26
To: [login to unmask email]
Subject: General SQL question - which SELECT is better?


This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.








Terry Purcell

Re: General SQL question - which SELECT is better?
(in response to david owen)
David,

Saying that a "correlated subquery is indexable in V7" is a bit of a
misnomer. The correlation predicates have always been indexable. What is new
in V7 is that the correlated subquery may be transformed to a join in more
situations (it did transform in earlier releases but only under limited
cases). This includes correlated EXISTS subqueries. Note: these will now
perform worse if the subquery table is larger and contains many duplicates.

By the way (and I would appreciate your feedback on this), I still don't see
how introducing a temporary table in this case will improve performance. The
original IN list is indexable, and we have a single table to access. By
introducing another table, we haven't improved the access to the first
table, just introduced more rows to read.

The only situation is if a single table chose a tablespace scan, whereas the
temp table forced index match, and that was a better solution. Everything
else would provide worse performance.

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
david owen
Sent: Friday, November 30, 2001 1:19 AM
To: [login to unmask email]
Subject: Re: General SQL question - which SELECT is better?


John,
your first 2 queries will be the same in DB2.

The Query
SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

will be transformed by DB2 into the following query

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

If an Index exists on COL1 and providing that you do not retrieve too much
data from the Table based on these values in the IN list it will be
efficient.
(The percentage of rows varies, but I usually find that < 20 - 25% will
qualify for an index).

The use of a small code Table (temporarily created or not) can provide the
list of values that you require. This is fine when there are a small no of
values.

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1)

The use of this IN non-correlated subquery is now indexable in V6 (or in V5
with APAR PQ23243).(correlated subquery is indexable in V7)

However using an EXISTS subquery is Stage 2 and non-indexable.



Alternatively using a JOIN to the temporary Table could also be used. With
the Temporary Table as the outer Table, the JOIN will provide a good
indexable access for your main Table. The Temporary Table if a Declared
Table can also support an index on COL1

SELECT *
FROM DATABASE D,TEMP1 T
WHERE D.COL1 =T.COL1



The best way is to check the alternatives and see what performs the best.

regards
David Owen
www.do-db2.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
McKown, John
Sent: 29 November 2001 17:26
To: [login to unmask email]
Subject: General SQL question - which SELECT is better?


This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.













david owen

Re: General SQL question - which SELECT is better?
(in response to Terry Purcell)
Terry,
my apologies for any confusion caused.

1 - I should have given more info on correlated subquery changes and V7.

2 - The use of an IN list accessing a Table through an Index is better than
using another Table when the values are known.
However, when the values are not known and the column is the first column in
the index, then using a small Table that contains those values and either
joining it or using a subselect can offer better performance than scanning.

EG Index on COL1, COL2 of DATABASE Table

SELECT * FROM DATABASE D,TEMP T
WHERE D.COL1 = T.COL1
AND D.COL2 = :HV2

would be better than

SELECT * FROM DATABASE D
WHERE D.COL2 = :HV2


If the IN list also qualifies too many rows then the Index will not be
chosen in either of the cases.


regards

David Owen

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Terry Purcell
Sent: 30 November 2001 13:08
To: [login to unmask email]
Subject: Re: General SQL question - which SELECT is better?


David,

Saying that a "correlated subquery is indexable in V7" is a bit of a
misnomer. The correlation predicates have always been indexable. What is new
in V7 is that the correlated subquery may be transformed to a join in more
situations (it did transform in earlier releases but only under limited
cases). This includes correlated EXISTS subqueries. Note: these will now
perform worse if the subquery table is larger and contains many duplicates.

By the way (and I would appreciate your feedback on this), I still don't see
how introducing a temporary table in this case will improve performance. The
original IN list is indexable, and we have a single table to access. By
introducing another table, we haven't improved the access to the first
table, just introduced more rows to read.

The only situation is if a single table chose a tablespace scan, whereas the
temp table forced index match, and that was a better solution. Everything
else would provide worse performance.

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
david owen
Sent: Friday, November 30, 2001 1:19 AM
To: [login to unmask email]
Subject: Re: General SQL question - which SELECT is better?


John,
your first 2 queries will be the same in DB2.

The Query
SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

will be transformed by DB2 into the following query

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

If an Index exists on COL1 and providing that you do not retrieve too much
data from the Table based on these values in the IN list it will be
efficient.
(The percentage of rows varies, but I usually find that < 20 - 25% will
qualify for an index).

The use of a small code Table (temporarily created or not) can provide the
list of values that you require. This is fine when there are a small no of
values.

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1)

The use of this IN non-correlated subquery is now indexable in V6 (or in V5
with APAR PQ23243).(correlated subquery is indexable in V7)

However using an EXISTS subquery is Stage 2 and non-indexable.



Alternatively using a JOIN to the temporary Table could also be used. With
the Temporary Table as the outer Table, the JOIN will provide a good
indexable access for your main Table. The Temporary Table if a Declared
Table can also support an index on COL1

SELECT *
FROM DATABASE D,TEMP1 T
WHERE D.COL1 =T.COL1



The best way is to check the alternatives and see what performs the best.

regards
David Owen
www.do-db2.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
McKown, John
Sent: 29 November 2001 17:26
To: [login to unmask email]
Subject: General SQL question - which SELECT is better?


This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);

or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;

Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis

All opinions are my own and are not the opinions of my employer.

Unsolicited telephone calls from vendors are NOT appreciated and tend to
upset my management.

Divine intervention---What happens when God steps in and does something for
the good of mankind...like ending Ronald Reagan's movie career.


















Terry Purcell

FW: General SQL question - which SELECT is better?
(in response to david owen)
David,

Thanks for the clarification, as it appears that we are both going in the
same direction, just some of the details are a little different.

One change though to your statement "If the IN list also qualifies too many
rows then the Index will not be
chosen in either of the cases". For the join scenario, if the index is not
chosen then you have a scan on the inner table of a nested loop join for
every outer row....So the index should always be chosen for the join, unless
you have merge scan where it's not as important".

I still believe that one table access will outperform two....so introducing
the extra table is merely an overhead. And if a single table chooses a scan,
whereas the join or non-correlated IN subquery uses the index (which proves
better), then that is a problem with the statistics, as the optimizer has
chosen the incorrect access path.

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
david owen
Sent: Sunday, December 02, 2001 3:28 PM
To: [login to unmask email]
Subject: Re: General SQL question - which SELECT is better?

Terry,
my apologies for any confusion caused.

1 - I should have given more info on correlated subquery changes and V7.

2 - The use of an IN list accessing a Table through an Index is better than
using another Table when the values are known.
However, when the values are not known and the column is the first column in
the index, then using a small Table that contains those values and either
joining it or using a subselect can offer better performance than scanning.

EG Index on COL1, COL2 of DATABASE Table

SELECT * FROM DATABASE D,TEMP T
WHERE D.COL1 = T.COL1
AND D.COL2 = :HV2

would be better than

SELECT * FROM DATABASE D
WHERE D.COL2 = :HV2

If the IN list also qualifies too many rows then the Index will not be
chosen in either of the cases.

regards
David Owen

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Terry Purcell
Sent: 30 November 2001 13:08
To: [login to unmask email]
Subject: Re: General SQL question - which SELECT is better?

David,

Saying that a "correlated subquery is indexable in V7" is a bit of a
misnomer. The correlation predicates have always been indexable. What is new
in V7 is that the correlated subquery may be transformed to a join in more
situations (it did transform in earlier releases but only under limited
cases). This includes correlated EXISTS subqueries. Note: these will now
perform worse if the subquery table is larger and contains many duplicates.

By the way (and I would appreciate your feedback on this), I still don't see
how introducing a temporary table in this case will improve performance. The
original IN list is indexable, and we have a single table to access. By
introducing another table, we haven't improved the access to the first
table, just introduced more rows to read.

The only situation is if a single table chose a tablespace scan, whereas the
temp table forced index match, and that was a better solution. Everything
else would provide worse performance.

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
david owen
Sent: Friday, November 30, 2001 1:19 AM
To: [login to unmask email]
Subject: Re: General SQL question - which SELECT is better?

John,
your first 2 queries will be the same in DB2.

The Query
SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;
will be transformed by DB2 into the following query

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);
If an Index exists on COL1 and providing that you do not retrieve too much
data from the Table based on these values in the IN list it will be
efficient.
(The percentage of rows varies, but I usually find that < 20 - 25% will
qualify for an index).

The use of a small code Table (temporarily created or not) can provide the
list of values that you require. This is fine when there are a small no of
values.

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1)

The use of this IN non-correlated subquery is now indexable in V6 (or in V5
with APAR PQ23243).(correlated subquery is indexable in V7)

However using an EXISTS subquery is Stage 2 and non-indexable.

Alternatively using a JOIN to the temporary Table could also be used. With
the Temporary Table as the outer Table, the JOIN will provide a good
indexable access for your main Table. The Temporary Table if a Declared
Table can also support an index on COL1

SELECT *
FROM DATABASE D,TEMP1 T
WHERE D.COL1 =T.COL1

The best way is to check the alternatives and see what performs the best.

regards
David Owen
www.do-db2.com

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
McKown, John
Sent: 29 November 2001 17:26
To: [login to unmask email]
Subject: General SQL question - which SELECT is better?

This is not specifically DB2 oriented, but a general question about SQL. I'm
going to write a program which will create another program. The second
program will contain a SELECT. I can think of two different ways to code the
SELECT which should give the same results. I wonder which might be better:

SELECT *
FROM DATABASE
WHERE COL1 IN ("VAR1"
,"VAR2"
,"VAR3"
);
or

SELECT *
FROM DATABASE
WHERE COL1 = "VAR1"
OR COL1 = "VAR2"
OR COL1 = "VAR3"
;
Note that what I'm doing is reading a sequential (SYSIN) file to get the
values of COL1. My example showed three values, but there could be literally
any number. This is actually a REXX routine which is creating another REXX
program based on some input. Or would it be better to have the first REXX
program create a table containing the values then do something like:

SELECT *
FROM DATABASE
WHERE COL1 IN (SELECT COL1 FROM TEMP1);

Where TEMP1 is a "temporary" table created by the first program and
populated by it from the input file.

Thanks for your thoughts,

----------------------------------------------------------------------
John McKown
HealthAxis



RIBEIRO Paulo Jorge

SQL question
(in response to Terry Purcell)
Hello All,

I'm a DB2 newbie and I have some query problems. I created the sample
database. The database tables are included in DB2admin schema. If I have
other user (for example db2client) and I try to make a select query from
employee table I get the error " DB2CLIENT.EMPLOYEE is an undefined name".
If instead of employee I make the query to db2admin.employee I get no
errors. Anyone can tell me if is mandatory to include the schema name before
the table name? What i'm doing wrong?

Thanks

Paulo



Bill Gallagher

Re: SQL question
(in response to RIBEIRO Paulo Jorge)
Paulo,

If you do not include the schema name before the table name, DB2 will
implicitly use the id of the user issuing the query as the schema name. If
the table you want to access is defined under a different schema from your
user id, then you must fully qualify the table name with the schema name.

As an alternative, you can create an alias with your user id to point to
the other table. Then you could issue unqualified SQL that would resolve
to the correct name.

Example: "create alias db2client.employee for db2admin.employee"

Hope this helps.

-------------------------------------------------------------------------------------------------------


Bill Gallagher, DBA
Phoenix Life Insurance
Enfield, CT 06083

IBM Certified Solutions Expert - DB2 UDB v7.1 Database Administration for
OS/390
IBM Certified Solutions Expert - DB2 UDB v7.1 Database Administration for
UNIX, Windows, and OS/2




"RIBEIRO
Paulo Jorge" To: [login to unmask email]
<[login to unmask email] cc:
BLER.PT> Subject: SQL question
Sent by: "DB2
Data Base
Discussion
List"
<[login to unmask email]
OM>


12/14/01
07:28 AM
Please
respond to
"DB2 Data
Base
Discussion
List"






Hello All,

I'm a DB2 newbie and I have some query problems. I created the sample
database. The database tables are included in DB2admin schema. If I have
other user (for example db2client) and I try to make a select query from
employee table I get the error " DB2CLIENT.EMPLOYEE is an undefined name".
If instead of employee I make the query to db2admin.employee I get no
errors. Anyone can tell me if is mandatory to include the schema name
before
the table name? What i'm doing wrong?

Thanks

Paulo








Judy Woodfield

Re: SQL question
(in response to Bill Gallagher)
Welcom to DB2.

If not specified, the default schema name is the user. It is always a good
practice to include the schema name when accessing the table.

-----Original Message-----
From: RIBEIRO Paulo Jorge [mailto:[login to unmask email]
Sent: Friday, December 14, 2001 7:28 AM
To: [login to unmask email]
Subject: SQL question


Hello All,

I'm a DB2 newbie and I have some query problems. I created the sample
database. The database tables are included in DB2admin schema. If I have
other user (for example db2client) and I try to make a select query from
employee table I get the error " DB2CLIENT.EMPLOYEE is an undefined name".
If instead of employee I make the query to db2admin.employee I get no
errors. Anyone can tell me if is mandatory to include the schema name before
the table name? What i'm doing wrong?

Thanks

Paulo








Iqbal Goralwalla

Re: SQL question
(in response to Judy Woodfield)
Another suggestion would be to set the current schema as your user id. For
example: set current schema db2admin
Now you can happily: select * from employee


-----Original Message-----
From: Bill Gallagher [mailto:[login to unmask email]
Sent: 14 December 2001 13:18
To: [login to unmask email]
Subject: Re: SQL question


Paulo,

If you do not include the schema name before the table name, DB2 will
implicitly use the id of the user issuing the query as the schema name. If
the table you want to access is defined under a different schema from your
user id, then you must fully qualify the table name with the schema name.

As an alternative, you can create an alias with your user id to point to
the other table. Then you could issue unqualified SQL that would resolve
to the correct name.

Example: "create alias db2client.employee for db2admin.employee"

Hope this helps.

----------------------------------------------------------------------------
---------------------------


Bill Gallagher, DBA
Phoenix Life Insurance
Enfield, CT 06083

IBM Certified Solutions Expert - DB2 UDB v7.1 Database Administration for
OS/390
IBM Certified Solutions Expert - DB2 UDB v7.1 Database Administration for
UNIX, Windows, and OS/2




"RIBEIRO
Paulo Jorge" To: [login to unmask email]
<[login to unmask email] cc:
BLER.PT> Subject: SQL question
Sent by: "DB2
Data Base
Discussion
List"
<[login to unmask email]
OM>


12/14/01
07:28 AM
Please
respond to
"DB2 Data
Base
Discussion
List"






Hello All,

I'm a DB2 newbie and I have some query problems. I created the sample
database. The database tables are included in DB2admin schema. If I have
other user (for example db2client) and I try to make a select query from
employee table I get the error " DB2CLIENT.EMPLOYEE is an undefined name".
If instead of employee I make the query to db2admin.employee I get no
errors. Anyone can tell me if is mandatory to include the schema name
before
the table name? What i'm doing wrong?

Thanks

Paulo













Tony Moore

SQL Question
(in response to Iqbal Goralwalla)
Yo Listers,
A DBA has asked one of me that I don't believe is possible, but thought I'd
pass it by y'all to see if you've ever come up with a way to do such. He
wants a query that will return a list of tablespaces that end in "01", "02",
"03" or "04"... easy enough so far... however, he wants all "01" tablespace
names returned in one row, all "02" tablespaces in the next row, and so
on.... And to put the icing on the cake, he'd like to have a string returned
at the end of the row that looks like 'etw' concatenated with the tablespace
suffix for that row.

For example, say the tablespace list looks like this....

TS0101
TS0201
TS0301
TS0401
TS0102
TS0202
TS0302
TS0103
TS0203

....he wants the query to return...
ROW1: TS0101, TS0201, TS0301, TS0401, etw01
ROW2: TS0102, TS0202, TS0302, etw03
ROW3: TS0103, TS0203, etw03

...any ideas?
Thanks,
Tony



Jeremy Schleicher

Re: SQL Question
(in response to Tony Moore)
Not without putting it in a program



From: "Moore, Tony" <[login to unmask email]>@RYCI.COM on 12/18/2001 12:27 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

Sent by: DB2 Data Base Discussion List <[login to unmask email]>


To: [login to unmask email]
cc:

Subject: SQL Question


Yo Listers,
A DBA has asked one of me that I don't believe is possible, but thought I'd
pass it by y'all to see if you've ever come up with a way to do such. He
wants a query that will return a list of tablespaces that end in "01",
"02",
"03" or "04"... easy enough so far... however, he wants all "01" tablespace
names returned in one row, all "02" tablespaces in the next row, and so
on.... And to put the icing on the cake, he'd like to have a string
returned
at the end of the row that looks like 'etw' concatenated with the
tablespace
suffix for that row.

For example, say the tablespace list looks like this....

TS0101
TS0201
TS0301
TS0401
TS0102
TS0202
TS0302
TS0103
TS0203

....he wants the query to return...
ROW1: TS0101, TS0201, TS0301, TS0401, etw01
ROW2: TS0102, TS0202, TS0302, etw03
ROW3: TS0103, TS0203, etw03

...any ideas?
Thanks,
Tony








James Kwan

Re: SQL Question
(in response to Jeremy Schleicher)
Tony,

I don't believe it can be done by just using simple SQL. It might be
possible to write very completed subqueries with union. But why bother? The
simpliest way I would do is generate the list like you have and may be using
EXCEL to make it the way your DBA wants. Or write a small REXX to read the
result and format it.

By the way what is the purpose of this format?

James Kwan
IBM Certified Specialist


In a message dated 12/18/01 12:57:02 PM Central Standard Time,
[login to unmask email] writes:


>
> Yo Listers,
> A DBA has asked one of me that I don't believe is possible, but thought I'd
> pass it by y'all to see if you've ever come up with a way to do such. He
> wants a query that will return a list of tablespaces that end in "01", "02",
> "03" or "04"... easy enough so far... however, he wants all "01" tablespace
> names returned in one row, all "02" tablespaces in the next row, and so
> on.... And to put the icing on the cake, he'd like to have a string returned
> at the end of the row that looks like 'etw' concatenated with the tablespace
> suffix for that row.
>
> For example, say the tablespace list looks like this....
>
> TS0101
> TS0201
> TS0301
> TS0401
> TS0102
> TS0202
> TS0302
> TS0103
> TS0203
>
> ....he wants the query to return...
> ROW1: TS0101, TS0201, TS0301, TS0401, etw01
> ROW2: TS0102, TS0202, TS0302, etw03
> ROW3: TS0103, TS0203, etw03
>
> ...any ideas?
> Thanks,
> Tony
>


Tony Moore

Re: SQL Question
(in response to Russell Collins)
Not sure of the purpose of this one. The request comes from one of our
ORACLE DBA's working in that mystical land of UNIX (This written by a die
hard DB2 / Mainframe Dinosaur).

-----Original Message-----
From: James Kwan [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 2:20 PM
To: [login to unmask email]
Subject: Re: SQL Question


Tony,

I don't believe it can be done by just using simple SQL. It might be
possible to write very completed subqueries with union. But why bother?
The simpliest way I would do is generate the list like you have and may be
using EXCEL to make it the way your DBA wants. Or write a small REXX to
read the result and format it.

By the way what is the purpose of this format?

James Kwan
IBM Certified Specialist


In a message dated 12/18/01 12:57:02 PM Central Standard Time,
[login to unmask email] writes:





Yo Listers,
A DBA has asked one of me that I don't believe is possible, but thought I'd
pass it by y'all to see if you've ever come up with a way to do such. He
wants a query that will return a list of tablespaces that end in "01", "02",

"03" or "04"... easy enough so far... however, he wants all "01" tablespace
names returned in one row, all "02" tablespaces in the next row, and so
on.... And to put the icing on the cake, he'd like to have a string returned

at the end of the row that looks like 'etw' concatenated with the tablespace

suffix for that row.

For example, say the tablespace list looks like this....

TS0101
TS0201
TS0301
TS0401
TS0102
TS0202
TS0302
TS0103
TS0203

....he wants the query to return...
ROW1: TS0101, TS0201, TS0301, TS0401, etw01
ROW2: TS0102, TS0202, TS0302, etw03
ROW3: TS0103, TS0203, etw03

...any ideas?
Thanks,
Tony



Russell Collins

Re: SQL Question
(in response to James Kwan)
You can probably accomplish this with a select statement using a "group by"
and "having" clause.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Moore, Tony
Sent: Tuesday, December 18, 2001 12:27 PM
To: [login to unmask email]
Subject: SQL Question


Yo Listers,
A DBA has asked one of me that I don't believe is possible, but thought I'd
pass it by y'all to see if you've ever come up with a way to do such. He
wants a query that will return a list of tablespaces that end in "01", "02",
"03" or "04"... easy enough so far... however, he wants all "01" tablespace
names returned in one row, all "02" tablespaces in the next row, and so
on.... And to put the icing on the cake, he'd like to have a string returned
at the end of the row that looks like 'etw' concatenated with the tablespace
suffix for that row.

For example, say the tablespace list looks like this....

TS0101
TS0201
TS0301
TS0401
TS0102
TS0202
TS0302
TS0103
TS0203

....he wants the query to return...
ROW1: TS0101, TS0201, TS0301, TS0401, etw01
ROW2: TS0102, TS0202, TS0302, etw03
ROW3: TS0103, TS0203, etw03

...any ideas?
Thanks,
Tony








Isaac Yassin

Re: SQL Question
(in response to Tony Moore)
Hi,
I remember that Terry posted some months ago an example that may help you.
I don't have it on hand but it should be in the archives.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Moore, Tony" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Tuesday, December 18, 2001 9:34 PM
Subject: Re: SQL Question


> Not sure of the purpose of this one. The request comes from one of our
> ORACLE DBA's working in that mystical land of UNIX (This written by a die
> hard DB2 / Mainframe Dinosaur).
>
> -----Original Message-----
> From: James Kwan [mailto:[login to unmask email]
> Sent: Tuesday, December 18, 2001 2:20 PM
> To: [login to unmask email]
> Subject: Re: SQL Question
>
>
> Tony,
>
> I don't believe it can be done by just using simple SQL. It might be
> possible to write very completed subqueries with union. But why bother?
> The simpliest way I would do is generate the list like you have and may be
> using EXCEL to make it the way your DBA wants. Or write a small REXX to
> read the result and format it.
>
> By the way what is the purpose of this format?
>
> James Kwan
> IBM Certified Specialist
>
>
> In a message dated 12/18/01 12:57:02 PM Central Standard Time,
> [login to unmask email] writes:
>
>
>
>
>
> Yo Listers,
> A DBA has asked one of me that I don't believe is possible, but thought I'd
> pass it by y'all to see if you've ever come up with a way to do such. He
> wants a query that will return a list of tablespaces that end in "01", "02",
>
> "03" or "04"... easy enough so far... however, he wants all "01" tablespace
> names returned in one row, all "02" tablespaces in the next row, and so
> on.... And to put the icing on the cake, he'd like to have a string returned
>
> at the end of the row that looks like 'etw' concatenated with the tablespace
>
> suffix for that row.
>
> For example, say the tablespace list looks like this....
>
> TS0101
> TS0201
> TS0301
> TS0401
> TS0102
> TS0202
> TS0302
> TS0103
> TS0203
>
> ....he wants the query to return...
> ROW1: TS0101, TS0201, TS0301, TS0401, etw01
> ROW2: TS0102, TS0202, TS0302, etw03
> ROW3: TS0103, TS0203, etw03
>
> ...any ideas?
> Thanks,
> Tony
>
>
> DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can
>



Tony Moore

Re: SQL Question
(in response to Isaac Yassin)
Thanks!! I'll check it out.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 3:06 PM
To: [login to unmask email]
Subject: Re: SQL Question

Hi,
I remember that Terry posted some months ago an example that
may help you.
I don't have it on hand but it should be in the archives.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Moore, Tony" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Tuesday, December 18, 2001 9:34 PM
Subject: Re: SQL Question


> Not sure of the purpose of this one. The request comes
from one of our
> ORACLE DBA's working in that mystical land of UNIX (This
written by a die
> hard DB2 / Mainframe Dinosaur).
>
> -----Original Message-----
> From: James Kwan [mailto:[login to unmask email]
> Sent: Tuesday, December 18, 2001 2:20 PM
> To: [login to unmask email]
> Subject: Re: SQL Question
>
>
> Tony,
>
> I don't believe it can be done by just using simple SQL.
It might be
> possible to write very completed subqueries with union.
But why bother?
> The simpliest way I would do is generate the list like you
have and may be
> using EXCEL to make it the way your DBA wants. Or write a
small REXX to
> read the result and format it.
>
> By the way what is the purpose of this format?
>
> James Kwan
> IBM Certified Specialist
>
>
> In a message dated 12/18/01 12:57:02 PM Central Standard
Time,
> [login to unmask email] writes:
>
>
>
>
>
> Yo Listers,
> A DBA has asked one of me that I don't believe is
possible, but thought I'd
> pass it by y'all to see if you've ever come up with a way
to do such. He
> wants a query that will return a list of tablespaces that
end in "01", "02",
>
> "03" or "04"... easy enough so far... however, he wants
all "01" tablespace
> names returned in one row, all "02" tablespaces in the
next row, and so
> on.... And to put the icing on the cake, he'd like to have
a string returned
>
> at the end of the row that looks like 'etw' concatenated
with the tablespace
>
> suffix for that row.
>
> For example, say the tablespace list looks like this....
>
> TS0101
> TS0201
> TS0301
> TS0401
> TS0102
> TS0202
> TS0302
> TS0103
> TS0203
>
> ....he wants the query to return...
> ROW1: TS0101, TS0201, TS0301, TS0401, etw01
> ROW2: TS0102, TS0202, TS0302, etw03
> ROW3: TS0103, TS0203, etw03
>
> ...any ideas?
> Thanks,
> Tony
>
>
> To change your subscription options or to cancel your
subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can
>


To change your subscription options or to cancel your
subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can



Steve Tennant

Re: SQL Question
(in response to Tony Moore)
This SQL was so good I just had to keep a copy....

The answer Terry gave was

Note: You must know the maximum number of columns you wish to output.

SELECT C.COLA, C.COLB
,MAX(CASE C.SEQ_NO WHEN 1 THEN C.COLC ELSE NULL END) AS COL1
,MAX(CASE C.SEQ_NO WHEN 2 THEN C.COLC ELSE NULL END) AS COL2
,MAX(CASE C.SEQ_NO WHEN 3 THEN C.COLC ELSE NULL END) AS COL3
FROM (
SELECT A.COLA, A.COLB, A.COLC, COUNT(*) AS SEQ_NO
FROM TABLE A , TABLE B
WHERE B.COLA = A.COLA
AND B.COLB = A.COLB
AND B.COLC <= A.COLC
GROUP BY A.COLA, A.COLB, A.COLC) AS C
GROUP BY C.COLA, C.COLB
ORDER BY C.COLA, C.COLB


Which does something very similar to what you want. You will have to play
with SUBSTR's and nested selects but it looks close.

HTH

Steve T

PS the original subject of Terry's reply was "SQL Request (was blank)"

-----Original Message-----
From: Moore, Tony [mailto:[login to unmask email]
Sent: Wednesday, 19 December 2001 8:33 AM
To: [login to unmask email]
Subject: Re: SQL Question


Thanks!! I'll check it out.

-----Original Message-----
From: Isaac Yassin [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 3:06 PM
To: [login to unmask email]
Subject: Re: SQL Question

Hi,
I remember that Terry posted some months ago an example that
may help you.
I don't have it on hand but it should be in the archives.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Moore, Tony" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Tuesday, December 18, 2001 9:34 PM
Subject: Re: SQL Question


> Not sure of the purpose of this one. The request comes
from one of our
> ORACLE DBA's working in that mystical land of UNIX (This
written by a die
> hard DB2 / Mainframe Dinosaur).
>
> -----Original Message-----
> From: James Kwan [mailto:[login to unmask email]
> Sent: Tuesday, December 18, 2001 2:20 PM
> To: [login to unmask email]
> Subject: Re: SQL Question
>
>
> Tony,
>
> I don't believe it can be done by just using simple SQL.
It might be
> possible to write very completed subqueries with union.
But why bother?
> The simpliest way I would do is generate the list like you
have and may be
> using EXCEL to make it the way your DBA wants. Or write a
small REXX to
> read the result and format it.
>
> By the way what is the purpose of this format?
>
> James Kwan
> IBM Certified Specialist
>
>
> In a message dated 12/18/01 12:57:02 PM Central Standard
Time,
> [login to unmask email] writes:
>
>
>
>
>
> Yo Listers,
> A DBA has asked one of me that I don't believe is
possible, but thought I'd
> pass it by y'all to see if you've ever come up with a way
to do such. He
> wants a query that will return a list of tablespaces that
end in "01", "02",
>
> "03" or "04"... easy enough so far... however, he wants
all "01" tablespace
> names returned in one row, all "02" tablespaces in the
next row, and so
> on.... And to put the icing on the cake, he'd like to have
a string returned
>
> at the end of the row that looks like 'etw' concatenated
with the tablespace
>
> suffix for that row.
>
> For example, say the tablespace list looks like this....
>
> TS0101
> TS0201
> TS0301
> TS0401
> TS0102
> TS0202
> TS0302
> TS0103
> TS0203
>
> ....he wants the query to return...
> ROW1: TS0101, TS0201, TS0301, TS0401, etw01
> ROW2: TS0102, TS0202, TS0302, etw03
> ROW3: TS0103, TS0203, etw03
>
> ...any ideas?
> Thanks,
> Tony
>
>
> To change your subscription options or to cancel your
subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can
>


To change your subscription options or to cancel your
subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can








Terry Purcell

Re: SQL Question
(in response to Steve Tennant)
Tony,

This sort of thing is quite easy to do on non-OS/390 DB2 using recursive
SQL. On the mainframe it gets a little more challenging, but is possible
provided you have a defined number of tablespaces to list.

If it's an Oracle DBA, then I guess it is for an Oracle database. Does their
SQL support recursion?

Also, if the names are really TS0101, TS0201 etc, then it makes it even
simpler (because of the numerics in position 3&4). But I guess it's not this
simple.

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
Moore, Tony
Sent: Tuesday, December 18, 2001 1:35 PM
To: [login to unmask email]
Subject: Re: SQL Question


Not sure of the purpose of this one. The request comes from one of our
ORACLE DBA's working in that mystical land of UNIX (This written by a die
hard DB2 / Mainframe Dinosaur).

-----Original Message-----
From: James Kwan [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 2:20 PM
To: [login to unmask email]
Subject: Re: SQL Question


Tony,

I don't believe it can be done by just using simple SQL. It might be
possible to write very completed subqueries with union. But why bother?
The simpliest way I would do is generate the list like you have and may be
using EXCEL to make it the way your DBA wants. Or write a small REXX to
read the result and format it.

By the way what is the purpose of this format?

James Kwan
IBM Certified Specialist


In a message dated 12/18/01 12:57:02 PM Central Standard Time,
[login to unmask email] writes:





Yo Listers,
A DBA has asked one of me that I don't believe is possible, but thought I'd
pass it by y'all to see if you've ever come up with a way to do such. He
wants a query that will return a list of tablespaces that end in "01", "02",

"03" or "04"... easy enough so far... however, he wants all "01" tablespace
names returned in one row, all "02" tablespaces in the next row, and so
on.... And to put the icing on the cake, he'd like to have a string returned

at the end of the row that looks like 'etw' concatenated with the tablespace

suffix for that row.

For example, say the tablespace list looks like this....

TS0101
TS0201
TS0301
TS0401
TS0102
TS0202
TS0302
TS0103
TS0203

....he wants the query to return...
ROW1: TS0101, TS0201, TS0301, TS0401, etw01
ROW2: TS0102, TS0202, TS0302, etw03
ROW3: TS0103, TS0203, etw03

...any ideas?
Thanks,
Tony








Tony Moore

Re: SQL Question
(in response to Terry Purcell)
Steve,
Thanks a bunch, that's exactly what I needed!!

-----Original Message-----
From: Steve Tennant [mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 6:54 PM
To: [login to unmask email]
Subject: Re: SQL Question

This SQL was so good I just had to keep a copy....

The answer Terry gave was

Note: You must know the maximum number of columns you wish
to output.

SELECT C.COLA, C.COLB
,MAX(CASE C.SEQ_NO WHEN 1 THEN C.COLC ELSE NULL END) AS
COL1
,MAX(CASE C.SEQ_NO WHEN 2 THEN C.COLC ELSE NULL END) AS
COL2
,MAX(CASE C.SEQ_NO WHEN 3 THEN C.COLC ELSE NULL END) AS
COL3
FROM (
SELECT A.COLA, A.COLB, A.COLC, COUNT(*) AS SEQ_NO
FROM TABLE A , TABLE B
WHERE B.COLA = A.COLA
AND B.COLB = A.COLB
AND B.COLC <= A.COLC
GROUP BY A.COLA, A.COLB, A.COLC) AS C
GROUP BY C.COLA, C.COLB
ORDER BY C.COLA, C.COLB


Which does something very similar to what you want. You
will have to play
with SUBSTR's and nested selects but it looks close.

HTH

Steve T

PS the original subject of Terry's reply was "SQL Request
(was blank)"

-----Original Message-----
From: Moore, Tony [mailto:[login to unmask email]
Sent: Wednesday, 19 December 2001 8:33 AM
To: [login to unmask email]
Subject: Re: SQL Question


Thanks!! I'll check it out.

-----Original Message-----
From: Isaac Yassin
[mailto:[login to unmask email]
Sent: Tuesday, December 18, 2001 3:06 PM
To: [login to unmask email]
Subject: Re: SQL Question

Hi,
I remember that Terry posted some months ago
an example that
may help you.
I don't have it on hand but it should be in
the archives.

Isaac Yassin
[login to unmask email]
----- Original Message -----
From: "Moore, Tony" <[login to unmask email]>
Newsgroups: bit.listserv.db2-l
To: <[login to unmask email]>
Sent: Tuesday, December 18, 2001 9:34 PM
Subject: Re: SQL Question


> Not sure of the purpose of this one. The
request comes
from one of our
> ORACLE DBA's working in that mystical land
of UNIX (This
written by a die
> hard DB2 / Mainframe Dinosaur).
>
> -----Original Message-----
> From: James Kwan
[mailto:[login to unmask email]
> Sent: Tuesday, December 18, 2001 2:20 PM
> To: [login to unmask email]
> Subject: Re: SQL Question
>
>
> Tony,
>
> I don't believe it can be done by just
using simple SQL.
It might be
> possible to write very completed
subqueries with union.
But why bother?
> The simpliest way I would do is generate
the list like you
have and may be
> using EXCEL to make it the way your DBA
wants. Or write a
small REXX to
> read the result and format it.
>
> By the way what is the purpose of this
format?
>
> James Kwan
> IBM Certified Specialist
>
>
> In a message dated 12/18/01 12:57:02 PM
Central Standard
Time,
> [login to unmask email] writes:
>
>
>
>
>
> Yo Listers,
> A DBA has asked one of me that I don't
believe is
possible, but thought I'd
> pass it by y'all to see if you've ever
come up with a way
to do such. He
> wants a query that will return a list of
tablespaces that
end in "01", "02",
>
> "03" or "04"... easy enough so far...
however, he wants
all "01" tablespace
> names returned in one row, all "02"
tablespaces in the
next row, and so
> on.... And to put the icing on the cake,
he'd like to have
a string returned
>
> at the end of the row that looks like
'etw' concatenated
with the tablespace
>
> suffix for that row.
>
> For example, say the tablespace list looks
like this....
>
> TS0101
> TS0201
> TS0301
> TS0401
> TS0102
> TS0202
> TS0302
> TS0103
> TS0203
>
> ....he wants the query to return...
> ROW1: TS0101, TS0201, TS0301, TS0401,
etw01
> ROW2: TS0102, TS0202, TS0302, etw03
> ROW3: TS0103, TS0203, etw03
>
> ...any ideas?
> Thanks,
> Tony
>
>

> To change your subscription options or to
cancel your
subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The
owners of the list can be reached at
[login to unmask email]
>



To change your subscription options or to
cancel your
subscription visit the DB2-L webpage at
http://www.ryci.com/db2-l. The
owners of the list can


To change your subscription options or to cancel your
subscription visit the
DB2-L webpage at http://www.ryci.com/db2-l. The owners of
the list can be



To change your subscription options or to cancel your
subscription visit the DB2-L webpage at http://www.ryci.com/db2-l. The
owners of the list can