DBQLSQLTbl's logging time

Database
Enthusiast

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.
5 REPLIES
Enthusiast

Re: DBQLSQLTbl's logging time

No idea?
Enthusiast

Re: DBQLSQLTbl's logging time

is this forum dead?
Enthusiast

Re: DBQLSQLTbl's logging time

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...
Teradata Employee

Re: DBQLSQLTbl's logging time

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.
Enthusiast

Re: DBQLSQLTbl's logging time

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,