Forums & Discussions Home

    A place for members, communities, and committees to have discussions online and via e-mail.
    Click a category or topic to below to start the conversation...

    You are currently in view only mode for this forum. Please click the appropriate below to login as a member and participate. If you are not a member, please CLICK HERE for more information.


    Nov 05
    2010

    DB2 9 partition load replace with large NPI

    Alison Pelletier
    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
    [FISA/City of New York]
    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]
    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
    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]
    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

    All Times America/New_York

    Copyright © 2014 IDUG. All Rights Reserved

    All material, files, logos and trademarks within this site are properties of their respective organizations.

    Terms of Service - Privacy Policy - Contact