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 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
Thanks a lot Larry! That was exactly what I was looking for!!!
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.
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
Serge - any hints for those of us who are impatient?
thx - ssg.
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
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...
|
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...
|
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...
|
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
|
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...
|
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
|
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},...
|
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...
|
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...
|
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...
|
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...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
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...
|
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...
|
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...
|
by: Defcon1945 |
last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
|
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....
|
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...
| |