Page Limit

I have a table reaching it's number of pages limit. Informix limit from the manual: Data pages per fragment 16,775,134 I was thinking of using the "alter fragment" option to "defrag" this table (lessen the number of pages, or "reorg" the table basically). I read that the alter fragment option uses transaction logging, and can cause long transaction problems. Is there a way to bypass this ? Or perhaps, a better way for me to do this ? IDS Version 12.10.FC9W1 AIX 7 Dirk

Hi Dirk, Some options: 1. Follow your plan but add a lot more logical logs to the system beforehand. 2. Compress the table: this requires Advanced Enterprise edition to be compliant with licensing. This option is really only a deferment of the problem. 3. Delete some data :-) 4. You can create a partitioned (fragmented) table with the same schema and then attach the current table to it, provided you can identify a suitable key on which to partition. Use this procedure outlined below at your own risk. The steps for (4) are: - Create a new table fragmented by expression with an identical schema to the current one. Identical means same column names in the same order with the same data types and same "not null" constraints. The table must be placed in a dbspace of the same page size. - The expression-based fragments should be ideally be based on the primary key of the existing. Let's say, for example, the key is a serial, serial8, bigserial or other incrementing id field called "my_id". Find the maximum value of this key in the existing table and create as many expression-based partitions as you need in the new partitioned table based on this key. All these partitions should only accept values greater than the current maximum value, i.e. all the data in the current table must not match the criteria for any of the new fragments. Do not create a remainder fragment for now. - The new table should not have any constraints of any kind including PK, FK(s) for now except maybe "not null". It should also not have any indices. - Drop any foreign keys on other tables referencing the original table. Be aware that if these FKs don't have an explicit index supporting them any implicit index will be dropped too. - Run this to attach the old table to the new partitioned table: alter fragment on table new_partitioned_table attach old_full_up_table aspartition my_existing_data my_id<=CURRENT_MAXIMUM_ID_VALUE; Docs for this command: https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_0081.htm - Rename the new partitioned table to the name of the original table. - Rebuild your original indices on the new fragmented table. Bear in mind that if you have LOG_INDEX_BUILDS set to 1, you need to make sure you have enough logical log space. - Re-add your original primary key on the new fragmented table. - Re-add any FKs on the new fragmented table with the NOVALIDATE option. - Re-add any FKs on other tables referencing the original table with the NOVALIDATE option. - Re-add any other constraints with the NOVALIDATE option (12.10.xC7+). If you do decide to use this procedure I strongly recommend you test it first using a copy of your production schema and some dummy data. Make sure the procedure meets your expectations before proceeding in live. It may also be possible to do this with range partitioning instead of expression, something to try. Ben.

Thanks Ben ! We are also looking at option 3. Our company (business) is very bad at archiving. I depend on other people, to tell me what to archive. But we are looking into that one. And what I like about that option is, less data, better performance overall. Thank you

Dirk, you did not mention that page size of the dbspace the table resides in but if its 4K (default for AIX) consider reorg' the table into a 12k or 16K dbspace (dbspaces with larger page sizes can store more rows per data page). Create the table as a raw table in order to avoid long trx when populating and when done alter it back to standard. Then of course define an archiving strategy. Mark

Dirk: If the table just needs to be reorganized to gather up previously freed up space, I would use the DEFRAGMENT API function (optional) along with the REPACK SHRINK API function to reduce the number of extents and the number of pages to the minimum number that will hold the data. If, on the other hand, most of the table's pages are full, then you have several good choices: 1 - Move the table to a dbspace with wider pages to reduce the number of pages. While you are doing that, try to size the pages to minimize the number of wasted bytes per row on each page. 2 - Partition the table. N partitions gets you N*16million pages available for data. 3 - Clean out older data that is no longer needed freeing up space, then REPACK SHRINK the table so that the newly freed up space it contiguous.