All Forums Database
monisiqbal 119 posts Joined 07/09
24 Aug 2009
DBQLSQLTbl's logging time

DBQLSQLTbl works in conjunction with DBQLogTbl and logs SQL Text, however the CollectTimeStamp field in this table does not relate to the CollectTimeStamp field in DBQLogTbl nor the FirstRespTime and StartTime in this table.

Most probably DBQLSQL has it's own buffer and data is logged when the buffer is filled with the CollectTimeStamp corresponding to that buffer.
We could not find the timing of this logging mechanism in Teradata documentation. Also, can we safely assume that DBQLSQLTbl logs data before DBQLogTbl for each request?

Please confirm.

monisiqbal 119 posts Joined 07/09
26 Aug 2009

No idea?

monisiqbal 119 posts Joined 07/09
30 Sep 2009

is this forum dead?

emilwu 72 posts Joined 12/07
30 Sep 2009

nah.. forum is not dead.. it is just dbql logging timestamp are nortorius for this issue and teradata might be embarassed to confirm it:) LOL...

Fred 1096 posts Joined 08/04
30 Sep 2009

CollectTimeStamp supports efficient blocking & deferred writing of log rows. The value is the same for all rows in the same memory buffer (but each log table has its own buffers). CollectTimeStamp was explicitly not intended for correlation between tables and there is no defined order in which buffers are initialized. There is always a chance that rows for some event could fill the buffer for one table while the other buffer has plenty of room remaining. In general, you would be well served to consider CollectTimeStamp to be "internal" data and ignore it.

The real "issue" was that in the initial DBQL implementation, the sequence of QueryID values would start over at each database restart. Many early DBQL examples added some sort of"fuzzy match" qualification on the CollectTimeStamp as a workaround for this non-uniqueness. The Long QueryID feature (standard in TD12, available as a DR and activated by DBSControl setting in V2R6) solves the issue.

monisiqbal 119 posts Joined 07/09
05 Oct 2009

Thanks for the clarification. We are more interested in the moment/time that the rows are logged in each table because we have to fetch rows from each of these DBQL tables and then correlate among them over QueryID.
From what I have observed, for the default buffer size, the entries are being logged in the following order:
1. DBQLSQLTbl
2. DBQLogTbl
3. DBQLObjTbl

I haven't looked into Explain and Step tables yet.
Could you please point to some TD document that could confirm the unreliability of the "time" of logging of these tables and that it depends solely on the filling of the buffer.
I've seen this behavior documented for ResUsage tables but not for DBQL tables.

Much thanks,

You must sign in to leave a comment.