Rename a partition

Greetings. I may let the IBM manual writers off the hook on this one owing to a 12.1 manual when I'm using an 11.5 server. I'm trying to rename a partition within a fragmentation strategy. My practice table, before I try this surgery on the production server, is defined as: create table tfrags ( pname char(8),pnumber integer ) fragment by expression partition tfrags_1_10 ( (pnumber >= 0) AND (pnumber <= 10) ) in pedbs, partition tfrags_11_20 ( (pnumber >= 11) AND (pnumber <= 20)) in rcdbs, partition tfrags_21_40 ( (pnumber >= 21) AND (pnumber <= 40)) in inqdbs ; I then "realized" I've made that last fragment, the partition named tfrags_21_40, too broad and need to narrow it's range. So I ran this ALTER command: alter fragment on table tfragsmodify tfrags_21_40 --X to tfrags_21_30 ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs to ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs ; Notice the commented-out clause. When I tried to rename the fragment to a new partition name it gave me a syntax error, with the caret pointing under the 0 of the 30 in the expression, like this: to tfrags_21_30 ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs #___________________________________________^ # 201: A syntax error has occurred. So I used the uncommented clause and that has worked, with a side effect: I now have a fragment with a nondescriptive partition name; the name of the dbspace I stuck it into. dbschema now shows: fragment by expression partition tfrags_1_10 ((pnumber >= 0 ) AND (pnumber <= 10) ) in pedbs , partition tfrags_11_20 ((pnumber >= 11 ) AND (pnumber <= 20 ) ) in rcdbs , ((pnumber >= 21 ) AND (pnumber <= 30 ) ) in inqdbs -- YIKES: Name vanished! See? No name on the last partition. Now I know from sysfragments (and my fragments.sh script) that it *does* have a partition name: The name of its host dbspace , inqdbs. But my attempts to change the name have only given me unspecified syntax errors: alter fragment on table tfrags modifypartition inqdbs to partition tfrags_21_30 #__________________^ # 201: A syntax error has occurred. This is EXACTLY the syntax for renaming a partition, given in the 12.1 manual, page 2-46 (page 92 using acrobat). Where am I going off the deep end on this one? Well, I might hazard this guess: That section of the manual discusses "Renaming fragments in range interval fragmentation"; this is pure expression fragmentation, similar to that of my targeted production table. (At over 100-million rows, I *ain't* changing that!) Thanks much for the right syntax for renaming an expression-fragmented partition. -- Jacob (Concise is my middle name. NOT!) S.

Am 05.12.2014 19:13, schrieb JACOB SALOMON: > Greetings. > > I may let the IBM manual writers off the hook on this one owing to a 12.1 > manual when I'm using an 11.5 server. > > I'm trying to rename a partition within a fragmentation strategy. > My practice table, before I try this surgery on the production server, is > defined as: > create table tfrags > ( pname char(8),> pnumber integer > ) > fragment by expression > partition tfrags_1_10 ( (pnumber >= 0) AND (pnumber <= 10) ) in pedbs, > partition tfrags_11_20 ( (pnumber >= 11) AND (pnumber <= 20)) in rcdbs, > partition tfrags_21_40 ( (pnumber >= 21) AND (pnumber <= 40)) in inqdbs > ; > > I then "realized" I've made that last fragment, the partition named > tfrags_21_40, too broad and need to narrow it's range. So I ran this ALTER > command: > alter fragment on table tfrags> modify tfrags_21_40 > --X to tfrags_21_30 ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs > > to ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs > ; > > Notice the commented-out clause. When I tried to rename the fragment to a new > partition name it gave me a syntax error, with the caret pointing under the 0 > of the 30 in the expression, like this: > > to tfrags_21_30 ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs > #___________________________________________^ > # 201: A syntax error has occurred. > > So I used the uncommented clause and that has worked, with a side effect: > I now have a fragment with a nondescriptive partition name; the name of the > dbspace I stuck it into. dbschema now shows: > fragment by expression > partition tfrags_1_10 ((pnumber >= 0 ) AND (pnumber <= 10) ) in pedbs , > partition tfrags_11_20 ((pnumber >= 11 ) AND (pnumber <= 20 ) ) in rcdbs , > ((pnumber >= 21 ) AND (pnumber <= 30 ) ) in inqdbs -- YIKES: Name vanished! > > See? No name on the last partition. > > Now I know from sysfragments (and my fragments.sh script) that it *does* have > a partition name: The name of its host dbspace , inqdbs. But my attempts to > change the name have only given me unspecified syntax errors: > > alter fragment on table tfrags modify> partition inqdbs > to partition tfrags_21_30 > #__________________^ > # 201: A syntax error has occurred. > > This is EXACTLY the syntax for renaming a partition, given in the 12.1 manual, > page 2-46 (page 92 using acrobat). > > Where am I going off the deep end on this one? Well, I might hazard this > guess: That section of the manual discusses "Renaming fragments in range > interval fragmentation"; this is pure expression fragmentation, similar to > that of my targeted production table. (At over 100-million rows, I *ain't* > changing that!) > > Thanks much for the right syntax for renaming an expression-fragmented > partition. > > -- Jacob (Concise is my middle name. NOT!) S. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Hi, you will find the documentation for V11.50 online here: http://www-01.ibm.com/support/knowledgecenter/SSGU8G_11.50.0/com.ibm.start.doc/w elcome.htm or some to d/l also here: http://www-01.ibm.com/support/docview.wss?uid=swg27010058#KC HTH dic_k --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. http://www.avast.com

Richard, Thank you. The web page provided the extra facet of information I could not find in the PDF manual. What I needed to do was to repeat the fragmentation expression, something like: alter fragment on table tfrags modify partition tfrags_21_40 to partition tfrags_21_30 ( (pnumber >= 21) AND (pnumber <= 30)) in inqdbs -- Restating expression but keep same dbspace This worked in the production environment. Unfortunately, despite that no data needing to be moved, the engine started recreating the partition from scratch. At 9 million pages in the partition and only 8 million pages of log space, with LTXHWM set to 50%, it would have run many hours then spent many hours rolling back. I saved time my killing it as soon as I realized what was happening. :-( Oh well, I had another solution (plan-B) in the wings (details not necessary for this thread) and have started implementing that one. -- Jacob S.