DB2 – How to Create Index for Long Columns

db2

I am looking for alternative to create index on long column:

create table line
(
    field_key   integer not null,
    value       varchar(4000),
    ...
);

create index key_value_idx on line (field_key, value);

results DB2 SQL Error: SQLCODE=-614, SQLSTATE=54008. Documentation says: The sum of the stored lengths of the specified columns must not be greater than 1024. For such cases in MySQL there is syntax:

create index key_value_idx on line (field_key, value(1000));

and HSQLDB just works without any limitations.

What is the analogue for DB2?

Best Answer

Here is the information on the CREATE INDEX statement for DB2. You may also want to check these links on designing indexes and space requirements for indexes as they also deal with your question on what factors into index page space.

Based on the last link it would seem to me that the page size for the index would be your column lengths + the index overhead. This may take you beyond the page size for your table. (Maybe that is what is happening here.) You could also try to place your table in a bigger tablespace. It may place the index in a bigger page size as well.

EDIT: I have tested this locally as well to see. Using Control Center I was able to get a more detailed picture of the error message. Per DB2: "Explanation:

The index could not be created or altered because the sum of the key column internal lengths would exceed the index key length limit. Also, an index cannot be created using a LONG VARCHAR, LONG VARGRAPHIC, or LOB column. If the index is defined on a typed table, there is additional index overhead that reduces the maximum length by 4 bytes. The index may be altered by an ALTER TABLE or ALTER NICKNAME statement that alters the data type of one or more columns.

An index extension could not be created because the sum of the columns returned by the GENERATE KEY function would exceed the index key length limit.

The index key length limit is based on the page size of the tablespace used by the index:

Max Key Length  Page size
--------------  ---------
1K              4K
2K              8K
4K              16K
8K              32K

The statement cannot be processed. The specified index or index extension was not created or the table or nickname could not be altered.

User response:

To modify the index definition or alter column, eliminate one or more key columns and reduce the key length to the permitted maximum. For an index extension definition, specify a different GENERATE KEY function, or redefine the function to reduce the length of the returned row."

As mentioned in my comment below your key size would be at least 32 + (4000 * 2 (for UTF-16)) + 2 (for varchar length identifier) + index overhead, so you are already at 8034+ bytes. This means just over 7K. I'm guessing you are not defining a tablespace, so it will default to USERSPACE1, which is an 8K tablespace), so you have a 2K max key length as shown by the table above, plus your error message). In order to make this key, you have to do one of two things

  1. Alter your table definition so it isn't so big (probably reduce your varchar size) so you can fit within a 1K max key definition. I'm guessing you don't want to do this so then you end doing the next one:

  2. Since your key space requires 8K key length, you need a 32K page size. You can leave the table in a default 4K tablespace, but the index MUST be in a 32K tablespace. If you are using automatic storage you could easily create a tablespace with the following: 

create bufferpool BP32K immediate size 250 automatic pagesize 32K;
create large tablespace TS32K pagesize 32K managed by automatic
storage bufferpool BP32K; 
create table line (
  field_key   integer not null,
  value       varchar(4000),
  ... ) index in TS32K; 
create index key_value_idx on line (field_key, value);

This will place the index in the 32K tablespace and you should be fine.

Related Question