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

SOME DIFFERENCE BETWEEN BTEQ AND FASTEXPORT WHEN EXPORTING DATA

BTEQ will truncates the trailing space, but Fastexport WILL NOT

http://www.coffingdw.com/utilities/tdutil/using_bteq_to_export_data.htm

Tweet

DEAL WITH THE PADDING DOT WHEN CAST A DECIMAL TO CHAR

in Teradata, when cast a decimal to char directly,
the result will show out with a padding dot at the end of the number

eg. the following query will results '123.'
select cast(123. as varchar(10))

the annoying dot can be dealed by cast to Z(I) format

select cast(cast(123. as format 'Z(I)') as varchar(10))

that will bring us the desired results '123'

cast decimal to integer can also do the same thing,
select cast(cast(123. as integer) as varchar(10))



but must take care of the lenth of the decimal number
because the integer can only accommodate numbers from -2,147,483,648 through +2,147,483,647. and when cast to Z(I) the max number can be 9999999999.

Tweet

USING FASTEXPORT TO EXPORT TAB-DELIMITED TEXT FILE

we must remember one thing that fastexport is used for export flat-file from Teradata and the output file is used for load to tables.
so do not try to open the exported file, that's not the purpose of fastexport.

but, sometimes, we indid have to use the exported file as a text file, eg. tab-delimited text file

if all output columns are fixed length char then we just need to add tab as dummy column between columns like

.EXPORT
OUTFILE E:/DAT3/CNV/DATA23/SPND040060.DAT
MODE RECORD
FORMAT TEXT;


SELECT
ADDRESS1(CHAR(20))
,' '||ADDRESS2(CHAR(20))
,' '||ADDRESS3(CHAR(20))
,' '||ADDRESS4(CHAR(20))
FROM
M_VM_KNRYO


.END EXPORT;

but, if any column is varchar, instead of fixed length char, we will got some trouble here
because teradata will place some indicators includes the infomation of the following varchar column, at each head of varchar column . eg. length of the varchar following

in this case, we can use some tricks
like, combine all output all columns as one, separated by tab

.EXPORT
OUTFILE E:/DAT3/CNV/DATA23/SPND040060.DAT
MODE RECORD
FORMAT TEXT;


SELECT
ADDRESS1
||' '||ADDRESS2
||' '||ADDRESS3
||' '||ADDRESS4
FROM
M_VM_KNRYO


.END EXPORT;

Although, as a solo varchar column, on the head of each line still exist the 2bytes indicators, we can easily delete it by external tools



FASTEXPORT的目的，原本便是为了输出LOAD所需的数据，并非用来导出可编辑的文本文件。
但是有时候我们不得不这么做。比如说，输出一个TAB间隔的文本文件，提供给其他系统。

如果每个字段都是定长，只需要在每两个字段间，手工添加TAB
但是如果有VARCHAR,那么事情就变得麻烦起来，Teradata在Fastexport可变长字段的时候，会在每个变长字段前添加某些标记信息，比如说变长字段的长度等
这些标记会被文本编辑器识别为乱码，这时候，我们可以把所有的字段和tab拼接成一个单独的字段，整体输出
这样只有在每行开头输出标记信息，可以容易的用外部工具删除

当然了，数据量小的时候，还是用定长，或者干脆用BTEQ来EXPORT容易些

Tweet