table name limits changed since teradata 14.10

since teradata 14.10, the table name limits was changed from 30 bytes to 128 bytes
that's maybe a good news , but DO NOT FORGET that when we refernce the DBC.TABLES view,
the tablename column still show the table name in 30 bytes. you should use DBC.Tablesv to view the "REAL" 128 bytes table name.

so when you reference dbc.tables and you find that two or more table under the same name in the same database, do not confused, use help database or DBC.Tablesv you will found that they were cut off by the 30 bytes and loooks like "the same name"

Tweet

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

Alter column length in Teradata

in teradata ,DDL alter table can not only add column but also can alter column length in specific case

eg.

DDL:

create table bbb
(col1 varchar(10),
col2 varchar(20)
)

DML:
alter  table bbb  ADD col2  varchar(100)

RESULT:
ALTER TABLE completed. Elapsed Time = 00:00:00

but, in the case , if the column is defined char but not varchar

DDL:

create table aaa
(col1 char(10),
col2 char(20)
)


DML:
ALTER table aaa  ADD col2  VARCHAR(15)

RESULT:
ALTER TABLE Failed.  [3558] Cannot alter the specified attribute(s) for COL2.

as we see, modify column length by "alter table ", the conditions are strictly limited.

so, usually, we create a new table with the new defined column length ,then do a ins-sel and finally rename the table.

Tweet

Join index is so restricted

CREATE TABLE TBLA
(
bank_shikib_shi CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
tengun CHAR(2) CHARACTER SET LATIN CASESPECIFIC NOT NULL,
tenban CHAR(3) CHARACTER SET LATIN CASESPECIFIC NOT NULL
）

CREATE TABLE TBLB
(
bnkptn CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
tengun CHAR(2) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL,
tenban CHAR(3) CHARACTER SET LATIN NOT CASESPECIFIC NOT NULL
)

we got above two tables and we need a join index

so the DDL is..


create join index testjoinindex as
select
...
...
...
from
TBLA t1
left outer join
TBLB t2
on
t1.bank_shikib_shi=t2.bnkptn
and t1.daihyosha_tenban=t2.tenban
primary index (bank_shikib_shi, tenban, cif_no)
;

we got a error:
CREATE INDEX Failed.  [5464] Error in Join Index DDL, A name specified for an index was not found in the select list.

the reason is:
TBLA IS CASESPECIFIC BUT TBLB IS NOT NOT CASESPECIFIC

WTF

Tweet