By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
437,610 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 437,610 IT Pros & Developers. It's quick & easy.

What is Max Length for Index Column

P: n/a
Hi,

This is probably a really dumb question but I have a table containing a
varchar 4000 column. The app team is asking me to index this column to
improve performance. I know this can't be done but I'm looking for
proof of the max length of the index key column in the DB2 doc and I
can't find it. I believe it is still 1024 (including index overhead).

Can anyone tell me what the current max length for a column to be in an
index and where I might find 'paper' doc to support that?

Thanks so much!!!

Martin

Nov 12 '05 #1
Share this Question
Share on Google+
7 Replies


P: n/a
See Table 11:

http://publib.boulder.ibm.com/infoce...n/r0001029.htm

Larry Edelstein

mghale wrote:
Hi,

This is probably a really dumb question but I have a table containing a
varchar 4000 column. The app team is asking me to index this column to
improve performance. I know this can't be done but I'm looking for
proof of the max length of the index key column in the DB2 doc and I
can't find it. I believe it is still 1024 (including index overhead).

Can anyone tell me what the current max length for a column to be in an
index and where I might find 'paper' doc to support that?

Thanks so much!!!

Martin

Nov 12 '05 #2

P: n/a
Thanks a lot Larry! That was exactly what I was looking for!!!

Nov 12 '05 #3

P: n/a
I's think you could still INDEX the COLUMN. If you ADD another COLUMN
with GENERATED ALWAYS AS LEFT(col1, 100) or something like that, it is
doable. And, depending on the data, for example a comment, the first
one-hundred characters should be more than enough for an INDEX.

I just tried:

DECLARE GLOBAL TEMPORARY TABLE A (A VARCHAR(4000), B VARCHAR(100)
GENERATED ALWAYS AS (LEFT(A, 1)))
CREATE INDEX SESSION.A ON SESSION.A(B)

Unfortunately, i get:

SQL0286N A default table space could not be found with a page size of
at least "8192" that authorization ID "........" is authorized to use.
SQLSTATE=42727

But maybe it'll work for you.

B.

Nov 12 '05 #4

P: n/a
On Wed, 05 Oct 2005 13:15:38 -0400, Larry wrote:

http://publib.boulder.ibm.com/infoce...n/r0001029.htm

Larry Edelstein


Would anyone know if this 1024 byte limit has changed in the upcoming
Viper?
Thanks
nat

Mar 29 '06 #5

P: n/a
natG wrote:
On Wed, 05 Oct 2005 13:15:38 -0400, Larry wrote:
http://publib.boulder.ibm.com/infoce...n/r0001029.htm

Larry Edelstein


Would anyone know if this 1024 byte limit has changed in the upcoming
Viper?

I know... but I ain't tellin'. Patience...

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Mar 29 '06 #6

P: n/a
Serge - any hints for those of us who are impatient?
thx - ssg.

Mar 29 '06 #7

P: n/a
Serge Rielau wrote:
natG wrote:
On Wed, 05 Oct 2005 13:15:38 -0400, Larry wrote:
http://publib.boulder.ibm.com/infoce...n/r0001029.htm
Larry Edelstein


Would anyone know if this 1024 byte limit has changed in the upcoming
Viper?

I know... but I ain't tellin'. Patience...

The new limit is 1/4 of the index table space page size and 64 columns.
So on a 32k page the new limit is 8K.
http://publib.boulder.ibm.com/infoce...c/r0001029.htm

Also index names can be 128 bytes long now.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
Apr 6 '06 #8

This discussion thread is closed

Replies have been disabled for this discussion.