convert non-partitioned TS to range partitioned without DR/CR

Kenneth Lahn

convert non-partitioned TS to range partitioned without DR/CR

Is there a way in DB2 z/OS V11 to convert an existing segmented, tablespace to a range-partitioned tablespace without dropping and recreating it. 

Phil Grainger

convert non-partitioned TS to range partitioned without DR/CR
(in response to Kenneth Lahn)
?Sorry, no


Not even with clever ISV tools


Phil G

BMC Software

________________________________
From: Kenneth Lahn <[login to unmask email]>
Sent: 09 January 2017 19:32
To: [login to unmask email]
Subject: [DB2-L] - convert non-partitioned TS to range partitioned without DR/CR


Is there a way in DB2 z/OS V11 to convert an existing segmented, tablespace to a range-partitioned tablespace without dropping and recreating it.

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.

Kai Stroh

RE: convert non-partitioned TS to range partitioned without DR/CR
(in response to Kenneth Lahn)

To my knowledge, no.

If it only contains one table, you can convert it to a PBG universal tablespace by using ALTER TABLESPACE with MAXPARTITIONS.

If it were a classic partitioned tablespace, you could convert it to a PBR universal tablespace by using ALTER TABLESPACE with SEGSIZE.

If it contains more than one table, there is currently no way to convert it to a different type or split it into multiple single-table tablespaces without unload / drop / create / load. At least there is no way that I know of.

Philip Sevetson

convert non-partitioned TS to range partitioned without DR/CR
(in response to Kenneth Lahn)
**please note my email address change**
Kenneth,

I’d urge you to

1) Create a new table in the design which you need

2) UNLOAD the data up to a certain time (you do have a date/time of some kind on your data, right?) from the old table

3) LOAD the data to the new table

4) START the old table as read-only

5) UNLOAD the remaining data (from after “a certain time” to the present) from the old table

6) LOAD that data RESUME to the new table

7) RENAME the old table to some historical/place-holder name

8) RENAME the new table to the original name of the old table

9) REBIND as necessary

10) Resume operations

Note that there’s an outage from 4 to 10, but it’s hopefully short, depending on how long it takes to copy the most recent data from old to new copies of the table. Also, I know you’ll need twice as much DASD as the table contains, but if you can’t afford the time which UNLOAD/DROP/CREATE/LOAD would take, this is your best alternative.

Also, depending on your costs, buying a third-party product to run your loads and unloads may save enough time to be worth doing – I don’t know what your constraints and costs are.

Philip Sevetson
Computer Systems Manager
450 West 33rd Street
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Kenneth Lahn [mailto:[login to unmask email]
Sent: Monday, January 09, 2017 2:32 PM
To: [login to unmask email]
Subject: [DB2-L] - convert non-partitioned TS to range partitioned without DR/CR


Is there a way in DB2 z/OS V11 to convert an existing segmented, tablespace to a range-partitioned tablespace without dropping and recreating it.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)