Partitioning index creation problem - never mind

Raymond Bell

Partitioning index creation problem - never mind
Guys,

Cracked it. '015' is higher than '01', but not when they're both padded
with X'FF'. Doh. X'F0F1F5' isn't higher than X'F0F1FF'. I thought
partial strings were padded with blanks. Nope; they're padded with X'FF'.
So now I see why my values clauses were in the wrong order.

Sorry to have bothered you all.


Raymond



Thomas E. Faglon

Re: Partitioning index creation problem
(in response to Raymond Bell)
raymond,
try partitions as
PART 11
VALUES ('NZ','010')
... PART 13
VALUES ('NZ','020')
etc.
hth Tom F




Raymond Bell <[login to unmask email]> on 01/09/2002 07:10:03 PM

Please respond to DB2 Data Base Discussion List <[login to unmask email]>

To: [login to unmask email]
cc: (bcc: Thomas E. Faglon/Telcordia)
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








Steve Tennant

Re: Partitioning index creation problem
(in response to Thomas E. Faglon)
Hi again Raymond,
is the problem the fact that you have '02' followed by '022'?

Regards,

Steve T

-----Original Message-----
From: Raymond Bell [mailto:[login to unmask email]
Sent: Thursday, 10 January 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








Raymond Bell

Re: Partitioning index creation problem
(in response to Steve Tennant)
Cheers, Kals. It might have been just a guess, but you're right on the
money. I've changed my 'values' values appropriately.


Raymond



teldb2kals
<[login to unmask email] To: [login to unmask email]
LSTRA.COM> cc:
Sent by: DB2 Subject: Re: Partitioning index creation problem
Data Base
Discussion
List
<[login to unmask email]
M>


10/01/02 13:50
Please respond
to DB2 Data
Base
Discussion
List





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



Raymond Bell

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

Yeah, it is, along with '01' followed by '015'. As strings they are in the
right order, but they're padded with x'FF' for the partitioning key so
they're actually in the wrong order. Tricky, huh? If one keeps to even
length keys one would never have the problem. Trust me to try to split out
some 'full' partitions. Oh well, I've done what Thomas F. suggested and
coded '020' and '022' etc.

Cheers,


Raymond



Steve Tennant
<[login to unmask email] To: [login to unmask email]
MS.GOV.AU> cc:
Sent by: DB2 Data Subject: Re: Partitioning index creation problem
Base Discussion List
<[login to unmask email]>


10/01/02 14:32
Please respond to
DB2 Data Base
Discussion List





Hi again Raymond,
is the problem the fact that you have '02' followed by '022'?

Regards,

Steve T