Here's a gotcha

Kathleen Lisle

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



Michael Ebert

Re: FW: Here's a gotcha
(in response to Kathleen Lisle)
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..