Thursday, May 23, 2013

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)

Sunday, May 19, 2013

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;