All Forums Viewpoint
andydoorey 35 posts Joined 05/09
01 Mar 2011
Canary Query Details

Can anyone give me details of how the canary query frequency works in viewpoint?

The issue I'm having is that we want to measure response times for all workloads using the canary queries in viewpoint. I can get it to work for short and medium queries, but our long workload can have very long delays, often over an hour (I know this isn't good for the users, but that's how it is!).

I want to run a canary query in this workload every 15 minutes. The problems I have are:

1) A new canary query doesn't start running whilst one is in progress (i.e. in the delay queue)
2) The queries are aborted by the application after 30 minutes.

Is there any way I can achieve what I'm trying to do?

stever 149 posts Joined 08/09
01 Mar 2011

The fact that only one execution of each canary query is running at a given time is a fundamental aspect of the Viewpoint Data Collection Service that is responsible for running these queries. I believe that making that limitless, or even allowing the user to enter the number of concurrent queries, would be a dangerous change that could potentially impact Viewpoint's ability to get connections to the database to execute other queries.

It is true that there is a hard-coded timeout of 30 minutes for any non-heartbeat canary query. Is this something you would like to be able to customize per query? This seems like a reasonable change. Anyone else running into a similar problem?

And to answer your final question, I can't think of a way to achieve what you want currently in Viewpoint.

andydoorey 35 posts Joined 05/09
02 Mar 2011

Thanks for your response.
I think it would be useful to have a customizable timeout value. However I do need some way to run overlapping queries, i.e. I want to see that a query which started at 06:00 took 65 minutes to complete, one at 6:15 took 55 minutes, one at 6:30 took 38 minutes, etc... I guess I will have to write that myself in a unix script which starts bteq jobs running in the background.

BGreen 2 posts Joined 10/09
18 Jul 2011

If you implement the performance COE PDCR queries you can utilize them the attain what you are looking for - you need to configure those outside of viewpoint sorry but it works better for this shop as I can control the process more.

mikesteeves 46 posts Joined 10/11
09 Mar 2012

We are in the process now of moving our canaries, which are implemented using the PDCR Toolkit, over to Viewpoint.  Our concern was there is logic in the PDCR script that looks to make sure there isn't another canary of the same type running before it runs another one.  Is my understanding correct that Viewpoint has this same check built into it to wait until the last canary3 finishes before it kicks off another canary3?  And if that wait time exceeds 30 minutes, it then aborts both canary3 processes?  The one that is running as well as the one waiting?

I read through the Teradata Viewpoint Users Guide in the Canary section and saw nothing on this topic.


stever 149 posts Joined 08/09
09 Mar 2012

Viewpoint will only allow one execution of a particular canary query at a time.  If it becomes time to run the canary query again and the previous execution is still running, the next execution will be delayed until the previous execution completes.  As soon as it completes, the query will be submitted to Teradata.

As for timeouts, the system heartbeat has a timeout of 1 minute.  All other canary queries have a timeout of 30 minutes.  The JDBC driver will abort any query that exceeds these timeouts.  Only the running query will be aborted.  The next query is run as it would normally run.

mikesteeves 46 posts Joined 10/11
09 Mar 2012

Thanks!  Just curious, the query that is aborted after the 30 minute timeout...does it appear in Viewpoint's chart?  I believe DBQL only captures queries that complete...correct?

stever 149 posts Joined 08/09
09 Mar 2012

The query that is aborted after the timeout will not appear in the Viewpoint Canary Response Time portlet.  You will find an exception in the dcs log on the Viewpoint server.  Viewpoint does its own timing of the canary queries and does not rely upon DBQL for this data.  However, I believe you are correct that DBQL does not currently capture aborted queries.

wicik 35 posts Joined 06/12
07 May 2013

Hi there,
Is there some default Canary Query which can be runed from time to time by user not via viewpoint?

teradatauser2 236 posts Joined 04/12
19 May 2013

I want ti run a macro - PDCRINFO.CapCanarySUMRT_Rpt. This macro will give me max and avg canary response time. I see that this macro uses PDCRDATA."Canary_Hst" and PDCRDATA."ShiftHour". However, both of these tables are empty.
Can anyone help me to identify how these tables are populated ? Do i need to enable logging for these ? I tried to see if canary query logging can be enabled, however, i dont see any option to log the canary query.

wicik 35 posts Joined 06/12
20 Jun 2013

Hi there...
Can you give us information where did you found information about macro PDCRINFO.CapCanarySUMRT_Rpt and information how to use it inSQL?
I'm lookin for cannary query which can be run with SQL - bteq and will show informations like: base up, base down, login up, login down... or simply...
Base works, connection speed 100%, connection speed 50%, base is dead so you will not connect anywhere ;)
Which document and page? Or maybe on some web page? Regards!

dicedude 5 posts Joined 02/08
31 Jul 2013

Hi, previously we get Canary Query response times from dbcmngr.loghearbeat table.
Does anyone know if Viewpoint access the same table to get Canary information? When I check Viewpoint setting i do not understand where it is getting the data for Canary Heartbeat queries as it shows dbc.dbcinfo which is ambiguous as it only gives us TD software version and not Heartbeat in milliseconds

stever 149 posts Joined 08/09
31 Jul 2013

Viewpoint does not access the dbcmngr.logheartbeat table.  Viewpoint opens its own JDBC connection to the monitored Teradata database and executes the canary query itself.  As you noted the default system heartbeat canary query runs "SELECT * FROM DBC.DBCINFO", but this can be modified if you so choose.  When Viewpoint runs the query, it captures the elapsed time to execute the query and then stores this value into its own database for later display in portlets such as Canary Response Times.
Hope that helps!

dicedude 5 posts Joined 02/08
05 Aug 2013

Many thanks Stever. But I am still wondering what would be the query that viewpoint server issues that captures Canary response times.
What dbc system table/view other than dbcinfo holds some value that would be meaningful performance metrics?
Hope to hear more about that soon.
When i go to Viewpoint config to find details how they setup VP server, i do not get any meaningful definition from the following:
replace macro console.dbscontrol as (;);
replace macro console.aborthost as (;);
replace macro console.config as (;);
replace macro console.checktableb as (;);
replace macro dumplocklog as (;);
replace macro console.ferret as (;);
replace macro console.xgtwglobal as (;);
replace macro console.lokdisp as (;);
replace macro console.cnscons as (;);
replace macro console.schmon as (;);
replace macro console.qryconfig as (;);
replace macro console.qrysessn as (;);
replace macro console.rcvmanager as (;);
replace macro console.showlocks as (;);
replace macro console.tdwmdmp as (;);
replace macro console.vprocmanager as (;);
Also i see that Viewpoint monitors TDWM. Probably i need to look into TDWM views to find any meaningful metrics that can give me Canary metrics.
Aany comments?

stever 149 posts Joined 08/09
06 Aug 2013

Viewpoint doesn't query a specific Teradata table for the canary response times.  It calculates the response times itself based upon how long it takes to execute the SQL you enter for the canary query.  For example, Viewpoint will know that it starts the query at 8:17:00 AM.  Then if it gets the response to this query from the Teradata system at 8:17:03 AM, Viewpoint knows that the response time for this execution of the query was 3 seconds.
So by default, the System Heartbeat canary query runs "SELECT * FROM DBC.DBCINFO".  This is probably a good "heartbeat" query for most systems to essentially determine that the Teradata system is up and able to respond to simple queries in a timely fashion.  However, you might also want to write other queries to evaluate how long it's taking to query certain tables or run some standard query from which you expect a specific response time.
All of the macros and grant statements that you reference above are simply setting up the access required for Viewpoint to run all of its data collectors against the Teradata system.  They have nothing to do with canary queries.

dicedude 5 posts Joined 02/08
08 Aug 2013

Hi Stever, Thanks but yes I understand that. I am interested to know how I translate data that used to come from logheartbeat in dbcmanager with the current Viewpoint.
1) What view, portlet holds the data that is related to logheartbeat in dbcmanager?
I am trying to analyze canary query response times etc. and that used to come from logheartbeat in dbcmanager.
So now I have to get that data from viewpoint and I do not know which system view or portlet can give me that data.

stever 149 posts Joined 08/09
08 Aug 2013

Viewpoint has never used the logheartbeat in dbcmanager.  This data in not available in Viewpoint.  This functionality was replaced by the existing Canary Response Times portlet.

dicedude 5 posts Joined 02/08
14 Aug 2013

Yes I agree Stever. That was precisely what I was trying to say. My question is if Viewpoint uses Canary Response Times portlet, where do I get it? Is it possible to send me screenshot of that portlet then I can ask CS to provide?
My email

stever 149 posts Joined 08/09
14 Aug 2013

The Canary Response Times portlet is available as part of the standard Viewpoint Self-Service portlet bundle.  It should be available on your Viewpoint server.  However, it's possible your admin hasn't granted access to that portlet.

dicedude 5 posts Joined 02/08
16 Aug 2013

Many thanks for your continued help Stever. Yes I do have that now and yes it does give me a lot of information but it doesn't give me idea where Viewpoint is getting these data. I will keep exploring.
Yes I can see the metrics and all the threshold settings but not awar of the system level view that gives viewpoint these metrics values.

You must sign in to leave a comment.