Teradata Viewpoint Services

Teradata viewpoint is web application and is very interactive and efficient monitoring tool, which replace teradata manager and its functionality from version td13, it is installed and run on dedicated server in the network to capture various monitoring and performance data of teradata database system.

viewpoint basically capture data using its data collection service (dcs) and save it to its back-end database runs in postgressql database, its portal service called viewpoint service displays the collected information interactively on viewpoint portlet.

there is mainly 3  services which manage the viewpoint operation
1. viewpoint (portal service).
2. dcs (data collector service)
3. postgresql (back-end database service)

we can check the status of services or start/stop/restart as we face some issue

$/etc/init.d/dcs start/stop/status

$/etc/init.d/postgresql start/stop/status        

$/etc/init.d/viewpoint start/stop/status         

we have their respective log files to check for any error or warnings.

/opt/teradata/viewpoint/logs/viewpoint.log

/opt/teradata/dcs/logs/dcs.log

* apart from these services there is supporting service called CAM services for all java based services.

Teradata error 3541 The request to assign new PERMANENT space is invalid.

While creating a new user or cloning a user from existing user, we may come across the error 3541. 

known remedy is to Either decrease the PERMANENT space in the new database or increase the PERMANENT space in the parent database.

Failure 3523 after teradata version upgrade

There are scenarios when user or owner has specific user rights on specific object or database, the statement  or stored procedure may fail with error code 3523(Failure 3523 COLLECT_STATS:The user does not have STATISTICS access to test_db.user_tbl), this is seen mostly after any upgrade,patch or efix. at this moment you may wonder what has gone wrong.

we could fix the issue by just compiling the stored procedure.

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;