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.


    Apr 29
    2005

    DB2 LOAD RESUME YES SHRLEVEL CHANGE

    Debra Anne Kopecky
    We are executing 5 parallel LOAD RESUME YES SHRLEVEL CHANGE jobs, each
    loading 5 million rows. This occurs 5 times per month. The final
    execution of the series of jobs ran for 40 hours elapsed and
    171.63minutes CPU each.

    We made several mistakes such as not executing a REORG on the data (only
    on the indexes) after each of the load series completed, and not having
    sufficient FREEPAGE (set at 31 for both data and indexes) and PCTFREE
    (set at 15 both data & indexes) The tablespace is nonsegmented/not
    partitioned. The table starts out empty and all data is loaded to the
    table via the LOAD RESUME YES SHRLEVEL CHANGE jobs.

    I have made freespace allocations for the new month's table at FREEPAGE
    15 (for both data and indexes) and PCTFREE = 40 for the indexes and
    PCTFREE = 30 for the data. And have a REORG scheduled after each series
    of loads. What I need to know is this going to be sufficient? What does
    DB2 do about the freespace settings when a new page is allocated? A
    traditional load creates the page and freepage with the defined values?
    I don't believe this is true with the SHRLEVEL CHANGE.

    OR Will the following be better?

    I thought about setting FREEPAGE/PCTFREE to 0 for the tablespace at the
    outset (the indexes would have the appropriate values of FREEPAGE 15
    PCTFREE 40.) This way the data pages at the end of the tablespace would
    be filled and written with deferred writes thus avoiding all the
    getpage/synchronous I/O and claim requests. But I'm not sure what would
    happen with the concurrent loads all writing to the last pages at the
    same time. What kind of contention would result? We're taking this
    approach for massive programmatic insert jobs executing in parallel
    since IBM recommended it. But is there really a difference? The load
    functions as an insert for the LOAD RESUME YES SHRLEVEL CHANGE.

    HELP!

    ---------------------------------------------------------------------------------
    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
    Debra Anne Kopecky
    OK change to the question I posted earlier.

    >>>>>>> Change to this comment <<<<<<<<

    I thought about setting FREEPAGE/PCTFREE to 0 for the tablespace at the
    outset (the indexes would have the appropriate values.) This was the data
    pages at the end of the tablespace would be filled and written with
    deferred writes thus avoiding all the getpage/synchronous I/O and claim
    requests. But I'm not sure what would happen with the concurrent loads all
    writing to the last pages at the same time. I know we're taking this 0/0
    approach for the eligibility loads but those are programmatic inserts.
    But is there really a difference? The load functions as an insert in this
    case.

    >>>>>>>>>>>>>>>>>>>>>>>>>>>>

    If the loads are executed serially then this would work. I realized that
    the approach of PCTFREE/FREEPAGE = 0 with parallel loads, was directed at
    a partitioned tablespace with the input files sorted and divided into
    partition key files. I relooked at PQ86037 and it also mentioned that
    the tablespace for this APAR was defined as MEMBER CLUSTER; I have an ETR
    into IBM asking if the PCTFREE/FREEPAGE = 0 technique will also skip
    scanning the spacemap pages if the tablespace is NOT defined as MEMBER
    CLUSTER (part of a data sharing environment.) I imagine the answer will
    be yes.


    Has anyone else run LOAD RESUME YES SHRLEVEL CHANGE using this technique in
    a non-data sharing environment?

    ---------------------------------------------------------------------------------
    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
    Phil Grainger
    [BMC Software]
    Hi Debra

    Some thoughts

    I guess you are running these loads in parallel to get them done faster,
    but as you say, they all have to be SHRLEVEL(CHANGE) otherwise they will
    contend with each other.

    If no other access is required to the data, have you timed a LOAD
    SHRLEVEL(NONE) with no parallelism just to see if it is actually slower
    - it might even be faster.

    Alternatively, have you thought about arbitrarily partitioning the
    table? If you can find a way to partition it such that the 5 load jobs
    will ONLY be loading data into one (or a few) partitions each with NO
    overlap where more than one job can load into the same partition, then
    you can do parallel SHRLEVEL(NONE) loads which will definately be faster
    than the SHRLEVEL(CHANGE) variety.

    I know that these are not necessarily answers to your questions, but
    they could be solutions to your problem!


    Phil Grainger
    Computer Associates
    Product Manager, DB2
    Tel: +44 (0)161 928 9334
    Fax: +44 (0)161 941 3775
    Mobile: +44 (0)7970 125 752
    [login to unmask email]


    -----Original Message-----
    From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
    Behalf Of Debra Anne Kopecky
    Sent: 29 April 2005 18:23
    To: [login to unmask email]
    Subject: [DB2-L] DB2 LOAD RESUME YES SHRLEVEL CHANGE

    We are executing 5 parallel LOAD RESUME YES SHRLEVEL CHANGE jobs, each
    loading 5 million rows. This occurs 5 times per month. The final
    execution of the series of jobs ran for 40 hours elapsed and
    171.63minutes CPU each.

    We made several mistakes such as not executing a REORG on the data (only
    on the indexes) after each of the load series completed, and not having
    sufficient FREEPAGE (set at 31 for both data and indexes) and PCTFREE
    (set at 15 both data & indexes) The tablespace is nonsegmented/not
    partitioned. The table starts out empty and all data is loaded to the
    table via the LOAD RESUME YES SHRLEVEL CHANGE jobs.

    I have made freespace allocations for the new month's table at FREEPAGE
    15 (for both data and indexes) and PCTFREE = 40 for the indexes and
    PCTFREE = 30 for the data. And have a REORG scheduled after each series
    of loads. What I need to know is this going to be sufficient? What does
    DB2 do about the freespace settings when a new page is allocated? A
    traditional load creates the page and freepage with the defined values?
    I don't believe this is true with the SHRLEVEL CHANGE.

    OR Will the following be better?

    I thought about setting FREEPAGE/PCTFREE to 0 for the tablespace at the
    outset (the indexes would have the appropriate values of FREEPAGE 15
    PCTFREE 40.) This way the data pages at the end of the tablespace would
    be filled and written with deferred writes thus avoiding all the
    getpage/synchronous I/O and claim requests. But I'm not sure what would
    happen with the concurrent loads all writing to the last pages at the
    same time. What kind of contention would result? We're taking this
    approach for massive programmatic insert jobs executing in parallel
    since IBM recommended it. But is there really a difference? The load
    functions as an insert for the LOAD RESUME YES SHRLEVEL CHANGE.

    HELP!

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

    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