ALTER FRAGMENT adding multiple fragments

Hi again. I am trying to decipher the manual pages on how to add fragments to a fragmented table. The syntax that finally worked was two separate ALTER FRAGMENT statements. But that's the cowards way out IMO. Here are some of the syntaxes that failed: -------------------------------- alter fragment on table tfragsadd partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs add partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs # ^ # 201: A syntax error has occurred. OK, maybe I needed a comma after the first new partition, although the manual does not seem to indicate the need. --------------------------------- alter fragment on table tfrags add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs,# ^ # 201: A syntax error has occurred. # add partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs Nope, it does not like that comma either. Maybe the second "add" clause is at fault in the first example; let's try taking it out: ---------------------------------- alter fragment on table tfragsadd partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs # ^ # 201: A syntax error has occurred. ---------------------------------- I have tried a few more permutations, with and without commas, omitting the second "partition" and just listing the new partition's name, etc. But whatever I've tried, it's always a syntax error with only a vague clue (the location) of what's wrong with the syntax. Like I said at the outset, I have no problem creating these additional partitions one at a time. But I feel I am missing some point if I can't add them in one statement. This is 11.5, BTW. Ideas, anyone? Thanks! -- Jacob S.

If you can use interval fragmentation you can decide on the range of "pnumbers" which should be stored in each fragment then let the fragment automatically add new fragments as needed (when it see a new value). no down time, no user intervention. John F. Miller III STSM, Lead Architect miller3@us.ibm.com 503-747-1366 IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 12/03/2014 03:54:52 PM: > From: "JACOB SALOMON" <jakesalomon@yahoo.com> > To: ids@iiug.org > Date: 12/03/2014 03:55 PM > Subject: ALTER FRAGMENT adding multiple fragments [34281] > Sent by: ids-bounces@iiug.org > > Hi again. > > I am trying to decipher the manual pages on how to add fragments to a > fragmented table. The syntax that finally worked was two separate ALTER > FRAGMENT statements. But that's the cowards way out IMO. Here are some of the > syntaxes that failed: > -------------------------------- > alter fragment on table tfrags> add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs > add partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs > # ^ > # 201: A syntax error has occurred. > OK, maybe I needed a comma after the first new partition, although the manual > does not seem to indicate the need. > --------------------------------- > alter fragment on table tfrags > add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs,> # ^ > # 201: A syntax error has occurred. > # > add partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs > > Nope, it does not like that comma either. Maybe the second "add" clause is at > fault in the first example; let's try taking it out: > ---------------------------------- > alter fragment on table tfrags> add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs > > partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs > # ^ > # 201: A syntax error has occurred. > ---------------------------------- > I have tried a few more permutations, with and without commas, omitting the > second "partition" and just listing the new partition's name, etc. But > whatever I've tried, it's always a syntax error with only a vague clue (the > location) of what's wrong with the syntax. > > Like I said at the outset, I have no problem creating these additional > partitions one at a time. But I feel I am missing some point if I can't add > them in one statement. > > This is 11.5, BTW. > > Ideas, anyone? > > Thanks! > > -- Jacob S. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Am 04.12.2014 00:54, schrieb JACOB SALOMON: > Hi again. > > I am trying to decipher the manual pages on how to add fragments to a > fragmented table. The syntax that finally worked was two separate ALTER > FRAGMENT statements. But that's the cowards way out IMO. Here are some of the > syntaxes that failed: > -------------------------------- > alter fragment on table tfrags> add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs > add partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs > # ^ > # 201: A syntax error has occurred. > OK, maybe I needed a comma after the first new partition, although the manual > does not seem to indicate the need. > --------------------------------- > alter fragment on table tfrags > add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs,> # ^ > # 201: A syntax error has occurred. > # > add partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs > > Nope, it does not like that comma either. Maybe the second "add" clause is at > fault in the first example; let's try taking it out: > ---------------------------------- > alter fragment on table tfrags> add partition tfrags_rem3 ( (mod(pnumber,5) = 3)) in rpdbs > > partition tfrags_rem4 ( (mod(pnumber,5) = 4)) in archdbs > # ^ > # 201: A syntax error has occurred. > ---------------------------------- > I have tried a few more permutations, with and without commas, omitting the > second "partition" and just listing the new partition's name, etc. But > whatever I've tried, it's always a syntax error with only a vague clue (the > location) of what's wrong with the syntax. > > Like I said at the outset, I have no problem creating these additional > partitions one at a time. But I feel I am missing some point if I can't add > them in one statement. > > This is 11.5, BTW. > > Ideas, anyone? > > Thanks! > > -- Jacob S. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > Hi Jacob, looking into my pdf-copy of 11.50.xC4 docs, 'IBM Informix Guide to SQL: Syntax' pg 2-7 the syntax diagramme does not look like it is possible to have more than once one of the keywords like ADD or MODIFY. IIRC we tested about 6 yrs ago, if ALTER or copy & rename will be faster and ended up with copying into RAW table then rebuiding the indexes using PDQ and drop old table & rename new copy. So I have no experience with ALTER .... ADD FRAGMENT. YMMV, but you cannot have a rule overlap at any given time and the records will be moved if you change rules using modify, thus indexes must be updated as well (which is a logged operation). Most of our fragmented tables used to be real huge, > 750 million rows and to create a new table having the new fragmentation and of type RAW was so much faster that we did not even reevalute when migrating to V11.70. dic_k --- Diese E-Mail wurde von Avast Antivirus-Software auf Viren geprüft. http://www.avast.com