Find out currently running queries in teradata using SQL instead of viewpoint

Step 1: Obtain Input values for MonitorSQLText() from MonitorSession() for active session

SELECT hostid,sessionno,runvprocno,logontime,username,ReqStartTime FROM TABLE(MonitorSession {HostID}, {UserName}, {SessionNo})) AS T2 where AMPSTATE ='ACTIVE';

 

HostID of -1 will include all hosts

UserName of '*' will include all users

SessionNo of 0 includes all sessions

 

Example:

SELECT hostid,sessionno,runvprocno,logontime,username,ReqStartTime FROM TABLE(MonitorSession(-1, 'PR_TAS_ETL', 0)) AS T2 where AMPSTATE ='ACTIVE';

HostId

SessionNo

RunVprocNo

LogonTime

UserName

ReqStartTime

1

50684229

30716

2018/04/10 19:40:11.00

PR_TAS_ETL

2018/04/10 21:40:31.00

 

 

Step 2: Obtain SQLText for the Session you are interested in

SELECT *
  FROM TABLE (MonitorSQLText({HostID}, {SessionNo}, {RunVprocNo})) AS T2;
 

Use the HostIDSessionNoRunVprocNo from the first step.

 

Example:

SELECTFROM TABLE (MonitorSQLText(1, 50684229, 30716)) AS T2;

 

HostId

SessionNo

SeqNum

SQLTxt

1

50684229

1

SQLTxt0001.txt

 

Comments