DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default

John Amsden

DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default
We are preparing for V8.1 new function mode. I am aware that the REORG
utility defaults to SORTDATA in V8.1 - and V7.1 did not. I seem to recall
that this situation ***could*** cause some difficulties but for the life of
me I can not recall what those difficulties might be. The difficulty possibly
had to do with the V8.1 REORG utility putting the data in different order if
there was *not* a clustering index on the table.

I've perused the manuals, DB2-L archives and googled the living daylights out
of this topic but can not find anything to support my concern.

Can anyone help me out here?

Thanks in advance,

John W. Amsden FLMI
Database Specialist

Lincoln Financial Group
1 Granite Place
Concord, NH 03301

(603) 226-5239 (bus)
(603) 724-1813 (cell)
(603) 229-6144 (fax)
[login to unmask email]




Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

David Simpson

Re: DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default
(in response to John Amsden)
John,

V7 reorg does not appear to organize the data when there is no clustering index whether or not you use the SORTDATA keyword. As you say in V8 SORTDATA is the default, but DB2 will also order the data by the first index created.

The other thing to watch for is SORKEYS (also now a default in V8). This is almost always what you want on REORG, but may NOT be what you want on LOAD if your input file is already sorted.

These utility changes are effective in V8 CM.

Hope this helps.

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com

________________________________

From: DB2 Data Base Discussion List on behalf of Amsden, John W
Sent: Tue 1/8/2008 2:13 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default



We are preparing for V8.1 new function mode. I am aware that the REORG
utility defaults to SORTDATA in V8.1 - and V7.1 did not. I seem to recall
that this situation ***could*** cause some difficulties but for the life of
me I can not recall what those difficulties might be. The difficulty possibly
had to do with the V8.1 REORG utility putting the data in different order if
there was *not* a clustering index on the table.

I've perused the manuals, DB2-L archives and googled the living daylights out
of this topic but can not find anything to support my concern.

Can anyone help me out here?

Thanks in advance,

John W. Amsden FLMI
Database Specialist

Lincoln Financial Group
1 Granite Place
Concord, NH 03301

(603) 226-5239 (bus)
(603) 724-1813 (cell)
(603) 229-6144 (fax)
[login to unmask email]




Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mike Kalena

Re: DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default
(in response to David Simpson)


One issue in v8 is when using SORTDATA against a huge table, you may not
have enough space to do the sort of the table space data.



In that case you have to specify SORTDATA NO and Reorg will unload the
table data using the index which avoids the sort.



We are still mostly v7 but have some huge warehouse tables that had to
be run this way when moving data between parts.



Hope this helps.



Michael Kalena
973-793-2133
[login to unmask email]



DB2 Info Page at BSC < http://whsysops1/db2/ >


The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L
list archives, the FAQ, and delivery preferences are at www.idug.org
< http://www.idug.org/lsidug > under the Listserv tab. While at the site,
you can also access the IDUG Online Learning Center, Tech Library and
Code Place, see the latest IDUG conference information
< http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at
no cost, click on Member Services < http://www.idug.org/lsms >

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

John Amsden

Re: DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default
(in response to Mike Kalena)
Thanks to all who responded. The thing that I could not recall was "order the data by the first index created" I believe that may have bitten us already.

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email]On Behalf Of David Simpson
Sent: Tuesday, January 08, 2008 5:51 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default



John,

V7 reorg does not appear to organize the data when there is no clustering index whether or not you use the SORTDATA keyword. As you say in V8 SORTDATA is the default, but DB2 will also order the data by the first index created.

The other thing to watch for is SORKEYS (also now a default in V8). This is almost always what you want on REORG, but may NOT be what you want on LOAD if your input file is already sorted.

These utility changes are effective in V8 CM.

Hope this helps.

David Simpson
Senior Technical Advisor
Themis Training
[login to unmask email]
http://www.themisinc.com


_____

From: DB2 Data Base Discussion List on behalf of Amsden, John W
Sent: Tue 1/8/2008 2:13 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default



We are preparing for V8.1 new function mode. I am aware that the REORG
utility defaults to SORTDATA in V8.1 - and V7.1 did not. I seem to recall
that this situation ***could*** cause some difficulties but for the life of
me I can not recall what those difficulties might be. The difficulty possibly
had to do with the V8.1 REORG utility putting the data in different order if
there was *not* a clustering index on the table.

I've perused the manuals, DB2-L archives and googled the living daylights out
of this topic but can not find anything to support my concern.

Can anyone help me out here?

Thanks in advance,

John W. Amsden FLMI
Database Specialist

Lincoln Financial Group
1 Granite Place
Concord, NH 03301

(603) 226-5239 (bus)
(603) 724-1813 (cell)
(603) 229-6144 (fax)
[login to unmask email]




Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms



The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at www.idug.org <http://www.idug.org/lsidug> under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information < http://www.idug.org/lsconf > , and much more.
If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member < http://www.idug.org/lsms > Services





Notice of Confidentiality: **This E-mail and any of its attachments may contain
Lincoln National Corporation proprietary information, which is privileged, confidential,
or subject to copyright belonging to the Lincoln National Corporation family of
companies. This E-mail is intended solely for the use of the individual or entity to
which it is addressed. If you are not the intended recipient of this E-mail, you are
hereby notified that any dissemination, distribution, copying, or action taken in
relation to the contents of and attachments to this E-mail is strictly prohibited
and may be unlawful. If you have received this E-mail in error, please notify the
sender immediately and permanently delete the original and any copy of this E-mail
and any printout. Thank You.**

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms

Mark McCormack

DB2 for Z/OS - V8.1 CM - REORG - sortdata now the default
(in response to John Amsden)
John,

We used the SORTDATA option for most standalone reorgs prior to DB2v8.
There were some reorgs, however, for which we had deliberately not used
SORTDATA in the past. DB2v8 meant a mandatory change, and it caused us
a fair amount of trouble. We had large warehouse tablespaces where
cluster ratio was high and for which we ran weekly reorgs with discard.
The purpose of those reorgs, therefore, was not so much to reorder the
data but to perform a purge. The change to the SORTDATA default under
DB2v8 added large sorts to the unload phases where there had been no
sorts at all.

DB2v8 also meant a mandatory switch for us from Syncsort to DFSort. We
found that DFSort required many more dynamically allocated sort work
data sets and much bigger region sizes for the reorg utilities compared
to what we had needed under DB2v7 and Syncsort. Since we ran large
numbers of simultaneous reorgs, this all put a big strain on sort dasd
volumes, etc. We all became adept at reorg utility restart.

It appears that enough people complained that a PTF was issued for DB2v8
to allow a SORTDATA NO option. SORTDATA is still the default, but at
least you have the other option. I do not apply maintenance in our
shop, so I do not have the PTF number. It is more than one year old.
We were able to revert to the old mechanism for those warehouse
tablespaces. Those reorgs ran faster and with fewer failures without
the SORTDATA "improvement".

This SORTDATA issue does not apply to online reorg. DB2v8 has several
enhancements compared to DB2v7 (ability to use discard, for example).
After our upgrade to DB2v8, we have converted most of our reorgs to
online reorgs.

Mark

The IDUG DB2-L Listserv is only part of your membership in IDUG. DB2-L list archives, the FAQ, and delivery preferences are at http://www.idug.org/lsidug under the Listserv tab. While at the site, you can also access the IDUG Online Learning Center, Tech Library and Code Place, see the latest IDUG conference information, and much more. If you have not yet signed up for Basic Membership in IDUG, available at no cost, click on Member Services at http://www.idug.org/lsms