Z/OS DB2 V10 -- Access path issue

Dave Nance

Z/OS DB2 V10 -- Access path issue
 Hi all,   Any help appreciated. Yes, I know V10 is out of support and we are in process of migrating to 11.
I have this query when I run as a SELECT I get a particular access path and run time is always consistent, when I change it to perform an UPDATE, the access path changes and I am having difficulty getting it to use the same access path as the SELECT
Query goes like this:
select col1,col2,(select col3 from tablea c                         where a.col = c.col                        fetch first 1 row only)   from tablea awhere some conditions....
  and exists (select col3 from tablea c                         where a.col = c.col)
   The subselect is identical in the select clause and the exists clause. I get the same index access the same on both subselects, index 7 matching 2 cols.
Now, I change the query to an UPDATE statement:
UPDATE tablea a   set  col1= ?,col2 = ?, col3 = (select col3 from tablea c                         where a.col = c.col                        fetch first 1 row only)where some conditions....
  and exists (select col3 from tablea c                         where a.col = c.col)
Everything the same except instead of selecting the values, we are setting the values. This time the EXISTS clause still uses index 7 matching 2 cols, but the subselect in the SET clause uses index 5 matching 1 col and runs forever. I used a No OP on the column for index 5(CONCAT '') and then it went to index 6 matching 1 column, I used the same No OP on this column and then got a tablespace scan. No matter what change I make in the subselect in the SET clause, I cannot get index 7 matching 2 cols, any cols for that matter.

David Nance

Dave Nance

Z/OS DB2 V10 -- Access path issue
(in response to Dave Nance)
   One more item from Visual Explain, something I left out of original. The columns in index 7 are index on an expression(substr(col3,1,15)) I get matching index when I run as an a select, but when I convert to the UPDATE, these predicates are now stage 2. The columns being updated are not in my indexes, so not sure why changing from select to insert I would go from stage 1 indexable to stage 2 not indexable.David Nance


From: Dave Nance <[login to unmask email]>
To: Michael Hannan <[login to unmask email]>
Sent: Tuesday, February 27, 2018 11:21 AM
Subject: Z/OS DB2 V10 -- Access path issue

 Hi all,   Any help appreciated. Yes, I know V10 is out of support and we are in process of migrating to 11.
I have this query when I run as a SELECT I get a particular access path and run time is always consistent, when I change it to perform an UPDATE, the access path changes and I am having difficulty getting it to use the same access path as the SELECT
Query goes like this:
select col1,col2,(select col3 from tablea c                         where a.col = c.col                        fetch first 1 row only)   from tablea awhere some conditions....
  and exists (select col3 from tablea c                         where a.col = c.col)
   The subselect is identical in the select clause and the exists clause. I get the same index access the same on both subselects, index 7 matching 2 cols.
Now, I change the query to an UPDATE statement:
UPDATE tablea a   set  col1= ?,col2 = ?, col3 = (select col3 from tablea c                         where a.col = c.col                        fetch first 1 row only)where some conditions....
  and exists (select col3 from tablea c                         where a.col = c.col)
Everything the same except instead of selecting the values, we are setting the values. This time the EXISTS clause still uses index 7 matching 2 cols, but the subselect in the SET clause uses index 5 matching 1 col and runs forever. I used a No OP on the column for index 5(CONCAT '') and then it went to index 6 matching 1 column, I used the same No OP on this column and then got a tablespace scan. No matter what change I make in the subselect in the SET clause, I cannot get index 7 matching 2 cols, any cols for that matter.

David Nance

Peter Vanroose

Re: Z/OS DB2 V10 -- Access path issue
(in response to Dave Nance)

Not 100% sure whether it's the case here, but I've seen similar things before.

What's probably happening is this:

When modifying multiple data rows (UPDATE), based on derived data, Db2 always makes sure that this derived data does not change while processing the rows. So that it can guarantee (as is required for an RDBMS) that the final outcome is not influenced by the order in which the rows to be updated are processed.

The case I was thinking of (which is different from yours, but where a similar access path change will be observed between SELECT and UPDATE) went as follows (much simplified here):

UPDATE my_table SET col1 = col1+10 WHERE col1 > 20

The corresponding select would of course use the index on col1 (even index-only).

The update should not use that index (or actually: will do list prefetch, which also means it runs through "frozen" index data) since that index is automatically made up-to-date with the update that just happened, so the same entry would appear again, further down the index, and be updated again (and again: infinite loop!)
[ Btw: a WHERE col1 < 1000 would even be worse: not an infinite loop, which would be a timeout & rollback, but plainly a wrong update end result: all col1 values would have become slightly larger than 1000 ! ]

In Reply to Dave Nance:

I have this query when I run as a SELECT I get a particular access path and run time is always consistent, when I change it to perform an UPDATE, the access path changes and I am having difficulty getting it to use the same access path as the SELECT

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        http://www.abis.be/

Michael Hannan

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

David,
You are hiding information from us. In your original SQLs, both subqueries had only 1 equals predicate. Therefore only only 1 Match Col is possible. Obviously you removed additional predicates especially those containing expressions. You need to show exactly what columns are in Index 5 and 7. Even the Data types of relevant columns might be important.

I have never tried to use Index On Expression for UPDATEs. It my not work there are "certain restrictions". One restriction detailed in the manuals: "DB2 does not use expression-based indexes for queries that use sensitive static scrollable cursors." UPDATEs were not specifically mentioned.

Its possible that setting col3 = (subquery on same table) is causing disqualification of Index on Expression. Like Peter, cannot be certain. I would have to be running quite a few trial explains of different options myself.

To make a proper assessment of an SQL, sometimes we need to see all of the code in at least some subqueries of it. Too much simplification of the SQL can just spoil it, unfortunately.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 28, 2018 - 01:25 PM (Europe/Berlin)
Michael Hannan[Organization Members] @ Feb 28, 2018 - 01:32 PM (Europe/Berlin)