DB2 9 partition load replace with large NPI

Alison Pelletier

DB2 9 partition load replace with large NPI
Hello, listers.

We migrated to DB2 9 for z/OS CM on 9/19. We are 2 data sharing members in production. One particular load partition replace job increased in cost by about 25%. Syntax is

LOAD DATA LOG NO SORTKEYS 1500000
- INTO TABLE xxx PART 43 REUSE REPLACE

I'm assuming because the NPI is now being rebuilt instead of updated, is the reason for the CPU increase. Actually, I only thought that would change for online load and reorg. The CPU increase is being captured in CPU_parallel. The index build is parallelizing, just as it was before DB2 9.

A week later the CPU jumped significantly to almost 3 times the original CPU...also recorded in CPU_parallel. It seems the huge jump happens when the load runs on one data sharing member....I'm assuming because most of the application processes are on the other data sharing member. The volume has not changed that much from day to day.

My questions...

1. Are my assumptions/theories correct in what I stated above?
2. Is there a way to get DB2 to not rebuild the entire NPI? This is shrlevel reference, so I don't believe there is concern about a short outage to the NPI.
3. Is there a way to keep the NPI from becoming GBP dependent during the load? (Alter to GBPCACHE none, perhaps?) Is that a bad idea?

Any help is appreciated.

Alison Pelletier
Discover Financial Services

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

Philip Sevetson

Re: DB2 9 partition load replace with large NPI
(in response to Alison Pelletier)
Alison,

0) Your name sounds familiar. Did you go to school in Minnesota (St. Paul) in the '70s or '80s?

1) Yes, the NPI is being rebuilt because you're specifying REPLACE with SHRLEVEL REFERENCE on the partition

2) No, you can't get DB2 not to rebuild the NPI unless you specify RESUME with SHRLEVEL CHANGE, which causes an "insert load" - you probably wouldn't like the CPU costs associated with that, either, but it's worth testing.

3) Someone else will have to take 3, as I haven't worked with datasharing in almost four years.



--Phil Sevetson



-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Alison Pelletier
Sent: Friday, November 05, 2010 5:50 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 9 partition load replace with large NPI



Hello, listers.



We migrated to DB2 9 for z/OS CM on 9/19. We are 2 data sharing members in production. One particular load partition replace job increased in cost by about 25%. Syntax is



LOAD DATA LOG NO SORTKEYS 1500000

- INTO TABLE xxx PART 43 REUSE REPLACE



I'm assuming because the NPI is now being rebuilt instead of updated, is the reason for the CPU increase. Actually, I only thought that would change for online load and reorg. The CPU increase is being captured in CPU_parallel. The index build is parallelizing, just as it was before DB2 9.



A week later the CPU jumped significantly to almost 3 times the original CPU...also recorded in CPU_parallel. It seems the huge jump happens when the load runs on one data sharing member....I'm assuming because most of the application processes are on the other data sharing member. The volume has not changed that much from day to day.



My questions...



1. Are my assumptions/theories correct in what I stated above?

2. Is there a way to get DB2 to not rebuild the entire NPI? This is shrlevel reference, so I don't believe there is concern about a short outage to the NPI.

3. Is there a way to keep the NPI from becoming GBP dependent during the load? (Alter to GBPCACHE none, perhaps?) Is that a bad idea?



Any help is appreciated.



Alison Pelletier

Discover Financial Services



_____________________________________________________________________

* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *

* Your only source for independent, unbiased, and trusted DB2 information. *

_____________________________________________________________________

http://www.IDUG.org/mentor

How can you expand your staff or do succession planning in this economy?

Mentoring is a proven, economical, way to train the next generation of DB2 Users!

_____________________________________________________________________



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

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* Your only source for independent, unbiased, and trusted DB2 information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2 Users!
_____________________________________________________________________

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

[login to unmask email]

Re: DB2 9 partition load replace with large NPI
(in response to Philip Sevetson)
Alison,

1.I agree with you that rebuild the entire NPI would use more CPU time,
25% is reasonable (actually depends on the number of partitions). Hope
the job elapesd time is reduced, which is the design objective.

2.No. But I haven't tried to stop/abend the load job after reload phase,
then rebuild NPI part manually.

3.Since you are in DB2 9, try ACCESS DB() SP() MODE(NGBPDEP) command.







Alison Pelletier <[login to unmask email]>
·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
2010-11-06 05:50
Çë´ð¸´ ¸ø
IDUG DB2-L <[login to unmask email]>


ÊÕ¼þÈË
[login to unmask email]
³­ËÍ

Ö÷Ìâ
[DB2-L] DB2 9 partition load replace with large NPI






Hello, listers.

We migrated to DB2 9 for z/OS CM on 9/19. We are 2 data sharing members
in production. One particular load partition replace job increased in
cost by about 25%. Syntax is

LOAD DATA LOG NO SORTKEYS 1500000
- INTO TABLE xxx PART 43 REUSE REPLACE

I'm assuming because the NPI is now being rebuilt instead of updated, is
the reason for the CPU increase. Actually, I only thought that would
change for online load and reorg. The CPU increase is being captured in
CPU_parallel. The index build is parallelizing, just as it was before DB2
9.

A week later the CPU jumped significantly to almost 3 times the original
CPU...also recorded in CPU_parallel. It seems the huge jump happens when
the load runs on one data sharing member....I'm assuming because most of
the application processes are on the other data sharing member. The
volume has not changed that much from day to day.

My questions...

1. Are my assumptions/theories correct in what I stated above?
2. Is there a way to get DB2 to not rebuild the entire NPI? This is
shrlevel reference, so I don't believe there is concern about a short
outage to the NPI.
3. Is there a way to keep the NPI from becoming GBP dependent during the
load? (Alter to GBPCACHE none, perhaps?) Is that a bad idea?

Any help is appreciated.

Alison Pelletier
Discover Financial Services

_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA
*
* Your only source for independent, unbiased, and trusted DB2
information. *
_____________________________________________________________________
http://www.IDUG.org/mentor
How can you expand your staff or do succession planning in this economy?
Mentoring is a proven, economical, way to train the next generation of DB2
Users!
_____________________________________________________________________

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



_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* 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

Alison Pelletier

Re: DB2 9 partition load replace with large NPI
(in response to hhuang@DCCSH.ICBC.COM.CN)
I mis-stated in my question 2. The SHRLEVEL is NONE, which is the default. Does that make a difference in your answer.? The ACCESS command certainly appears it will be helpful. I would like to issue that command on the NPI. Does that just flush the GBP of the NPI and then the whole NPI could become GBP dependent soon after if there is update to it by a process on the member the load is not running on?

Thank you very much for your help.

Alison
Discover Financial Services

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 * http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it! *
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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

[login to unmask email]

Re: DB2 9 partition load replace with large NPI
(in response to Alison Pelletier)
Alison,

1. REORG with SHRLEVEL NONE is an old-fashion method, there should be no
changes
between DB2 V8 and 9. So, I believe you can still use the traditional
method of
rebuilding logical part of NPI. But I have never tried it in DB2
9.(SHRLEVEL NONE,
a story many years ago)

2. For ACCESS command,
1)drain all readers and writers on all members, except the member where
you issue
the command.
2)drain writers on the member where you issue the command.
3)the object then be converted to NON-GBPDEP, changed pages be castout.
Finally, yes, the object will be immediately GBPDEP if any update from
other members.







Alison Pelletier <[login to unmask email]>
·¢¼þÈË: IDUG DB2-L <[login to unmask email]>
2010-11-08 22:39
Çë´ð¸´ ¸ø
IDUG DB2-L <[login to unmask email]>


ÊÕ¼þÈË
[login to unmask email]
³­ËÍ

Ö÷Ìâ
Re: [DB2-L] DB2 9 partition load replace with large NPI






I mis-stated in my question 2. The SHRLEVEL is NONE, which is the
default. Does that make a difference in your answer.? The ACCESS command
certainly appears it will be helpful. I would like to issue that command
on the NPI. Does that just flush the GBP of the NPI and then the whole
NPI could become GBP dependent soon after if there is update to it by a
process on the member the load is not running on?

Thank you very much for your help.

Alison
Discover Financial Services

_____________________________________________________________________
* IDUG North America * Anaheim, California * May 2-6 2011 *
http://IDUG.ORG/NA *
* If you are going to attend only one conference this year, this is it!
*
** The best DB2 technical sessions in the world
** Independent, not-for-profit, User Run - the IDUG difference!
_____________________________________________________________________

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



_____________________________________________________________________
* IDUG EMEA * Vienna, Austria * 8-12 November 2010 * http://IDUG.ORG/EMEA *
* 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