DB2 V11 - change the partition key for an existing table that is Partitioned by Range

Paul Kautza

DB2 V11 - change the partition key for an existing table that is Partitioned by Range

We are in the process to change the partition key for an existing table that is Partitioned by Range. Currently the table is                                PARTITION BY RANGE (COL1 asc)

Need to redefine  PARTITION BY RANGE (COL1 asc, COL2 asc, COL3 asc).

We have a vendor package that can handle this process but it suggest to drop and recreate the table. With V11, there are many Table Alter Statements that will not require a drop and recreate of the table.

Tried Alter table     Alter |_partition – alteration_| But seems like I am not setting up the Syntax correctly or maybe there is not an Alter Statement for this option.

Any Suggestions?

Walter Janißen

AW: DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Paul Kautza)
Hi

This alter still requires a drop/create of the entire tablespace, even in V12.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Paul Kautza [mailto:[login to unmask email]
Gesendet: Montag, 10. Juli 2017 17:11
An: [login to unmask email]
Betreff: [DB2-L] - DB2 V11 - change the partition key for an existing table that is Partitioned by Range


We are in the process to change the partition key for an existing table that is Partitioned by Range. Currently the table is PARTITION BY RANGE (COL1 asc)

Need to redefine PARTITION BY RANGE (COL1 asc, COL2 asc, COL3 asc).

We have a vendor package that can handle this process but it suggest to drop and recreate the table. With V11, there are many Table Alter Statements that will not require a drop and recreate of the table.

Tried Alter table Alter |_partition – alteration_| But seems like I am not setting up the Syntax correctly or maybe there is not an Alter Statement for this option.

Any Suggestions?

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Steen Rasmussen

DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Walter Janißen)
Walter is absolutely correct – you can change the LIMITKEY but not expand it (at least yet).

ALTER PARTITION
Specifies that the partitioning limit key for the identified partition is to be
changed.
This clause applies only to tables in a partitioned table space. ALTER
PARTITION must not be specified for a table in a partition-by-growth table
space or for tables that have XML columns. If there are any pending definition
changes to insert a new partition in the middle of the table for a
range-partitioned table space, altering the limit key is not allowed for any
partition in the same table until the newly inserted partition is materialized by
a REORG execution.
integer
If integer is specified, it must be in the range 1 to n, where n is the number
of partitions in the table. integer is the physical partition number. When
this option is specified for any partition except for the last, both the
identified partition and the partition following it are placed in advisory
REORG-pending (AREOR) status.


Steen

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, July 10, 2017 10:13 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: DB2 V11 - change the partition key for an existing table that is Partitioned by Range

CAUTION: This email originated from outside of CA. Do not click links or open attachments unless you recognize the sender and know the content is safe.
Hi

This alter still requires a drop/create of the entire tablespace, even in V12.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Paul Kautza [mailto:[login to unmask email]
Gesendet: Montag, 10. Juli 2017 17:11
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - DB2 V11 - change the partition key for an existing table that is Partitioned by Range


We are in the process to change the partition key for an existing table that is Partitioned by Range. Currently the table is PARTITION BY RANGE (COL1 asc)

Need to redefine PARTITION BY RANGE (COL1 asc, COL2 asc, COL3 asc).

We have a vendor package that can handle this process but it suggest to drop and recreate the table. With V11, there are many Table Alter Statements that will not require a drop and recreate of the table.

Tried Alter table Alter |_partition – alteration_| But seems like I am not setting up the Syntax correctly or maybe there is not an Alter Statement for this option.

Any Suggestions?

-----End Original Message-----

________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8452&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=Px3XNS5a7W8wBG0Un5ksjvLXKLbirJ77C9iGkEVYBxA&s=l-rrTD420v99f5If32Ca921hEqTuvfy_Hpavq08RXDA&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D182067-26anc-3Dp182067-23p182067&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=Px3XNS5a7W8wBG0Un5ksjvLXKLbirJ77C9iGkEVYBxA&s=eIycTYC0FbsmM7sY4UObfneyPIFK3aD3Zns9X6ebCkg&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=Px3XNS5a7W8wBG0Un5ksjvLXKLbirJ77C9iGkEVYBxA&s=rDGVUWKwACz8vJ2Lm62m_V6IVGdhLvsLhcR8UunCQB0&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=Px3XNS5a7W8wBG0Un5ksjvLXKLbirJ77C9iGkEVYBxA&s=PWy4ZibzOuZgRftVwS6JSLOOCuJO5Q2nRFh67FxS2xc&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=Px3XNS5a7W8wBG0Un5ksjvLXKLbirJ77C9iGkEVYBxA&s=-565dnOsYgj-wXeZK2k-yUtxSBPFBo4B0MMnyKrb5s8&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=_hRq4mqlUmqpqlyQ5hkoDXIVh6I6pxfkkNxQuL0p-Z0&r=8G_DmW0CTa1-mRqqCc2bPPrkruU7frF_6CmvV0GqNtc&m=Px3XNS5a7W8wBG0Un5ksjvLXKLbirJ77C9iGkEVYBxA&s=V2dQhCtEQvhBHNam9oVgyNgSkPC7Qu0ji6RHr_tC6Hg&e=

________________________________

Larry Jardine

DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Walter Janißen)
You may want to consider creating a new version of the tablespace/table/indexes and cross-loading the data (and RENAME TABLE). That will allow you the means of fallback in case of any issues.

Larry Jardine
Aetna


From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, July 10, 2017 11:13 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: DB2 V11 - change the partition key for an existing table that is Partitioned by Range

Hi

This alter still requires a drop/create of the entire tablespace, even in V12.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Paul Kautza [mailto:[login to unmask email]
Gesendet: Montag, 10. Juli 2017 17:11
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - DB2 V11 - change the partition key for an existing table that is Partitioned by Range


We are in the process to change the partition key for an existing table that is Partitioned by Range. Currently the table is PARTITION BY RANGE (COL1 asc)

Need to redefine PARTITION BY RANGE (COL1 asc, COL2 asc, COL3 asc).

We have a vendor package that can handle this process but it suggest to drop and recreate the table. With V11, there are many Table Alter Statements that will not require a drop and recreate of the table.

Tried Alter table Alter |_partition – alteration_| But seems like I am not setting up the Syntax correctly or maybe there is not an Alter Statement for this option.

Any Suggestions?

-----End Original Message-----

________________________________
Attachment Links: image001.png (3 k) https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_do_-3Fdownload-3D1-26fid-3D8452&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=ampMtkk2wPix6eEOd2LsKRPJ4gs7ZAeyUvH4Zr1gkhU&s=fKQ8v6GwESIRFUogf5IBp2y65U2Zsr3NikdN4mAk8no&e=
Site Links: View post online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_st_-3Fpost-3D182067-26anc-3Dp182067-23p182067&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=ampMtkk2wPix6eEOd2LsKRPJ4gs7ZAeyUvH4Zr1gkhU&s=wJk1phTBwQrCSYDoHLxVtw2FxdUg2CQeEcrL-OYLt8M&e= View mailing list online https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_fo_si_-3Ftopic-3D19&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=ampMtkk2wPix6eEOd2LsKRPJ4gs7ZAeyUvH4Zr1gkhU&s=7Vpwc-JOKCk-RBVqorysnwf7YXTujUAOKLFwZ9Bingg&e= Start new thread via email<mailto:[login to unmask email]> Unsubscribe from this mailing list<mailto:[login to unmask email]?Subject=Unsubscribe> Manage your subscription https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_us_to_&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=ampMtkk2wPix6eEOd2LsKRPJ4gs7ZAeyUvH4Zr1gkhU&s=PbjYl3s50d6eRqhKPy_68ZCDh2f481QTTrzKVvB_6xc&e=

This email has been sent to: [login to unmask email]<mailto:[login to unmask email]>

Setup a data refresh task in less time than it takes to make a cup of coffee + save up to 90% in CPU
ESAi's BCV5 & XDM fast data refresh & Test Data Mgmt products will make you a hero to users. See
http://www.ESAIGroup.com/idug https://urldefense.proofpoint.com/v2/url?u=http-3A__www.ESAIGroup.com_idug&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=ampMtkk2wPix6eEOd2LsKRPJ4gs7ZAeyUvH4Zr1gkhU&s=5F6tYROqKXUtBIVuHxkAVwfU7LvCavE-Bz3I-WG7GoU&e=

Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2 https://urldefense.proofpoint.com/v2/url?u=http-3A__www.idug.org_p_cm_ld_fid-3D2&d=DwMFaQ&c=wluqKIiwffOpZ6k5sqMWMBOn0vyYnlulRJmmvOXCFpM&r=wfE0VOZJYg9uIjc5BuTMYexNGrByDwh1giBA3sSm6qo&m=ampMtkk2wPix6eEOd2LsKRPJ4gs7ZAeyUvH4Zr1gkhU&s=Gy3qqGvr_iq5LJ-RxhVlBKZQ72XmaaQC7JTwnWDPOEs&e=

________________________________

This e-mail may contain confidential or privileged information. If you think you have received this e-mail in error, please advise the sender by reply e-mail and then delete this e-mail immediately. Thank you. Aetna

Bruce Williamson

RE: DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Paul Kautza)

Are you changing the primary key?

Cheers
Bruce

P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and find

Martin &#197;lund

RE: DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Bruce Williamson)

Please give your vore for the RFEs suggesting this kind of online alter.

 

Regards Martin 

Bruce Williamson

RE: DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Martin Ålund)

Howzit Martin?

Do you have a specific RFE in mind?

Cheers
Bruce

 

P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" group and find

Martin &#197;lund

RE: DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Bruce Williamson)

I am sorry I don't have the RFE number available. I just know there is one that I wrote at least two years ago. Search for Alter Partitioning Key.

 

Martin 

Edited By:
Martin Alund[Organization Members] @ Jul 12, 2017 - 08:40 PM (Europe/Stockholm)

Walter Jani&#223;en

AW: DB2 V11 - change the partition key for an existing table that is Partitioned by Range
(in response to Bruce Williamson)
Hi

It’s RFE 26491

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Bruce Williamson [mailto:[login to unmask email]
Gesendet: Mittwoch, 12. Juli 2017 01:49
An: [login to unmask email]
Betreff: [DB2-L] - RE: DB2 V11 - change the partition key for an existing table that is Partitioned by Range


Howzit Martin?

Do you have a specific RFE in mind?

Cheers
Bruce



P.S. Want to make a difference but don't know how? Join the RFE Community (Requests For Enhancement https://www.ibm.com/developerworks/rfe ), no time like the present!!!

P.P.S. While you're at it why not join the "All DB2 for z/OS" https://www.ibm.com/developerworks/rfe/execute?use_case=groupLanding&GROUP_ID=1283 group and find

-----End Original Message-----
Attachments

  • image001.png (2.6k)