DB2 LUW Reorg Question

Bill Gallagher

DB2 LUW Reorg Question
DB2 LUW v8.2 Fixpak 14 on AIX.

I've been searching for an answer all day through books, manuals, materials
from various classes I've taken, and the web but have been unable to get a
definitive answer to this question.

If I perform a reorg of a table and use the "USE tablespace-name"
parameter, are the indexes also rebuilt in the specified system temp
tablespace and then copied to their permanent tablespace at the end? Or do
the indexes always get rebuilt in their regular tablespace such that you
could potentially have both the old and new versions of the indexes
co-residing in the permanent tablespace?

Thanks in advance for any responses.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]


-----------------------------------------
*******************************************************************
CONFIDENTIAL: This communication, including
attachments, is intended only for the exclusive use of addressee
and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, you are hereby
notified that you have received this document in error, and any
use, review, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this
communication and destroy any and all copies of this
communication.
*******************************************************************

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Gunning

Re: DB2 LUW Reorg Question
(in response to Bill Gallagher)
Bill, a classic offline table reorg results in all indexes being rebuilt as
the last phase of the reorg. I have never seen a problem with
"old" indexes hanging around if the reorg completed successfully. Now, if
the reorg fails you can have temporary files left over which I have not
found doc on. When I wrote my last book, I tried to get it but was not
successful. My interpretation is that they are rebuilt using tempspace if
specified and then copied, however as you indicated, the doc does not
clearly state it. Sounds like you are having some problem, if you can share
it with us we might be able to help or if not, then open a pmr with support.
Note that with a range partitioned table you can reorg specified indexes. PG

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Tuesday, January 20, 2009 3:34 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 LUW Reorg Question

DB2 LUW v8.2 Fixpak 14 on AIX.

I've been searching for an answer all day through books, manuals, materials
from various classes I've taken, and the web but have been unable to get a
definitive answer to this question.

If I perform a reorg of a table and use the "USE tablespace-name"
parameter, are the indexes also rebuilt in the specified system temp
tablespace and then copied to their permanent tablespace at the end? Or do
the indexes always get rebuilt in their regular tablespace such that you
could potentially have both the old and new versions of the indexes
co-residing in the permanent tablespace?

Thanks in advance for any responses.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]


-----------------------------------------
*******************************************************************
CONFIDENTIAL: This communication, including
attachments, is intended only for the exclusive use of addressee
and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, you are hereby
notified that you have received this document in error, and any
use, review, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this
communication and destroy any and all copies of this
communication.
*******************************************************************

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Gunning

Re: DB2 LUW Reorg Question
(in response to Philip Gunning)
Bill, also in the DB2 9.5 Performance Guide there is a pretty good
description of reorg and the temporary files involved. I believe this info
was included in 9.5 doc after some "prompting". PG

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Tuesday, January 20, 2009 3:34 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 LUW Reorg Question

DB2 LUW v8.2 Fixpak 14 on AIX.

I've been searching for an answer all day through books, manuals, materials
from various classes I've taken, and the web but have been unable to get a
definitive answer to this question.

If I perform a reorg of a table and use the "USE tablespace-name"
parameter, are the indexes also rebuilt in the specified system temp
tablespace and then copied to their permanent tablespace at the end? Or do
the indexes always get rebuilt in their regular tablespace such that you
could potentially have both the old and new versions of the indexes
co-residing in the permanent tablespace?

Thanks in advance for any responses.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]


-----------------------------------------
*******************************************************************
CONFIDENTIAL: This communication, including
attachments, is intended only for the exclusive use of addressee
and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, you are hereby
notified that you have received this document in error, and any
use, review, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this
communication and destroy any and all copies of this
communication.
*******************************************************************

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Bill Gallagher

Re: DB2 LUW Reorg Question
(in response to Philip Gunning)
Phil,

If I understand your response correctly, the "classic offline table reorg"
will not see the new indexes being rebuilt in-flight as part of the actual
table rebuild, but will in fact be a separate last phase of the table
reorg. So while we will have two separate copies of the table (old and
new) in existence during the table reorg, we will not see two separate
copies of each index (old and new) in existence during the reorg. Is that
correct?

The situation that prompted this thread:

One of our applications just put in a script that runs at the end of their
batch cycle that will reorganize certain tables in their database.

For each table they are reorganizing, they have the following two commands:

REORG TABLE $TBNAME
REORG INDEXES ALL FOR TABLE $TBNAME

. . . where $TBNAME is a variable containing the actual name of the table
to be reorged.

The problem they are running into is they are getting occasional -289
SQLCODE ("unable to allocate new pages in tablespace") on some reorgs, both
on the REORG TABLE and INDEX commands.

For the REORG TABLE commands, I'm pretty sure that it is because the newly
reorged copy of the table is being created in the same tablespace as the
original un-reorged copy of the table, and there is not enough free space
in the tablespace to handle two copies of the table on a temporary basis.
The obvious solution would be to add the "USE tablespace-name" clause to
the REORG TABLE command, since we have a sufficiently sized system temp
tablespace to hold a temp copy of our largest table.

Also, it seems that the REORG INDEXES command is extraneous, since the
indexes will be rebuilt by the REORG TABLE command. I believe the reason
why they had that in there was because the original version of their script
used the INPLACE clause on the REORG TABLE command, and that would not
reorg the indexes.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]




Phil Gunning
<[login to unmask email]
TS.COM> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] DB2 LUW Reorg Question


01/20/2009 09:53
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






Bill, a classic offline table reorg results in all indexes being rebuilt as
the last phase of the reorg. I have never seen a problem with
"old" indexes hanging around if the reorg completed successfully. Now, if
the reorg fails you can have temporary files left over which I have not
found doc on. When I wrote my last book, I tried to get it but was not
successful. My interpretation is that they are rebuilt using tempspace if
specified and then copied, however as you indicated, the doc does not
clearly state it. Sounds like you are having some problem, if you can share
it with us we might be able to help or if not, then open a pmr with
support.
Note that with a range partitioned table you can reorg specified indexes.
PG

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Tuesday, January 20, 2009 3:34 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 LUW Reorg Question

DB2 LUW v8.2 Fixpak 14 on AIX.

I've been searching for an answer all day through books, manuals, materials
from various classes I've taken, and the web but have been unable to get a
definitive answer to this question.

If I perform a reorg of a table and use the "USE tablespace-name"
parameter, are the indexes also rebuilt in the specified system temp
tablespace and then copied to their permanent tablespace at the end? Or do
the indexes always get rebuilt in their regular tablespace such that you
could potentially have both the old and new versions of the indexes
co-residing in the permanent tablespace?

Thanks in advance for any responses.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]


-----------------------------------------
*******************************************************************
CONFIDENTIAL: This communication, including
attachments, is intended only for the exclusive use of addressee
and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, you are hereby
notified that you have received this document in error, and any
use, review, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this
communication and destroy any and all copies of this
communication.
*******************************************************************

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html

Philip Gunning

Re: DB2 LUW Reorg Question
(in response to Bill Gallagher)
Bill, you are correct and the reorg index step is extraneous, it is reorging
indexes that have just been recreated and is double duty. I have found this
at quite a few locations in the past. Cheers, Phil

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Wednesday, January 21, 2009 1:41 PM
To: [login to unmask email]
Subject: Re: [DB2-L] DB2 LUW Reorg Question

Phil,

If I understand your response correctly, the "classic offline table reorg"
will not see the new indexes being rebuilt in-flight as part of the actual
table rebuild, but will in fact be a separate last phase of the table
reorg. So while we will have two separate copies of the table (old and
new) in existence during the table reorg, we will not see two separate
copies of each index (old and new) in existence during the reorg. Is that
correct?

The situation that prompted this thread:

One of our applications just put in a script that runs at the end of their
batch cycle that will reorganize certain tables in their database.

For each table they are reorganizing, they have the following two commands:

REORG TABLE $TBNAME
REORG INDEXES ALL FOR TABLE $TBNAME

. . . where $TBNAME is a variable containing the actual name of the table
to be reorged.

The problem they are running into is they are getting occasional -289
SQLCODE ("unable to allocate new pages in tablespace") on some reorgs, both
on the REORG TABLE and INDEX commands.

For the REORG TABLE commands, I'm pretty sure that it is because the newly
reorged copy of the table is being created in the same tablespace as the
original un-reorged copy of the table, and there is not enough free space
in the tablespace to handle two copies of the table on a temporary basis.
The obvious solution would be to add the "USE tablespace-name" clause to
the REORG TABLE command, since we have a sufficiently sized system temp
tablespace to hold a temp copy of our largest table.

Also, it seems that the REORG INDEXES command is extraneous, since the
indexes will be rebuilt by the REORG TABLE command. I believe the reason
why they had that in there was because the original version of their script
used the INPLACE clause on the REORG TABLE command, and that would not
reorg the indexes.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]




Phil Gunning
<[login to unmask email]
TS.COM> To
Sent by: DB2 Data [login to unmask email]
Base Discussion cc
List
<[login to unmask email] Subject
ORG> Re: [DB2-L] DB2 LUW Reorg Question


01/20/2009 09:53
PM


Please respond to
DB2 Database
Discussion list
at IDUG
<[login to unmask email]
2-l.org>






Bill, a classic offline table reorg results in all indexes being rebuilt as
the last phase of the reorg. I have never seen a problem with
"old" indexes hanging around if the reorg completed successfully. Now, if
the reorg fails you can have temporary files left over which I have not
found doc on. When I wrote my last book, I tried to get it but was not
successful. My interpretation is that they are rebuilt using tempspace if
specified and then copied, however as you indicated, the doc does not
clearly state it. Sounds like you are having some problem, if you can share
it with us we might be able to help or if not, then open a pmr with
support.
Note that with a range partitioned table you can reorg specified indexes.
PG

-----Original Message-----
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf
Of [login to unmask email]
Sent: Tuesday, January 20, 2009 3:34 PM
To: [login to unmask email]
Subject: [DB2-L] DB2 LUW Reorg Question

DB2 LUW v8.2 Fixpak 14 on AIX.

I've been searching for an answer all day through books, manuals, materials
from various classes I've taken, and the web but have been unable to get a
definitive answer to this question.

If I perform a reorg of a table and use the "USE tablespace-name"
parameter, are the indexes also rebuilt in the specified system temp
tablespace and then copied to their permanent tablespace at the end? Or do
the indexes always get rebuilt in their regular tablespace such that you
could potentially have both the old and new versions of the indexes
co-residing in the permanent tablespace?

Thanks in advance for any responses.

-------------------------------------------------------
Bill Gallagher, Logical DBA
IT / Infrastructure Services
Phoenix Life Insurance Company
(W) 860-403-6327
(C) 860-539-5576
[login to unmask email]


-----------------------------------------
*******************************************************************
CONFIDENTIAL: This communication, including
attachments, is intended only for the exclusive use of addressee
and may contain proprietary, confidential and/or privileged
information. If you are not the intended recipient, you are hereby
notified that you have received this document in error, and any
use, review, copying, disclosure, dissemination or distribution is
strictly prohibited. If you are not the intended recipient, please
notify the sender immediately by return e-mail, delete this
communication and destroy any and all copies of this
communication.
*******************************************************************

______________________________________________________________________

* IDUG 2009 Melbourne, Australia * 18-20 March * http://IDUG.ORG/Events *
______________________________________________________________________




IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You
should have gotten an e-mail with the temporary password assigned to your
account. Please log in and update your member profile. If you are not
already an IDUG.org member, please register at
http://www.idug.org/component/juser/register.html


______________________________________________________________________

* IDUG 2009 Rome, Italy * 5-9 October * http://IDUG.ORG/Events *
______________________________________________________________________



IDUG.org was recently updated requiring members to use a new password. You should have gotten an e-mail with the temporary password assigned to your account. Please log in and update your member profile. If you are not already an IDUG.org member, please register at http://www.idug.org/component/juser/register.html