Row with minimum priorities

Ron Root

Row with minimum priorities
I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

Michael Ebert

Re: Row with minimum priorities
(in response to Ron Root)
Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a
one-fetch access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts


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

Philip Sevetson

Re: Row with minimum priorities
(in response to Michael Ebert)
Ron,

Would you please clarify this? I think I'm seeing that your CASES table
also has CUST_ID on it; is this correct? If not, how are you filtering
CASES itself?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






Ron Root <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/20/2004 09:49 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Row with minimum priorities


I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

Ron Root

Re: Row with minimum priorities
(in response to Philip Sevetson)
There are multiple cases per customer in my simplified example. I want the
ones with max priority and within that list, the one with the max sub
priority. Everything in my where predicate is indexed. I am looking for the
cheapest way to find that single row. The order by and fetch one row works,
it just performs poorly when there are lots of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1
ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities



Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a one-fetch
access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

Philip Sevetson

Re: Row with minimum priorities
(in response to Ron Root)
Thanks for the clarification. I think you want the SQL to read:

SELECT CASE_ID FROM CASES C1
WHERE C1.CUST_ID = ?
AND C1.PRTY = (
SELECT MAX(C2.PRTY)
FROM CASES C2
WHERE C2.CUST_ID = C1.CUST_ID
)
AND C1.SUB_PRTY = (
SELECT MAX(C3.SUB_PRTY)
FROM CASES C3
WHERE C3.CUST_ID = C1.CUST_ID
AND C3.PRTY = C1.PRTY
)
;

And then you want an index on (CUST_ID, PRTY, SUB_PRTY and maybe CASE).

This will return one row (More if there is a perfect tie in PRTY and
SUB_PRTY for a customer. You can prevent that with a unique index on
CUST_ID, PRTY, SUB_PRTY).

There is a simplification available for this in DB2V7, and the
simplification should lead to better performance.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]






Ron Root <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/20/2004 11:18 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] Row with minimum priorities


There are multiple cases per customer in my simplified example. I want the
ones with max priority and within that list, the one with the max sub
priority. Everything in my where predicate is indexed. I am looking for
the cheapest way to find that single row. The order by and fetch one row
works, it just performs poorly when there are lots of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1
ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a
one-fetch access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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


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

Michael Ebert

Re: Row with minimum priorities
(in response to Philip Sevetson)
In that case the leading index columns should be (CUST_ID, PRTY DESC,
SUB_PRTY DESC). If you're getting bad performance with this one, then
either DB2 isn't using this index (might be because of a datatype mismatch
or because the optimiser is confused - check EXPLAIN for dynamic or the
PLAN_TABLE for static SQL) or you've got some other non-DB2 problem, e.g.
I/O problems or STC priorities, I'd say...
The FETCH FIRST ROW ONLY should not make a performance difference for this
query, except that it implies OPTIMIZE FOR 1 ROW which would favour using
an index. Mostly it would simply prevent you from fetching a second row.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany



There are multiple cases per customer in my simplified example. I want the
ones with max priority and within that list, the one with the max sub
priority. Everything in my where predicate is indexed. I am looking for
the cheapest way to find that single row. The order by and fetch one row
works, it just performs poorly when there are lots of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1
ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a
one-fetch access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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



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

Philip Sevetson

Re: Row with minimum priorities
(in response to Scott Hodgin)
What a team! We all got it.

Michael, thanks for specifying DESC on the index keys. Since going to V7,
I haven't thought much about that angle.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]






"Hodgin, Scott" <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/20/2004 11:45 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] Row with minimum priorities


Perhaps something like...

SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES A WHERE CUST_ID = ? AND
PRTY =
(SELECT
MAX(PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID) AND
SUB_PRTY =
(SELECT
MAX(SUB_PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID AND
PRTY = A.PRTY)

Scott Hodgin, Database Administrator<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />
South Carolina Farm Bureau Insurance Company
[login to unmask email]
-----Original Message-----
From: Ron Root [mailto:[login to unmask email]
Sent: Tuesday, January 20, 2004 11:19 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities

There are multiple cases per customer in my simplified example. I want the
ones with max priority and within that list, the one with the max sub
priority. Everything in my where predicate is indexed. I am looking for
the cheapest way to find that single row. The order by and fetch one row
works, it just performs poorly when there are lots of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1
ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a
one-fetch access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

Scott Hodgin

Re: Row with minimum priorities
(in response to Michael Ebert)
Perhaps something like...

SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES A WHERE CUST_ID = ? AND
PRTY =
(SELECT
MAX(PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID) AND
SUB_PRTY =
(SELECT
MAX(SUB_PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID AND
PRTY = A.PRTY)

Scott Hodgin, Database Administrator<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />

South Carolina Farm Bureau Insurance Company

<outbind://[login to unmask email]> [login to unmask email]

-----Original Message-----
From: Ron Root [mailto:[login to unmask email]
Sent: Tuesday, January 20, 2004 11:19 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


There are multiple cases per customer in my simplified example. I want the
ones with max priority and within that list, the one with the max sub
priority. Everything in my where predicate is indexed. I am looking for the
cheapest way to find that single row. The order by and fetch one row works,
it just performs poorly when there are lots of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1
ROW ONLY in V7 will help.


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of
Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities



Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a one-fetch
access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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


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

Ron Root

Re: Row with minimum priorities
(in response to Philip Sevetson)
The filter on cases is a rather complex subselect that did not appear
relevant to the the example. I just want to avoid materializing the cursor
and fetching a single row out of many thousands. I have tried a table select
and selecting the max value and joining that to the original query. But it
does not seem to reduce the number of pages accessed,.
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of [login to unmask email]
Sent: Tuesday, January 20, 2004 9:19 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities



Ron,

Would you please clarify this? I think I'm seeing that your CASES table
also has CUST_ID on it; is this correct? If not, how are you filtering CASES
itself?

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
mailto:[login to unmask email]




Ron Root <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/20/2004 09:49 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: [DB2-L] Row with minimum priorities



I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

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

Ron Root

Re: Row with minimum priorities
(in response to Ron Root)
Phil, et al, Scott, Dr. Ebert,
This is exactly what I tried. But I did not have an index on PRTY and
SUB_PRTY. Their cardinality is very low ( <10 ) but it may help a bit . I
was hoping for a different approach. I will try adding the indexes.

Thanks everyone for the quick response.

Ro
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of [login to unmask email]
Sent: Tuesday, January 20, 2004 10:33 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities



Thanks for the clarification. I think you want the SQL to read:

SELECT CASE_ID FROM CASES C1
WHERE C1.CUST_ID = ?
AND C1.PRTY = (
SELECT MAX(C2.PRTY)
FROM CASES C2
WHERE C2.CUST_ID = C1.CUST_ID
)
AND C1.SUB_PRTY = (
SELECT MAX(C3.SUB_PRTY)
FROM CASES C3
WHERE C3.CUST_ID = C1.CUST_ID
AND C3.PRTY = C1.PRTY
)
;

And then you want an index on (CUST_ID, PRTY, SUB_PRTY and maybe CASE).

This will return one row (More if there is a perfect tie in PRTY and
SUB_PRTY for a customer. You can prevent that with a unique index on
CUST_ID, PRTY, SUB_PRTY).

There is a simplification available for this in DB2V7, and the
simplification should lead to better performance.

--Phil Sevetson
Database Administration
Wakefern Food Corporation CISD
230 Raritan Center Parkway, P5-105
Edison, NJ 08837
Phone: (732) 225-8086
Fax: (732) 225-8109
mailto:[login to unmask email]




Ron Root <[login to unmask email]>
Sent by: DB2 Data Base Discussion List <[login to unmask email]>
01/20/2004 11:18 AM
Please respond to DB2 Database Discussion list at IDUG


To: [login to unmask email]
cc:
Subject: Re: [DB2-L] Row with minimum priorities



There are multiple cases per customer in my simplified example. I want the
ones with max priority and within that list, the one with the max sub
priority. Everything in my where predicate is indexed. I am looking for the
cheapest way to find that single row. The order by and fetch one row works,
it just performs poorly when there are lots of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1
ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf
Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you
add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a one-fetch
access. I think from V7 on (on zOS), you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to address this
problem. But they all seem to access the same number of pages. Hopefully
an
SQL guru out there has an elegant solution. The actual problem involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the one with
MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR
and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and sorted. It can
run for many seconds to get what the customer views as a simple request.

Any ideas would be appreciated by the poor customer waiting for someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

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

Horacio Villa

Re: Row with minimum priorities
(in response to Ron Root)
When you use something like:
PRTY = (SELECT MAX(PRTY) FROM CASES......)
this can possibly return null and it does not use an index.
Try
PRTY = (SELECT COALESCE(MAX(PRTY), 9999) FROM CASES......)
If MAX(PRTY) is null (does not exist), it returns 9999 (use your max
possible value).

Horacio Villa




"Hodgin, Scott"
<[login to unmask email] To: [login to unmask email]
COM> cc:
Sent by: DB2 Data Subject: Re: Row with minimum priorities
Base Discussion
List
<[login to unmask email]
ORG>


01/20/04 01:45 PM
Please respond to
DB2 Database
Discussion list
at IDUG






Perhaps something like...

SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES A WHERE CUST_ID = ? AND
PRTY =
(SELECT
MAX(PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID) AND
SUB_PRTY =
(SELECT
MAX(SUB_PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID AND
PRTY = A.PRTY)

Scott Hodgin, Database Administrator<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />
South Carolina Farm Bureau Insurance Company
[login to unmask email]


-----Original Message-----
From: Ron Root [mailto:[login to unmask email]
Sent: Tuesday, January 20, 2004 11:19 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities

There are multiple cases per customer in my simplified example. I
want the ones with max priority and within that list, the one with
the max sub priority. Everything in my where predicate is indexed. I
am looking for the cheapest way to find that single row. The order by
and fetch one row works, it just performs poorly when there are lots
of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope FETCH
FIRST 1 ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that
case, if you add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC),
you should get a one-fetch access. I think from V7 on (on zOS),
you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's to
address this
problem. But they all seem to access the same number of pages.
Hopefully an
SQL guru out there has an elegant solution. The actual problem
involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row for
the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want the
one with
MAX(SUB_PRTY). The current SQL in the program opens the
following CURSOR and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned and
sorted. It can
run for many seconds to get what the customer views as a simple
request.

Any ideas would be appreciated by the poor customer waiting for
someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts

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

Horacio Villa

Re: Row with minimum priorities
(in response to Peter Backlund)
Peter,

the original problem was with DB2 V6.

Horacio Villa




Peter Backlund
<[login to unmask email] To: [login to unmask email]
L.NET> cc:
Sent by: DB2 Data Subject: Re: Row with minimum priorities
Base Discussion
List
<[login to unmask email]
ORG>


01/20/04 05:48 PM
Please respond to
DB2 Database
Discussion list
at IDUG






This has finally been fixed!

This construction is not needed in DB2 for z/OS Version 7.

Peter

Horacio Villa wrote:
When you use something like:
PRTY = (SELECT MAX(PRTY) FROM CASES......)
this can possibly return null and it does not use an index.
Try
PRTY = (SELECT COALESCE(MAX(PRTY), 9999) FROM CASES......)
If MAX(PRTY) is null (does not exist), it returns 9999 (use your max
possible value).

Horacio Villa




"Hodgin, Scott"
<[login to unmask email] To:
[login to unmask email]
COM> cc:
Sent by: DB2 Data Subject: Re: Row with
minimum priorities
Base Discussion
List
<[login to unmask email]
ORG>


01/20/04 01:45 PM
Please respond to
DB2 Database
Discussion list
at IDUG






Perhaps something like...

SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES A WHERE CUST_ID = ? AND
PRTY =
(SELECT
MAX(PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID) AND
SUB_PRTY =
(SELECT
MAX(SUB_PRTY)
FROM
CASES
WHERE
CUST_ID = A.CUST_ID AND
PRTY = A.PRTY)

Scott Hodgin, Database Administrator<?xml:namespace prefix = o ns =
"urn:schemas-microsoft-com:office:office" />
South Carolina Farm Bureau Insurance Company
[login to unmask email]


-----Original Message-----
From: Ron Root [mailto:[login to unmask email]
Sent: Tuesday, January 20, 2004 11:19 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities

There are multiple cases per customer in my simplified example.
I
want the ones with max priority and within that list, the one
with
the max sub priority. Everything in my where predicate is
indexed. I
am looking for the cheapest way to find that single row. The
order by
and fetch one row works, it just performs poorly when there are
lots
of cases.
I forgot to mention we are on V6. Soon to be on V7. I hope
FETCH
FIRST 1 ROW ONLY in V7 will help.

-----Original Message-----
From: DB2 Data Base Discussion List
[mailto:[login to unmask email]On Behalf Of Michael Ebert
Sent: Tuesday, January 20, 2004 9:12 AM
To: [login to unmask email]
Subject: Re: Row with minimum priorities


Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In
that
case, if you add an index (CASE_ID, PRTY DESC, SUB_PRTY
DESC),
you should get a one-fetch access. I think from V7 on (on
zOS),
you don't need the DESC.

Dr. Michael Ebert
DB2 Database Administrator
aMaDEUS Data Processing
Erding / Munich, Germany




I have tried many forms of table selects and group by's
to
address this
problem. But they all seem to access the same number of
pages.
Hopefully an
SQL guru out there has an elegant solution. The actual
problem
involves a
couple of joins, but following is the basics.
Table CASES has PRTY and SUB_PRTY columns. I want the row
for
the CASE_ID
with maximum priority. Of all rows with max(PRTY) I want
the
one with
MAX(SUB_PRTY). The current SQL in the program opens the
following CURSOR and
fetches the first row.
SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE
CUST_ID = ?
ORDER BY PRTY DESC, SUB_PRTY DESC
At times there are thousands of rows that are returned
and
sorted. It can
run for many seconds to get what the customer views as a
simple
request.

Any ideas would be appreciated by the poor customer
waiting for
someone to
pull up his next case.

Ron Root
Performance and Capacity Planning
Texas Comptroller of Public Accounts


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

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

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



--

===> See you October, 11-14, at IDUG in Prague, Czech Republic <===

+-------------------------------+---------------------------------+
| Peter G Backlund | Telephone: 08 - 38 69 08 |
| Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 |
| Smastuguvagen 2 | Country Code (Sweden): 46 |
| S-165 72 HASSELBY | |
| Sweden | E-mail: [login to unmask email] |
+-------------------------------+---------------------------------+
| IBM Database GOLD Consultant |
+-------------------------------+---------------------------------+
| Working with DB2 since 1981 ... and forever! |
+-----------------------------------------------------------------+
---------------------------------------------------------------------------------
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 Backlund

Re: Row with minimum priorities
(in response to Horacio Villa)
This has finally been fixed!

This construction is not needed in DB2 for z/OS Version 7.

Peter

Horacio Villa wrote:
When you use something like: PRTY = (SELECT MAX(PRTY) FROM CASES......) this can possibly return null and it does not use an index. Try PRTY = (SELECT COALESCE(MAX(PRTY), 9999) FROM CASES......) If MAX(PRTY) is null (does not exist), it returns 9999 (use your max possible value). Horacio Villa "Hodgin, Scott" <[login to unmask email] To: [login to unmask email] COM> cc: Sent by: DB2 Data Subject: Re: Row with minimum priorities Base Discussion List <[login to unmask email] ORG> 01/20/04 01:45 PM Please respond to DB2 Database Discussion list at IDUG Perhaps something like... SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES A WHERE CUST_ID = ? AND PRTY = (SELECT MAX(PRTY) FROM CASES WHERE CUST_ID = A.CUST_ID) AND SUB_PRTY = (SELECT MAX(SUB_PRTY) FROM CASES WHERE CUST_ID = A.CUST_ID AND PRTY = A.PRTY) Scott Hodgin, Database Administrator<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> South Carolina Farm Bureau Insurance Company [login to unmask email] -----Original Message----- From: Ron Root [mailto:[login to unmask email]] Sent: Tuesday, January 20, 2004 11:19 AM To: [login to unmask email] Subject: Re: Row with minimum priorities There are multiple cases per customer in my simplified example. I want the ones with max priority and within that list, the one with the max sub priority. Everything in my where predicate is indexed. I am looking for the cheapest way to find that single row. The order by and fetch one row works, it just performs poorly when there are lots of cases. I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1 ROW ONLY in V7 will help. -----Original Message----- From: DB2 Data Base Discussion List [mailto:[login to unmask email]]On Behalf Of Michael Ebert Sent: Tuesday, January 20, 2004 9:12 AM To: [login to unmask email] Subject: Re: Row with minimum priorities Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a one-fetch access. I think from V7 on (on zOS), you don't need the DESC. Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany I have tried many forms of table selects and group by's to address this problem. But they all seem to access the same number of pages. Hopefully an SQL guru out there has an elegant solution. The actual problem involves a couple of joins, but following is the basics. Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID with maximum priority. Of all rows with max(PRTY) I want the one with MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR and fetches the first row. SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ? ORDER BY PRTY DESC, SUB_PRTY DESC At times there are thousands of rows that are returned and sorted. It can run for many seconds to get what the customer views as a simple request. Any ideas would be appreciated by the poor customer waiting for someone to pull up his next case. Ron Root Performance and Capacity Planning Texas Comptroller of Public Accounts --------------------------------------------------------------------------------- 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 --------------------------------------------------------------------------------- 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
-- ===> See you October, 11-14, at IDUG in Prague, Czech Republic <=== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- 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 Backlund

Re: Row with minimum priorities
(in response to Horacio Villa)
Horacio,

then your comment was very appropriate!

Peter

Horacio Villa wrote:
Peter, the original problem was with DB2 V6. Horacio Villa Peter Backlund <[login to unmask email] To: [login to unmask email] L.NET> cc: Sent by: DB2 Data Subject: Re: Row with minimum priorities Base Discussion List <[login to unmask email] ORG> 01/20/04 05:48 PM Please respond to DB2 Database Discussion list at IDUG This has finally been fixed! This construction is not needed in DB2 for z/OS Version 7. Peter Horacio Villa wrote: When you use something like: PRTY = (SELECT MAX(PRTY) FROM CASES......) this can possibly return null and it does not use an index. Try PRTY = (SELECT COALESCE(MAX(PRTY), 9999) FROM CASES......) If MAX(PRTY) is null (does not exist), it returns 9999 (use your max possible value). Horacio Villa "Hodgin, Scott" <[login to unmask email] To: [login to unmask email] COM> cc: Sent by: DB2 Data Subject: Re: Row with minimum priorities Base Discussion List <[login to unmask email] ORG> 01/20/04 01:45 PM Please respond to DB2 Database Discussion list at IDUG Perhaps something like... SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES A WHERE CUST_ID = ? AND PRTY = (SELECT MAX(PRTY) FROM CASES WHERE CUST_ID = A.CUST_ID) AND SUB_PRTY = (SELECT MAX(SUB_PRTY) FROM CASES WHERE CUST_ID = A.CUST_ID AND PRTY = A.PRTY) Scott Hodgin, Database Administrator<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" /> South Carolina Farm Bureau Insurance Company [login to unmask email] -----Original Message----- From: Ron Root [mailto:[login to unmask email]] Sent: Tuesday, January 20, 2004 11:19 AM To: [login to unmask email] Subject: Re: Row with minimum priorities There are multiple cases per customer in my simplified example. I want the ones with max priority and within that list, the one with the max sub priority. Everything in my where predicate is indexed. I am looking for the cheapest way to find that single row. The order by and fetch one row works, it just performs poorly when there are lots of cases. I forgot to mention we are on V6. Soon to be on V7. I hope FETCH FIRST 1 ROW ONLY in V7 will help. -----Original Message----- From: DB2 Data Base Discussion List [mailto:[login to unmask email]]On Behalf Of Michael Ebert Sent: Tuesday, January 20, 2004 9:12 AM To: [login to unmask email] Subject: Re: Row with minimum priorities Shouldn't it read ...WHERE CASE_ID... (not CUST_ID)? In that case, if you add an index (CASE_ID, PRTY DESC, SUB_PRTY DESC), you should get a one-fetch access. I think from V7 on (on zOS), you don't need the DESC. Dr. Michael Ebert DB2 Database Administrator aMaDEUS Data Processing Erding / Munich, Germany I have tried many forms of table selects and group by's to address this problem. But they all seem to access the same number of pages. Hopefully an SQL guru out there has an elegant solution. The actual problem involves a couple of joins, but following is the basics. Table CASES has PRTY and SUB_PRTY columns. I want the row for the CASE_ID with maximum priority. Of all rows with max(PRTY) I want the one with MAX(SUB_PRTY). The current SQL in the program opens the following CURSOR and fetches the first row. SELECT PRTY,SUB_PRTY, CASE_ID, ... FROM CASES WHERE CUST_ID = ? ORDER BY PRTY DESC, SUB_PRTY DESC At times there are thousands of rows that are returned and sorted. It can run for many seconds to get what the customer views as a simple request. Any ideas would be appreciated by the poor customer waiting for someone to pull up his next case. Ron Root Performance and Capacity Planning Texas Comptroller of Public Accounts --------------------------------------------------------------------------------- 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 --------------------------------------------------------------------------------- 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 -- ===> See you October, 11-14, at IDUG in Prague, Czech Republic <=== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- 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
-- ===> See you October, 11-14, at IDUG in Prague, Czech Republic <=== +-------------------------------+---------------------------------+ | Peter G Backlund | Telephone: 08 - 38 69 08 | | Peter Backlund DB2-Konsult AB | Mobile: 070 - 764 7554 | | Smastuguvagen 2 | Country Code (Sweden): 46 | | S-165 72 HASSELBY | | | Sweden | E-mail: [login to unmask email] | +-------------------------------+---------------------------------+ | IBM Database GOLD Consultant | +-------------------------------+---------------------------------+ | Working with DB2 since 1981 ... and forever! | +-----------------------------------------------------------------+ --------------------------------------------------------------------------------- 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