reorg w/copyddn and it abends?

Jessen Michael

reorg w/copyddn and it abends?
We're on DB2 Version 5.1 (OS390).

I'm looking into using the COPYDDN option of the REORG.

I've looked thru the utility guide, but I'm not finding much information on
the following
(and I was hoping someone out there has already done some research into
this):

1. Is this faster than running a copy after the reorg (My 1st guess would
be yes, but I was wondering if anyone has tested this -
and whether it's better for certain type of tables (partitioned, very
large, etc. - or if it matters on the table size)?

2. If the reorg abends, what happens with the imagecopy dataset. Is there
an entry within syscopy? Is the dataset cataloged?

3. If you restart the reorg, is a complete copy recreated? I guess I'm not
sure at what point the copy is actually taking place?

On a side note, if you specify SORTDEVT, is it still possible to run out of
work space? What would be reason to manually specify workspace instead of
using SORTDEVT - or should a good standard default always be to use
SORTDEVT?

My final question (and probably another one I should already know, but
don't) - How do I find the Hi-used RBA for the tablespace from the VSAM
catalog? - I have the name of the vsam dataset, but I'm not sure how to find
the hi-used RBA...?

I really appreciate your help!

Mike Jessen
DBA
John Deere Credit
(515) 267-3672
[login to unmask email]

\\\|///
\ ~ ~ /
(\ @ @ /)
o000--(_)--000o



Michael Ebert

Re: reorg w/copyddn and it abends?
(in response to Jessen Michael)
1. I have not tested it either, but I would think that you gain a little time.
Not much, because an Imagecopy is much faster than a REORG anyway. The main
advantage would be that you get a SHRLEVEL REFERENCE IC and may not need a
subsequent QUIESCE (all our developers always do a QUIESCE after an IC for what
that is worth).
2. This depends on the Imagecopy DD disposition. If you use (MOD,CATLG,CATLG),
you do not need to change JCL if you have to restart. I think the entry in
SYSCOPY depends on whether or not you use SORTKEYS: if no, at the end of the
RELOAD phase; if yes, at the end of the BUILD phase. At least that's when the
Copy pending status is reset (Utilities Guide, p. 2-215).
3. I think DB2 is clever enough to reposition any files to the position it
needs. However, many performance improving options (like SORTDEVT, SORTKEYS,
SORTDATA, NOSYSREC) affect the possibility to restart successfully, and may lead
to mismatches between SYSCOPY and your IC files.

For this reason, I do NOT use Inline copy with LOAD or REORG (I experimented a
little with this some time ago). This just increases the possibility of an ABEND
(eg. B37 on the COPYDDN) and complicates restart and/or cleanup for a re-run.
There is one exception: Online Reorg, where you are required to take an Inline
copy. However, due to the use of shadow datasets, you can specify
DISP=(,CATLG,DELETE) for the IC dataset, and you are all set. If there's an
abend, just TERM the utility, correct the problem, and re-run the entire job
step. This may waste some CPU in the (hopefully rare) cases where it happens,
but saves a lot of manpower.

4. It still is possible to run out of sort space. With compressed and variable
length records, DB2 cannot accurately predict the output file size, and so it
cannot pass a valid estimate to SORT. During the execution of some extreme (in
this respect) Reorgs, I got a message about "out-of-space"-abends on the sort
volumes, but this was followed by another message saying something like "the
failing situation has been recovered". I don't know whether that is part of
SORT, SMS, or whatever. The REORG itself did not fail.

I always use the SORTDEVT (with SORTNUM 3), SORTKEYS, SORTDATA (if a clustering
index exists) and NOSYSREC (SHRLEVEL CHANGE, i.e. OLR only - otherwise
DANGEROUS) options. That way you also keep the auxiliary datasets to a minimum
(zero, if you have a clustering index and use OLR). A dataset you don't have
can't run out of space.
5. You can get the HI-USED-RBA (and also the HI-ALLOC-RBA and lots of other
information) by using the TSO LISTCAT command, e.g.
LISTC LVL(ssid.DSNDBD.dbname.tsname) ALLOC. Try TSO HELP LISTC from an ISPF
command line.

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




From: Jessen Michael <[login to unmask email]> on 16/12/99 16:46 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 w/copyddn and it abends?




We're on DB2 Version 5.1 (OS390).

I'm looking into using the COPYDDN option of the REORG.

I've looked thru the utility guide, but I'm not finding much information on
the following
(and I was hoping someone out there has already done some research into
this):

1. Is this faster than running a copy after the reorg (My 1st guess would
be yes, but I was wondering if anyone has tested this -
and whether it's better for certain type of tables (partitioned, very
large, etc. - or if it matters on the table size)?

2. If the reorg abends, what happens with the imagecopy dataset. Is there
an entry within syscopy? Is the dataset cataloged?

3. If you restart the reorg, is a complete copy recreated? I guess I'm not
sure at what point the copy is actually taking place?

On a side note, if you specify SORTDEVT, is it still possible to run out of
work space? What would be reason to manually specify workspace instead of
using SORTDEVT - or should a good standard default always be to use
SORTDEVT?

My final question (and probably another one I should already know, but
don't) - How do I find the Hi-used RBA for the tablespace from the VSAM
catalog? - I have the name of the vsam dataset, but I'm not sure how to find
the hi-used RBA...?

I really appreciate your help!

Mike Jessen
DBA
John Deere Credit
(515) 267-3672
[login to unmask email]

\\\|///
\ ~ ~ /
(\ @ @ /)
o000--(_)--000o








Myron Miller

Re: reorg w/copyddn and it abends?
(in response to Michael Ebert)
1) Its faster but the savings depend upon the size of
the tablespace being copied. Obviously a small
tablespace won't have much savings whereas an
extremely large tablespace could save 1+hours.

2) In my runs, when the reorg abends, the copy is
cataloged and put into syscopy. This may or may not
be a problem. Depends on how you have things setup.

3) Depends upon where you restart. If from beginning,
then you'll get a full complete copy. Other places,
see the manual.



--- Jessen Michael <[login to unmask email]>
wrote:
> We're on DB2 Version 5.1 (OS390).
>
> I'm looking into using the COPYDDN option of the
> REORG.
>
> I've looked thru the utility guide, but I'm not
> finding much information on
> the following
> (and I was hoping someone out there has already done
> some research into
> this):
>
> 1. Is this faster than running a copy after the
> reorg (My 1st guess would
> be yes, but I was wondering if anyone has tested
> this -
> and whether it's better for certain type of
> tables (partitioned, very
> large, etc. - or if it matters on the table size)?
>
> 2. If the reorg abends, what happens with the
> imagecopy dataset. Is there
> an entry within syscopy? Is the dataset cataloged?
>
> 3. If you restart the reorg, is a complete copy
> recreated? I guess I'm not
> sure at what point the copy is actually taking
> place?
>
> On a side note, if you specify SORTDEVT, is it still
> possible to run out of
> work space? What would be reason to manually
> specify workspace instead of
> using SORTDEVT - or should a good standard default
> always be to use
> SORTDEVT?
>
> My final question (and probably another one I should
> already know, but
> don't) - How do I find the Hi-used RBA for the
> tablespace from the VSAM
> catalog? - I have the name of the vsam dataset, but
> I'm not sure how to find
> the hi-used RBA...?
>
> I really appreciate your help!
>
> Mike Jessen
> DBA
> John Deere Credit
> (515) 267-3672
> [login to unmask email]
>
> \\\|///
> \ ~ ~ /
> (\ @ @ /)
> o000--(_)--000o
>
>
> To change your subscription options or to cancel
> your subscription visit the DB2-L webpage at
> http://www.ryci.com/db2-l. The owners of the list
> can
>

__________________________________________________
Do You Yahoo!?
Thousands of Stores. Millions of Products. All in one place.
Yahoo! Shopping: http://shopping.yahoo.com