DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?

Michael Jessen

DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?



We have a 72 partition large (dssize 8g) tablespace that contains LOB data(3Meg blobs).

In reading the DB2 Version 9.1 for z/OS Admin Guide one of the restrictions for adding a partition is:
"You cannot add a new partition to an existing partitioned table space if the table has LOB columns."

I'm assuming this means we also cannot Rotate Partitions - can anyone confirm if rotating parts with LOBs is possible or not?

If I can't add and/or Rotate partitions, our next option is to rename the current table to something like CURRENTNAME_HIST, create a new partitioned table called CURRENTNAME_NEW, and then create a view called CURRENTNAME that joins the _HIST and _NEW tables together.
We don't see any restrictions that don't allow a Rename on a LOB table... anyone know of any?

If anyone knows of any issues/concerns with the planned approach to rename & use views or knows of a better approach - we are open to suggestions.

Thank you!
Mike

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Phil Grainger

Re: DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?
(in response to Michael Jessen)
Hi Mark



Take a look at PK50692



I think you'll find this to be good news :-)



Phil Grainger

Grainger Database Solutions Ltd

_____

From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Mike Jessen
Sent: 17 December 2009 18:41
To: [login to unmask email]
Subject: [DB2-L] DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?





We have a 72 partition large (dssize 8g) tablespace that contains LOB
data(3Meg blobs).



In reading the DB2 Version 9.1 for z/OS Admin Guide one of the restrictions
for adding a partition is:

"You cannot add a new partition to an existing partitioned table space if
the table has LOB columns."



I'm assuming this means we also cannot Rotate Partitions - can anyone
confirm if rotating parts with LOBs is possible or not?



If I can't add and/or Rotate partitions, our next option is to rename the
current table to something like CURRENTNAME_HIST, create a new partitioned
table called CURRENTNAME_NEW, and then create a view called CURRENTNAME that
joins the _HIST and _NEW tables together.

We don't see any restrictions that don't allow a Rename on a LOB table...
anyone know of any?



If anyone knows of any issues/concerns with the planned approach to rename &
use views or knows of a better approach - we are open to suggestions.



Thank you!

Mike




_____

< http://www.idug.org > IDUG - The Worldwide DB2 User Community!

The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
not already an IDUG member, please register here.
< http://www.idug.org/register >


_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.idug.org/rug/index.html - with almost 150 IDUG Regional User Groups,
there is probably one near you!
Regional User Groups are your local connection to the Worldwide DB2 User Community
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: DB2 z/OS v9: Adding/Rotate a partitioned LOB tablespace?
(in response to Phil Grainger)
Mike, I believe that the DB2 for z/OS V9 Administration Guide is incorrect
in this instance (and Phil Grainger mentioned to me that he thought that
this might be the case, as the DB2 V9 SQL Reference mentions no such
restriction). On a DB2 V9 subsystem, I can successfully execute both ALTER
TABLE ADD PARTITION and ALTER TABLE ROTATE PARTITION FIRST TO LAST for a
table containing a LOB column.

Robert


On Thu, Dec 17, 2009 at 1:41 PM, Mike Jessen <[login to unmask email]> wrote:

>
>
> We have a 72 partition large (dssize 8g) tablespace that contains LOB
> data(3Meg blobs).
>
>
>
> In reading the DB2 Version 9.1 for z/OS Admin Guide one of the restrictions
> for adding a partition is:
>
> "You cannot add a new partition to an existing partitioned table space if
> the table has LOB columns."
>
>
>
> I'm assuming this means we also cannot Rotate Partitions - can anyone
> confirm if rotating parts with LOBs is possible or not?
>
>
>
> If I can't add and/or Rotate partitions, our next option is to rename the
> current table to something like CURRENTNAME_HIST, create a new partitioned
> table called CURRENTNAME_NEW, and then create a view called CURRENTNAME that
> joins the _HIST and _NEW tables together.
>
> We don't see any restrictions that don't allow a Rename on a LOB table...
> anyone know of any?
>
>
>
> If anyone knows of any issues/concerns with the planned approach to rename
> & use views or knows of a better approach - we are open to suggestions.
>
>
>
> Thank you!
>
> Mike
>
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community!] < http://www.idug.org >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L