Undoing DPSIs in DB2 V9 z/os

Art McEwen

Undoing DPSIs in DB2 V9 z/os
Hi folks,

I'm planning on converting some of our larger non clustering/partitioning indexes to Data Partitioning Secondary indexes so that it's easier to manage the space in multiple smaller datasets. Creating a DPSI automatically alters the table to be table partitioned instead of having a partitioned tablespace, no fuss. I've done it before no problem.

However.... My change management folks are going to want to see a backout plan. :( I can't figure out how to alter a partitioned table back to being a table in a partitioned tablespace short of dropping the tablespace and recreating it.

Looks like it's alterable one way but not the other, am I missing something?

Thanks,

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

Philip Sevetson

Re: Undoing DPSIs in DB2 V9 z/os
(in response to Art McEwen)
You're right, it's not a reversible process. The fallback is to have the full re-implementation DDL and an UNLOAD handy.

-----Original Message-----
From: IDUG DB2-L [mailto:[login to unmask email] On Behalf Of Art McEwen
Sent: Thursday, May 05, 2011 4:15 PM
To: [login to unmask email]
Subject: [DB2-L] Undoing DPSIs in DB2 V9 z/os

Hi folks,

I'm planning on converting some of our larger non clustering/partitioning indexes to Data Partitioning Secondary indexes so that it's easier to manage the space in multiple smaller datasets. Creating a DPSI automatically alters the table to be table partitioned instead of having a partitioned tablespace, no fuss. I've done it before no problem.

However.... My change management folks are going to want to see a backout plan. :( I can't figure out how to alter a partitioned table back to being a table in a partitioned tablespace short of dropping the tablespace and recreating it.

Looks like it's alterable one way but not the other, am I missing something?

Thanks,

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv

_________________________________________________________________

Register NOW for the IDUG DB2 Tech Conference in Anaheim, May 2-6, 2011!
_________________________________________________________________
If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L
is the home of IDUG's Listserv