[DB2 NFM z/OS] What's wrong with this query?

Walter Janißen

[DB2 NFM z/OS] What's wrong with this query?
Hi

I am just puzzling with a query and I don't know what I am doing wrong.
Here is a little excerpt of our SYSPACKAGE:

NAME VERSION
-------- --------------------------
VEUP0851 2003-10-22-10.30.57.046774
VEUP0851 2004-08-13-10.48.28.857664
VEUP0851 2004-08-31-15.05.51.603562
VEUP0851 2005-04-06-16.17.37.842742
VEUP0851 2006-10-20-05.06.01.351108
VEUP0851 2006-11-07-05.33.02.820025
VEUP0851 2006-11-08-08.15.36.287666
VEUP0851 2006-11-20-12.45.09.675015
VEUP0851 2006-11-20-16.26.37.268922
VEUP0851 2006-11-21-07.07.26.810245
VEUP0851 2006-12-05-17.24.55.493290
VEUP0851 2006-12-12-13.03.04.394441
VEUP0851 2007-01-11-12.17.47.175350
VEUP0851 2007-01-17-10.08.05.768056
VEUP0851 2007-01-17-12.47.49.478531
VEUP0851 2007-01-23-18.13.28.495136
VEUP0851 2007-01-24-06.46.25.754452

I want to run the following query (simplified):

SELECT SUBSTR(NAME, 1, 8) AS NAME,
SUBSTR(VERSION, 1, 26) AS VERSION
FROM SYSIBM.SYSPACKAGE B
WHERE LOCATION = ' '
AND COLLID = 'DB2'
AND NAME = 'VEUP0851'
AND VERSION =
(SELECT DISTINCT CASE
WHEN B.NAME LIKE '__UP%' THEN B.VERSION
ELSE MAX(C.VERSION)
END AS VERSION
FROM SYSIBM.SYSPACKAGE C
WHERE C.LOCATION = ' '
AND C.COLLID = 'DB2'
AND B.NAME = C.NAME
)

The query's result is only 1 row:

NAME VERSION
-------- --------------------------
VEUP0851 2003-10-22-10.30.57.046774

That's the oldest version. Because the subquery is correlated, it will be
executed for each outer table row, and obviously the WHEN-branch of the
CASE-expression must be executed, because VEUP0851 contains 'UP' So merely
the query is as follwos:

SELECT SUBSTR(NAME, 1, 8) AS NAME,
SUBSTR(VERSION, 1, 26) AS VERSION
FROM SYSIBM.SYSPACKAGE B
WHERE LOCATION = ' '
AND COLLID = 'DB2'
AND NAME = 'VEUP0851'
AND VERSION =
(SELECT DISTINCT B.VERSION
FROM SYSIBM.SYSPACKAGE C
WHERE C.LOCATION = ' '
AND C.COLLID = 'DB2'
AND B.NAME = C.NAME
)

When I execute this query, I get all rows. When I change the predicate AND
VERSION = to AND VERSION IN in the first query, I also get all rows. So
what's wrong with the first query or is this a bug?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Brian Bear

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Walter Janißen)
What's in LOCATION?

-----Original Message-----
From: Walter Janißen [mailto:[login to unmask email]
Sent: Wednesday, January 24, 2007 10:32 AM
To: [login to unmask email]
Subject: [DB2-L] [DB2 NFM z/OS] What's wrong with this query?






Hi

I am just puzzling with a query and I don't know what I am doing wrong.
Here is a little excerpt of our SYSPACKAGE:

NAME VERSION
-------- --------------------------
VEUP0851 2003-10-22-10.30.57.046774
VEUP0851 2004-08-13-10.48.28.857664
VEUP0851 2004-08-31-15.05.51.603562
VEUP0851 2005-04-06-16.17.37.842742
VEUP0851 2006-10-20-05.06.01.351108
VEUP0851 2006-11-07-05.33.02.820025
VEUP0851 2006-11-08-08.15.36.287666
VEUP0851 2006-11-20-12.45.09.675015
VEUP0851 2006-11-20-16.26.37.268922
VEUP0851 2006-11-21-07.07.26.810245
VEUP0851 2006-12-05-17.24.55.493290
VEUP0851 2006-12-12-13.03.04.394441
VEUP0851 2007-01-11-12.17.47.175350
VEUP0851 2007-01-17-10.08.05.768056
VEUP0851 2007-01-17-12.47.49.478531
VEUP0851 2007-01-23-18.13.28.495136
VEUP0851 2007-01-24-06.46.25.754452

I want to run the following query (simplified):

SELECT SUBSTR(NAME, 1, 8) AS NAME,
SUBSTR(VERSION, 1, 26) AS VERSION
FROM SYSIBM.SYSPACKAGE B
WHERE LOCATION = ' '
AND COLLID = 'DB2'
AND NAME = 'VEUP0851'
AND VERSION =
(SELECT DISTINCT CASE
WHEN B.NAME LIKE '__UP%' THEN B.VERSION
ELSE MAX(C.VERSION)
END AS VERSION
FROM SYSIBM.SYSPACKAGE C
WHERE C.LOCATION = ' '
AND C.COLLID = 'DB2'
AND B.NAME = C.NAME
)

The query's result is only 1 row:

NAME VERSION
-------- --------------------------
VEUP0851 2003-10-22-10.30.57.046774

That's the oldest version. Because the subquery is correlated, it will be executed for each outer table row, and obviously the WHEN-branch of the CASE-expression must be executed, because VEUP0851 contains 'UP' So merely the query is as follwos:

SELECT SUBSTR(NAME, 1, 8) AS NAME,
SUBSTR(VERSION, 1, 26) AS VERSION
FROM SYSIBM.SYSPACKAGE B
WHERE LOCATION = ' '
AND COLLID = 'DB2'
AND NAME = 'VEUP0851'
AND VERSION =
(SELECT DISTINCT B.VERSION
FROM SYSIBM.SYSPACKAGE C
WHERE C.LOCATION = ' '
AND C.COLLID = 'DB2'
AND B.NAME = C.NAME
)

When I execute this query, I get all rows. When I change the predicate AND VERSION = to AND VERSION IN in the first query, I also get all rows. So what's wrong with the first query or is this a bug?

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

Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Peter Vanroose

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Brian Bear)
About the query

> WHERE ... VERSION =
> (SELECT DISTINCT CASE
> WHEN B.NAME LIKE '__UP%' THEN B.VERSION
> ELSE MAX(C.VERSION)
> END AS VERSION
> FROM SYSIBM.SYSPACKAGE C
> WHERE C.LOCATION = ' '
> AND C.COLLID = 'DB2'
> AND B.NAME = C.NAME
> )

versus

> WHERE ... VERSION =
> (SELECT DISTINCT B.VERSION
> FROM SYSIBM.SYSPACKAGE C
> WHERE C.LOCATION = ' '
> AND C.COLLID = 'DB2'
> AND B.NAME = C.NAME
> )

To begin with, I would move the condition B.NAME LIKE '__UP%' out of the subquery, and write the
result set as a UNION ALL.
That will be far more performant.

Now for the two queries:
The most important difference between the two subqueries is the presence of MAX(c.VERSION) in the
first one.
Because of that, there is an implicit GROUP BY <nothing>, i.e., the whole table C of the subquery
(or what remains from it after applying all conditions) forms a single group for which just a
single row is returned.
Hence the DISTINCT is unnecessary, and an IN (SELECT ...) will give exactly the same as an =
(SELECT ...)

The second subquery has no MAX(..), so no implicit GROUP BY, so potentially more than one row to
be returned by the subquery. Depending on the data, you could be faced with an SQLCODE -811
(unless using IN (SELECT ...)).
Of course, the particular nature of the query (nothing returned from C, only from B), and the
DISTINCT, guarantees a single row as a result, so again IN (...) and = (...) will return the same
result.

Now for the result set: all rows of B satisfy the subquery, since B.VERSION always equals
B.VERSION (unless it would be NULL). This explains the output of the second query.
In the first query, because of the GROUP BY, I guess that after having operated on the first row
(apparently the one with lowest VERSION -- check that the access path is indeed using an index on
VERSION) DB2 encounters an error (maybe a -811?) and stops processing. Still stange that it
returned one row though. This looks indeed like a bug.

-- Peter.





_________________________________________________________
Flyger tiden iväg? Fånga dagen med Yahoo! Mails inbyggda
kalender. Dessutom 250 MB gratis, virusscanning och antispam. Få den på: http://se.mail.yahoo.com

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Steve Tennant

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Peter Vanroose)
Well knowing your SQL proficiency Walter, I am unlikely to be able to
assist. The only observations I can make are that I too, like Peter,
suspect the MAX and its ability to return a NULL. If you change the '__UP%'
to something silly so force the logic to go through the ELSE then you do get
a -811, BUT if you just remove the MAX, which is not supposed to be
"traversed" then you get the same result as your second query.

I'd vote for bug also.

Terry?



-----Original Message-----
From: Walter Janißen [mailto:[login to unmask email]
Sent: Thursday, 25 January 2007 02:32
To: [login to unmask email]
Subject: [DB2-L] [DB2 NFM z/OS] What's wrong with this query?


Hi

I am just puzzling with a query and I don't know what I am doing wrong.
Here is a little excerpt of our SYSPACKAGE:

NAME VERSION
-------- --------------------------
VEUP0851 2003-10-22-10.30.57.046774
VEUP0851 2004-08-13-10.48.28.857664
VEUP0851 2004-08-31-15.05.51.603562
VEUP0851 2005-04-06-16.17.37.842742
VEUP0851 2006-10-20-05.06.01.351108
VEUP0851 2006-11-07-05.33.02.820025
VEUP0851 2006-11-08-08.15.36.287666
VEUP0851 2006-11-20-12.45.09.675015
VEUP0851 2006-11-20-16.26.37.268922
VEUP0851 2006-11-21-07.07.26.810245
VEUP0851 2006-12-05-17.24.55.493290
VEUP0851 2006-12-12-13.03.04.394441
VEUP0851 2007-01-11-12.17.47.175350
VEUP0851 2007-01-17-10.08.05.768056
VEUP0851 2007-01-17-12.47.49.478531
VEUP0851 2007-01-23-18.13.28.495136
VEUP0851 2007-01-24-06.46.25.754452

I want to run the following query (simplified):

SELECT SUBSTR(NAME, 1, 8) AS NAME,
SUBSTR(VERSION, 1, 26) AS VERSION
FROM SYSIBM.SYSPACKAGE B
WHERE LOCATION = ' '
AND COLLID = 'DB2'
AND NAME = 'VEUP0851'
AND VERSION =
(SELECT DISTINCT CASE
WHEN B.NAME LIKE '__UP%' THEN B.VERSION
ELSE MAX(C.VERSION)
END AS VERSION
FROM SYSIBM.SYSPACKAGE C
WHERE C.LOCATION = ' '
AND C.COLLID = 'DB2'
AND B.NAME = C.NAME
)

The query's result is only 1 row:

NAME VERSION
-------- --------------------------
VEUP0851 2003-10-22-10.30.57.046774

That's the oldest version. Because the subquery is correlated, it will be
executed for each outer table row, and obviously the WHEN-branch of the
CASE-expression must be executed, because VEUP0851 contains 'UP' So merely
the query is as follwos:

SELECT SUBSTR(NAME, 1, 8) AS NAME,
SUBSTR(VERSION, 1, 26) AS VERSION
FROM SYSIBM.SYSPACKAGE B
WHERE LOCATION = ' '
AND COLLID = 'DB2'
AND NAME = 'VEUP0851'
AND VERSION =
(SELECT DISTINCT B.VERSION
FROM SYSIBM.SYSPACKAGE C
WHERE C.LOCATION = ' '
AND C.COLLID = 'DB2'
AND B.NAME = C.NAME
)

When I execute this query, I get all rows. When I change the predicate AND
VERSION = to AND VERSION IN in the first query, I also get all rows. So
what's wrong with the first query or is this a bug?

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

************************************************************************
IMPORTANT:

* This transmission is intended for the use of the addressee only and might contain sensitive or legally privileged information. If you are NOT the intended recipient, you are notified that any use or dissemination of this communication is strictly prohibited. If you receive this transmission in error, please notify the author immediately by telephone and delete all copies of this transmission together with any attachments.

* The Australian Customs Service DOES NOT AUTHORISE the recipient to further disclose this email or its contents without permission of the originator.

* Unsolicited commercial emails MUST NOT be forwarded to the originator of this transmission unless prior consent has been given.


***********************************************************************

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Terry Purcell

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Steve Tennant)
I just skimmed through this thread but I think I saw that Peter answered
this correctly (in my opinion) at the end of his note:

>SELECT SUBSTR(NAME, 1, 8) AS NAME,
> SUBSTR(VERSION, 1, 26) AS VERSION
> FROM SYSIBM.SYSPACKAGE B
> WHERE LOCATION = ' '
> AND COLLID = 'DB2'
> AND NAME = 'VEUP0851'
> AND VERSION =
> (SELECT DISTINCT B.VERSION
> FROM SYSIBM.SYSPACKAGE C
> WHERE C.LOCATION = ' '
> AND C.COLLID = 'DB2'
> AND B.NAME = C.NAME
> )

Since the subquery refers to B.VERSION in the SELECT list, and B is the
outer table, this comparison is effectively B.VERSION = B.VERSION. And
that is true for every row.

Regards
Terry

On Thu, 25 Jan 2007 16:12:00 +1100, [login to unmask email] wrote:

>Well knowing your SQL proficiency Walter, I am unlikely to be able to
>assist. The only observations I can make are that I too, like Peter,
>suspect the MAX and its ability to return a NULL. If you change
the '__UP%'
>to something silly so force the logic to go through the ELSE then you do
get
>a -811, BUT if you just remove the MAX, which is not supposed to be
>"traversed" then you get the same result as your second query.
>
>I'd vote for bug also.
>
>Terry?
>
>
>
>-----Original Message-----
>From: Walter Janißen [mailto:[login to unmask email]
>Sent: Thursday, 25 January 2007 02:32
>To: [login to unmask email]
>Subject: [DB2-L] [DB2 NFM z/OS] What's wrong with this query?
>
>
>Hi
>
>I am just puzzling with a query and I don't know what I am doing wrong.
>Here is a little excerpt of our SYSPACKAGE:
>
>NAME VERSION
>-------- --------------------------
>VEUP0851 2003-10-22-10.30.57.046774
>VEUP0851 2004-08-13-10.48.28.857664
>VEUP0851 2004-08-31-15.05.51.603562
>VEUP0851 2005-04-06-16.17.37.842742
>VEUP0851 2006-10-20-05.06.01.351108
>VEUP0851 2006-11-07-05.33.02.820025
>VEUP0851 2006-11-08-08.15.36.287666
>VEUP0851 2006-11-20-12.45.09.675015
>VEUP0851 2006-11-20-16.26.37.268922
>VEUP0851 2006-11-21-07.07.26.810245
>VEUP0851 2006-12-05-17.24.55.493290
>VEUP0851 2006-12-12-13.03.04.394441
>VEUP0851 2007-01-11-12.17.47.175350
>VEUP0851 2007-01-17-10.08.05.768056
>VEUP0851 2007-01-17-12.47.49.478531
>VEUP0851 2007-01-23-18.13.28.495136
>VEUP0851 2007-01-24-06.46.25.754452
>
>I want to run the following query (simplified):
>
>SELECT SUBSTR(NAME, 1, 8) AS NAME,
> SUBSTR(VERSION, 1, 26) AS VERSION
> FROM SYSIBM.SYSPACKAGE B
> WHERE LOCATION = ' '
> AND COLLID = 'DB2'
> AND NAME = 'VEUP0851'
> AND VERSION =
> (SELECT DISTINCT CASE
> WHEN B.NAME LIKE '__UP%' THEN B.VERSION
> ELSE MAX(C.VERSION)
> END AS VERSION
> FROM SYSIBM.SYSPACKAGE C
> WHERE C.LOCATION = ' '
> AND C.COLLID = 'DB2'
> AND B.NAME = C.NAME
> )
>
>The query's result is only 1 row:
>
>NAME VERSION
>-------- --------------------------
>VEUP0851 2003-10-22-10.30.57.046774
>
>That's the oldest version. Because the subquery is correlated, it will be
>executed for each outer table row, and obviously the WHEN-branch of the
>CASE-expression must be executed, because VEUP0851 contains 'UP' So merely
>the query is as follwos:
>
>SELECT SUBSTR(NAME, 1, 8) AS NAME,
> SUBSTR(VERSION, 1, 26) AS VERSION
> FROM SYSIBM.SYSPACKAGE B
> WHERE LOCATION = ' '
> AND COLLID = 'DB2'
> AND NAME = 'VEUP0851'
> AND VERSION =
> (SELECT DISTINCT B.VERSION
> FROM SYSIBM.SYSPACKAGE C
> WHERE C.LOCATION = ' '
> AND C.COLLID = 'DB2'
> AND B.NAME = C.NAME
> )
>
>When I execute this query, I get all rows. When I change the predicate AND
>VERSION = to AND VERSION IN in the first query, I also get all rows. So
>what's wrong with the first query or is this a bug?
>

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Terry Purcell

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Terry Purcell)
When I reread this thread I noticed that you were asking what was wrong
with the 1st query....I had thought you were asking what is wrong with the
2nd query (hence my answer).

I would suggest a PMR.

Not sure if this query was simply to prove that you have found a bug, or
whether this is a legitimate query. If legitimate, this may be more
efficient:
>> WHERE LOCATION = ' '
>> AND COLLID = 'DB2'
>> AND NAME = 'VEUP0851' <--- Of course this makes the LIKE redudant
>> AND (NAME LIKE '__UP%'
>> OR VERSION =
>> (SELECT MAX(C.VERSION)
>> FROM SYSIBM.SYSPACKAGE C
>> WHERE C.LOCATION = ' '
>> AND C.COLLID = 'DB2'
>> AND B.NAME = C.NAME
>> ))

If I have misinterpreted again, my apologies. Trying to answer quickly.

Regards
Terry Purcell

On Thu, 25 Jan 2007 06:48:27 -0600, Terry Purcell <[login to unmask email]>
wrote:

>I just skimmed through this thread but I think I saw that Peter answered
>this correctly (in my opinion) at the end of his note:
>
>>SELECT SUBSTR(NAME, 1, 8) AS NAME,
>> SUBSTR(VERSION, 1, 26) AS VERSION
>> FROM SYSIBM.SYSPACKAGE B
>> WHERE LOCATION = ' '
>> AND COLLID = 'DB2'
>> AND NAME = 'VEUP0851'
>> AND VERSION =
>> (SELECT DISTINCT B.VERSION
>> FROM SYSIBM.SYSPACKAGE C
>> WHERE C.LOCATION = ' '
>> AND C.COLLID = 'DB2'
>> AND B.NAME = C.NAME
>> )
>
>Since the subquery refers to B.VERSION in the SELECT list, and B is the
>outer table, this comparison is effectively B.VERSION = B.VERSION. And
>that is true for every row.
>
>Regards
>Terry
>
>On Thu, 25 Jan 2007 16:12:00 +1100, [login to unmask email] wrote:
>
>>Well knowing your SQL proficiency Walter, I am unlikely to be able to
>>assist. The only observations I can make are that I too, like Peter,
>>suspect the MAX and its ability to return a NULL. If you change
>the '__UP%'
>>to something silly so force the logic to go through the ELSE then you do
>get
>>a -811, BUT if you just remove the MAX, which is not supposed to be
>>"traversed" then you get the same result as your second query.
>>
>>I'd vote for bug also.
>>
>>Terry?
>>
>>
>>
>>-----Original Message-----
>>From: Walter Janißen [mailto:[login to unmask email]
>>Sent: Thursday, 25 January 2007 02:32
>>To: [login to unmask email]
>>Subject: [DB2-L] [DB2 NFM z/OS] What's wrong with this query?
>>
>>
>>Hi
>>
>>I am just puzzling with a query and I don't know what I am doing wrong.
>>Here is a little excerpt of our SYSPACKAGE:
>>
>>NAME VERSION
>>-------- --------------------------
>>VEUP0851 2003-10-22-10.30.57.046774
>>VEUP0851 2004-08-13-10.48.28.857664
>>VEUP0851 2004-08-31-15.05.51.603562
>>VEUP0851 2005-04-06-16.17.37.842742
>>VEUP0851 2006-10-20-05.06.01.351108
>>VEUP0851 2006-11-07-05.33.02.820025
>>VEUP0851 2006-11-08-08.15.36.287666
>>VEUP0851 2006-11-20-12.45.09.675015
>>VEUP0851 2006-11-20-16.26.37.268922
>>VEUP0851 2006-11-21-07.07.26.810245
>>VEUP0851 2006-12-05-17.24.55.493290
>>VEUP0851 2006-12-12-13.03.04.394441
>>VEUP0851 2007-01-11-12.17.47.175350
>>VEUP0851 2007-01-17-10.08.05.768056
>>VEUP0851 2007-01-17-12.47.49.478531
>>VEUP0851 2007-01-23-18.13.28.495136
>>VEUP0851 2007-01-24-06.46.25.754452
>>
>>I want to run the following query (simplified):
>>
>>SELECT SUBSTR(NAME, 1, 8) AS NAME,
>> SUBSTR(VERSION, 1, 26) AS VERSION
>> FROM SYSIBM.SYSPACKAGE B
>> WHERE LOCATION = ' '
>> AND COLLID = 'DB2'
>> AND NAME = 'VEUP0851'
>> AND VERSION =
>> (SELECT DISTINCT CASE
>> WHEN B.NAME LIKE '__UP%' THEN B.VERSION
>> ELSE MAX(C.VERSION)
>> END AS VERSION
>> FROM SYSIBM.SYSPACKAGE C
>> WHERE C.LOCATION = ' '
>> AND C.COLLID = 'DB2'
>> AND B.NAME = C.NAME
>> )
>>
>>The query's result is only 1 row:
>>
>>NAME VERSION
>>-------- --------------------------
>>VEUP0851 2003-10-22-10.30.57.046774
>>
>>That's the oldest version. Because the subquery is correlated, it will be
>>executed for each outer table row, and obviously the WHEN-branch of the
>>CASE-expression must be executed, because VEUP0851 contains 'UP' So
merely
>>the query is as follwos:
>>
>>SELECT SUBSTR(NAME, 1, 8) AS NAME,
>> SUBSTR(VERSION, 1, 26) AS VERSION
>> FROM SYSIBM.SYSPACKAGE B
>> WHERE LOCATION = ' '
>> AND COLLID = 'DB2'
>> AND NAME = 'VEUP0851'
>> AND VERSION =
>> (SELECT DISTINCT B.VERSION
>> FROM SYSIBM.SYSPACKAGE C
>> WHERE C.LOCATION = ' '
>> AND C.COLLID = 'DB2'
>> AND B.NAME = C.NAME
>> )
>>
>>When I execute this query, I get all rows. When I change the predicate
AND
>>VERSION = to AND VERSION IN in the first query, I also get all rows. So
>>what's wrong with the first query or is this a bug?

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Terry Purcell)
Terry

This query was simplified to prove that there could be a bug in DB2. The
original query was without the predicate AND NAME = 'VEUP0851'. What I
wanted to get: If the package name contains the string 'UP', then all
versions should be returned (because the package is from a program linked
statically), and the maximum of a version if not. But I was surprised, that
I only get one single version per package.

I don't know, but I think there was a restriction in prior DB2 Versions, to
use in a CASE-expression MAX with something else wasn't it?

But anyway, as you suggested, I will open a PMR.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Walter Jani&#223;en

Re: [DB2 NFM z/OS] What's wrong with this query?
(in response to Walter Janißen)
Hi

I just tested a variation of my query to make it easier for recreating the
problem in the lab.

SELECT SUBSTR(NAME, 1, 8) AS NAME
FROM SYSIBM.SYSINDEXES A
WHERE TBCREATOR = 'SYSIBM'
AND TBNAME = 'SYSROUTINES'
AND CREATOR = 'SYSIBM'
AND NAME =
(SELECT DISTINCT CASE
WHEN A.NAME LIKE 'DSN%' THEN A.NAME
ELSE MAX(C.NAME)
END AS NAME
FROM SYSIBM.SYSINDEXES C
WHERE C.TBCREATOR = 'SYSIBM'
AND C.TBNAME = A.TBNAME
)
I was more than surprized, that this query returned the right rows:

NAME
--------
DSNOFX01
DSNOFX02
DSNOFX03
DSNOFX04
DSNOFX05
DSNOFX06
DSNOFX07
DSNOFX08

I thought, that this query is an equivalent of my problem query. When I
looked at the explain output, I saw, that my problem query has one more
matching column and I1 for the subquery. So I looked up the index used for
the above query. When I now insert the predicate
AND C.CREATOR = 'SYSIBM'
in the above subquery I got:

NAME
--------
DSNOFX01

So, it must definitely be a bug and I will open a PMR.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm