Archives of the TeradataForum

Message Posted: Tue, 20 Jun 2006 @ 21:46:38 GMT

	<Prev	Next>		<<First	<Prev	Next>	Last>>

<-- Anonymously Posted: Tuesday, June 20, 2006 17:37 -->

You're confusing Primary Key (which by definition would be a unique identifier) and Primary Index (which is used to hash distribute the data and need not be unique).

I don't know where you're seeing the PK/FK comments you quoted, but those are wrong. Note that the PI (ProcID, CollectTimestamp) is only useful for distributing the data and the work of writing the log buffers; it's no good for querying. Sites that use DBQL extensively will move the log data from DBC to their own tables with better PI, maybe pre-join to translate UserID to UserName, etc. and do most of the queries against these user tables instead of DBC.

With Long QueryID enabled you can consider (ProcID, QueryID) the PK for QryLog and the FK for most other non-summary tables. (ProcID, QueryID, SqlRowNo) would be PK for QryLogSQL, etc.

DBQL is specifically designed for fast, low-impact logging. In initial releases, buffers were not written to disk until either they filled up or DBQL rules were changed; newer releases allow you to specify a maximum interval to wait before flushing the current buffer to disk.

QryLogSQL view is based on DBC.DBQLSqlTbl not DBQLogTbl. Different tables, different log buffers, so buffer timestamps can differ. Also in earlier releases SQL may not be not logged for failed queries...

DBQL is an efficient way to log a lot of detail, but there are tradeoffs. The PI is one example, also DBQL is "best effort" but data may occasionally be lost e.g. due to database restart. If you need 100% guaranteed logging for security or audit purposes then look at Access Log instead.

	<Prev	Next>		<<First	<Prev	Next>	Last>>