Db2 (LUW) Index on Expression and Inplace Reorg

Joe Geller

Db2 (LUW) Index on Expression and Inplace Reorg

I know that an Inplace Reorg cannot be run on a table with an extended index, but I can't find clear documentation as to whether an Index on Expression is an extended index.  Does anyone know the answer to that and whether or not you can do an inplace reorg on such a table?

Joe

Joe Geller

RE: Db2 (LUW) Index on Expression and Inplace Reorg
(in response to Joe Geller)

And the answer is - you cannot do an inplace reorg if you have an index on expression.   AAARRGGGHHH.  Index on expression is such as great feature, but so is inplace reorg.  How could they do that???   Time for an RFE.

Joe





In Reply to Joe Geller:

I know that an Inplace Reorg cannot be run on a table with an extended index, but I can't find clear documentation as to whether an Index on Expression is an extended index.  Does anyone know the answer to that and whether or not you can do an inplace reorg on such a table?

Joe

Nadir Doctor

Db2 (LUW) Index on Expression and Inplace Reorg
(in response to Joe Geller)
Hi Joe,

We've often found that inplace reorgs can blow up the high water mark for a
tablespace and therefore prefer to use a temporary tablespace of requisite
pagesize when such reorgs are needed.

Thank you.


Best Regards,
Nadir



On Sat, Dec 30, 2017 at 7:08 PM, Joe Geller <[login to unmask email]> wrote:

> And the answer is - you cannot do an inplace reorg if you have an index on
> expression. AAARRGGGHHH. Index on expression is such as great feature,
> but so is inplace reorg. How could they do that??? Time for an RFE.
>
> Joe
> ------------------------------
> ------------------------------
>
>
>
> In Reply to Joe Geller:
>
> I know that an Inplace Reorg cannot be run on a table with an extended
> index, but I can't find clear documentation as to whether an Index on
> Expression is an extended index. Does anyone know the answer to that and
> whether or not you can do an inplace reorg on such a table?
>
> Joe
>
>
> -----End Original Message-----
>

Joe Geller

RE: Db2 (LUW) Index on Expression and Inplace Reorg
(in response to Nadir Doctor)

Hi Nadir,

I thought that the use of a temporary tablespace was an option of a classic reorg, not an inplace reorg?

Joe

In Reply to Nadir Doctor:

Hi Joe,

We've often found that inplace reorgs can blow up the high water mark for a
tablespace and therefore prefer to use a temporary tablespace of requisite
pagesize when such reorgs are needed.

Thank you.


Best Regards,
Nadir



On Sat, Dec 30, 2017 at 7:08 PM, Joe Geller <[login to unmask email]> wrote:

> And the answer is - you cannot do an inplace reorg if you have an index on
> expression. AAARRGGGHHH. Index on expression is such as great feature,
> but so is inplace reorg. How could they do that??? Time for an RFE.
>
> Joe
> ------------------------------
> ------------------------------
>
>
>
> In Reply to Joe Geller:
>
> I know that an Inplace Reorg cannot be run on a table with an extended
> index, but I can't find clear documentation as to whether an Index on
> Expression is an extended index. Does anyone know the answer to that and
> whether or not you can do an inplace reorg on such a table?
>
> Joe
>
>
> -----End Original Message-----
>

Nadir Doctor

Db2 (LUW) Index on Expression and Inplace Reorg
(in response to Joe Geller)
Hi Joe,

One work-around which could be used to perform such an online reorg is to
move this table to a new tablespace via an admin move table.

Thank you.


Best Regards,
Nadir



On Sat, Dec 30, 2017 at 8:38 PM, Joe Geller <[login to unmask email]> wrote:

> Hi Nadir,
>
> I thought that the use of a temporary tablespace was an option of a
> classic reorg, not an inplace reorg?
>
> Joe
>
> In Reply to Nadir Doctor:
>
> Hi Joe,
>
> We've often found that inplace reorgs can blow up the high water mark for a
> tablespace and therefore prefer to use a temporary tablespace of requisite
> pagesize when such reorgs are needed.
>
> Thank you.
>
>
> Best Regards,
> Nadir
>
>
>
> On Sat, Dec 30, 2017 at 7:08 PM, Joe Geller wrote:
>
> > And the answer is - you cannot do an inplace reorg if you have an index
> on
> > expression. AAARRGGGHHH. Index on expression is such as great feature,
> > but so is inplace reorg. How could they do that??? Time for an RFE.
> >
> > Joe
> > ------------------------------
> > ------------------------------
> >
> >
> >
> > In Reply to Joe Geller:
> >
> > I know that an Inplace Reorg cannot be run on a table with an extended
> > index, but I can't find clear documentation as to whether an Index on
> > Expression is an extended index. Does anyone know the answer to that and
> > whether or not you can do an inplace reorg on such a table?
> >
> > Joe
> >
> >
> > -----End Original Message-----
> >
>
>
> -----End Original Message-----
>

Jeff Goss

RE: Db2 (LUW) Index on Expression and Inplace Reorg
(in response to Nadir Doctor)

Yeah I believe Nadir meant an in table space classic reorg here.

 

The restriction could definitely be lifted but it would take some non-trivial effort to code and test.  The recovery model was changed to support Shared Data by deferring the final move/cleanup work to a runtime agent when the database is first brought back up, so loading the IoE code is now possible. This wasn't the case when IoE was first being developed, and the other options at the time were not very palatable from either development or end user standpoint. 

Joe Geller

RE: Db2 (LUW) Index on Expression and Inplace Reorg
(in response to Jeff Goss)

Jeff,

Thank you for the response.  It's good to hear that the change is possible (non-trivial but not impossible).  I opened an RFE requesting the change (https://www.ibm.com/developerworks/rfe/execute?use_case=viewRfe&CR_ID=114749).

I've been extolling the benefits of IOE and now I have to modify my enthusiasm with a warning about inplace reorgs.

Hopefully there will be enough support for the RFE, and hopefully Db2 development will think so to.

Joe

In Reply to Jeff Goss:

Yeah I believe Nadir meant an in table space classic reorg here.

 

The restriction could definitely be lifted but it would take some non-trivial effort to code and test.  The recovery model was changed to support Shared Data by deferring the final move/cleanup work to a runtime agent when the database is first brought back up, so loading the IoE code is now possible. This wasn't the case when IoE was first being developed, and the other options at the time were not very palatable from either development or end user standpoint.