NPI question

ROY SHAFER

NPI question
We have a partitioned table that we reorg each month and purge data from the table by doing reorg with discards on each partition of the tablespace. The table, prior to a recent change, had 1 partitioning index and 1 NPI. Recently we added a second NPI because of some performance issues we were facing.
The table has 160 million records in the 72 partition table. When we reorg the tables we run 8 jobs at the same time and then work through the all of the partitions.
For example job 1 reorgs parts 1-9 at the same time job 2 reorgs parts 10-18 an so on.

This last month when we ran the reorgs, they ran 5 times as long and the NPI's were contending all over the place and we had a lot of job failures. This was the first reorg since we added the 2nd NPI. We didn't realize adding the 2nd NPI would have such an impact on reorg with discards.

Version 7, Z/OS...

Any suggestions other than trying to reorg the entire tablespace at one time. We probably don't have enough resources for that.

Thanks....JDC




---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Al Greuter

Re: NPI question
(in response to ROY SHAFER)
I would suggest dropping the NPI's and then running your reorgs. Then
recreate the NPI's and rebuild. Finally rebind your packages.

That will be significantly faster.



Thanks,

Al Greuter
Lockheed Martin
DB2 Database Administrator
(410) 496-9547

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Shafer Roy E
Sent: Friday, December 10, 2004 1:38 PM
To: [login to unmask email]
Subject: NPI question

We have a partitioned table that we reorg each month and purge data from the
table by doing reorg with discards on each partition of the tablespace. The
table, prior to a recent change, had 1 partitioning index and 1 NPI.
Recently we added a second NPI because of some performance issues we were
facing.
The table has 160 million records in the 72 partition table. When we reorg
the tables we run 8 jobs at the same time and then work through the all of
the partitions.
For example job 1 reorgs parts 1-9 at the same time job 2 reorgs parts 10-18
an so on.

This last month when we ran the reorgs, they ran 5 times as long and the
NPI's were contending all over the place and we had a lot of job failures.
This was the first reorg since we added the 2nd NPI. We didn't realize
adding the 2nd NPI would have such an impact on reorg with discards.

Version 7, Z/OS...

Any suggestions other than trying to reorg the entire tablespace at one
time. We probably don't have enough resources for that.

Thanks....JDC




----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

John Jurban

Re: NPI question
(in response to Al Greuter)
NPI's reference rows in all of the partitions, they can have a significant
impact upon the time to do a reorg of a single partition. Because a reorg of
a partition would be doing a load replace and the RIDS pointing to the
replaced rows be deleted and RIDS pointing to the new rows be inserted. The
load of the second to the last partitions would require inserts of the RIDS.
The inserts are similar to an insert statement - as a result the reorg of a
single partition with NPI's can take almost as long as a reorg of the entire
tablespace.

John Jurban
Database Administrator
Compuware Corporation


-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On
Behalf Of Greuter, Al
Sent: Friday, December 10, 2004 1:55 PM
To: [login to unmask email]
Subject: Re: NPI question


I would suggest dropping the NPI's and then running your reorgs. Then
recreate the NPI's and rebuild. Finally rebind your packages.

That will be significantly faster.



Thanks,

Al Greuter
Lockheed Martin
DB2 Database Administrator
(410) 496-9547

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of Shafer Roy E
Sent: Friday, December 10, 2004 1:38 PM
To: [login to unmask email]
Subject: NPI question

We have a partitioned table that we reorg each month and purge data from the
table by doing reorg with discards on each partition of the tablespace. The
table, prior to a recent change, had 1 partitioning index and 1 NPI.
Recently we added a second NPI because of some performance issues we were
facing.
The table has 160 million records in the 72 partition table. When we reorg
the tables we run 8 jobs at the same time and then work through the all of
the partitions.
For example job 1 reorgs parts 1-9 at the same time job 2 reorgs parts 10-18
an so on.

This last month when we ran the reorgs, they ran 5 times as long and the
NPI's were contending all over the place and we had a lot of job failures.
This was the first reorg since we added the 2nd NPI. We didn't realize
adding the 2nd NPI would have such an impact on reorg with discards.

Version 7, Z/OS...

Any suggestions other than trying to reorg the entire tablespace at one
time. We probably don't have enough resources for that.

Thanks....JDC




----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

----------------------------------------------------------------------------
-----
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home
page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select
"Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org.
The IDUG List Admins can be reached at [login to unmask email] Find
out the latest on IDUG conferences at http://conferences.idug.org/index.cfm



The contents of this e-mail are intended for the named addressee only. It
contains information that may be confidential. Unless you are the named
addressee or an authorized designee, you may not copy or use it, or disclose
it to anyone else. If you received it in error please notify us immediately
and then destroy it.

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm