Z/OS V10 -- Access path Frustration

Dave Nance

Z/OS V10 -- Access path Frustration
I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.   I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.

Dave Nance 

Venkat Srinivasan

RE: Z/OS V10 -- Access path Frustration
(in response to Dave Nance)

What happens with reopt?

Venkat
 
In Reply to Dave Nance:

I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.   I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.

Dave Nance 

Dave Nance

Z/OS V10 -- Access path Frustration
(in response to Venkat Srinivasan)
I was considering using REOPT ONCE on the program tonight to see if maybe we get the correct access path at run time.
Dave Nance 



From: Venkat Srinivasan <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, December 15, 2016 3:09 PM
Subject: [DB2-L] - RE: Z/OS V10 -- Access path Frustration

What happens with reopt?Venkat
 
In Reply to Dave Nance:
I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.   I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.

Dave Nance 

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Venkat Srinivasan

RE: Z/OS V10 -- Access path Frustration
(in response to Dave Nance)

From what you are saying it should probably be reopt always aka old fashioned reopt vars. reopt once is for prepared statements. Are you preparing sql or static sql?

Venkat

In Reply to Dave Nance:

I was considering using REOPT ONCE on the program tonight to see if maybe we get the correct access path at run time.
Dave Nance 



From: Venkat Srinivasan <[login to unmask email]>
To: [login to unmask email]
Sent: Thursday, December 15, 2016 3:09 PM
Subject: [DB2-L] - RE: Z/OS V10 -- Access path Frustration

What happens with reopt?Venkat
 
In Reply to Dave Nance:
I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.   I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.

Dave Nance 

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

Muthuraj Kumaresan

Z/OS V10 -- Access path Frustration
(in response to Venkat Srinivasan)


When you say you explain the SQL or ran a dynamic SQL, did you use DELETE statement or SELECT statement using same WHERE predicates?



Sent from my iPhone

> On 16 Dec 2016, at 3:52 AM, Venkat Srinivasan <[login to unmask email]> wrote:
>
> From what you are saying it should probably be reopt always aka old fashioned reopt vars. reopt once is for prepared statements. Are you preparing sql or static sql?
>
> Venkat
>
> In Reply to Dave Nance:
>
> I was considering using REOPT ONCE on the program tonight to see if maybe we get the correct access path at run time.
> Dave Nance
>
>
>
> From: Venkat Srinivasan
> To: [login to unmask email]
> Sent: Thursday, December 15, 2016 3:09 PM
> Subject: [DB2-L] - RE: Z/OS V10 -- Access path Frustration
>
> What happens with reopt?Venkat
>
> In Reply to Dave Nance:
> I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds. I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.
>
> Dave Nance
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Walter Jani&#223;en

AW: Z/OS V10 -- Access path Frustration
(in response to Dave Nance)
Hi Dave

I also experienced similar things and never found a reason for that nor got any explanation from the optimizer team. Are there any optimizer recommendations? If so, that might get you to the same access path. I got confirmation though, if there are host variables, that the prepare can show a different access path than the execute really uses, because the prepare can only guess, but the execute has the exact values and explain always shows the access path for the prepared statement. But that could not be the reason here, because you said that this statement doesn’t have any host-variable.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 15. Dezember 2016 21:49
An: Venkat Srinivasan
Betreff: [DB2-L] - Z/OS V10 -- Access path Frustration

I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.
I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.


Dave Nance


-----End Original Message-----
Attachments

  • image001.png (2.6k)

Joe Geller

RE: AW: Z/OS V10 -- Access path Frustration
(in response to Walter Janißen)

Dave, I hate to ask an obvious question, but are you sure you are going against the exact same table?  Does the query reference a fully qualified table name or does it use an unqualified name? Is the Bind qualifier the same one you use in the dynamic query?

Joe

In Reply to Walter Janißen:

Hi Dave

I also experienced similar things and never found a reason for that nor got any explanation from the optimizer team. Are there any optimizer recommendations? If so, that might get you to the same access path. I got confirmation though, if there are host variables, that the prepare can show a different access path than the execute really uses, because the prepare can only guess, but the execute has the exact values and explain always shows the access path for the prepared statement. But that could not be the reason here, because you said that this statement doesn’t have any host-variable.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 15. Dezember 2016 21:49
An: Venkat Srinivasan
Betreff: [DB2-L] - Z/OS V10 -- Access path Frustration

I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.
I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.


Dave Nance


-----End Original Message-----

Terry Purcell

RE: AW: Z/OS V10 -- Access path Frustration
(in response to Walter Janißen)

Hi Dave (and Walter),

1st to Walter - I don't recall any situations where we have confirmed a problem access path, and not been able to provide an explanation. It is possible that DB2 level 2 and development cannot reproduce a problem, and a customer also cannot - in those situations we typically agree to revisit once the customer can reproduce - this is somewhat rare.

To Dave - there isn't enough information in this post for DB2 to provide an answer. I suggest you open a PMR to allow further investigation.

Regards

Terry Purcell

In Reply to Walter Janißen:

Hi Dave

I also experienced similar things and never found a reason for that nor got any explanation from the optimizer team. Are there any optimizer recommendations? If so, that might get you to the same access path. I got confirmation though, if there are host variables, that the prepare can show a different access path than the execute really uses, because the prepare can only guess, but the execute has the exact values and explain always shows the access path for the prepared statement. But that could not be the reason here, because you said that this statement doesn’t have any host-variable.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 15. Dezember 2016 21:49
An: Venkat Srinivasan
Betreff: [DB2-L] - Z/OS V10 -- Access path Frustration

I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.
I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.


Dave Nance


-----End Original Message-----

Dave Nance

AW: Z/OS V10 -- Access path Frustration
(in response to Joe Geller)
 Joe,   We only have the one schema, while the table is not fully qualified in the program, it does use the same qualifier from the bind statement.
Also, I was asked about the explain that I ran and the query I ran, I explained the same  DELETE statement in SPUFI and data studio as in the program. I, also, explained it as a SELECT and received the same index access as the delete in my explains, saw that there were 0 rows to be deleted as this query is looking for exceptions to the rule to clean them up, then ran the actual delete outside of the program as well. It is a larger table and as I said previously a little under 6% of the 75 Million rows qualify on that first column of the index and then the NOT EXISTS subselect is done against those qualified rows. The select and delete took about 20 seconds and change each. Where the normal almost daily run of the program with the tablespace scan is taking around 1000 seconds. Those are elapsed times.
Dave Nance


From: Joe Geller <[login to unmask email]>
To: [login to unmask email]
Sent: Friday, December 16, 2016 8:08 AM
Subject: [DB2-L] - RE: AW: Z/OS V10 -- Access path Frustration

Dave, I hate to ask an obvious question, but are you sure you are going against the exact same table?  Does the query reference a fully qualified table name or does it use an unqualified name? Is the Bind qualifier the same one you use in the dynamic query?Joe

In Reply to Walter Janißen:
Hi Dave

I also experienced similar things and never found a reason for that nor got any explanation from the optimizer team. Are there any optimizer recommendations? If so, that might get you to the same access path. I got confirmation though, if there are host variables, that the prepare can show a different access path than the execute really uses, because the prepare can only guess, but the execute has the exact values and explain always shows the access path for the prepared statement. But that could not be the reason here, because you said that this statement doesn’t have any host-variable.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Dave Nance [mailto:[login to unmask email]
Gesendet: Donnerstag, 15. Dezember 2016 21:49
An: Venkat Srinivasan
Betreff: [DB2-L] - Z/OS V10 -- Access path Frustration

I have this query in a program is performing a tablespace scan for a DELETE statement. I started reviewing it and saw it should really be index access match cols 1. I copied and pasted the query in data studio and explained it and it shows the access path I want. However, the program does not get same access path. There are no host variables in the query. There is a literal "< 0", qualifies just under 6% of the 75 million rows in the table and the column is the first column of the primary key, clustered index, and a not exists clause with a correlated subquery. The subquery gets matchcols = 2 as it should no matter where I explain the statement, however, the outer table, when I explain via data studio or SPUFI has matchcols = 1 on the desired index, but when explaining the exact statement in the program I continue to get TS scan. I have tried collecting stats in different manners. I have compared the bind options on spufi against the bind options of the program and have rebound the program, so that all of the bind options are the same and I still get a TS scan. I decided to use an OPTHINT, copied the rows from my run against SPUFI into the programs explain and rebound using the hint. The program was successfully rebound with a +394, stating that 1 query fully used the OPTHINT. However, I go review the program's access path and it shows non-matching index access(matchcols = 0). I tried running the job thinking that this access path might still be better, but it actually. took 3 times longer to run(3000 seconds), but when I run the query dynamically, with the MATCHCOLS = 1, it runs in 20 seconds.
I'm really at a loss now as to what my next step should be to get the desired access path. Any suggestions would be greatly appreciated. Thanks.


Dave Nance


-----End Original Message-----

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
---> Anaheim, California, April 30 - May 04, 2017 <---
http://www.idug.org/na

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2