REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?

Max Scarpa

REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
One year ago I posted a mail regarding REORG SHRLEVEL REFERENCE & SYSCOPY
DDNAME,

i.e. I was searching a method to execute a reorg a tablespace with SHRLEVEL
REFERENCE option WITHOUT

executing a concurrent image copy via SYSCOPY DDNAME in the same jobstep.

One year ago it was not possible, but now is it still true ? I tried to
find if there's any new PTF to correct this annoyance,

but the the result was negative.

Can anyone confirm that nothing change since last year ??

Thanks in Advance

Regards

Max Scarpa
DB2 sysprog



RICK (SBCSI) DAVIS

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
Hi Max,
Still true. See DB2 UDB for OS390 Utility Guide, 2.16.1.2, Option
Descriptions:
"COPYDDN(SYSCOPY) is assumed, and a DD statement for SYSCOPY is required if:
You specify REORG SHRLEVEL REFERENCE or CHANGE, and do not specify COPYDDN"

HTH,
Rick Davis

"This e-mail and any files transmitted with it are the property of SBC, are
confidential, and are intended solely for the use of the individual or
entity to whom this e-mail is addressed. If you are not one of the named
recipient(s) or otherwise have reason to believe that you have received this
message in error, please notify the sender at 314-235-6854 and delete this
message immediately from your computer. Any other use, retention,
dissemination, forwarding, printing, or copying of this e-mail is strictly
prohibited."

-----Original Message-----
From: Max Scarpa [mailto:[login to unmask email]
Sent: Wednesday, December 20, 2000 5:13 AM
To: [login to unmask email]
Subject: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?

One year ago I posted a mail regarding REORG SHRLEVEL REFERENCE & SYSCOPY
DDNAME, i.e. I was searching a method to execute a reorg a tablespace with
SHRLEVEL REFERENCE option WITHOUT executing a concurrent image copy via
SYSCOPY DDNAME in the same jobstep.

One year ago it was not possible, but now is it still true ? I tried to
find if there's any new PTF to correct this annoyance, but the the result
was negative.

Can anyone confirm that nothing change since last year ??

Thanks in Advance

Regards

Max Scarpa
DB2 sysprog



Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to RICK (SBCSI) DAVIS)
Hi Rick, thanks for your reply.

I read the manual, but I hoped to find a new PTF developed by IBM to bypass
the 'problem' (vain hope..)

For my job it's a real annoyance.

Kind Regards

Max Scarpa



Tim Lowe

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
Max,
I talked to Jim Ruddy from IBM DB2 development about this at the 1999 DB2
Tech conference, and followed up via email just afterword, but I have not
heard anything about this since then.
I have assumed that my voice was only 1 in a crowd of other requests, and
it has not gone anywhere.

Good luck,
Tim



Max Scarpa
<[login to unmask email] To: [login to unmask email]
E.IT> cc:
Sent by: DB2 Subject: REORG SHRLEVEL REFERENCE & SYSCOPY
Data Base DDNAME: any news ?
Discussion
List
<[login to unmask email]
OM>


12/20/2000
05:12 AM
Please
respond to
DB2 Data Base
Discussion
List






One year ago I posted a mail regarding REORG SHRLEVEL REFERENCE & SYSCOPY
DDNAME,

i.e. I was searching a method to execute a reorg a tablespace with SHRLEVEL
REFERENCE option WITHOUT

executing a concurrent image copy via SYSCOPY DDNAME in the same jobstep.

One year ago it was not possible, but now is it still true ? I tried to
find if there's any new PTF to correct this annoyance,

but the the result was negative.

Can anyone confirm that nothing change since last year ??

Thanks in Advance

Regards

Max Scarpa
DB2 sysprog








Max Scarpa

Re: REORG SHRLEVEL REFERENCE
(in response to Isaac Yassin)
BE SURE !!!!

But I think mother IBM will build this PTF for DB2 V 69..... ;-)

Regards

Max Scarpa



Michael Ebert

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
Max

REORG changes the physical locations of data rows. Because DB2 identifies rows
by their location (the RID), you lose recoverability after a REORG. With LOG NO,
this leaves the TS in COPY Pending, and you have to take an IC. Because SHRLEVEL
REF & CHANGE are meant to reduce downtime, this would be unacceptable; that's
why DB2 forces you to take an Inline IC. For a read-only TS, leaving the TS in
COPY would be ok - but who needs to REORG a read-only TS??
It's probably safe to say that you'll wait forever for this change. It serves no
useful function.
So the real question is still the same as last year - why would anybody even
want to run OLR without an IC??
The solutions are still the same as well - create an IC and delete it afterwards
(you'll lose recoverability, but if that's what you want....)

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



From: Max Scarpa <[login to unmask email]> on 20/12/2000 11:12 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: |
|--------->
>--------------------------------------------------------------------------->
|REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ? |
>--------------------------------------------------------------------------->
>-------------------------------------------|
| |
>-------------------------------------------|



One year ago I posted a mail regarding REORG SHRLEVEL REFERENCE & SYSCOPY
DDNAME,
i.e. I was searching a method to execute a reorg a tablespace with SHRLEVEL
REFERENCE option WITHOUT
executing a concurrent image copy via SYSCOPY DDNAME in the same jobstep.

One year ago it was not possible, but now is it still true ? I tried to
find if there's any new PTF to correct this annoyance,
but the the result was negative.

Can anyone confirm that nothing change since last year ??

Thanks in Advance

Regards

Max Scarpa
DB2 sysprog



Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Michael Ebert)
Hi mebert.

Really ? During a reorg RIDs are changed ? After a reorg tablespaces are in
copy pending ? Incredible !!! ;-)

Sorry but I disagree with you.

In my opinion it's an annoying feature to permit a SHRLEVEL REFERENCE (NOT
SHRLEVEL CHANGE) with an high
degree of safety AND force users to execute an inline image copy. It's
more & more & more clever let the user decide what kind of reorg she/he
desire. MAYBE she/he knows what she/he's doing, even if she/he's not
certified.

If I have a very narrow reorg window I'll use SHRLEVEL CHANGE or SHRLEVEL
REFERENCE with inline copy, this permit an high degree of safety,it's
fast, it reduces dowtime and you can sleep (more or less) all night long
(maybe).

If I have enough time to execute reorg (in parallell and I have few DASDs
space, few tape drives and no HSM) and I want to sleep all night long (or
I want to spend my sunday flying with my paraglider while reorgs are
executed), I'll use SHRLEVEL REFERENCE to avoid, for instance, B37-like
abends and a subsequent recover. Why not ?

If something goes wrong, with an IF step in JCL I can terminate the utility
and I can resolve (almost) any restricted status of tablespace (and/or I
can use a scheduler to execute REXX to perform some checks). All is ok at
the start of production. BUT I'm obliged to waste a lot of DASDs space to
execute an inline image copy for each tablespace (with additional &
potential B37 abends for image copy files that I can avoid with a
wonderful scheduler and an automated tape library).
In this case (it's not rare) it's better to have all (or a group) reorged
tablespaces in COPY PENDING (and you can read the tables - not all
transactions in an application update the tablespace) and execute an image
copy on one (or more) tapes, which is cheaper and I don't loose any
recoverability. BMC reorg did it (when I used it).

So I think that it's useful and more flexible. And maybe useful for some
developer to have a tour in a real production shop.

Regards
Max Scarpa
Non-certified DB2 sysprog - 'Here we do what we know, but we know what we
are doing'



Michael Ebert

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
<snip>
...BUT I'm obliged to waste a lot of DASDs space to
execute an inline image copy for each tablespace (with additional &
potential B37 abends for image copy files that I can avoid with a
wonderful scheduler and an automated tape library).
In this case (it's not rare) it's better to have all (or a group) reorged
tablespaces in COPY PENDING (and you can read the tables - not all
transactions in an application update the tablespace) and execute an image
copy on one (or more) tapes, which is cheaper and I don't loose any
recoverability. BMC reorg did it (when I used it).
</snip>

I don't quite understand this. You can direct an OLR Inline IC to tape as well
(you can even use SL=n and RETAIN). So what's the problem?

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



Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Michael Ebert)
***

I don't quite understand this. You can direct an OLR Inline IC to tape as
well
(you can even use SL=n and RETAIN). So what's the problem?

***

Did you ever tried it with 10 reorgs in parallel ?

Max Scarpa



Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
I don't quite understand this. You can direct an OLR Inline IC to tape as
well
(you can even use SL=n and RETAIN). So what's the problem?

***

Did you ever try it with 10 (or more) reorgs of tablespaces differing in
size running in parallel ?

Max Scarpa



Eric Pearson

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
Max,
We run > 10 Reorgs (SHRLEVEL CHANGE) in parallel every day.
All we had to do to accomodate this is increase the BP used for the
mapping table indexes.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Max Scarpa [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 8:47 AM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?


I don't quite understand this. You can direct an OLR Inline IC to tape as
well
(you can even use SL=n and RETAIN). So what's the problem?

***

Did you ever try it with 10 (or more) reorgs of tablespaces differing in
size running in parallel ?

Max Scarpa








Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Eric Pearson)
Max,
We run > 10 Reorgs (SHRLEVEL CHANGE) in parallel every day.
All we had to do to accomodate this is increase the BP used for the
mapping table indexes.

****
We run >> 10 reorgs in parallel,too. And we are happy.

But do you mean you execute inline IC using only 1 tape drive (with LBL
=1..n and RETAIN) for > 10 tablespaces ? Is it pratical ?

Regards
Max Scarpa



Eric Pearson

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
Max,
What we do is to have each REORG job use one tape drive.
We stack multiple REORG executions within the same step
to the same tape drive. For smaller databases we may REORG
dozens of tablespaces within one job step and stack their
inline copies to the same drive.

For larger tablespaces we do only one tablespace per jobstep.

No problems so far.

We let JES3 allocation manage the tape drive situation.
The job does not start unless a tape drive is available.
So sometimes there are 10+ reorg/copym jobs running, sometimes
only 1.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Max Scarpa [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 9:03 AM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?


Max,
We run > 10 Reorgs (SHRLEVEL CHANGE) in parallel every day.
All we had to do to accomodate this is increase the BP used for the
mapping table indexes.

****
We run >> 10 reorgs in parallel,too. And we are happy.

But do you mean you execute inline IC using only 1 tape drive (with LBL
=1..n and RETAIN) for > 10 tablespaces ? Is it pratical ?

Regards
Max Scarpa








Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Eric Pearson)
Max,
What we do is to have each REORG job use one tape drive.

*** AhAh ! 1 job 1 tape drive !. AH, you lucky man who can steal > 10 tape
drives to production batch... As I said I cannot steal (the fews) tape
drives to production batch jobs, so i cannot use 1 drive for EACH reorg and
I am obliged to execute IC on disk.
AND using 1 tape drive for > 10 reorgs running in parallel it not possible.
BUT If I could execute reorgs without inline IC I'd be able to avoid this
annoying problem.

We stack multiple REORG executions within the same step
to the same tape drive. For smaller databases we may REORG
dozens of tablespaces within one job step and stack their
inline copies to the same drive.

*** OK, that's right for small tablspace. Anyway you loose the capacity to
execute
the same reorg jobs in parallel. I've the problem to execute > 10 reorg
jobs in parallel, the tablespace are medium to large size (and partitioned)
and with limited
DASDs space and tape drives and within a relatively small window and all
MUST work at 7.00 o'clock (so no time for recoveries)....Nice, isn't it ?

For larger tablespaces we do only one tablespace per jobstep. No problems
so far.

*** I believe it !

The job does not start unless a tape drive is available.

*** I suppose you use OPC or Control-m. We use Control-M

Regards

Max Scarpa



Tim Lowe

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
The only thing that really bothered me about this was the different replies
to questions, and the lack of a response.

When a question was asked during a DB2 tech conference presentation (1999?)
about why an in-line image copy was required for a SHRLEVEL REFERENCE
REORG, the answer was that this was a coding mistake and should not be
required. I don't remember who was talking, but they want on to say that
almost all of their testing was with SHRLEVEL CHANGE, and that they did not
put much time into SHRLEVEL REFERENCE because they did not anticipate much
need for this. He said that he did not think that this would be a big
change. (OK, I went away happy.)

Then, I spent some time describing problems with B37 errors in reorgs, and
why I wanted to use SHRLEVEL REFERENCE reorgs without imbedded copies.
But, I never got any response back. (no news is bad news?)

I can only hope that the B37 problems that I described are no longer an
issue in DB2 V7 with the implementation of object wildcarding and dynamic
allocation in the utilities. (Perhaps, my input was not ignored, but my
problems were just resolved in a different way? It can't hurt to be
optimistic can it? So, I figure that I should just be patient, and look
forward to V7!)

Thanks,
Tim



Mohammed Nayeem

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Tim Lowe)
Hi Tim

For B37 errors in reorgs, assign high space to all working sort files
can sove problem where their DISP=(MOD,DELETE,CATLG),

SPACE=(CYL,(2000,2000),,,ROUND)

Thanks
Nayeem



---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000
11:40 AM ---------------------------


[login to unmask email] on 12/21/2000 10:48:01 AM

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

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?



The only thing that really bothered me about this was the different replies
to questions, and the lack of a response.

When a question was asked during a DB2 tech conference presentation (1999?)
about why an in-line image copy was required for a SHRLEVEL REFERENCE
REORG, the answer was that this was a coding mistake and should not be
required. I don't remember who was talking, but they want on to say that
almost all of their testing was with SHRLEVEL CHANGE, and that they did not
put much time into SHRLEVEL REFERENCE because they did not anticipate much
need for this. He said that he did not think that this would be a big
change. (OK, I went away happy.)

Then, I spent some time describing problems with B37 errors in reorgs, and
why I wanted to use SHRLEVEL REFERENCE reorgs without imbedded copies.
But, I never got any response back. (no news is bad news?)

I can only hope that the B37 problems that I described are no longer an
issue in DB2 V7 with the implementation of object wildcarding and dynamic
allocation in the utilities. (Perhaps, my input was not ignored, but my
problems were just resolved in a different way? It can't hurt to be
optimistic can it? So, I figure that I should just be patient, and look
forward to V7!)

Thanks,
Tim








Isaac Yassin

REORG SHRLEVEL REFERENCE
(in response to Tim Lowe)
Hi Max,
When you find the PTF - please let me know.... ;o))

Isaac Yassin
DBMS & IT Consultant
[login to unmask email]



Tim Lowe

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Mohammed Nayeem)
Nayeem,
That is NOT a good universal solution for all possible B37 problems in
reorgs!
It would certainly be a simple world if that solved all B37 errors.
Perhaps it would help if I described the types of B37 errors that I am
trying to solve.

Some reorgs are for very small tablespaces, in which case allocating
thousands of cylinders of dasd can be a huge waste.
Some reorgs are for very large tablespaces, in which case you may need to
go to tape instead of dasd.
Some tablespaces experience a very large unexpected growth (surprise!) and
create problems.
I can setup the temporary datasets in the reorg to extend to multiple
volumes, and I can somewhat overallocate them, and this solves many
problems.
But, there are still a few occasions where very large growth occurs very
fast, that this is not sufficient.
And, I have hundreds of reorg jobs that need to be monitored and I must
periodically adjust the temporary dataset allocations to account for normal
growth.

Currently, if you use SHRLEVEL REFERENCE to eliminate the need for the
temporary datasets, then you need to use inline copy, and this inroduces a
new problem.
If you go to tape, then the tape drive is busy during the entire reorg.
Since tape drives seem to be very critical resources today, this IS a
problem.
If you go to disk, then you introduce the possibility of B37 errors in the
inline copy.

DB2 V7 utility wildcarding, templates and dynamic allocation introduce the
option to dynamically allocate these datasets, therefore reducing the
possibility of B37 errors caused by growth between the time the jcl was
created and when it was run.
To tell the truth, one objective is simply to get the utilities run without
loosing sleep. (mine or my co-workers)

I hope this helps explain things, and why I look forward to DB2 V7.

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Re: REORG SHRLEVEL REFERENCE &
Base Discussion SYSCOPY DDNAME: any news ?
List
<[login to unmask email]>


12/21/2000 11:42
AM
Please respond to
DB2 Data Base
Discussion List






Hi Tim

For B37 errors in reorgs, assign high space to all working sort files
can sove problem where their DISP=(MOD,DELETE,CATLG),

SPACE=(CYL,(2000,2000),,,ROUND)

Thanks
Nayeem



---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on
12/21/2000
11:40 AM ---------------------------


[login to unmask email] on 12/21/2000 10:48:01 AM

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

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?



The only thing that really bothered me about this was the different replies
to questions, and the lack of a response.

When a question was asked during a DB2 tech conference presentation (1999?)
about why an in-line image copy was required for a SHRLEVEL REFERENCE
REORG, the answer was that this was a coding mistake and should not be
required. I don't remember who was talking, but they want on to say that
almost all of their testing was with SHRLEVEL CHANGE, and that they did not
put much time into SHRLEVEL REFERENCE because they did not anticipate much
need for this. He said that he did not think that this would be a big
change. (OK, I went away happy.)

Then, I spent some time describing problems with B37 errors in reorgs, and
why I wanted to use SHRLEVEL REFERENCE reorgs without imbedded copies.
But, I never got any response back. (no news is bad news?)

I can only hope that the B37 problems that I described are no longer an
issue in DB2 V7 with the implementation of object wildcarding and dynamic
allocation in the utilities. (Perhaps, my input was not ignored, but my
problems were just resolved in a different way? It can't hurt to be
optimistic can it? So, I figure that I should just be patient, and look
forward to V7!)

Thanks,
Tim



the










Mohammed Nayeem

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Tim Lowe)
I mean if you do not wanted to retain these files then you can specify that way
and any how those sort file will be deleted once the job sucessfully ends.

Thx
Nayeem
---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000
12:57 PM ---------------------------


[login to unmask email] on 12/21/2000 12:22:45 PM

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

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?



Nayeem,
That is NOT a good universal solution for all possible B37 problems in
reorgs!
It would certainly be a simple world if that solved all B37 errors.
Perhaps it would help if I described the types of B37 errors that I am
trying to solve.

Some reorgs are for very small tablespaces, in which case allocating
thousands of cylinders of dasd can be a huge waste.
Some reorgs are for very large tablespaces, in which case you may need to
go to tape instead of dasd.
Some tablespaces experience a very large unexpected growth (surprise!) and
create problems.
I can setup the temporary datasets in the reorg to extend to multiple
volumes, and I can somewhat overallocate them, and this solves many
problems.
But, there are still a few occasions where very large growth occurs very
fast, that this is not sufficient.
And, I have hundreds of reorg jobs that need to be monitored and I must
periodically adjust the temporary dataset allocations to account for normal
growth.

Currently, if you use SHRLEVEL REFERENCE to eliminate the need for the
temporary datasets, then you need to use inline copy, and this inroduces a
new problem.
If you go to tape, then the tape drive is busy during the entire reorg.
Since tape drives seem to be very critical resources today, this IS a
problem.
If you go to disk, then you introduce the possibility of B37 errors in the
inline copy.

DB2 V7 utility wildcarding, templates and dynamic allocation introduce the
option to dynamically allocate these datasets, therefore reducing the
possibility of B37 errors caused by growth between the time the jcl was
created and when it was run.
To tell the truth, one objective is simply to get the utilities run without
loosing sleep. (mine or my co-workers)

I hope this helps explain things, and why I look forward to DB2 V7.

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Re: REORG SHRLEVEL
REFERENCE &
Base Discussion SYSCOPY DDNAME: any news ?
List
<[login to unmask email]>


12/21/2000 11:42
AM
Please respond to
DB2 Data Base
Discussion List






Hi Tim

For B37 errors in reorgs, assign high space to all working sort files
can sove problem where their DISP=(MOD,DELETE,CATLG),

SPACE=(CYL,(2000,2000),,,ROUND)

Thanks
Nayeem



---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on
12/21/2000
11:40 AM ---------------------------


[login to unmask email] on 12/21/2000 10:48:01 AM

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

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?



The only thing that really bothered me about this was the different replies
to questions, and the lack of a response.

When a question was asked during a DB2 tech conference presentation (1999?)
about why an in-line image copy was required for a SHRLEVEL REFERENCE
REORG, the answer was that this was a coding mistake and should not be
required. I don't remember who was talking, but they want on to say that
almost all of their testing was with SHRLEVEL CHANGE, and that they did not
put much time into SHRLEVEL REFERENCE because they did not anticipate much
need for this. He said that he did not think that this would be a big
change. (OK, I went away happy.)

Then, I spent some time describing problems with B37 errors in reorgs, and
why I wanted to use SHRLEVEL REFERENCE reorgs without imbedded copies.
But, I never got any response back. (no news is bad news?)

I can only hope that the B37 problems that I described are no longer an
issue in DB2 V7 with the implementation of object wildcarding and dynamic
allocation in the utilities. (Perhaps, my input was not ignored, but my
problems were just resolved in a different way? It can't hurt to be
optimistic can it? So, I figure that I should just be patient, and look
forward to V7!)

Thanks,
Tim



the















Eric Pearson

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Mohammed Nayeem)
So maybe consider going to REORG SHRLEVEL CHANGE so you can spread these
out over 24 hrs rather than fighting a relatively small window?
Or do you have apps which refuse to COMMIT?

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: Max Scarpa [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 11:20 AM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?


Max,
What we do is to have each REORG job use one tape drive.

*** AhAh ! 1 job 1 tape drive !. AH, you lucky man who can steal > 10 tape
drives to production batch... As I said I cannot steal (the fews) tape
drives to production batch jobs, so i cannot use 1 drive for EACH reorg and
I am obliged to execute IC on disk.
AND using 1 tape drive for > 10 reorgs running in parallel it not possible.
BUT If I could execute reorgs without inline IC I'd be able to avoid this
annoying problem.

We stack multiple REORG executions within the same step
to the same tape drive. For smaller databases we may REORG
dozens of tablespaces within one job step and stack their
inline copies to the same drive.

*** OK, that's right for small tablspace. Anyway you loose the capacity to
execute
the same reorg jobs in parallel. I've the problem to execute > 10 reorg
jobs in parallel, the tablespace are medium to large size (and partitioned)
and with limited
DASDs space and tape drives and within a relatively small window and all
MUST work at 7.00 o'clock (so no time for recoveries)....Nice, isn't it ?

For larger tablespaces we do only one tablespace per jobstep. No problems
so far.

*** I believe it !

The job does not start unless a tape drive is available.

*** I suppose you use OPC or Control-m. We use Control-M

Regards

Max Scarpa








John Arbogast

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Mohammed Nayeem)
I agree with Tim. Where would I find 2000 cylinders laying around for every
sortwork DS in every reorg? Thankfully, I have the luxury of BMC reorg that
already has dynamic file allocation and thresholding for disk or tape
allocation.
Just my 2c.
Speaking of reorg, how does everyone handle the mapping table and its TS?
One common TS? One per DB? Etc?


Mohammed Nayeem

Reorg
(in response to Eric Pearson)
Hi

I am getting hell of time in sort phase when running REORG against partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem



Hugo Hernandez

Re: Reorg
(in response to John Arbogast)
Few questions :
1) Do you want to reorg all the partitions for the tablespace or just few
ones.
2) How many non-partitioned indexes you have for the tablespace
3) How many rows have the table

This give me more details to give you and answer instead a guess.


-----Original Message-----
From: Mohammed Nayeem [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 3:25 PM
To: [login to unmask email]
Subject: Reorg


Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem








Tim Lowe

Re: Reorg
(in response to Hugo Hernandez)
Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List






Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem








Mohammed Nayeem

Re: Reorg
(in response to Tim Lowe)
Yep I am using this way

REORG TABLESPACE XXXXXXXX.YYYYYYYY SORTDATA SORTKEY

How many Sort files is required ?
We have 38 partitions.




---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on 12/21/2000
04:19 PM ---------------------------


[login to unmask email] on 12/21/2000 03:41:55 PM

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

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg



Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List






Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem













Rob Wright

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Mohammed Nayeem)
We don't specify sortwork datasets in our utilities (SWnnWKnn) - SORT allocates
them as required and automatically recovers from B37 etc. We use SYNCSORT, but
DFSORT supports this too - see your nearest friendly Systems Programmer or the
SORT doco for further information.

Rob



Tim Lowe

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Rob Wright)
Rob,
We dynamically allocate our sortworks. (doesn't everyone today?)
But that is not where the B37s typically cause a problem, the problems
occur with SYSREC, SYSUT1 and SORTOUT.
Obviously, using shrlevel reference, it is these datasets (and the B37s
associated with them) that can go away in most cases (unless you don't have
an explicit clustering index).

Thanks,
Tim



Rob Wright
<[login to unmask email] To: [login to unmask email]
CO.NZ> cc:
Sent by: DB2 Subject: Re: REORG SHRLEVEL REFERENCE &
Data Base SYSCOPY DDNAME: any news ?
Discussion
List
<[login to unmask email]
OM>


12/21/2000
05:41 PM
Please
respond to
DB2 Data Base
Discussion
List






We don't specify sortwork datasets in our utilities (SWnnWKnn) - SORT
allocates
them as required and automatically recovers from B37 etc. We use SYNCSORT,
but
DFSORT supports this too - see your nearest friendly Systems Programmer or
the
SORT doco for further information.

Rob








Tim Lowe

Re: Reorg
(in response to Rob Wright)
Nayeem,
When reorganizing very large tablespaces, I generally use the SORTNUM 32.
But, I think that your sort program can override this. For example, I know
that even if I specify SORTNUM 32, it can use fewer sortworks based on the
number of rows and the rowsize that reorg passes it.
Obviously, I dynamically allocate my sortworks.
And, if sort uses fewer sortwork datasets, then it must make them larger.

If you want a rough idea of the size of these sortworks, then you would
need to know how big your indexes are. I am sure that there are formulas
for calculating this, but I would only guess that if you doubled the size
of your indexes, you might have a good general idea of how big all of the
sortworks are. By dynamically allocating sortworks, I like to let it do
the math. I just try to make sure that we have enough space in this dasd
pool.

I am really not sure what your question is, but does this help?
When you ran your reorg, did you dynamically allocate your sortworks?
And, based on your EXCP numbers, how many sortworks did you actually use?
Hugo also asked some very good questions about how many rows, how many
non-partitioned indexes??
In general, just how big is this?
And, if there are no non-partitioned indexes, could you reorg each
partition one at a time instead of the entire tablespace at once?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Re: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 04:20
PM
Please respond to
DB2 Data Base
Discussion List






Yep I am using this way

REORG TABLESPACE XXXXXXXX.YYYYYYYY SORTDATA SORTKEY

How many Sort files is required ?
We have 38 partitions.




---------------------- Forwarded by Mohammed Nayeem/MoMedicaid/US on
12/21/2000
04:19 PM ---------------------------


[login to unmask email] on 12/21/2000 03:41:55 PM

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

To: [login to unmask email]
cc: (bcc: Mohammed Nayeem/MoMedicaid/US)

Subject: Re: Reorg



Nayeem,
At the last DB2 tech conference, one of the DB2 developers tried to make
this as obvious as possible.
If I remember correctly, he said:
"If your tablespace needs to be reorged, you should use sortdata. If your
tablespace does not need to be reorged, then why are you running reorg on
it?".

So, I am curious, why don't you want to use sortdata?

Thanks,
Tim



Mohammed Nayeem
<Mohammed_Nayeem@ To: [login to unmask email]
MOMED.COM> cc:
Sent by: DB2 Data Subject: Reorg
Base Discussion
List
<[login to unmask email]>


12/21/2000 03:25
PM
Please respond to
DB2 Data Base
Discussion List






Hi

I am getting hell of time in sort phase when running REORG against
partitioned
TS and I am using DB2 Utility :
REORG TABLESPACE XXXXXXXX.YYYYYYYY

If without SORTDATA and SORTKEYS , in sort phase after 75 minutes I
terminated
util and ran REBUILD INDEX ALL

Why and how?

Thx
Nayeem








the










Rob Wright

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Tim Lowe)
Hi Tim

Don't have the post I responded to, but it talked about sort work, which is why
I posted this. WRT to the other datasets, sometimes the best way around it is to
use smaller primary/secondary allocations and expect datasets to take extents.
Sometimes you will have 2000 cylinders available but the actual cylinders are in
small 'chunks'.

Cheers
Rob





[login to unmask email] on 12/22/2000 11:47:33 AM

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

To: [login to unmask email]
cc: (bcc: Rob Wright/ham/LIC)

Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?



Rob,
We dynamically allocate our sortworks. (doesn't everyone today?)
But that is not where the B37s typically cause a problem, the problems
occur with SYSREC, SYSUT1 and SORTOUT.
Obviously, using shrlevel reference, it is these datasets (and the B37s
associated with them) that can go away in most cases (unless you don't have
an explicit clustering index).

Thanks,
Tim



Rob Wright
<[login to unmask email] To: [login to unmask email]
CO.NZ> cc:
Sent by: DB2 Subject: Re: REORG SHRLEVEL
REFERENCE &
Data Base SYSCOPY DDNAME: any news ?
Discussion
List
<[login to unmask email]
OM>


12/21/2000
05:41 PM
Please
respond to
DB2 Data Base
Discussion
List






We don't specify sortwork datasets in our utilities (SWnnWKnn) - SORT
allocates
them as required and automatically recovers from B37 etc. We use SYNCSORT,
but
DFSORT supports this too - see your nearest friendly Systems Programmer or
the
SORT doco for further information.

Rob













Max Scarpa

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Tim Lowe)
As far as I can see from replies there is almost one DB2 dataAdmin/sysprog
that needs to reorg tablespaces with SHRLEVEL REFERENCE without the
execution of concurrent inline copy. And (if true) this is a BUG (and I
believe it, I cannot believe that it was left intentionallly).

What I cannot admit is a bullet-proof utility (like reorg SHRLEVEL
REFERENCE) that obligates users to allocate a lot of DASDs space for inline
copy.
So you can avoid datasets for SYSUT1s & SYSRECs, you can reorg tablespace
with a high degree of safety with SHRLEVEL REFERENCE, you think you can
sleep all night long IF your reorg doesn't abend for a B37 error in SYSCOPY
dataset ! And you cannot use all tape drives in you shop if you execute
reorgs in parallel (that's the truth) !
As far as I know there are a lot of data center where image copie use
tapes, and with an automated tape library you can avoid almost all b37
problems. Ok I know IBM sells DASDs but....

Thanks all for replies (especially to Tim Lowe)

Regards and MERRY XMAX to all listers !!!!

Dr. Max Scarpa
DB2 sysprog



Eric Pearson

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Max Scarpa)
We had some mapping table learning to do.
We found that to minimize lock situations our best option
was one mapping table per tablespace. In the beginning we did
a CREATE in each job. Once we hit about 8 reorgs at the same time
we hit DBD lockouts on the CREATE/DROP. So now we have 'permanent'
mapping tablespaces/indexes. If we run short of DASD space we can drop
the larger ones then create them just before running the reorg (but
this reduces concurrency.

regards,

eric pearson
NS ITO Database Support


-----Original Message-----
From: John Arbogast [mailto:[login to unmask email]
Sent: Thursday, December 21, 2000 3:30 PM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?


I agree with Tim. Where would I find 2000 cylinders laying around for every
sortwork DS in every reorg? Thankfully, I have the luxury of BMC reorg that
already has dynamic file allocation and thresholding for disk or tape
allocation.
Just my 2c.
Speaking of reorg, how does everyone handle the mapping table and its TS?
One common TS? One per DB? Etc?








John Arbogast

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to Eric Pearson)
Sounds like something that could be generated on the fly by IBM?
How long did it take to work out all the kinks?

Thanks for the reply Eric.
Anyone else?

-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 22, 2000 6:50 AM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?


We had some mapping table learning to do.
We found that to minimize lock situations our best option
was one mapping table per tablespace. In the beginning we did
a CREATE in each job. Once we hit about 8 reorgs at the same time
we hit DBD lockouts on the CREATE/DROP. So now we have 'permanent'
mapping tablespaces/indexes. If we run short of DASD space we can drop
the larger ones then create them just before running the reorg (but
this reduces concurrency.

regards,

eric pearson
NS ITO Database Support











Eric Pearson

Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?
(in response to John Arbogast)
John,
Once we learned the syntax of Online Reorg things got simple.
Each problem (the locking etc) was very easy to find.
Within one day's testing we were ready for prime time.


regards,

eric pearson
NS ITO Database Support

-----Original Message-----
From: John Arbogast [mailto:[login to unmask email]
Sent: Friday, December 22, 2000 10:43 AM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?



Sounds like something that could be generated on the fly by IBM?
How long did it take to work out all the kinks?

Thanks for the reply Eric.
Anyone else?

-----Original Message-----
From: [login to unmask email] [ mailto:[login to unmask email]
<mailto:[login to unmask email]> ]
Sent: Friday, December 22, 2000 6:50 AM
To: [login to unmask email]
Subject: Re: REORG SHRLEVEL REFERENCE & SYSCOPY DDNAME: any news ?


We had some mapping table learning to do.
We found that to minimize lock situations our best option
was one mapping table per tablespace. In the beginning we did
a CREATE in each job. Once we hit about 8 reorgs at the same time
we hit DBD lockouts on the CREATE/DROP. So now we have 'permanent'
mapping tablespaces/indexes. If we run short of DASD space we can drop
the larger ones then create them just before running the reorg (but
this reduces concurrency.

regards,

eric pearson
NS ITO Database Support





DB2-L webpage at http://www.ryci.com/db2-l < http://www.ryci.com/db2-l > . The
owners of the list can be




DB2-L webpage at http://www.ryci.com/db2-l < http://www.ryci.com/db2-l > . The
owners of the list can