Tuesday, January 1, 2013

Teradata 13.10 new features (part 1)

Well..I thought it’s still not late to share some interesting things about  new features of Teradata 13.10  . I want to keep this topic as simple and useful as possible for someone who needs to catch a train or bus in next 30 mins.

Here are few new features of Teradata 13.10 from development point of view as developers need to be aware of what is in house for new version.

1. New calendar functions in TD13.10
There are lot of calendar functions introduced in 13.10   which are 
·         day_of_week
·         day_of_month
·         day_of_year
·         day_of_calendar
·         weekday_of_month
·         week_of_month
·         week_of_year
·         week_of_calendar
·         month_of_quarter
·         month_of_year
·         month_of_calendar
·         quarter_of_year
·         quarter_of_calendar
·         year_of_calendar

These can be found in SYSLIB database.

The biggest problem when it comes to migrating from previous versions to 13.10 is "calendar functions".  As we have implemented these concepts using SYS_CALENDAR.CALENDAR table in previous versions.
Every client going through migration has to work on calendar functions . this is a mammothic task if sys_calendar is used in many places in ETL framework.

Following is an scenario. Which is beneficial?
-- Sample Query written in TD12, TD13
SEL A.*,DAY_OF_WEEK
 FROM DBC.TABLES  A
 ,SYS_CALENDAR.CALENDAR  -- calendar table referred to pick current date
 WHERE DATABASENAME ='DBC'
 AND CALENDAR_DATE=CURRENT_DATE;


 -- Sample QUERY rewrite for TD13.10
 SEL A.*,
 SYSLIB.DAY_OF_WEEK(CURRENT_DATE)  --elimination of calendar table and usage of  function in TD13.10
  FROM DBC.TABLES  A
 WHERE DATABASENAME ='DBC' ;

Advantages of calendar functions
1. Elimination of Calendar tables resulting in unnecessary spool. 
2. Avoiding product joins in some cases
3. Complexity of query & logic is reduced


Please note - In some cases, if you still need to retain the original functionality of queries then "table qualifier should be used as follows"
 -- Sample Query written in TD12, TD13
 -- addition of table qualifier CAL to make query compactible with TD13.10
SEL A.*,
CAL.DAY_OF_WEEK  -- Table qualifier CAL is used
 FROM DBC.TABLES  A
 ,SYS_CALENDAR.CALENDAR CAL  -- calendar table with qualifier CAL  referred to pick current date
  WHERE DATABASENAME ='DBC'
 AND CALENDAR_DATE=CURRENT_DATE;


To be continued….
 

No comments:

Post a Comment