Find the list of active running queries through SQL

Database
Enthusiast

Find the list of active running queries through SQL

Hi,

I am using Teradata 13.10, I want to know the list of queries which is executing in Teradata using sql.

Thanks for your help

11 REPLIES

Re: Find the list of active running queries through SQL

If SQL in this context is refering to SQLAssistant then from dbqlogtbl based on column "APPID" it can be determind.

This field tells the application through which the Query has been submitted.

Re: Find the list of active running queries through SQL

for active sessions we can refer the column "PARTITION" from sessiontbl.

Enthusiast

Re: Find the list of active running queries through SQL

Hi Vhota,

Thanks for your response.

Just to rephrase my query, I assume that we can get the list of active sessions using MONITOR SESSION function, but I want to get the exact colums  we get in Viewpoint query monitor portlet.

Thanks in advance for your help

Senior Apprentice

Re: Find the list of active running queries through SQL

The syslib.MonitorSession uses the PMon API, too.

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

Re: Find the list of active running queries through SQL

Hi Dieter,

Thanks for your response, But I am not able to find the query ID while querying the same.

The below columns are only returned.

Is it possible to also get the queryid , So it would be helpful.

Note :-

Columns  returned by the query

HostId

SessionNo

LogonPENo

RunVprocNo

PartName

PEstate

LogonTime

UserId

LSN

UserName

UserAccount

PECPUsec

XActCount

ReqCount

ReqCacheHits

AMPState

AMPCPUSec

AMPIO

ReqSpool

Blk1HostId

Blk1SessNo

Blk1UserId

Blk1Lmode

Blk1Otype

Blk1ObjDBID

Blk1ObjTID

Blk1Status

Blk2HostId

Blk2SessNo

Blk2UserId

Blk2Lmode

Blk2Otype

Blk2ObjDBID

Blk2ObjTID

Blk2Status

Blk3HostId

Blk3SessNo

Blk3UserId

Blk3Lmode

Blk3Otype

Blk3ObjDBID

Blk3ObjTID

Blk3Status

MoreBlockers

LogonSource

HotAmp1CPU

HotAmp2CPU

HotAmp3CPU

HotAmp1CPUId

HotAmp2CPUId

HotAmp3CPUId

HotAmp1IO

HotAmp2IO

HotAmp3IO

HotAmp1IOId

HotAmp2IOId

HotAmp3IOId

LowAmp1CPU

LowAmp2CPU

LowAmp3CPU

LowAmp1CPUId

LowAmp2CPUId

LowAmp3CPUId

LowAmp1IO

LowAmp2IO

LowAmp3IO

LowAmp1IOId

LowAmp2IOId

LowAmp3IOId

AvgAmpCPUSec

AvgAmpIOCnt

AmpCount

TempSpaceUsg

ReqStartTime

ReqCPU

ReqIO

ReqNo

WlcId

DontReclassifyFlag

ProxyUser

Enthusiast

Re: Find the list of active running queries through SQL

Hi All,

Revisiting on my request.

Thanks to Dieter to help me to get the list of active sessions.

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

I want to understand if we can get the list of active sessions along with the queries which is running.

Junior Supporter

Re: Find the list of active running queries through SQL

Step 1:- To get Active Queries 

Sel *  FROM TABLE (MonitorSession(-1,'*',0)) AS T1

Where  AMPState='Active’;

Step 2:-

To get SQLtext of active sessions use MonitorSQLText

You need to use the values of HostId, SessionNo,and RunVProcNo from step 1 


Abhishek Jadhav
Enthusiast

Re: Find the list of active running queries through SQL

Hi Abhishek,

Thanks for your quick response,it is possible to combine both the queries together,

as  MonitorSQLText        is allowing only constant values to be provided inside

As per documentation we can send only constant values, but any waclub two sql together

Junior Supporter

Re: Find the list of active running queries through SQL

You can create a shell script which will have something like below

This way you do not have to pass the parameters manually every-time for each session .

1 - 

INS into  DB.TABLE1

Sel *  FROM TABLE (MonitorSession(-1,'*',0)) AS T1

Where  AMPState='Active’;

2 - Export the o/p of below query to a file

SEL 'UPDATE A FROM DB.TABLE1 A ,(SELECT SQLTxt FROM tABLE (MonitorSQLText('||HostId||','||SessionNo||','||RunVProcNo||')) AS T2 WHERE SessionNo='||SessionNo||') B

   SET SQLTxt= B.SQLTxt

   WHERE SessionNo='||SessionNo||';' (TITLE '')

   FROM DB.TABLE1;

 This will generate a bunch of update statements which you will have to execute using BTEQ.

3 - Execute BTEQ file

Note:- If the query is already completed then the Update will fail and will not give you any SQL. You can ignore Updates that fail with code 7504.


Abhishek Jadhav