How to identify Expired Partitions in Teradata v14.0

Database
N/A

How to identify Expired Partitions in Teradata v14.0

HI Folks,

We have requirement to identify Expired/About to expire Partitions [PPI] On Teradata V14.0 to prevent our batch jobs from failing.

So please help me out to figure out this issue.

Thanks.

DiRa

Tags (1)
4 REPLIES
Teradata Employee

Re: How to identify Expired Partitions in Teradata v14.0

N/A

Re: How to identify Expired Partitions in Teradata v14.0

Hi,

Thanks for the post, but am looking for help where SQL can extend the existing PPI's based on currendate to avoid patition violation failures in daily batch jobs.

Please shed some light on this...

Thanks.

Teradata Employee

Re: How to identify Expired Partitions in Teradata v14.0

There is no simple solution. You can parse ConstraintText from DBC.PartitioningConstraintsV.

N/A

Re: How to identify Expired Partitions in Teradata v14.0

HI Fred,

As per you suggestion , am trying to run below query to figure out the MAXimum PPI DATE for given table, in this case "2009-06-30" as per below ConstraintText..

SELECT  token

FROM

TABLE (REGEXP_SPLIT_TO_TABLE(1,'CHECK ((RANGE_N(Calendar_Date  BETWEEN DATE 2002-01-01 AND DATE 2002-12-31 EACH INTERVAL 1 DAY ,

DATE 2003-01-01 AND DATE 2003-12-31 EACH INTERVAL 1 DAY ,

DATE 2004-01-01 AND DATE 2004-10-04 EACH INTERVAL 1 DAY ,

DATE 2004-10-05 AND DATE 2006-11-14 EACH INTERVAL 1 DAY ,

DATE 2006-11-15 AND DATE 2007-06-07 EACH INTERVAL 1 DAY ,

2007-06-08(DATE) AND DATE 2009-06-30 EACH INTERVAL 1 DAY )) BETWEEN 1 and 65535) ', ' ', 'I')

 RETURNS (id INTEGER, tokennum INTEGER, token VARCHAR(10000) CHARACTER SET UNICODE)) AS dt

 WHERE   REGEXP_SIMILAR (token, '^[A-Z]*$') = 0 

 AND REGEXP_SIMILAR (token, '^[0-9]*$') = 0 

 AND token NOT IN (',DATE','1',',','and')

But am getting answer set like below with above query rather





Current Answer-Set Expected Answer-set
6/30/2009 6/30/2009
6/7/2007 6/7/2007
11/15/2006 11/15/2006
11/14/2006 11/14/2006
10/5/2004 10/5/2004
10/4/2004 10/4/2004
1/1/2004 1/1/2004
12/31/2003 12/31/2003
1/1/2003 1/1/2003
12/31/2002 12/31/2002
1/1/2002 1/1/2002
, DATE 2007-06-08
, DATE  
, DATE  
, DATE  
, 2007-06-08(DATE)  
))  
((RANGE_N(Calendar_Date
 

So please shed some light to keep me posted to remove non DATE data, so i can only get DATE [format DD-MM-YYYY] data out of token column as shown above.

Thanks in Advance.