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