Online conversion of DB2 table page size

To meet the new requirements of a table created by a DB2 user on the size, number of fields, or row length, the page size of the existing table is changed. The following describes how to convert the page size of a DB2 table online for your reference.

Product: DB2 UDB

Platform: cross-platform

Version: v8.1 and v8.2

For DB2, the maximum table size, the maximum number of fields that can be included, and the maximum length of records in each row are related to the page size of the table.
All Tables created in the tablespace must have the same page size as the tablespace, while the page size of the DB2 buffer pool or tablespace can be 4 K, 8 K, 16 K, and 32 K, the number of fields, row length, and table size are as follows:
4 K page size 8 K page size 16 K page size 32 K page size
Maximum number of fields in a table: 500 1012 1012 1012
The maximum length of each table row is 4005 8101 16293 32677
Maximum table size: 64G 128G 256G 512G

For this reason, you may sometimes change the page size of an existing table to meet the new requirements of the table on the size, number of fields, or row length. Because the page size cannot be changed after the table is created, you must create a new table that meets the page size requirements.

If the database system used does not allow a long period of time that the table data cannot be accessed due to the deletion of the original table and the reconstruction of the new table, you can use the method described here, converts the size of an online page to a table.

To convert the "online" page size that the table data can still be accessed when the table's page size is changed, follow these steps:

1) if no tablespace or buffer pool of the page size needs to be used exists, you should first create the buffer pool and tablespace of the required page size.

2) You can use the DB2LOOK tool to obtain the DDL statements for creating a table, such:

Db2look-d <Database Name>-e-tw <Table Name>-o <output file name>
Modify the output file to create a new table with a table name different from the original table in a tablespace with the required page size.

3) use the modified DDL script to create a new table that is the same as the original table.

4) use the following statement to insert data from the original table to the new table:

Db2 "insert into <new Table Name> (select * from <original table name> )"
5) Delete the original table:

Db2 "drop table <original table name>"

After this operation, the data in the table cannot be accessed by the application.

6) Rename the new table name to the original table name:

Db2 "rename table <new table Name> to <original table name>"

7) restore the application's access to the data in the table.

At this time, the page size of the new table renamed the original table name has been converted from the original page size to the required page size, thus meeting the user's new requirements for the table size. Table data is only inaccessible at an instant from step 4 to step 5 of the entire process. Data in other timelines is in an accessible "online" status.

Note that the preceding steps only apply to the absence of objects dependent on the table in the database. If there are objects dependent on the table in the database, after the DDL statements containing these objects are saved, remove these dependencies first, delete the original table and rename the new table, and then recreate these dependencies.