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?


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.

  • Thanks for links. If I have varchar(20), does it count as 20 (ASCII) bytes or 40 bytes (UTF-16), or ...? I have page table size 4K which fits well all columns (otherwise I would get error at CREATE TABLE). What would be the solution in my case? – dma_k Jun 26 '12 at 16:58
  • With DB2 varchar you are allowed up to 32,672 bytes of length. There is also a 2 byte length identifier. I am assuming that is separate, but I'm not positive. Had a hard time finding documentation on that. UTF-16 does take 2 bytes per character. So varchar(20) at UTF-16 would minimally 2 bytes (for null) and maximally 42 bytes for complete storage. So if your varchar(4000) is UTF-16 I would understand to take 2 bytes as null and 8002 bytes completely filled. – Chris Aldrich Jun 26 '12 at 17:41
  • @dma_k varchar(20) is 20 bytes, not 20 characters. If you have multibyte characters you'll need to account for that in the column length. – Ian Bjorhovde Mar 5 '13 at 18:24

If you plan to use the index for equality, you could add a checksum computed column:

alter table line add value_checksum as checksum(value)
create index IX_LINE_VALUE_CHECKSUM on line(value_checksum)

You can then select like:

select * from line where checksum_value = checksum('search text here')
  • 1
    Good solution, but SQL query is now non-portable to other databases. For example, if I use ORM like Hibernate, I need to write custom queries per DB. Also you said index for equality, but any checksum is targeting "non-equality", I mean that two strings may have same checksums, and then I assume that DB will do char-by-char comparison. In general case your query is not correct and should be rewritten as select * from line where checksum_value = checksum('search text here') and line = 'search text here' and hopefully DB optimizer will evaluate it in the same order. – dma_k Jun 26 '12 at 9:02
  • @dma_k: A very small portion of SQL is portable. – ypercubeᵀᴹ Jun 26 '12 at 15:34
  • @ypercube: Yes, but that small portion of SQL syntax covers 80% of the use cases :) – dma_k Jun 26 '12 at 16:47
  • 1
    You are either kidding or I have misunderstood you. 90% of the MySQL tables out there have an AUTO_INCREMENT column. A similar percent for SQL-Server have an IDENTITY. None of the two is portable to any other DBMS. Different datatypes (or same with different names) are also common across DBMS. – ypercubeᵀᴹ Jun 26 '12 at 16:52
  • You have a good point here, because MySQL does not follow SQL standard recommendations. However id integer generated by default as identity works perfectly OK for HSQL and DB2. And on SQL Server as you said. Not bad? – dma_k Jun 26 '12 at 17:50

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.