SQL UDF support since Teradata 13.10

here is a good news for the guys who's poor at c or java, like me

at past, when talked about UDF in Teradata , the only choice is C or Java.

since Teradata 13.10, a new feature is supported:SQL UDF

sample:


CREATE FUNCTION test.MyUDF2 (d INT, e INT, f INT)
RETURNS INT
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
COLLATION INVOKER
INLINE TYPE 1
RETURN d + e + f;

Tweet

Conversion of Oracle function to its equivalent Teradata function

original link here：
http://www.javaorator.com/oracle/tutorial/conversion-of-oracle-function-to-its-equivalent-teradata-function-152.code

http://blog.csdn.net/fanxiaoliang83/article/details/4402378

Oracle Functions	Teradata Functions
DECODE(A,NULL,B,C)	CASE WHEN A IS NULL THEN B ELSE C END
DECODE(A,B,C,D)	CASE A WHEN B THEN C ELSE D END
GREATEST(A,B)	CASE WHEN A >= B THEN A ELSE B END
INSTR(A,B)	INDEX(A,B)
LEAST(A,B)	CASE WHEN A <= B THEN A ELSE B END
LENGTH(A)	CHAR_LENGTH(A)
LPAD(A,B)	CAST(A AS CHAR(B))
LTRIM(A,B)	TRIM(LEADING B FROM A)
MONTHS_BETWEEN(A,B)	ABS(CAST((A - B MONTH) AS INTEGER))
RTRIM(A,B)	TRIM(TRAILING B FROM A)
TO_CHAR(ANUMBER,B)	CAST((ANUMBER (FORMAT B)) AS CHAR(X))
NVL(A,B)	COALESCE(A,B)
TO_CHAR(ADATE,B)	CAST((ADATE (FORMAT B)) AS CHAR(X))
TO_CHAR(ADATE,'MM_YY')	(ADATE (FORMAT'MM')) || '_' || (ADATE (FORMAT'YY'))
TO_CHAR(X[,F])	CAST(X AS CHAR [FORMAT F])
TRUNC(T[,F])	CAST(D AS DATE [FORMAT F])
TO_NUM(X[,F])	CAST(X AS NUMERIC [FORMAT F])
TO_DATE(X[,F])	CAST(X AS DATE [FORMAT F])
TO_NUMBER(X[,F])	CAST(X AS NUMERIC [FORMAT F])
SIGN(A)	CASE A WHEN > 0 THEN 1 WHEN < 0 THEN -1 ELSE 0 END

Tweet

call stored procedure in stored procedure

CALL DBC.SYSEXECSQL('CALL PROC_A(''VALUE'',OUTPUT)'); http://blog.emergingbytes.com/2012/02/09/teradata-calling-a-procedure-within-a-procedure/

Tweet

utility to view Table Lock

it seems that the only way to view Table lock in Teradata is LOKDISP we can not view the lock info by any SQL.

Tweet

Geospatial Data in Teradata

Teradata begin to support Geospatial Data since Teradata 13
here is some Interesting way to use it for BI analysis https://docs.google.com/file/d/0B7i7b3i-AZGoUnd0N09ISGxhYnM/preview

Tweet

USI ON PI in PPI table

normally, create a SI on PI will cause a error:


create table tbl1
( col1 char(10),
  col2 char(10),
  col3 char(10)
  )
  primary index(col1, col2)
  index(col1,col2)

CREATE TABLE Failed. 3519:  Two indexes with the same columns. 

here is some special circumstances:


create table tbl1
( col1 char(10),
  col2 DATE
  )
primary index(col1)
PARTITION BY RANGE_N(col2 BETWEEN DATE '2000-01-01' AND DATE '2020-12-31' EACH INTERVAL '1' DAY)
unique index(col1)

the reason is performance.

Tweet

Teradata restart

shutdown Teradata Database: tpareset -x
check state:pdestate -a
poweroff the node: shutdown
after shutoff the node, power off all disk array
to start Teradata, check disk array:psh verify_pdisks
start database:/etc/init.d/tpa start
check state:pdestate -a

Tweet

Teradata Archive and Recovery Utility

yes, finally , archive strategy of our Teradata machine was made.
we will perform a full volumn database archive per week and perform a incremental archive everyday.
based on this archive strategy, In case of emergency, All DB can be restored to the last week status , Specified Key DB can be restored to the last night or current status.

https://docs.google.com/file/d/0B7i7b3i-AZGoemotSGNFbmY3T28/preview">https://docs.google.com/file/d/0B7i7b3i-AZGoemotSGNFbmY3T28/preview

Tweet

install,replace,remove jar on teradata

call sqlj.install_jar('CJ!/home2/tableudf/hdfsudf.jar', 'hdfsudf', 0); 

call sqlj.replace_jar('CJ!/home2/tableudf/hdfsudf.jar', 'hdfsudf'); 

call sqlj.remove_jar('hdfsudf',0); 

of course, to run the above content, appropriate accsess rights were needed

installed Jar will be shown in dbc.tables as tablekind 'D'

teradata's procedure really sucks

Tweet

Abort Sessions in teradata

i used to kill sessions in teradata by pmon

there is another way : by  udf  SYSLIB.AbortSessions()


from

http://dadawarehousingwithtd.blogspot.com/2012/10/kill-session-in-teradata.html
http://www.teradataforum.com/teradata/20111122_082726.htm

1. Find the session info as:

select * from dbc.SessionInfo;

2. Execute abortsession function:                

SELECT SYSLIB.AbortSessions(1,'MyUser',0,'Y','Y');

Param1: Hostno

Param2:UserName

Param3: SessionNo

Param4: LogoffSessions

Param5: UserOverride

Where 0 in Param3 means all session of user will be aborted,you can specify sessionno to kill a specific session.

Param4=Y means logoff the session first.

Tweet

drop database in teradata

we can clear a database in teradata by a "delete" easily, not have to delete all tables and views one by one.

eg.
delete database db01;
drop database db01;


as we know we can't drop a database with the any objects under it , eg table, view , trigger and so on. we need to clear a database before we drop it.

so we can delete all object under a database by run the following sql's result


sel 'drop trigger ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'G'
union all
sel 'drop view ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'V'
union all
sel 'drop procedure ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'P'
union all
sel 'drop table ' || trim(databasename) || '.' || trim(tablename) || ';' from dbc.tables  where databasename = 'hst_db' and tablekind = 'T'

Tweet

query logging on/off

a user need execute privilage on DBC.DBQLAccessMacro to setting query on or off
so at first we neet to give execute right to the user who neeed to manage query logging


1.GRANT EXECUTE ON DBC.DBQLAccessMacro TO USER_A;

and then logon with USER_A to set logging on

2.begin query logging on USER_B;

3.end query logging on USER_B;

4.revoke EXECUTE ON DBC.DBQLAccessMacro from USER_A;

Tweet