Partition Violation Error

Database

Partition Violation Error

We have a table definition as mentioned below:

CREATE SET TABLE xxx.aaa ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
OFFICE_INTERNAL_ID DECIMAL(18,0) NOT NULL,
FA_INTERNAL_ID DECIMAL(18,0) NOT NULL,
office_number DECIMAL(18,0) NOT NULL,
fa_number DECIMAL(18,0) NOT NULL,
product_code INTEGER NOT NULL,
trade_commission_amount DECIMAL(18,4) COMPRESS 0.0000 ,
trade_commission_amount_ytd DECIMAL(18,4) COMPRESS 0.0000 ,
trade_commission_amount_t12 DECIMAL(18,4) COMPRESS 0.0000 ,
production_month_year CHAR(8) CHARACTER SET LATIN NOT CASESPECIFIC,
production_year INTEGER NOT NULL,
production_month_start_date DATE FORMAT 'YYYY-MM-DD',
production_month_end_date DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( OFFICE_INTERNAL_ID ,FA_INTERNAL_ID )
PARTITION BY HASHBUCKET(HASHROW(production_month_year ));

This was running perfectly alright, before we went for teradat upgrade to 64 bit processor. Just last weekend tere data upgrate to 64 bit processor happened and this table is not getting loaded with the Partition Violation Error. This has something to do with TD 64 bit processor not supporting HASHBUCKET(HASHROW) and something alike. Any immediate help is highly appreciated.

Reagrds,
Shweta
3 REPLIES
Enthusiast

Re: Partition Violation Error

A PARTITION BY expression must return value between 1 and 65535.

TD 12.0 increased the maximum value that can be returned by the HASHBUCKET function from 65535 to 1048575. This would have no effect when an existing machine is upgraded to 12.0, as the legacy hash bucket size (16 bits) would still be in effect. However, when you migrated to new hardware, your system was initialized based on the new default 20-bit hash bucket size.
KVB
Enthusiast

Re: Partition Violation Error

Hi

I am using TD 13.I have an erro 5728 with partition violation.

I have a table with follwoing structure

CREATE TABLE T1

(

COL1 INTEGER,

COL2 DATE FORMAT 'YYYY-MM-DD',

COL3 CHAR(1),

...

..

..

)

PRIMARY INDEX(COL1)

PARTITION BY CAST(COL2 AS INTEGER);

While I am inserting the data from select logic into this table.I am getting partition violation because the values allowed in the partition from 1 to 65535.the dates being converted into integer format is >65535 value.

  We constructed this table as per requirement.How far this is correct by puttin cast(col2 as integer) in partition by.

Is there any approach to do this.

I thought to have partition by range_n.How do you suggest?

Regards

KVB

Enthusiast

Re: Partition Violation Error

Good that you know the reason for getting the error!

Having a partition defined on the integer value of dates doesn't seem logical to me. Normaly the tables are partitioned based on the intervals like MONTH or DAY, and it is always preferred to have the paritions that have data and avoid the empty partitions.

I think you should try to use RANGE_N and define the partitions on DAY or MONTH interval, and if you analyze the data properly, you won't have partitions more than 65K.

Create table T1(...)

PRIMARY INDEX (col1)

PARTITION BY( RANGE_N (col2 between '2012-01-01' and '2012-12-31' each interval  '1' MONTH))