Partitioning index creation problem

Raymond Bell

Partitioning index creation problem
Colleagues,

Sometimes a second pair of eyes is required to spot what's wrong. I've a
26-partition TS and I'm trying to create the index for it. Everytime I run
the SQL I get -626 - the keys are in the wrong order. The 'create index'
SQL is below, edited to remove all but the guts:

CREATE TYPE 2 INDEX blah.blah
ON blah.blah
( col1 ASC ,
col2 ASC ,
col3 ASC )
CLUSTER (
PART 1
VALUES ('AM')
...
,
PART 2
VALUES ('AN', '2')
...
,
PART 3
VALUES ('AN', '4')
...
,
PART 4
VALUES ('AN', '8')
...
,
PART 5
VALUES ('AN')
...
,
PART 6
VALUES ('HA')
...
,
PART 7
VALUES ('LH')
...
,
PART 8
VALUES ('NY')
...
,
PART 9
VALUES ('NZ','002')
...
,
PART 10
VALUES ('NZ','005')
...
,
PART 11
VALUES ('NZ','01')
...
,
PART 12
VALUES ('NZ','015')
...
,
PART 13
VALUES ('NZ','02')
...
,
PART 14
VALUES ('NZ','022')
...
,
PART 15
VALUES ('NZ','03')
...
,
PART 16
VALUES ('NZ','07')
...
,
PART 17
VALUES ('NZ','08')
...
,
PART 18
VALUES ('NZ','09')
...
,
PART 19
VALUES ('NZ','7')
...
,
PART 20
VALUES ('NZ','8')
...
,
PART 21
VALUES ('NZ','9')
...
,
PART 22
VALUES ('NZ')
...
,
PART 23
VALUES ('TZ')
...
,
PART 24
VALUES ('UA','01')
...
,
PART 25
VALUES ('UA')
...
,
PART 26
VALUES ('ZZ')
...
)
BUFFERPOOL BP2
CLOSE YES

When I run it I get this:

DSNT408I SQLCODE = -636, ERROR: THE PARTITIONING KEYS FOR PARTITION 012
ARE
NOT SPECIFIED IN ASCENDING OR DESCENDING ORDER

Now, as far as I can see, they are in order. col1 is char(2), col2, is
char(20) and col3 is char(8). The CCSID for the tablespace is EBCDIC.
I've even extracted the values above and bunged them in a wee table of my
own and done an 'order by'. AOK, except for the defaults of spaces which
bubble to the top, but my partitioning keys, where I haven't given values,
should default to the highest value.

What am I doing wrong? They look in order to me. Help!


Raymond



teldb2kals

Re: Partitioning index creation problem
(in response to Raymond Bell)
Hi Raymond,

I think the problem is due to the fact that column 2 is char(20) and
Part 11 has values of '01' for the second column, while part 12
has '015' for the column. Since padding is done using x'FF' for an
ascending column, 015 followed by x'FF' becomes less than 01 followed
by x'FF'.

PART 11
VALUES ('NZ','01')
...
,
PART 12
VALUES ('NZ','015')

Just a guess.

Cheers,
Kals

-----Original Message-----
From: Raymond Bell [SMTP:[login to unmask email]
Sent: Thursday, January 10, 2002 11:10 AM
To: [login to unmask email]
Subject: Partitioning index creation problem

Colleagues,

Sometimes a second pair of eyes is required to spot what's wrong. I've
a
26-partition TS and I'm trying to create the index for it. Everytime I
run
the SQL I get -626 - the keys are in the wrong order. The 'create
index'
SQL is below, edited to remove all but the guts:

CREATE TYPE 2 INDEX blah.blah
ON blah.blah
( col1 ASC ,
col2 ASC ,
col3 ASC )
CLUSTER (
PART 1
VALUES ('AM')
...
,
PART 2
VALUES ('AN', '2')
...
,
PART 3
VALUES ('AN', '4')
...
,
PART 4
VALUES ('AN', '8')
...
,
PART 5
VALUES ('AN')
...
,
PART 6
VALUES ('HA')
...
,
PART 7
VALUES ('LH')
...
,
PART 8
VALUES ('NY')
...
,
PART 9
VALUES ('NZ','002')
...
,
PART 10
VALUES ('NZ','005')
...
,
PART 11
VALUES ('NZ','01')
...
,
PART 12
VALUES ('NZ','015')
...
,
PART 13
VALUES ('NZ','02')
...
,
PART 14
VALUES ('NZ','022')
...
,
PART 15
VALUES ('NZ','03')
...
,
PART 16
VALUES ('NZ','07')
...
,
PART 17
VALUES ('NZ','08')
...
,
PART 18
VALUES ('NZ','09')
...
,
PART 19
VALUES ('NZ','7')
...
,
PART 20
VALUES ('NZ','8')
...
,
PART 21
VALUES ('NZ','9')
...
,
PART 22
VALUES ('NZ')
...
,
PART 23
VALUES ('TZ')
...
,
PART 24
VALUES ('UA','01')
...
,
PART 25
VALUES ('UA')
...
,
PART 26
VALUES ('ZZ')
...
)
BUFFERPOOL BP2
CLOSE YES

When I run it I get this:

DSNT408I SQLCODE = -636, ERROR: THE PARTITIONING KEYS FOR PARTITION 012
ARE
NOT SPECIFIED IN ASCENDING OR DESCENDING ORDER

Now, as far as I can see, they are in order. col1 is char(2), col2, is
char(20) and col3 is char(8). The CCSID for the tablespace is EBCDIC.
I've even extracted the values above and bunged them in a wee table of
my
own and done an 'order by'. AOK, except for the defaults of spaces
which
bubble to the top, but my partitioning keys, where I haven't given
values,
should default to the highest value.

What am I doing wrong? They look in order to me. Help!


Raymond



----------------
Powered by telstra.com