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.
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.
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.
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!
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.
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
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.
Is there a way to Abort IDLE sessions for just 1 user? I see the exception list , but i need an include option.
Account is currently offered but specific to a username is currently an enhancement request.
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?
They will show up in the Alert Viewer portlet!
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)