Partitioned Indecies

[Liam1]

We have just gone over to V2R5.
I have created a table with a partioned primary index:
CREATE TABLE Syntax_Test
(
ID INTEGER,
Dte integer,
Prdcd INTEGER COMPRESS (12 ,1044 ,200 ),
Bal DECIMAL(15,2))
PRIMARY INDEX ( Prdid )
PARTITION by Dte;

When I execute an insert
insert into Syntax_Test
Select
1234 ID
,1030401 Dte
,null Prdcd
,null Bal

I get the message Partitioning Violation for Table Syntax_Test.

This occurs with a single row and a multi insert, though I cannot see how number of rows would be affected.
I have tried to use a unique partitioned index, but again our TD box doesn't like it.

Does anyone have any helpfull hints?

Thanks,

Liam1

 

[dnoeth]

There may be up to 65534 partitions.
Just submit:
sel constrainttext from dbc.indexconstraints
where tablename = 'syntax_test'
and databasename = ...

You'll see the check definition for your table and know why it's failing ;-)

The column Dte looks like a date, what range of dates are you going to use in your table?
Depending on you queries you could partition by day/week/month.

PARTITION BY RANGE_N (dte BETWEEN DATE '2000-01-01' AND DATE '2003-12-31' EACH INTERVAL '1' MONTH)

or

,PARTITION BY RANGE_N (dte BETWEEN DATE '2000-01-01' AND DATE '2003-12-31' EACH INTERVAL '1' DAY)

Partitioning by day is probably not the best way, because the partitioning column is not included in the Primary Index, this may slow down PI access without including the date.

Check the manuals for more details.

Dieter

 

[Liam1]

Dieter,

Thanks - JAQ
When I look at the constaints I get:

The output:

CHECK ((RANGE_N(EXTRACT(MONTH FROM (Dte )) BETWEEN 1 AND 12 )) BETWEEN 1 and 65535)

I have noticed that whatever the constraints are, there is always a BETWEEN 1 and 65535 wrapped around my conition. I hae also noticed that there is a CHECK constraint, I was after the partition to be 1-12.

Thanks again

Liam1

 

[dnoeth]

Don't ask me why it's not "BETWEEN 1 AND 12" ;-)
The partition boundaries are enforced by a Check and although the check always returns "BETWEEN 1 AND 65535" the optimizer knows about the 12 partitions.

Btw, you should always consider adding a NO RANGE/UNKNOWN for values outside the range or Nulls, this can't be added with ALTER TABLE MODIFY PRIMARY INDEX.

Dieter

 

[tdatgod]

Hi,
You can't partition by date because the partition value must be between 1 and 65535.

you dte field 1030401 is greater than 65535 and therefore it violates the partition constrant.

You must use some sort of formula that comes up with a value that is < 65535.

partition ( dte mod 64000 )

but that would basically put every day in its own partition repeating every 6 years so that probably isn't a good function.

That is why as Dieter points out you need to specify something like....

PARTITION BY RANGE_N (dte BETWEEN DATE '2000-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' MONTH)

or

,PARTITION BY RANGE_N (dte BETWEEN DATE '2000-01-01' AND DATE '2030-12-31' EACH INTERVAL '1' DAY)


I would chose a year a little further out ( 2030 instead of 2003 ) so I won't have to redefine the partition when we pass the end date.

65535 Months is roughly 5400+ years

65535 days is roughly 175+ years