Teradata database port

the default port for Teradata database is 1025.

this can be checked by netstat command on database node.

MD3NODE1-9:~ # netstat -aon|grep 1025

tcp        0      0 10.35.48.11:1025       10.51.180.204:2610      ESTABLISHED keepalive (314.05/0/0)
tcp        0      0 10.35.48.11:1025       10.51.183.23:1669       ESTABLISHED keepalive (512.87/0/0)
tcp        0      0 10.35.48.11:1025       10.44.10.68:3209        ESTABLISHED keepalive (6.78/0/0)
tcp        0      0 10.35.48.11:1025       10.44.32.80:2241        ESTABLISHED keepalive (462.68/0/0)
tcp        0      0 10.35.48.11:1025       10.51.181.17:2087       ESTABLISHED keepalive (54.02/0/0)
tcp        0      0 10.35.48.11:1025       10.18.32.237:43922      ESTABLISHED keepalive (21.70/0/0)
tcp        0      0 10.35.48.11:1025       10.18.70.125:55381      ESTABLISHED keepalive (97.30/0/0)
tcp        0      0 10.35.48.11:1025       10.18.32.237:40140      ESTABLISHED keepalive (322.54/0/0)
tcp        0      0 10.35.48.11:1025       10.18.32.237:42456      ESTABLISHED keepalive (187.41/0/0)
tcp        0      0 10.35.48.11:1025       10.18.32.237:53040      ESTABLISHED keepalive (322.93/0/0)

Space query

Query to get space report database wise.


SELECT Databasename AS "Database Name",
Max_perm_gb (DECIMAL (10,1)) AS "Max Perm (GB)",
current_perm_gb (DECIMAL (10,1)) AS "Current Perm (GB)",
EffectiveUsedSpace_Gb (DECIMAL (10,1)) AS "Effective Used Space (GB)",
Current_Perm_Percent AS "Current Perm %",
(Max_perm_gb (DECIMAL (10,1))) -( current_perm_gb (DECIMAL (10,1))) as "unused space in GB" ,
(100) - (Current_Perm_Percent)  as "unused %",
Peak_Perm_Gb (DECIMAL (10,1)) AS "Peak Perm (GB)",
Peak_Perm_Percent AS "Peak Perm %",
D_SKEW AS "Skew %"
FROM
(
SELECT
databasename, SUM(maxperm)/1024/1024/1024 AS Max_Perm_Gb,
SUM(currentperm)/1024/1024/1024 AS Current_Perm_Gb,
MAX(currentperm)*COUNT(*) /1024/1024/1024 AS EffectiveUsedSpace_Gb,
Current_Perm_Gb/Max_Perm_Gb * 100 AS Current_Perm_Percent,
SUM(peakperm)/1024/1024/1024 AS Peak_Perm_Gb,
Peak_Perm_Gb/Max_Perm_Gb * 100 AS Peak_Perm_Percent,
ZEROIFNULL((MAX(currentperm) - AVG(currentperm))/MAX(NULLIFZERO(currentperm)) * 100) AS D_SKEW
FROM dbc.diskspace
GROUP BY 1
HAVING Max_Perm_Gb >= 9.9
) a
--WHERE databasename IN ('database1','database2')
ORDER BY 2 DESC;

Teradata Table Kind List

All the object in teradata is listed in "dbc.tables", different objects are differentiated  through column "Tablekind"

List of table kind:

A	=	AGGREGATE UDF
B	=	COMBINED AGGREGATE AND ORDERED ANALYTICAL FUNCTION
E	=	EXTERNAL STORED PROCEDURE
F	=	SCALAR UDF
G	=	TRIGGER
H	=	INSTANCE OR CONSTRUCTOR METHOD
I	=	JOIN INDEX
J	=	JOURNAL
M	=	MACRO
N	=	HASH INDEX
P	=	STORED PROCEDURE
Q	=	QUEUE TABLE
R	=	TABLE FUNCTION
S	=	ORDERED ANALYTICAL FUNCTION
T	=	TABLE
U	=	USER-DEFINED DATA TYPE
V	=	VIEW
X	=	AUTHORIZATION

O            =            NOPI TABLE
D            =            JAR

Failed. 6706: The string contains an untranslatable character

Problem Description: SELECT Failed. 6706: The string contains an untranslatable character

This error usually comes when a junk character come across when selecting from column of a table using some function like cast (), coalesce(),trim() etc.

Example :

 Select Prop_Name, cast(coalesce(Prop_DSC,'') as char(400) ) from P_PROPERTY. PROPTYPE ;

Problem seems to be with data in column "Prop_DSC" in P_PROPERTY. PROPTYPE  table. column character set is LATIN.

Problem Solution:  Please use translate_chk function to determine untranslatable column values that are causing this issue

You can use the proper “source_TO_target”  value for the translation. e.g. LATIN_TO_UNICODE
please check “show table” to verify any character set is specified for column in table definition and choose character set translation string accordingly e.g. LATIN_TO_UNICODE, UNICODE _TO_ LATIN etc .

SELECT Prop_DSC  FROM P_ PROPERTY.PROPTYPE  WHERE TRANSLATE_CHK(Prop_DSC USING LATIN_TO_UNICODE) <> 0;

Coalesce() function in teradata

COALESCE is used to check if the argument is NULL, it takes the default value if it encounter NULL.

It will check for NOT NULL values sequentially in the list and it will return the first NOT NULL value.

Syntax:

COALESCE(Argument list,['default value'])

Argument list - May be columns of a table or expressions

COALESCE returns NULL if all its arguments evaluate to null. 
Otherwise, it returns the value of the first non-null argument in the given expression list.
Each COALESCE function must have at least two operands. Either two columns or one column with one default value.

It is possible to have multiple columns under coalesce like below:

COALESCE(col1, col2, col3, 0)

The above code says that if col1 is null then it will check col2.
If col2 is null then it will check for col3.
If col3 is also null, then 0 will be the output.

If coalesce encounters any of the column having not null value then the column value will be returned by the query. (Left to right priority)

Example : 1
If the country takes NULL value, 'US' will be supplied by Teradata

1.  SELECT COALESCE(Country,'US') as Output

Output

Country	Output
Italy	Italy
NULL	US

Example: 2
The following example returns the home phone number of the named individual (if present), or office phone if HomePhone is null, or CellPhone if present and both home and office phone values are null. Returns NULL if all three values are null.

1.  SELECT

2.  Name,

3.  COALESCE (HomePhone, OfficePhone, cellPhone) FROM  PhoneDirectory;

When a default value is provided, default value will be returned if all the input fields are null

1.  SELECT

2.  Name,

3.  COALESCE (HomePhone, OfficePhone, cellPhone,'No Phone') FROM  PhoneDirectory;

Example:3

Whenever you combine COALESCE with non-comparison operators, such as || or * or +, you must enclose the entire COALESCE function and its operands in parentheses.

1.  SELECT (COALESCE(100,0)) * 5;

Online Archiving in Teradata

Online archiving feature of the arcmain utility allows backing up data online in teradata without recording a Permanent Journal. instead it internally create log for object that is being archived and any changes on the tables are captured. it permits you to archive tables or entire databases
while applications are actively updating the tables that are being archived.

You can enable online archiving for pertculer database or table by using below command in arcmain prompt.

LOGGING ONLINE ARCHIVE ON FOR (DATABASENAME.TABLENAME);

Or

you can specify ALL keyword to enable all tables in a database.


LOGGING ONLINE ARCHIVE ON FOR (DATABASENAME) ALL;

Once Online archiving is enabled, it can be confirmed by querying database, for Ex.

select CreateTimeStamp,

DatabaseName (VARCHAR(30)),

TVMName (VARCHAR(30))

from DBC.ArchiveLoggingObjsV;

          

 Finally Online archiving can be disabled by command.

LOGGING ONLINE ARCHIVE OFF FOR (DATABASENAME) ALL;

Query to find out biggest table in teradata

Here is query you can find out biggest table in the system. top clause can be modified to display top 10,100 biggest tables in the system.

select top 1 databasename, tablename ,sum(currentperm) from dbc.tablesize  group by 1,2 order by 3 desc;