Index length of in the DB2v9.

[vamsi]

Hi,

I'm trying to create a table with a unique index consits of 5 columns with the datatypes ( VARCHAR(80), VARCHAR(144), VARCHAR(1020), VARCHAR(1020), VARCHAR(1020)). But my create table structure is failing as my primary key length is getting exceeded. But I've defined my page size as 32K. which means I can create an index with a length of 8K, but my key size is less than 8k. Can you please suggest me what could be the problem?

Your answer for this is highly appriciated.

Thanks & Regards,
Vamsi

[Rabbit]

Indexes have overhead, and that can count against your max. It is also affected by index options.

This article by IBM discusses the limits of SQL and XML.

http://publib.boulder.ibm.com/infocenter/db2luw/v9/index.jsp?topic=%2Fcom.ibm.db2.udb.admin.doc%2Fdoc%2Fr0001029.htm

[vamsi]

Hi Rabbit,

Thanks for the response. I had gone through this and found that it is quarter of the page size. I've declared my bufferpool as BP32K and hence I'm exepcting that it should accept a length of 8K. But it is still failing with a SQLCODE of -613.

Thanks & Regards,
Vamsi

[Rabbit]

You didn't read it carefully enough. The maximum is a fourth of your index page size. However, it also says it's unlikely you can ever get that max because of index options and overhead. You think it's a fourth, but it's not it can be considerably less than that.