CODEWITHSUNDEEP
teradata
venkat
venkat

Reputation: 77

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

I want to find out what are the queries that are currently under execution specific to a user or a group . I don't want to use view point . I need to achieve this functionality using a SQL.

Upvotes: 4

Views: 10996

Answers (3)

dnoeth
dnoeth

Reputation: 60462

The PMon-API used by Viewpoint is also available as SQL-functions, e.g. this is returning info about all currently logged on sessions:

SELECT * FROM TABLE (MonitorSession(-1,'*',0)) AS dt;

You can check the PEState or AMPState column to find active queries.

Upvotes: 2

Rob Paller
Rob Paller

Reputation: 7786

To obtain the SQL for a session currently active without using Viewpoint you will need to use the PMPC APIs (which I believe are what Viewpoint might be using as well) to obtain that information from the database.

Step 1: Obtain Input Values for MonitorSQLText() from MontiorSession()

SELECT HostID
     , SessionNo
     , RunVprocNo 
  FROM TABLE(MonitorSession({HostID}, {UserName}, {SessionNo})) AS T2;

HostID of -1 will include all hosts

UserName of '*' will include all users

SessionNo of 0 includes all sessions

You should provide at least one of those values to narrow your search.

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

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

Use the HostID, SessionNo, RunVprocNo from the first step.

I tried to put the values from Step 1 into a Volatile Table and a CTE to feed step 2 dynamically. When I did the database responded with an error indicated that MonitorSQLText() could not be run in variable mode. This coincides with the documentation indicating that this API in particular must be used in 'constant mode'.

This should work on TD 14+ and possibly TD 13.10.

Upvotes: 2

Ahmed
Ahmed

Reputation: 345

Try this 

select * from dbc.SessionInfo;

you'll have a list of request with their user and group

I hope to help you

Upvotes: 3

Related Questions