Performance in DB2 V11 on Z/OS

Carol Anne Sutfin

Performance in DB2 V11 on Z/OS
I have one of our programming teams complaining about the performance in their application.
I have an idea of what is wrong but currently prove it.

The evil vendor at one point decided to "partition" some of the tablespaces.
They did this by adding a new column to the tables of SEGID CHAR(6)

The primary key for these partitioned tables remained the same

COL1 DECIMAL (4)
COL2 DECIMAL (2)
COL3 CHAR (18)
COL4 DATE
COL5 DECIMAL (4)
COL6 DECIMAL (2)
COL7 CHAR (18)

COL1 has a cardinality of 4

SEGID is made up of the literal 'SEG' and the two low order numbers for COL1
SEGID therefore have a cardinality of 4 also.

SEGID is NOT indexed or referenced in any SQL.

In looking at the EXPLAIN output of about 50 SQL statements almost no indexes are used for accessing tables within the partitioned tablespaces.

I believe that this is the result of using a separate column for the partitioning scheme and not using the actual value in the Primary Index.

Any thoughts on the matter.

Daniel Luksetich

Performance in DB2 V11 on Z/OS
(in response to Carol Anne Sutfin)


I don't think that the answer is that simple. If the primary key remains
the same, then it must be an NPSI. If the queries use the leading
columns of that primary key in predicates then they would likely still
get index access whether or not the table is partitioned. It would be
interesting to see an example of a query that failed to use an index.

There could also be an issue with out of date statistics, and so a
RUNSTATS would be in order.

Cheers,

Dan

On 12.20.2016 08:40, Carol A. Sutfin wrote:

> I have one of our programming teams complaining about the performance in their application.
>
> I have an idea of what is wrong but currently prove it.
>
> The evil vendor at one point decided to "partition" some of the tablespaces.
>
> They did this by adding a new column to the tables of SEGID CHAR(6)
>
> The primary key for these partitioned tables remained the same
>
> COL1 DECIMAL (4)
>
> COL2 DECIMAL (2)
>
> COL3 CHAR (18)
>
> COL4 DATE
>
> COL5 DECIMAL (4)
>
> COL6 DECIMAL (2)
>
> COL7 CHAR (18)
>
> COL1 has a cardinality of 4
>
> SEGID is made up of the literal 'SEG' and the two low order numbers for COL1
>
> SEGID therefore have a cardinality of 4 also.
>
> SEGID is NOT indexed or referenced in any SQL.
>
> In looking at the EXPLAIN output of about 50 SQL statements almost no indexes are used for accessing tables within the partitioned tablespaces.
>
> I believe that this is the result of using a separate column for the partitioning scheme and not using the actual value in the Primary Index.
>
> Any thoughts on the matter.
>
> -----End Original Message-----


Links:
------
[1] http://www.idug.org/p/fo/st/post=179435&anc=p179435#p179435
[2] http://www.idug.org/p/fo/si/topic=19
[3] http://www.idug.org/p/us/to/
[4] http://www.idug.org/na
[5] http://www.idug.org/p/cm/ld/fid=2

Dave Nance

Performance in DB2 V11 on Z/OS
(in response to Carol Anne Sutfin)
  This segid doesn't have to be in an index to get partition independence,  but it does have to be referenced in the SQL. As you mentioned it's not referenced in any SQL so even having it in an index would be of no use to you.   How about this as a workaround? Rename your tables, create a view with the existing name. Since you stated that segid is based on col1, use the view to translate into the segid. 

Sent from Yahoo Mail on Android

On Tue, Dec 20, 2016 at 10:52, Carol A. Sutfin<[login to unmask email]> wrote:
I have one of our programming teams complaining about the performance in their application. 

I have an idea of what is wrong but currently prove it.  

 

The evil vendor at one point decided to “partition” some of the tablespaces.  

They did this by adding a new column to the tables of SEGID CHAR(6)

 

The primary key for these partitioned tables remained the same  

 

COL1  DECIMAL (4)

COL2  DECIMAL (2)

COL3  CHAR (18)

COL4  DATE

COL5  DECIMAL (4)

COL6  DECIMAL (2)

COL7  CHAR (18)

 

COL1 has a cardinality of 4

 

SEGID  is made up of the literal ‘SEG’ and the two low order numbers for COL1

SEGID therefore have a cardinality of 4 also.  

 

SEGID is NOT indexed or referenced in any SQL.  

 

In looking at the EXPLAIN output of about 50 SQL statements almost no indexes are used for accessing tables within the partitioned tablespaces. 

 

I believe that this is the result of using a separate column for the partitioning scheme and not using the actual value in the Primary Index. 

 

Any thoughts on the matter.      

 

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

Dave Nance

Performance in DB2 V11 on Z/OS
(in response to Dave Nance)
I forgot to mention that you should change your primary key index to a dpsi if you try this approach

Sent from Yahoo Mail on Android

On Tue, Dec 20, 2016 at 11:07, Dave Nance<[login to unmask email]> wrote:   This segid doesn't have to be in an index to get partition independence,  but it does have to be referenced in the SQL. As you mentioned it's not referenced in any SQL so even having it in an index would be of no use to you.   How about this as a workaround? Rename your tables, create a view with the existing name. Since you stated that segid is based on col1, use the view to translate into the segid. 

Sent from Yahoo Mail on Android

On Tue, Dec 20, 2016 at 10:52, Carol A. Sutfin<[login to unmask email]> wrote:
I have one of our programming teams complaining about the performance in their application. 

I have an idea of what is wrong but currently prove it.  

 

The evil vendor at one point decided to “partition” some of the tablespaces.  

They did this by adding a new column to the tables of SEGID CHAR(6)

 

The primary key for these partitioned tables remained the same  

 

COL1  DECIMAL (4)

COL2  DECIMAL (2)

COL3  CHAR (18)

COL4  DATE

COL5  DECIMAL (4)

COL6  DECIMAL (2)

COL7  CHAR (18)

 

COL1 has a cardinality of 4

 

SEGID  is made up of the literal ‘SEG’ and the two low order numbers for COL1

SEGID therefore have a cardinality of 4 also.  

 

SEGID is NOT indexed or referenced in any SQL.  

 

In looking at the EXPLAIN output of about 50 SQL statements almost no indexes are used for accessing tables within the partitioned tablespaces. 

 

I believe that this is the result of using a separate column for the partitioning scheme and not using the actual value in the Primary Index. 

 

Any thoughts on the matter.      

 

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

Venkat Srinivasan

RE: Performance in DB2 V11 on Z/OS
(in response to Carol Anne Sutfin)

What was the original access path? If there was index access before the change there should still be an index access. Perhaps you are now opening more datasets than what it used to be?......

Venkat

In Reply to Carol Anne Sutfin:

I have one of our programming teams complaining about the performance in their application.
I have an idea of what is wrong but currently prove it.

The evil vendor at one point decided to "partition" some of the tablespaces.
They did this by adding a new column to the tables of SEGID CHAR(6)

The primary key for these partitioned tables remained the same

COL1 DECIMAL (4)
COL2 DECIMAL (2)
COL3 CHAR (18)
COL4 DATE
COL5 DECIMAL (4)
COL6 DECIMAL (2)
COL7 CHAR (18)

COL1 has a cardinality of 4

SEGID is made up of the literal 'SEG' and the two low order numbers for COL1
SEGID therefore have a cardinality of 4 also.

SEGID is NOT indexed or referenced in any SQL.

In looking at the EXPLAIN output of about 50 SQL statements almost no indexes are used for accessing tables within the partitioned tablespaces.

I believe that this is the result of using a separate column for the partitioning scheme and not using the actual value in the Primary Index.

Any thoughts on the matter.

Terry Purcell

RE: Performance in DB2 V11 on Z/OS
(in response to Carol Anne Sutfin)

Hi Carol,

I believe some of the comments up to this point are assuming that this is an arbitrary change to the table partitioning, and the index remains as it was originally - although as Dan points out, it would need to be a non-partitioned index if the partitioning column(s) are not contained in the index.

Assuming that is true - the index didn't change columns and its now a NPI/NPSI, the next thing to look at is whether taking those last 2 digits results in the rows being dispersed to different partitions. If so, the clusterratio of that primary index may have plummeted - even if it is still defined as CLUSTERing. I would look 1st at the CLUSTERRATIOF to see if the partitioning has impacted that. As has been implied, if this is simple OLTP queries (few rows qualified), then a clustering change wont impact much, but if many rows qualified - this will have an impact.

So it's either as mentioned above - the clusterratio has dropped and the queries qualify a lot of rows - making a poorly clustered index a potentially bad choice, OR the indexes have changed and they added the irrelevant column as leading (and no queries can match on any columns now), OR they made the index PARTITIONED (which means adding that column somewhere - maybe last) and its a DPSI now which is less attractive.

So you asked for thoughts - I would start with the above possibilities and confirm or rule out each. If none - then more detail is needed to help.

Regards

Terry Purcell

In Reply to Carol Anne Sutfin:

I have one of our programming teams complaining about the performance in their application.
I have an idea of what is wrong but currently prove it.

The evil vendor at one point decided to "partition" some of the tablespaces.
They did this by adding a new column to the tables of SEGID CHAR(6)

The primary key for these partitioned tables remained the same

COL1 DECIMAL (4)
COL2 DECIMAL (2)
COL3 CHAR (18)
COL4 DATE
COL5 DECIMAL (4)
COL6 DECIMAL (2)
COL7 CHAR (18)

COL1 has a cardinality of 4

SEGID is made up of the literal 'SEG' and the two low order numbers for COL1
SEGID therefore have a cardinality of 4 also.

SEGID is NOT indexed or referenced in any SQL.

In looking at the EXPLAIN output of about 50 SQL statements almost no indexes are used for accessing tables within the partitioned tablespaces.

I believe that this is the result of using a separate column for the partitioning scheme and not using the actual value in the Primary Index.

Any thoughts on the matter.