How to find out Active sessions at any point of time.

Database

How to find out Active sessions at any point of time.

DBC.SessionInfo provides information about all sessions but does not have a field which mentions current state of a session. My objective is to find out the sessions active at various times within an hour and then notify on-call on a long running transaction.

I started by looking into dbc.sessioninfo for ALL sessions and then dbc.diskspace for sessions which have current spool > 0. But all data does not match up with Teradata manager active session screen output.

Has anyone implemented such a process. Please share your thoughts.

Thanks Much in advance

Sabir Shakeel
ProKarma inc.
6 REPLIES
Enthusiast

Re: How to find out Active sessions at any point of time.

Just a pointer: SessionInfo contains all active active sessions. When a session ends, it is removed from SessionInfo and logged into LogOnOff.
Enthusiast

Re: How to find out Active sessions at any point of time.

Hello Sabir,

Were you able to come up with a script to find out what you are looking for with out using some thing like TASM?

If so, Can you please share it?

Thanks,

Murali

Enthusiast

Re: How to find out Active sessions at any point of time.

This sql will give you all the current running (ACTIVE) sessions and the time they have been logged on for.

SELECT Username , LogonSequenceNo , PARTITION AS Utility_Type ,

     MAXIMUM ( CURRENT_TIMESTAMP - ( CAST( ( CAST( LogonDate AS DATE FORMAT 'YYYY-MM-DD'  ) ( CHAR ( 10 ) )  ) || ' ' || LogonTime  AS TIMESTAMP ) ) HOUR TO SECOND ) AS TimeLoggedIn ,

     COUNT ( * ) AS num_of_sessions    

FROM Dbc.Sessioninfo        

GROUP BY 1 , 2 , 3 ;

Enthusiast

Re: How to find out Active sessions at any point of time.

If you have select access on DBC.SessionTbl ..try this.

select * from DBC.SessionTbl where currentdatabase='DB_Name'  AND USERNAME='User_Name' ORDER BY SESSIONNO;

 

Re: How to find out Active sessions at any point of time.

Neither DBC.SessionInfo nor DBC.SessionTbl give you active sessions. They list all "connected" sessions, whether these are active or idle or delayed.

Enthusiast

Re: How to find out Active sessions at any point of time.

Hello, This is what I do to find active sessions:

SELECT *

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