All Forums General
chill3che 99 posts Joined 10/12
21 Dec 2013
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.
 

Thanks, Cheeli
M.Saeed Khurram 544 posts Joined 09/12
21 Dec 2013

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/Alter_Function-Syntax.020.143.html#ww15979208
 

Khurram

Raja_KT 1246 posts Joined 07/09
21 Dec 2013

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

You must sign in to leave a comment.