Add tablespace for existing table in DB2

Question

I am using DB2 in my project. I have a table which has following DDL:

CREATE TABLE TEST (
            ID INTEGERT NOT NULL,
            ACCESS_INFO VARCHAR (2048) NOT NULL,
            NAME VARCHAR (2048))

When trying to create index for the column "ACCESS_INFO", am getting following error:

The index or index extension "xxxx" cannot be created or altered because the combined length of the specified columns is too long..

I referred [1] which explains to add large tablespace for creating indexes for long column. But this for new tables. What is the proper way to add it for an existing table?

[1] https://dba.stackexchange.com/questions/19894/create-index-for-long-columns

What is the Db2-server operating system (z/OS, i-series, Linux/Unix/Windows) ? — mao

mao mao · Accepted Answer · 2019-06-25T08:08:47

The documentation for SQL0614N explains this clearly, a 4K pagesize tablespace allows max 1K key-length, an 8KB pagesize tablespace allows a 2k max key length, 16KB pagesize allows max 4k key-length, 32kb pagesize allows max 8kb key length.

You can recreate your table in the next higher pagesize to avoid this issue.

I assume you are using a 4Kb pagesize tablespace (this is the default), so the next higher pagesize is 8KB.

Create (or re-use suitable existing) an 8KB pagesize bufferpool (CREATE BUFFERPOOL ) Create (or re-use suitable existing) an 8KB tablespace (CREATE TABLESPACE) to use the new bufferpool of matching pagesize.

If the table contains data already that you don't want to reload, then use the stored procedure ADMIN_MOVE_TABLE to move the existing data to new tablespace(s).

If the table contains no rows (or can be quickly reloaded), drop the table and recreate with modified DDL to add IN $your_8kb_tablespace.

If the table will contain many millions of rows, it is wise to split it over three tablespaces (one for data, one for indexes, one for long data) - you can also do this with the create table statement, refer to the documentation for details.

For very large tables consider using range-partitioning and hash-partitioning if licensing and hardware allows that.

Add tablespace for existing table in DB2

1 Answers