All Forums Viewpoint
southsong 5 posts Joined 02/10
09 Sep 2010
idle-session alerts

We currently have Teradata manager for data collection and monitoring alerts on a Teradata V12.00.02 system with one non-tpa channel node.
Idle-session alerts is currently in place using the alert policies of Teradata manager to abort any session that is idle for over 30minutes. Certain batch ids that are not subjected to idle-session aborts are put on the exclusion list.
For the channel node, we've 4 sessions that are always ON, but never get aborted for sitting idle and these sessions are not on the exclusion list.

When we recently migrated alerts to the viewpoint, all the sessions to the channel nodes were aborted as they were idle for over 30 minutes.

Does the alert migration feature on viewpoint migrate all the alerts from Manager or are there any implicit alerts in the manager that are not migrated, becauase it appears the idle-session alert was not migrated completely.

stever 149 posts Joined 08/09
12 Sep 2010

The next patch for Viewpoint 13.10 will include an enhancement to allow Viewpoint alerts to filter on the host ID of a session. This will allow you to specify the host ID of your LAN as a part of the session alert conditions so only those sessions from the LAN are aborted.

stever 149 posts Joined 08/09
15 Sep 2010

I've also talked with the Teradata Manager engineers and any alerts in the Alert Policy Editor were applied to both LAN and channel sessions. These are the alerts that were migrated to Viewpoint.

Garyadmin 8 posts Joined 05/10
04 Oct 2010

For alerting, we'd like to have an alert defined that can Email the DBAs if queries get demoted to a 'penalty box' (or any workload move) workload - so we can check quickly whether to Abort manually or allow to run. Also we have a TASM state for when we get AWT overload - this goes automatically into this state to disallow more online queries getting onto the system until AWT numbers reduce - but we'd like an alert for this - currently the current TASM state only shows in the VP TASM workload monitor portlet - or we run sql:
select *from dbc.qrylogevents
where eventinfo like 'State change initiated%'
order by collecttimestamp desc
;
Lastly (theres always something else!)...we want to have a VP alert to run some SQL via BTEQ (in our situation, we were thinking of inserting a row into the DBCMNGR.ALERTREQUEST table, so it can email users directly) if the alert detects a certain level of blocking - but (may have asked this before!) can you confirm that we need the Teradata notification service running and the BTEQ script to do this only on a windows server - why can't we have this all stored on the Viewpoint server?
Thats it for the moment!

stever 149 posts Joined 08/09
04 Oct 2010

The first 2 items you mentioned are already part of TASM and Viewpoint. For both exceptions and unplanned events, you can specify an alert that will be executed by the Viewpoint Alerting functionality when that exception/event occurs. I'm not sure how this worked with TDWM and Teradata Manager, but the alert name can be selected as a part of the new Workload Designer portlet in Viewpoint.

You do need a Windows server to run BTEQ at this point, but I'm not sure this is exactly what you're looking for. Can you define what you mean by "detects a certain level of blocking"? How were you envisioning this detection would occur? I think what you might want to do is schedule a job to determine whether the level of blocking you are looking for exists on the system. If it does, then write a row to the dbcmngr.alertrequest table from within your scheduled job. From that point forward, the Viewpoint Alerting functionality will take care of sending out the email.

Garyadmin 8 posts Joined 05/10
04 Oct 2010

we were thinking of using the existing alerting on VP - ie Blocked queries by duration or large number of blocked sessions, then trigger SQL which picks up the sessionid of the blocking session, so then gets the userid, from this gets their email account (stored by us on another table) in order to insert a row into ALERTREquest, so they get emailed directly about the issue - at present our alert sends an email to our email accounts (as coded into our alert definition) and we chase up the user . We were also thinking of doing this for other problems - eg for highly skewed queries. For this we need to run a nice bit of SQL with a few table joins - thought it would be good to have this stored somewhere safe and secure like a BTEQ script on the VP server - or even in a stored procedure?. Good point about Workload Designer - we currently are VP 13.03, so about to upgrade to 13.10 - I'll take a look at this, thanks

stever 149 posts Joined 08/09
04 Oct 2010

So yes, in order to do all the logic you described about picking up the Teradata database user's email address, you would need to run a BTEQ script on a Windows server. I'm sure you could do this with a script or a stored procedure, but I'd recommend the stored procedure if possible. That seems like a cleaner approach, basically defining a procedure that takes a session ID and writes a row with the relevant data to send email to dbcmngr.alertrequest.

koebyj 8 posts Joined 10/07
01 Dec 2011

Is there a way to Abort IDLE sessions for just 1 user?  I see the exception list , but i need an include option.

Koeby J

gryback 271 posts Joined 12/08
01 Dec 2011

Account is currently offered but specific to a username is currently an enhancement request.

surish 5 posts Joined 12/11
03 May 2012

Currently the sessions which are idle for 6 hours are getting aborted.I want to know all the users whom sessions are gettnng aborted by being idle for 6 hours..

 

Can anyone throw some light?

stever 149 posts Joined 08/09
03 May 2012

They will show up in the Alert Viewer portlet!

garyadmin3 3 posts Joined 11/11
24 May 2012

 

sel * from dbc.qrylog where  querytext = 'abort'

and username = 'viewpoint'

 

to see the queries that ran an ABORT by Viewpoint user - although of course querylogging has to be on for this user

our querylogs in DBC only contain todays data , so this is quick

 

(or whatever is the Teradata userid that Viewpoint uses to do the aborting - as defined in your Teradata Systems portlet/setup)

amittera 35 posts Joined 12/09
12 Jul 2013

Hi ,
 
I need to put two alerts on TD 12 Manager.
 
1) Blocked sessions emailed to by if they are in blocking status for more than an hour, and give who blocked who?
2) Get a report/email of idle sessions who are idle for let say 1 hr.
Apart of them getting aborted automatically, is there any other way?
Also, if you can tell me what is the impact of idle session being logged in?
Regards,
 

Amit Saxena
Teradata Consultant

You must sign in to leave a comment.