Teradata : How to get the server name using query

Question

How can I get the server name using query in Teradata? That is, if I am writing a query on the 'dev' server, it should return the dev server name.

for example, in Sybase : we will be using select @@servername.

Answer 1

There's nothing like @@servername in TD. You might create a SQL UDF on each server returning the name, e.g.

REPLACE FUNCTION syslib.#servername ()
RETURNS VARCHAR(30)
LANGUAGE SQL
CONTAINS SQL
DETERMINISTIC
RETURNS NULL ON NULL INPUT
SQL SECURITY DEFINER
COLLATION INVOKER
INLINE TYPE 1
RETURN 'dev'

If it's created in syslib it can be accessed without qualifying it like this:

SELECT #servername();

Answer 2

SELECT CASE 
         WHEN LogonSource LIKE '%UAT%'  THEN 'UAT' 
         WHEN LogonSource LIKE '%PROD%' THEN 'Prod' 
         ELSE 'Unknown' 
       END DatabaseName 
FROM   DBC.SessionInfoV 
WHERE  UserName = 'myname';

Answer 3

This will give you information close to @@servername.

select ClientTdHostName, ServerIPAddrByServer, ServerPortByServer 
from DBC.SessionInfo where SessionNo=Session;

Validated against 17.xx TD.