ALTER FRAGMENT with HDR

There are a couple of ways to reduce the number of extents that a table/index is using. I plan to use ALTER FRAGMENT ON TABLE <tablename> INIT IN <dbspacename>. How well does this work with HDR ON in case of large tables? I would have logs backed up continuously on the primary. Assume there is sufficient disk and log space, are there any other precautions I need to take? (Informix v11.70.FC7, HP-UX 11.31. Ia).

if its a very large table you may want to attempt this in a maintenance window since the begin log of the alter table will remain open for a longer period of time and this may trip a long transaction error if the system remains busy.

I'd be very careful with "alter fragment" because of the time it can take and once you're a fair way into the process, any roll backs can be heavy and slow. I can't recommend enough practising on a production-sized test system. If you haven't got a full test system, building a similar database with just the table and data and any tables it references by a foreign key would suffice. The process is likely to involve rebuilding indices and so I'd set PSORT_DBTEMP in the environment to point to a suitable large filesystem or RAM disk and set PDQPRIORITY in the session as appropriate. You can also set PSORT_NPROCS or leave Informix to use its defaults. This may avoid slower partial index builds in DBSPACETEMP. If you have logged index builds turned on, which is optional with HDR, you could create a lot of logs from this too. You mention it's one way. Unloading to a named pipe, re-loading to a new table via HPL and building indices manually would almost certainly be faster especially if there are no foreign keys. Deluxe mode would be needed to replicate via HDR. I would politely ask whether you really need to rebuild a table because of too many extents. There is no discernible performance impact in any modern engine in having a lot of extents. If it's in a dbspace with a page size larger than 2 kb the maximum number of extents is significantly larger than the 238 or so per fragment a 2 kb page size allows. Setting the "next size" of future extents on the table can be an effective way of avoiding any further extent growth.