Defrag large fragmented table in 7.32

Hi all, I would like some advise on defraging a large 45GB table that is fragmented, round robin accross 2 dbspaces (dbspace1, dbspaces2) and detached index on a different dbspace (dbspace3). The table will reside in 2 brand new separate dbspaces (dbspace4, dbspace5). I am thinking of doing online ALTER FRAGMENT ON TABLE. Should I run this? ALTER FRAGMENT ON TABLE tablenameINIT FRAGMENT BY ROUND ROBIN IN dbspace4, dbspace5 Logging is an issue since downtime is almost impossible to arrange, and I don't want to turn logging off. Any way around this? How much log space do I need in order for it to not get transaction rollback? What will happen to the index that's on the dbspace3 when I do the Alter Fragment? System: IDS 7.32. HP-UX 11. 4 CPU L2000 HP Server. 7GB RAM. We have 3 dbspaces for logical logs. Each on separate 2+2 raid with 20GB capacity each. Please advise on the best solution.

cacunet@hotmail.com wrote: > Hi all, > I would like some advise on defraging a large 45GB table that is > fragmented, round robin accross 2 dbspaces (dbspace1, dbspaces2) and > detached index on a different dbspace (dbspace3). The table will reside > in 2 brand new separate dbspaces (dbspace4, dbspace5). > > I am thinking of doing online ALTER FRAGMENT ON TABLE. > Should I run this? > ALTER FRAGMENT ON TABLE tablename> INIT FRAGMENT BY ROUND ROBIN IN dbspace4, dbspace5 Yes, that will work well. > Logging is an issue since downtime is almost impossible to arrange, and > I don't want to turn logging off. Any way around this? How much log > space do I need in order for it to not get transaction rollback? Just disable logging for this table during the reorg, see below. > What will happen to the index that's on the dbspace3 when I do the > Alter Fragment? It would be rebuilt during the reorg by fixing up all of the nodes as the pages are rewritten. Better to drop and recreate it (indeed all constraints that require indexes and their associated indexes on the table except FOREIGN KEY constraints and indexes, it takes too long to recheck the constraints), the reorg will go faster and the index can be rebuild using parallel scanning and sorting. Total runtime should be much shorter. > System: IDS 7.32. HP-UX 11. 4 CPU L2000 HP Server. 7GB RAM. We have 3 > dbspaces for logical logs. Each on separate 2+2 raid with 20GB capacity > each. 2+2 RAID10 I hope! OK, so the desired sequence of events is: - BEGIN WORK; - SET PDQPRIORITY 100; - LOCK TABLE <tabname> IN EXCLUSIVE MODE; - DROP INDEX <indexname>; - ALTER TABLE <tabname> TYPE (RAW); - ALTER FRAGMENT ON TABLE tablename INIT FRAGMENT BY ... - ALTER TABLE <tablename> TYPE (STANDARD); - CREATE ... INDEX <indexname ON <tablename>... - COMMIT WORK; Art S. Kagel

>2+2 RAID10 I hope! >OK, so the desired sequence of events is: >- BEGIN WORK; >- SET PDQPRIORITY 100; >- LOCK TABLE <tabname> IN EXCLUSIVE MODE; >- DROP INDEX <indexname>; >- ALTER TABLE <tabname> TYPE (RAW); >- ALTER FRAGMENT ON TABLE tablename INIT FRAGMENT BY ... >- ALTER TABLE <tablename> TYPE (STANDARD); >- CREATE ... INDEX <indexname ON <tablename>... >- COMMIT WORK; Yes, it's RAID10. So this means that the table being reorg will not be accessible by the users? How long do you estimate this process will run? I understand that there's a lot of different variables that can impact the run time, but I just want a rough idea of how long it will take assuming the rest of the chain in our system is on par with the rest of the world.

<cacunet@hotmail.com> wrote in message news:1138913359.955371.36300@g43g2000cwa.googlegroups.com... > So this means that the table being reorg will not be accessible by the > users? > How long do you estimate this process will run? I understand that > there's a lot of different variables that can impact the run time, but > I just want a rough idea of how long it will take assuming the rest of > the chain in our system is on par with the rest of the world. I have a server a bit more powerful than yours and it takes about 10 hours on a 30G table. What Art might have mentioned also is that you will need at least as much free space in the dbspaces as the table already takes. I'd seriously consider HPL if I were you. Is there an IDS 7.32?

cacunet@hotmail.com wrote: >>2+2 RAID10 I hope! > > >>OK, so the desired sequence of events is: > > > >>- BEGIN WORK; >>- SET PDQPRIORITY 100; >>- LOCK TABLE <tabname> IN EXCLUSIVE MODE; >>- DROP INDEX <indexname>; >>- ALTER TABLE <tabname> TYPE (RAW); >>- ALTER FRAGMENT ON TABLE tablename INIT FRAGMENT BY ... >>- ALTER TABLE <tablename> TYPE (STANDARD); >>- CREATE ... INDEX <indexname ON <tablename>... >>- COMMIT WORK; > > > Yes, it's RAID10. > > So this means that the table being reorg will not be accessible by the > users? > How long do you estimate this process will run? I understand that > there's a lot of different variables that can impact the run time, but > I just want a rough idea of how long it will take assuming the rest of > the chain in our system is on par with the rest of the world. Neil's right this is going to take time. If you absolutely cannot have this table offline for more than a brief period, then you'll have to copy the data to a new table in the new dbspace, create new matching indexes and constraints with different names, then when you can get exclusive access and keep the users off for a short time, rename the old table then rename the new table to the old name. Once that's done you can drop the old table or scan it for records added deleted and update since you made the copy. As Neil says, using hploader reading the original table from one pipe and writing to a second pipe may be a bit faster than the in-place reorg that ALTER FRAGMENT buys you, especially if you can do the writting in express mode, but it's still not going to be instantaneous. You will have somedowntime if you want to lock the users out during the copy. That's the problem with trying to reog while there are active users, you cannot control what the users are doing while you are trying to copy the table. BTW, Neil is also correct that there is no IDS version 7.32. The last 7.xx release is 7.31 and the current maintenance release IB is 7.31xD8. Art S. Kagel

Neil Truby wrote: > Is there an IDS 7.32? No - 7.31 is the latest. There is I4GL 7.32, and I would suspect that version led to the confusion. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2005.02 -- http://dbi.perl.org/

First, thank you for all the responses. My Informix Version is 7.31.FD3. Can't fool you guys. Looks like I'll need to arrange some downtime. I think I can get a 24 hr window. My short story: We are installing a new disk array, so disk space is not a problem. With all the prompt and knowledgeable responses, I wish I'd post a question when I was doing the disk layout design, but I think i did ok :). Raid 2+2 and 1+1 = Raid10. Raid 2+1 = Raid5 VG RAID Dbspace vg05 2+2 logical log #1 vg06 2+2 logical log #2 vg07 2+2 logical log #3 vg08 2+2 phys log vg09 2+2 indexa vg10 1+1 integtrans1=table1044 vg11 1+1 integtrans2=table1045 vg12 1+1 tempdbs vg13 1+1 rootdbs + baandbs vg14 1+1 table1041 vg15 1+1 spare vg16 1+1 misc r/w tables vg17 2+1 table1042 vg18 2+1 index1 vg19 2+1 archive

cacunet@hotmail.com wrote: > First, thank you for all the responses. > > My Informix Version is 7.31.FD3. Can't fool you guys. > > Looks like I'll need to arrange some downtime. I think I can get a 24 > hr window. > > My short story: We are installing a new disk array, so disk space is > not a problem. With all the prompt and knowledgeable responses, I wish > I'd post a question when I was doing the disk layout design, but I > think i did ok :). > > Raid 2+2 and 1+1 = Raid10. Raid 2+1 = Raid5 NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! NO RAID5!!! Otherwise it looks reasonable. Art S. Kagel > VG RAID Dbspace > vg05 2+2 logical log #1 > vg06 2+2 logical log #2 > vg07 2+2 logical log #3 > vg08 2+2 phys log > vg09 2+2 indexa > vg10 1+1 integtrans1=table1044 > vg11 1+1 integtrans2=table1045 > vg12 1+1 tempdbs > vg13 1+1 rootdbs + baandbs > vg14 1+1 table1041 > vg15 1+1 spare > vg16 1+1 misc r/w tables > vg17 2+1 table1042 > vg18 2+1 index1 > vg19 2+1 archive >