Identify tables whose partition has to be changed relatively to incorporate new year values in narrow window

General

Identify tables whose partition has to be changed relatively to incorporate new year values in narrow window

Greetings,

Can you please let me know the query to have dynamic query to build an alter statement to incorporate the new narrow partition values for the new year(PPI are to be changed to incorporate new year), which otherwise will reside in wider window.

for eg.  if the partitions are defined to carry the recent data for

1 day, then we need to extend the partition to include 2014 with 1 day variance,

1 week, then extend to include 2014 with 1 week variance,

1 month, then extend to include 2014 with 1 month variance

to a narrow window.

Sample PPI's defined as

(RANGE_N(date1  BETWEEN DATE '0001-01-01' AND DATE '2005-12-31' EACH INTERVAL '3000' YEAR ,DATE '2006-01-01' AND DATE '2013-12-31' EACH INTERVAL '1' YEAR ,DATE '2014-01-01' AND DATE '8999-12-31' EACH INTERVAL '8000' YEAR , NO RANGE, UNKNOWN) IS NOT NULL AND RANGE_N((HASHBUCKET(HASHROW(orgcode )))/ 16  BETWEEN 0  AND 65533  EACH 3000 , NO RANGE, UNKNOWN) IS NOT NULL AND RANGE_N((HASHBUCKET(HASHROW(matcode )))/ 16  BETWEEN 0  AND 65533  EACH 10000 , NO RANGE, UNKNOWN) IS NOT NULL AND RANGE_N((HASHBUCKET(HASHROW(so_type )))/ 16  BETWEEN 0  AND 65533  EACH 5000 , NO RANGE, UNKNOWN) IS NOT NULL )

((RANGE_N(ValdFromDt BETWEEN DATE '2008-01-01' AND DATE '2014-12-31' EACH INTERVAL '1' MONTH ,DATE '2015-01-01' AND DATE '8999-12-31' EACH INTERVAL '7988' YEAR , NO RANGE, UNKNOWN)) BETWEEN 1 AND 00087)

(RANGE_N(CalRunDt BETWEEN DATE '2012-01-01' AND DATE '2014-12-31' EACH INTERVAL '1' DAY , NO RANGE) IS NOT NULL AND RANGE_N(divcd BETWEEN 1 AND 6 EACH 1 ,10 AND 11 EACH 1 , NO RANGE) IS NOT NULL AND RANGE_N(orgcode BETWEEN '1000' AND '1100', NO RANGE) IS NOT NULL )

For example, the first range_n, should be changed to

(RANGE_N(date1  BETWEEN DATE '0001-01-01' AND DATE '2005-12-31' EACH INTERVAL '3000' YEAR ,DATE '2006-01-01' AND DATE '2014-12-31' EACH INTERVAL '1' YEAR ,DATE '2015-01-01' AND DATE '8999-12-31' EACH INTERVAL '8000' YEAR , NO RANGE, UNKNOWN) IS NOT NULL AND RANGE_N((HASHBUCKET(HASHROW(orgcode )))/ 16  BETWEEN 0  AND 65533  EACH 3000 , NO RANGE, UNKNOWN) IS NOT NULL AND RANGE_N((HASHBUCKET(HASHROW(matcode )))/ 16  BETWEEN 0  AND 65533  EACH 10000 , NO RANGE, UNKNOWN) IS NOT NULL AND RANGE_N((HASHBUCKET(HASHROW(so_type )))/ 16  BETWEEN 0  AND 65533  EACH 5000 , NO RANGE, UNKNOWN) IS NOT NULL )

Also, can you please let me know why the "IS NOT NULL" is defined in the above declarations.

Tags (2)
2 REPLIES

Re: Identify tables whose partition has to be changed relatively to incorporate new year values in narrow window

Hi Cheeli,

What I have understand from your explaination is that you want to dynamically update all the prtitions to the current date, according to the defined partitions. To handle such a scenario, Teradata has provided a feature TO CURRENT. To use this feature you need to defined you partitions in terms of CURRENT_DATE, or CURRENT_TIMESTAMP. 

You can consult the following document to better understand how you can use TO CURRENT feature along with you defined partitions. 

http://www.info.teradata.com/htmlpubs/DB_TTU_14_00/index.html#page/SQL_Reference/B035_1144_111A/Alte...

Khurram

Re: Identify tables whose partition has to be changed relatively to incorporate new year values in narrow window

I feel that UNKNOWN partitioning is not needed after you specify IS NOT NULL. Let us hear from other experts too.

Cheers,

Raja