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.


    Sep 28
    2005

    Resarting a LOAD utility after an abend DB2V8 ZOS

    Mike Backes
    [State of Missouri]
    Heres the situation.
    Loading approx 200,000 rows into a 60 million row partitioned
    table/space resume yes.
    The reload phase completes indicating all rows were loaded to the
    table/space,
    Then dfsort thows a message during the sort phase that states sort
    capacity exceeded, abeding the utility with a s04e.
    When the utility was cancelled the the tablespace was fine but all of
    the indexes were in rebuild pending, which once done
    Completed the load basically.

    I am of the opinion that terminating the utility and rebuilding the
    indexes in this way was the only way to proceed with this load, without
    the obvious restore and try again.

    Other staff in the office are asserting that I could have done a
    restart(current) or a restart(phase) of the utility after correcting the
    space issue with the sortwrks. However the manual on restarting a load
    utility has two notes related to table 42, on restarting in the sort
    phase.
    4. The utility can be restarted with either RESTART or RESTART(PHASE).
    However, because this phase does not take checkpoints, RESTART is always
    re-executed from the beginning of the phase.
    10. |Any job that finished abnormally in the RELOAD, SORT, BUILD, |or
    SORTBUILD phase restarts from the beginning of the RELOAD phase.

    Which means to my way of thinking that it would reload 200,000
    duplicate rows, and really mess up the build index phase because there
    would be 200,000 rows on the table with no keys to put into the index.
    which basically means there is no restart capability for a load utility.

    Am I interpreting this right or am I missing something?

    Mike Backes
    ph: 522-5829



    ****************************
    CONFIDENTIALITY STATEMENT:
    This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system.

    ---------------------------------------------------------------------------------
    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
    Hanne Lyssand
    [Verdipapirsentralen ASA]
    I would go for the rebuild of indexes, and since sort capasity seems to be
    the problem, to do one index at the time.
    A restart will not help because You have the to unload and sort of index
    records anyway.

    best regards
    Hanne



    **********************************************************************
    For Your service, this message has been checked for viruses
    http://www.vps.no
    **********************************************************************

    ---------------------------------------------------------------------------------
    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
    Mark McCormack
    [State Street Corp.]
    Mike,

    I assume you are asking about Load Shrlevel None and failure after the
    reload phase. (Shrlevel Change is a different beast). Your situation may
    or may not be more complicated than you describe.

    Do you need the processing in the indexval, enforce, discard, or report
    phases?
    In other words, are either of these conditions possible:
    1. rows with duplicate keys, where a uniques index disallows this.
    2. referential integrity violations (children with no parents).

    The load utility adds to the table every row that passes the format edit.
    Rows that should be disallowed are added anyway.
    1. Disallowed duplicates are not detected and eliminated until the sort (or
    sortbld) and indexval phases.
    2. RI violations are not resolved until the enforce phase.

    If you have problems in either area, then terminate load / rebuild indices
    may not work. If you do not need to worry about duplicates or RI, then
    term and rebuild is a viable strategy.

    Once the reload phase is successfully complete, and failure occurs in sort
    or build, you may or may not be able to restart the load. It depends on
    whether you have sysut1 and sortout files which survive the failure. If
    so, then you can restart in the failed phase. Specify either RESTART or
    RESTART(PHASE). The result will be the same: the entire phase will be
    rerun. You will save the time and effort to recover and the time to rerun
    the entire load from the beginning. If you use parameter SORTKEYS, then
    there are no sysut1 and sortout files, and restart in those phases is not
    possible. I don't use SORTKEYS with the Load utility for that reason.
    (But I do use SORTKEYS with reorg).

    Since Load handles table rows and index entries in different phases, it is
    necessary upon failure to get the data back in sync with the indices (at
    the very least), so you are wise to have made such plans.

    The worst table screwup I ever saw involved a botched restart of Load
    Resume Yes Shrlevel None.
    1. Load resume (not Load replace, which should have been used).
    2. Reload phase adds 1M+ rows.
    3. Sort fails on sortworks too small.
    At this point the table has 1M+ rows; the indices are empty.
    4. Term Load / fix sortworks / rerun Load resume from the beginning.
    <--worst mistake possible
    5. Same 1M+ rows added again.
    6. Sort and Build run without failing. Rows from original run are not
    indexed; rows from rerun are indexed.
    7. Zero duplicates detected (duplicate detection based on indices).
    8. Reorg attempted months later. 1M+ duplicates on unique index. It took
    a month to fix this mess.
    Please don't do this.

    Mark

    ---------------------------------------------------------------------------------
    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
    Jim Ruddy
    [IBM]
    If you have any unique indexes, cheek contraints, or RI contraints and you
    allowed discarding then you should have restarted the LOAD. If you get any
    uniqueness violations during REBUILD you get to resolve them manually with
    REPAIR. If you have any check or RI constraints, you need to run CHECK DATA.

    Jim Ruddy
    DB2 for z/OS Development.

    ---------------------------------------------------------------------------------
    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
    Mike Backes
    [State of Missouri]
    Mark, yes that's what im talking about load shrlevel none.
    There are no dup keys or ri in the load so the conditions you mentioned
    do not come into play.
    What we are doing is a load resume yes as you mentioned of around 200k
    rows into a 60mil row table(ie. Replace is not an option). I guess I
    wasn't asking if term util and rebuild was a viable option, I guess I
    was saying, that according to my reading of the load restart manual,
    it's the only option, after a sucessful reload and an abend in the sort
    phase due to the fact that either a restart(current) or restart(phase)
    will start the utility from the beginning of the reload step regardless.
    In effect creating the situation that you described in your worst case
    example. At least the way I read table 42, term util and rebuild is the
    only answer. Short of recovering and rebuilding and trying again.

    If not can you explain it to me how you would restart the load and avoid
    the problem you mentioned? Because even though you say this 'If so, then
    you can restart in the failed phase. ' note 10 from table 42 says
    otherwise. I would agree with you that you could restart in the middle
    of the reload phase if your files survived, but once that is done, you
    cant restart without messing up the table.

    Am I missing something?


    Mike Backes
    ph: 522-5829
    =

    Once the reload phase is successfully complete, and failure occurs in
    sort or build, you may or may not be able to restart the load. It
    depends on whether you have sysut1 and sortout files which survive the
    failure. If so, then you can restart in the failed phase. Specify
    either RESTART or RESTART(PHASE). The result will be the same: the
    entire phase will be rerun. You will save the time and effort to
    recover and the time to rerun the entire load from the beginning. If
    you use parameter SORTKEYS, then there are no sysut1 and sortout files,
    and restart in those phases is not possible. I don't use SORTKEYS with
    the Load utility for that reason.
    (But I do use SORTKEYS with reorg).

    Since Load handles table rows and index entries in different phases, it
    is necessary upon failure to get the data back in sync with the indices
    (at the very least), so you are wise to have made such plans.

    The worst table screwup I ever saw involved a botched restart of Load
    Resume Yes Shrlevel None.
    1. Load resume (not Load replace, which should have been used).
    2. Reload phase adds 1M+ rows.
    3. Sort fails on sortworks too small.
    At this point the table has 1M+ rows; the indices are empty.
    4. Term Load / fix sortworks / rerun Load resume from the beginning.
    <--worst mistake possible
    5. Same 1M+ rows added again.
    6. Sort and Build run without failing. Rows from original run are not
    indexed; rows from rerun are indexed.
    7. Zero duplicates detected (duplicate detection based on indices).
    8. Reorg attempted months later. 1M+ duplicates on unique index. It
    took a month to fix this mess.
    Please don't do this.

    Mark


    ***********************************
    CONFIDENTIALITY STATEMENT:
    This e-mail and any attachments are intended only for those to which it is addressed and may contain information which is privileged, confidential and prohibited from disclosure and unauthorized use under applicable law. If you are not the intended recipient of this e-mail, you are hereby notified that any use, dissemination, or copying of this e-mail or the information contained in this e-mail is strictly prohibited by the sender. If you have received this transmission in error, please return the material received to the sender and delete all copies from your system.



    ---------------------------------------------------------------------------------
    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
    Mark McCormack
    [State Street Corp.]
    Mike,

    I have reread several times the notes to the restart instructions in both
    the v7 and v8 manuals, and I now notice some differences I didn't see
    before. I am now as puzzled as you are about restart that would go back to
    the reload phase.

    The points about duplicates and RI are valid, as Jim Ruddy has confirmed.
    My other suggestions seem to me to be accurate for load replace but not for
    load resume yes shrlevel none.

    It appears that some of the restrictions on restart whle using SORTKEYS are
    removed. Perhaps that is involved. But that is speculation on my part
    and not helpful. So I apologize for my inaccurate response.

    Mark

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