CPU Increase for Inserts with DB2 v9 CM

Dave Petronella

CPU Increase for Inserts with DB2 v9 CM
We are wondering if anyone has seen an increase in CPU for inserts to
certain tables after a conversion from DB2 v8 to DB2 v9 CM. Specifically,
we are seeing as much as a 5x increase in CPU and up to 50% increase in
Getpages for inserts in the following situation:

- Tables are Table-partitioned on both v8 and v9 CM
- Tables have 2 partitions, with the higher percentage of Inserts
going to Partition #2
- Partitioning column for Part #1 allows only values starting
with "@"
- Partitioning column for Part #2 allows values starting with A -
Z and 0 - 9
- Partitions are utilizing Freepage/Pctfree of either 7/20 or
7/30 to allow for random inserts
- One table has a fixed row length (665)
- Two tables have a variable row length to due a VARCHAR column
in each
175 - 429 bytes for one table
823 - 919 bytes for another table
- Tables are not utilizing DB2 Compression
- All tables are being REORGed daily
- The DB2 is 2-way data sharing but all Insert activity is
coming from one member
- We see no significant lock/latch or global locking problems
- There is an increase in Getpages as well but not as high a
percentage increase as the CPU increase

Does anyone know of any open issues, PTF, etc, that relate to increases
in CPU on Inserts to Partitioned tables?

Thanks in advance for any feedback.

Dave.


________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.

******************************************************
IMPORTANT: Any information contained in this communication is intended for the use of the named individual or entity. All information contained in this communication is not intended or construed as an offer, solicitation, or a recommendation to purchase any security. Advice, suggestions or views presented in this communication are not necessarily those of Pershing LLC nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the sender and delete/destroy any and all copies of this communication. Unintended recipients shall not review, reproduce, disseminate nor disclose any information contained in this communication. Pershing LLC reserves the right to monitor and retain all incoming and outgoing communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC does not accept liability nor does it warrant that email communications are virus or defect free.
******************************************************

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* Your only source for independent, unbiased, and trusted DB2 information. *
** The most DB2 technical sessions of any conference
** Access IBM experts and developers
_____________________________________________________________________

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

Roy Boxwell

Re: CPU Increase for Inserts with DB2 v9 CM
(in response to Dave Petronella)
Hi dave!

This looks like a very good candidate from IBM

PM12935: POOR PERFORMANCE WITH SQL INSERTS IN CLASSIC PARTITION SINCE
TOOMANY SPACEMAPS SEARCHED TO FIND THE CANDIDATE PAGE
R810 PSY UK57871
UP10/07/02 P F007
R910 PSY UK57872
UP10/07/02 P F007


Roy Boxwell
SOFTWARE ENGINEERING GMBH
-Product Development-
Robert-Stolz-Straße 5
40470 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



Dave Petronella <[login to unmask email]>
Gesendet von: IDUG DB2-L <[login to unmask email]>
05.02.2011 14:58
Bitte antworten an
IDUG DB2-L <[login to unmask email]>


An
[login to unmask email]
Kopie

Thema
[DB2-L] CPU Increase for Inserts with DB2 v9 CM







We are wondering if anyone has seen an increase in CPU for inserts to
certain tables after a conversion from DB2 v8 to DB2 v9 CM. Specifically,
we are seeing as much as a 5x increase in CPU and up to 50% increase in
Getpages for inserts in the following situation:

- Tables are Table-partitioned on both v8 and v9 CM
- Tables have 2 partitions, with the higher percentage of Inserts
going to Partition #2
- Partitioning column for Part #1 allows only values starting
with "@"
- Partitioning column for Part #2 allows values starting with A -
Z and 0 - 9
- Partitions are utilizing Freepage/Pctfree of either 7/20 or
7/30 to allow for random inserts
- One table has a fixed row length (665)
- Two tables have a variable row length to due a VARCHAR column
in each
175 - 429 bytes for one table
823 - 919 bytes for another table
- Tables are not utilizing DB2 Compression
- All tables are being REORGed daily
- The DB2 is 2-way data sharing but all Insert activity is
coming from one member
- We see no significant lock/latch or global locking problems
- There is an increase in Getpages as well but not as high a
percentage increase as the CPU increase

Does anyone know of any open issues, PTF, etc, that relate to increases
in CPU on Inserts to Partitioned tables?

Thanks in advance for any feedback.

Dave.


________________________
David Petronella
Vice President
Data Resource Management / DB2 Database Administration
Pershing LLC, a BNY Mellon company
www.pershing.com
Office: (973) 360-7747
Fax: (973) 360-3121
Email: [login to unmask email]

Go Green. Think before you print.
******************************************************
IMPORTANT: Any information contained in this communication is intended for
the use of the named individual or entity. All information contained in
this communication is not intended or construed as an offer, solicitation,
or a recommendation to purchase any security. Advice, suggestions or views
presented in this communication are not necessarily those of Pershing LLC
nor do they warrant a complete or accurate statement.

If you are not an intended party to this communication, please notify the
sender and delete/destroy any and all copies of this communication.
Unintended recipients shall not review, reproduce, disseminate nor
disclose any information contained in this communication. Pershing LLC
reserves the right to monitor and retain all incoming and outgoing
communications as permitted by applicable law.

Email communications may contain viruses or other defects. Pershing LLC
does not accept liability nor does it warrant that email communications
are virus or defect free.
******************************************************



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 *
* Your only source for independent, unbiased, and trusted DB2 information. *
** DB2 certification -> no additional charge
** Meet fellow DB2 users and leading DB2 consultants
_____________________________________________________________________

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