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.


    Kathleen Lisle
    [GMAC Insurance]
    Following up on last week's MODIFY discussion, here's a situation found by
    two of my former colleagues. They forwarded it to me, and, with permission,
    I'm passing it on to you.

    -----Original Message-----
    From: Steve Carson [mailto:[login to unmask email]
    Sent: Friday, May 12, 2000 10:13 AM
    To: [login to unmask email]
    Subject: Here's a gotcha


    Just thought I'd brighten your day a little.

    Scenario:

    Two tablespaces exist in a DB2 subsystem, both partitioned. The
    tablespaces have been in existence for 2 years. They were completely
    refreshed prior to production use. One of the tablespaces, call it TSP1,
    used LOAD REPLACE LOG YES. The other one, TSP2, used LOAD RESUME
    YES...PART X REPLACE. All the usual utilities have been running for the
    full time. Image copy jobs are done at the partition level. All copy
    jobs have MODIFY RECOVERY as the last step.

    Problem:

    The copy jobs for tablespace TSP2 periodically complete with condition
    code 4 in the last step, but are most often 0. When a 4 is returned, the
    message is "No backup copies found for this tablespace or dataset" for
    the MODIFY RECOVERY. All copy jobs for tablespace TSP1 consistently
    complete with condition code 4 in the last step. The message returned is
    "Modify has not deleted all SYSCOPY records as requested" which is
    followed by "No backup copies found for this tablespace or dataset." The
    syntax is identical, except for the tablespace name, and correct for all
    MODIFY RECOVERY statements.

    Looking at SYSCOPY shows a complete list of all utilities ever run
    against TSP1 since it was refreshed just prior to production use 2 years
    ago. The SYSCOPY listing for TSP2 is quite different. All utilities are
    shown going back only 30 days, the MODIFY RECOVERY DELETE AGE. The only
    utility going back 2 years is QUIESCE. All others are full image copies
    and REORG LOG NO.

    Question:

    Why does the TSP1 MODIFY RECOVERY not remove all the old utility entries
    from SYSCOPY like the TSP2 statements do?

    Answer:

    The LOAD REPLACE LOG YES for TSP1 used to refresh the table is what is
    causing the problem. The SYSCOPY entry has ICTYPE R and a DSNUM of 0
    since the LOAD was done at the table level rather than the partition
    level. Even though good image copies have been taken at the partition
    level, DB2 believes it does not have a valid recovery point after the
    LOAD since the LOAD was done at the full table level, DSNUM of 0.

    LOAD RESUME YES...PART X REPLACE allows the MODIFY to work correctly
    because recovery points are established at the partition level rather
    than the tablespace or full tablespace.

    Have not yet figured out how to fix it without impacting somebody. May
    just have to bite the bullet and have it go into COPYP and have a repair
    ready to flip the flag.

    Fun huh? Bet you that's not something they tell you in DBA class..



    Michael Ebert
    To explain why this is so, let's take a simple example: you have a partitioned
    TS. 30 days ago you took a full TS IC (DSNUM 0). After that, you took daily ICs
    of partition 2 only (DSNUM 2). Now you run a MODIFY TABLESPACE ... DSNUM 2
    DELETE AGE(15). You will get the "No SYSCOPY records removed" message.
    MODIFY cleans up SYSCOPY and SYSLGRNX. So if you remove entries older than 15
    days concerning partition 2, you cannot do a recovery to a point before. But you
    still have the DSNUM 0 IC, and because this implicitly includes info for
    partition 2, you would get an inconsistency - you cannot "punch out" the DSNUM 2
    info from the DSNUM 0 IC. So DB2 ignores your request.

    A different possibility would have been to remove the DSNUM 2 entries from
    SYSCOPY, but leave the info in SYSLGRNX back to the oldest DSNUM 0 IC,
    effectively creating the situation you would have if you hadn't taken the DSNUM
    2 ICs 16 to 29 days ago in the first place - but the DB2 developers chose the
    easier path.

    And that's why the Util Guide says "ICs should be made either by table space or
    by partition, but not by both" (p. 2-60, COPY utility), even though they don't
    explain.

    Dr. Michael Ebert
    DB2 Database Administrator
    aMaDEUS Data Processing
    Erding / Munich, Germany




    From: "Lisle, Kathy" <[login to unmask email]> on 17/05/2000 16:20 GMT

    Please respond to DB2 Data Base Discussion List <[login to unmask email]>





    |--------->
    | |
    |--------->
    >--------------------------------------------------------------------------->
    | |
    >--------------------------------------------------------------------------->
    >-------------------------------------------|
    | |
    >-------------------------------------------|
    |--------->
    |To: |
    |--------->
    >--------------------------------------------------------------------------->
    |[login to unmask email] |
    >--------------------------------------------------------------------------->
    >-------------------------------------------|
    | |
    | |
    >-------------------------------------------|
    |--------->
    |cc: |
    |--------->
    >--------------------------------------------------------------------------->
    | (bcc: Michael Ebert/MUC/AMADEUS) |
    >--------------------------------------------------------------------------->
    >-------------------------------------------|
    | |
    | |
    >-------------------------------------------|
    |--------->
    | |
    |--------->
    >--------------------------------------------------------------------------->
    | |
    >--------------------------------------------------------------------------->
    >-------------------------------------------|
    | |
    >-------------------------------------------|
    |--------->
    |Subject: |
    |--------->
    >--------------------------------------------------------------------------->
    |FW: Here's a gotcha |
    >--------------------------------------------------------------------------->
    >-------------------------------------------|
    | |
    >-------------------------------------------|







    Following up on last week's MODIFY discussion, here's a situation found by
    two of my former colleagues. They forwarded it to me, and, with permission,
    I'm passing it on to you.

    -----Original Message-----
    From: Steve Carson [mailto:[login to unmask email]
    Sent: Friday, May 12, 2000 10:13 AM
    To: [login to unmask email]
    Subject: Here's a gotcha


    Just thought I'd brighten your day a little.

    Scenario:

    Two tablespaces exist in a DB2 subsystem, both partitioned. The
    tablespaces have been in existence for 2 years. They were completely
    refreshed prior to production use. One of the tablespaces, call it TSP1,
    used LOAD REPLACE LOG YES. The other one, TSP2, used LOAD RESUME
    YES...PART X REPLACE. All the usual utilities have been running for the
    full time. Image copy jobs are done at the partition level. All copy
    jobs have MODIFY RECOVERY as the last step.

    Problem:

    The copy jobs for tablespace TSP2 periodically complete with condition
    code 4 in the last step, but are most often 0. When a 4 is returned, the
    message is "No backup copies found for this tablespace or dataset" for
    the MODIFY RECOVERY. All copy jobs for tablespace TSP1 consistently
    complete with condition code 4 in the last step. The message returned is
    "Modify has not deleted all SYSCOPY records as requested" which is
    followed by "No backup copies found for this tablespace or dataset." The
    syntax is identical, except for the tablespace name, and correct for all
    MODIFY RECOVERY statements.

    Looking at SYSCOPY shows a complete list of all utilities ever run
    against TSP1 since it was refreshed just prior to production use 2 years
    ago. The SYSCOPY listing for TSP2 is quite different. All utilities are
    shown going back only 30 days, the MODIFY RECOVERY DELETE AGE. The only
    utility going back 2 years is QUIESCE. All others are full image copies
    and REORG LOG NO.

    Question:

    Why does the TSP1 MODIFY RECOVERY not remove all the old utility entries
    from SYSCOPY like the TSP2 statements do?

    Answer:

    The LOAD REPLACE LOG YES for TSP1 used to refresh the table is what is
    causing the problem. The SYSCOPY entry has ICTYPE R and a DSNUM of 0
    since the LOAD was done at the table level rather than the partition
    level. Even though good image copies have been taken at the partition
    level, DB2 believes it does not have a valid recovery point after the
    LOAD since the LOAD was done at the full table level, DSNUM of 0.

    LOAD RESUME YES...PART X REPLACE allows the MODIFY to work correctly
    because recovery points are established at the partition level rather
    than the tablespace or full tablespace.

    Have not yet figured out how to fix it without impacting somebody. May
    just have to bite the bullet and have it go into COPYP and have a repair
    ready to flip the flag.

    Fun huh? Bet you that's not something they tell you in DBA class..









    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