RID failures and the access paths

Vidya Attuluri

RID failures and the access paths
Platform: DB2 9 CM for z/OS 1.10

I have an SQL with correlated subquery. The access path taken for the outer query is Index access with List Prefetch and the access path chosen for subquery is just Index access without any prefetch.

The question is, if at run-time, there is a RID failure, the access path for outer query will be Table space scan (will it use Page range?), we have provided the leading column of the partitioning index. We have index controlled partitioning for the underlying table.

Another question is, when RID failure occurs, will it change the access path for the sub-query also (where the original access path is index scan without any prefetch)?

Regards
Vidya

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

[login to unmask email]

Re: RID failures and the access paths
(in response to Vidya Attuluri)
Vidya,

To the first question, if RID failure, it degrades to table scan, which
means no page range scan or partition scan.

To the second question, I'm not sure. Just guess, since these query blocks

are separate, so it should be no impacts on each other.



William Huang,
Best regards




Vidya Attuluri <[login to unmask email]>
·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
2010-11-17 03:48
Çë´ð¸´ ¸ø
IDUG DB2-L <[login to unmask email]>


ÊÕ¼þÈË
[login to unmask email]
³­ËÍ

Ö÷Ìâ
[DB2-L] RID failures and the access paths






Platform: DB2 9 CM for z/OS 1.10

I have an SQL with correlated subquery. The access path taken for the
outer query is Index access with List Prefetch and the access path chosen
for subquery is just Index access without any prefetch.

The question is, if at run-time, there is a RID failure, the access path
for outer query will be Table space scan (will it use Page range?), we
have provided the leading column of the partitioning index. We have index
controlled partitioning for the underlying table.

Another question is, when RID failure occurs, will it change the access
path for the sub-query also (where the original access path is index scan
without any prefetch)?

Regards
Vidya

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 *
http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it!
*
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is
the home of IDUG's Listserv



_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv

Suresh Sane

Re: RID failures and the access paths
(in response to hhuang@DCCSH.ICBC.COM.CN)

As a plug for DB2 10...

Such ridpool failures should vanish/be reduced with the "defensive optimization" initiative. DB2 will create workfiles and continue. Defaults increased.

Thx
Suresh


Date: Thu, 18 Nov 2010 15:41:38 +0800
From: [login to unmask email]
Subject: Re: [DB2-L] RID failures and the access paths
To: [login to unmask email]


Vidya,

To the first question, if RID failure, it degrades to table scan, which
means no page range scan or partition scan.

To the second question, I'm not sure. Just guess, since these query blocks
are separate, so it should be no impacts on each other.



William Huang,
Best regards







Vidya Attuluri <[login to unmask email]>
·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
2010-11-17 03:48




Çë´ð¸´ ¸ø
IDUG DB2-L <[login to unmask email]>






ÊÕ¼þÈË
[login to unmask email]


³­ËÍ



Ö÷Ìâ
[DB2-L] RID failures and the access paths









Platform: DB2 9 CM for z/OS 1.10

I have an SQL with correlated subquery. The access path taken for the outer query is Index access with List Prefetch and the access path chosen for subquery is just Index access without any prefetch.

The question is, if at run-time, there is a RID failure, the access path for outer query will be Table space scan (will it use Page range?), we have provided the leading column of the partitioning index. We have index controlled partitioning for the underlying table.

Another question is, when RID failure occurs, will it change the access path for the sub-query also (where the original access path is index scan without any prefetch)?

Regards
Vidya

_____________________________________________________________________
* IDUG EMEA * Prague, Czech Republic * 14-18 November 2011 * http://IDUG.ORG/EMEA *
* If you are going to attend only one conference this year, this is it! *
_____________________________________________________________________
http://www.IDUG.org/mentor
Mentoring should be a rewarding experience for everyone...
IDUG is offering up to 80% off when you both come to the conference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv






The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's Listserv