DB2 on Z/OS - Enforcing List Prefetch

Siva Kondamadugula

DB2 on Z/OS - Enforcing List Prefetch
Hi,

We are running DB2 V7 on Z/OS.

I have a Data Warehouse query running for over 20 mins where 90% of the time
its waiting on DB2 I/O! The indexes and tablespaces are placed in different
bufferpools. EXPLAIN shows that the tables are being joined using Nested
Loop. The index being used to access the inner table is clustered (cluster
ratio=99%). But its the join column (foreign key) on the outer table that
has very random distribution (clusterratio < 50%). The inner table has 26
million rows and the result set is expected to have just about 20000 rows.

I have specified 'Optimize for 20000 rows' on my query, but that made no
difference!

Any suggestions as to how I can reduce the I/O wait time? How can I enforce
List Prefetch / Merge Scan / Hybrid Join?

Thanks,
Siva Reddy
Production Database Support

-------------------------------------------------------------------------------------------
***National City made the following annotations
-------------------------------------------------------------------------------------------
This communication is a confidential and proprietary business communication. It is intended solely for the use of the designated recipient(s). If this communication is received in error, please contact the sender and delete this communication.
==================

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

Venkat Srinivasan

Re: DB2 on Z/OS - Enforcing List Prefetch
(in response to Siva Kondamadugula)
Unfortunately you chose to omit the vital stats, the query, the explain,
the avlbl indexes and the stats.
In a join you have to determine which table should be the first table in
the sequence.
Improper colcard / clusteratio (on index with leading col as the col in
question) / freq distribution can skew the access path in join.
Depending on the columns you select, setting colcard to a fictious val/ -1
may give you a you a difft join method (if at all that is what you want
to do). Not that I recommend.
Apply the local predicates to each table and determine which one should be
outer and inner.
Post the explain results.

Venkat

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

Mike Vaughan

Re: DB2 on Z/OS - Enforcing List Prefetch
(in response to Venkat Srinivasan)
I'm not saying I recommend that you do this since it's really hard to say given the information provided, but there is a sort-of standard trick for converting a nested loop join into a merge-scan join by coding it as a full outer join and adding additional criteria to turn the results back into an inner-join. Note - I make no guarantees that this will be more efficient for you, and in all honesty it will probably make it worse (given the small understanding I have of your query).

In any case, the basic format would look something like this...

original query:
Select <Cols> from table1 a, table2 b
where a.col1 = ?
and a.col2 = b.col2

modified query:
Select <Cols> from table1 a
full outer join table 2 b
on a.col2=b.col2
where a.col1 = ?
and value(a.col2,'') > '' and value(b.col2,'') > ''

Really need more information to give a better answer.

Mike.
-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Reddy, Siva (TATA)
Sent: Tuesday, December 28, 2004 4:43 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 on Z/OS - Enforcing List Prefetch


Hi,

We are running DB2 V7 on Z/OS.

I have a Data Warehouse query running for over 20 mins where 90% of the time
its waiting on DB2 I/O! The indexes and tablespaces are placed in different
bufferpools. EXPLAIN shows that the tables are being joined using Nested
Loop. The index being used to access the inner table is clustered (cluster
ratio=99%). But its the join column (foreign key) on the outer table that
has very random distribution (clusterratio < 50%). The inner table has 26
million rows and the result set is expected to have just about 20000 rows.

I have specified 'Optimize for 20000 rows' on my query, but that made no
difference!

Any suggestions as to how I can reduce the I/O wait time? How can I enforce
List Prefetch / Merge Scan / Hybrid Join?

Thanks,
Siva Reddy
Production Database Support

-------------------------------------------------------------------------------------------
***National City made the following annotations
-------------------------------------------------------------------------------------------
This communication is a confidential and proprietary business communication. It is intended solely for the use of the designated recipient(s). If this communication is received in error, please contact the sender and delete this communication.
===================

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


-----Message Disclaimer-----

This e-mail message is intended only for the use of the individual or
entity to which it is addressed, and may contain information that is
privileged, confidential and exempt from disclosure under applicable law.
If you are not the intended recipient, any dissemination, distribution or
copying of this communication is strictly prohibited. If you have
received this communication in error, please notify us immediately by
reply email to [login to unmask email] and delete or destroy all copies of
the original message and attachments thereto. Email sent to or from the
Principal Financial Group or any of its member companies may be retained
as required by law or regulation.

Nothing in this message is intended to constitute an Electronic signature
for purposes of the Uniform Electronic Transactions Act (UETA) or the
Electronic Signatures in Global and National Commerce Act ("E-Sign")
unless a specific statement to the contrary is included in this message.

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