473,320 Members | 1,945 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

What is Max Length for Index Column

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
7 7249
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
Thanks a lot Larry! That was exactly what I was looking for!!!

Nov 12 '05 #3
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
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
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
Serge - any hints for those of us who are impatient?
thx - ssg.

Mar 29 '06 #7
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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
by: siliconmike | last post by:
Somewhere there is a column called blogs - type TEXT with a FULLTEXT index. Most entries would be in English, but few might be in any other language. Now, if I make it UTF8, it will be a...
3
by: sdowney717 | last post by:
I would like to increase the length of the column from LOCNumber VARCHAR(30) DEFAULT '' to LOCNumber VARCHAR(100) DEFAULT '' without losing any data currently stored in the field. And it...
14
by: Sean C. | last post by:
Helpful folks, Most of my previous experience with DB2 was on s390 mainframe systems and the optimizer on this platform always seemed very predictable and consistent. Since moving to a WinNT/UDB...
375
by: rkusenet | last post by:
This article is very bleak about future of DB2. How credible is the author. http://www.eweek.com/article2/0,1895,1839681,00.asp
14
by: Rich | last post by:
Yes, I need to store some values in an array type collection object that can hold 3 or more parameters per index. I have looked at the collection object, hashtable object and would prefer not to...
53
by: jaso | last post by:
Can you give any comments on this code? I used one goto, is it bad? #include <stdio.h> #include <stdlib.h> #include <ctype.h> #include <string.h> #include <assert.h> #define NOT_NULL 1
8
by: kd | last post by:
Newbie question here. It's been a while since I've done C programming, and I hit a wall last night. Let's say I have a three dimensional array, like so: int p = {{{0,0,0}, {1,1,1},...
89
by: Tubular Technician | last post by:
Hello, World! Reading this group for some time I came to the conclusion that people here are split into several fractions regarding size_t, including, but not limited to, * size_t is the...
9
by: erictheone | last post by:
Ok so what I'm trying to do is create a trans location cipher. For those among us that don't know alot about cryptography it is a method for jumbling up letters to disguise linguistic...
0
by: DolphinDB | last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation. Take...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
0
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.