Creating indexes for columns with higher field size in DB2

Indexes are created in tables for increasing the performance. For instance, we can create indexes for columns of tables from which data is fetched more frequently. But when creating indexes for columns with higher field size in DB2, there is an error thrown as:

SQL Error [54008]: The index or index extension “IDX_AT_XX” cannot be created or altered because the combined length of the specified columns is too long.. SQLCODE=-614, SQLSTATE=54008, DRIVER=3.59.81

The error is due to the insufficient pagesize of tablespace for creating index. The pagesize of default tablespace is 4Kb. When we create an index for a column having higher field size, it requires a tablespace with higher pagesize.

We need to create the table in a new tablespace with increased pagesize. You can use the following script to create the table in new tablespace:

create bufferpool BP32K immediate size 250 automatic pagesize 32K;
create large tablespace TS32K pagesize 32K managed by automatic
storage bufferpool BP32K; 
create table TestTable (
   ...DDL of table
)
create index key_value_idx on line (field_key, value);

In case, if you have already have data in existing table, we need to re-create the table in new tablespace and copy the data. ADMIN_MOVE_TABLE stored procedure can be used here. Following is an example of the stored procedure using which we can move the data from current table to table in a different tablesapce with same name:

CALL SYSPROC.ADMIN_MOVE_TABLE(
<$TABLE_SCHEMA_OF_TEST_TABLE>,
'TEST',
(SELECT TBSPACE FROM SYSCAT.TABLES where TABNAME = 'TEST' AND TABSCHEMA = <$TABLE_SCHEMA_OF_TEST_TABLE>),
'TS32K',
(SELECT TBSPACE FROM SYSCAT.TABLES where TABNAME = 'TEST' AND TABSCHEMA = <$TABLE_SCHEMA_OF_TEST_TABLE>),
'',
'',
'',
'',
'',
'MOVE');

^_^