Customer has requested that we add extraction and scripting support of index partition information. Example:
CREATE SET TABLE ivan_a ( a INTEGER, b VARCHAR(25), c VARCHAR(100), d DATE FORMAT 'YYYY-MM-DD' ) PRIMARY INDEX (a) PARTITION BY RANGE_N (d BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY) GO
Extraction and scripting of the primary key partition information is now complete for teradata 14.x and above. There has been no GUI support added for this index information.
Below are scripts used for testing and these are the links to the documentation I used, Teradata CREATE TABLE & Partitioned. Code checked into US trunk with Committed revision: 54080
CREATE TABLE ivan_a1 (a INTEGER,b VARCHAR(25),c VARCHAR(100),d DATE FORMAT 'YYYY-MM-DD') PRIMARY INDEX (a) PARTITION BY RANGE_N (d BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY) GO CREATE TABLE ivan_a2 (ORDER_NO INTEGER NOT NULL,CUST_NO INTEGER,ORDER_DATE DATE,ORDER_TOTAL DECIMAL(10,2)) PRIMARY INDEX(ORDER_NO) PARTITION BY RANGE_N (ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-12-31' EACH INTERVAL '7' DAY) GO CREATE TABLE ivan_a3 (ORDER_NO INTEGER NOT NULL,CUST_NO INTEGER,ORDER_DATE DATE,ORDER_TOTAL DECIMAL(10,2)) PRIMARY INDEX(ORDER_NO) PARTITION BY CASE_N(ORDER_TOTAL < 1000, ORDER_TOTAL < 2000, ORDER_TOTAL < 5000, ORDER_TOTAL < 10000, ORDER_TOTAL < 20000, NO CASE, UNKNOWN) GO CREATE TABLE ivan_a4 (ORDER_NO INTEGER NOT NULL,CUST_NO INTEGER,ORDER_DATE DATE,ORDER_TOTAL DECIMAL(10,2)) PRIMARY INDEX(ORDER_NO) PARTITION BY (RANGE_N (ORDER_DATE BETWEEN DATE '2012-01-01' AND DATE '2012-12-31' EACH INTERVAL '1' DAY), CASE_N (ORDER_TOTAL < 5000, ORDER_TOTAL < 10000, ORDER_TOTAL < 15000, ORDER_TOTAL < 20000, NO CASE, UNKNOWN)) GO CREATE TABLE ivan_a5 (PRODUCT_ID INTEGER NOT NULL,PRODUCT_NAME CHAR(30),PRODUCT_COST DECIMAL(10,2),PRODUCT_DESCRIPTION VARCHAR(100)) PRIMARY INDEX(PRODUCT_ID) PARTITION BY CASE_N (PRODUCT_NAME < 'Apples',PRODUCT_NAME < 'Bananas',PRODUCT_NAME < 'Cantaloupe',PRODUCT_NAME < 'Grapes' ,PRODUCT_NAME < 'Lettuce',PRODUCT_NAME < 'Mangos', PRODUCT_NAME >='Mangos' and PRODUCT_NAME <='Tomatoes') GO CREATE TABLE ivan_a6 (a INTEGER,b INTEGER,c INTEGER,d VARCHAR(25),e VARCHAR(100),f DATE FORMAT 'YYYY-MM-DD') PRIMARY INDEX (b, a, c) PARTITION BY RANGE_N (f BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY) CHECK ((RANGE_N(f BETWEEN DATE '2010-01-01' AND '2016-12-31' EACH INTERVAL '1' DAY )) BETWEEN 1 AND 02557) GO
Verified in ADS v18.0.9-10. @ivan : Scripts in Schema Script Generator is not working though. Can you confirm if this needs to be also fixed ?
Verified in ADS v18.0.9-10. @ivan : Scripts in Schema Script Generator is not working though. Can you confirm if this needs to be also fixed ?
I have reopened the issue as I need to add the [x] Table Storage Option, to the schema script generator dialog for teradata in order to have the index partition information extracted. This will only be done for versions of teradata 14.x and above.
I have reopened the issue as I need to add the [x] Table Storage Option, to the schema script generator dialog for teradata in order to have the index partition information extracted. This will only be done for versions of teradata 14.x and above.
I have added the option for Teradata and checked code in to US trunk with Committed revision: 54133, 54135 & 54134
I have added the option for Teradata and checked code in to US trunk with Committed revision: 54133, 54135 & 54134
Issue #15189 |
Closed |
Fixed |
Resolved |
Completion |
No due date |
Fixed Build v18.0.9-11, v19.0.0-dev-34 |
No time estimate |
Extraction and scripting of the primary key partition information is now complete for teradata 14.x and above. There has been no GUI support added for this index information.
Below are scripts used for testing and these are the links to the documentation I used, Teradata CREATE TABLE & Partitioned. Code checked into US trunk with Committed revision: 54080