Archives of the TeradataForum

Message Posted: Tue, 17 Feb 2004 @ 23:55:50 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

IDEAS PLEASE

TDATA Version 5.0, merging two tables partitioned on primary index by date range

Whatever I do, in a Multi Statement Request with volumes of data, I obtain ERROR 5728: Partitioning violation for table TARGET.

I have three tables with identical DDL (except the name).

The 2nd was created as first with no data. 3rd likewise.

All 3 have a single column primary index partitioned by range_N(dte_reported between '20003-01-1' and '2004-01-01' each interval '1' month)

Tables 1 and 2 are both over 15Gb and I want to use a Multi-Statement_Request both for this case and to establish the way for several large table merges, avoiding journalling several hundred million individual rows.

In each case, EXPLAIN reveals no problems.

SQL

Insert into TARGET sel * from table1
; Insert into TARGET sel * from table2
;

obtained ERROR 5728.

Since all the data is in tables that have the same partition range I could not see how there could be invalid data and subsequent checks on the dates identified that

table 1 has 4 valid months in partitions 1,2,3,4 respectively and

table 2 has 4 valid months in partitions 5,7,8,9.

So I tried

sel t1.* from table1 T1 where T1.partition in (1,2,3,4)
; insert into TARGET
sel t2.* from table1 T2 where T2.partition in (5,7,8,9)
;

That obtained ERROR 5728

So I tried

insert into TARGET
sel t1.* from table1 T1 where T1.partition =1 sample 10
; insert into TARGET
sel t1.* from table1 T1 where T1.partition =2 sample 10
; insert into TARGET
sel t1.* from table1 T1 where T1.partition =3 sample 10
;insert into TARGET
sel t1.* from table1 T1 where T1.partition =4 sample 10
;insert into TARGET
sel t2.* from table2 T2 where T2.partition =5 sample 10
; etc

When submitted as an MSR it obtains ERROR 5728

although it works when submitted to work in series!

Presumably that proves what I already knew: the data fits in the partition range.

Identical results submitting the SQL via QueryMan and via WinBTEQ.

Ironically, I have tried and failed to replicate the problem with sample data in new test tables.

Where my colleagues have succeeded I suspect that their samples are so small that Tdata may be processing in series even though the SQL is submitted for parallel processing.

This is my first time with partitioned tables and I would really appreciate your ideas.

Thanks,

Ken Hansen

	<Prev	Next>		<<First	<Prev	Next>	Last>>