IDS Forum

Re: Page Limit

Posted By: BENJAMIN THOMPSON
Date: Wednesday, 7 November 2018, at 9:38 a.m.

In Response To: Page Limit (DIRK MOOLMAN)

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 as partition 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/ids_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.

Messages In This Thread

• Page Limit
  DIRK MOOLMAN -- Wednesday, 7 November 2018, at 6:11 a.m.
  • Re: Page Limit
    BENJAMIN THOMPSON -- Wednesday, 7 November 2018, at 9:38 a.m.
    • Re: Page Limit
      DIRK MOOLMAN -- Thursday, 8 November 2018, at 3:49 a.m.
  • Re: Page Limit
    MARK JALKIEWICZ -- Thursday, 8 November 2018, at 10:43 a.m.
  • Re: Page Limit
    ART KAGEL -- Monday, 12 November 2018, at 4:39 p.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.