How to interpret Teradata SQL Logging Rules for qInsight?

As part of the qInsight log collection process, the validation script collects the SQL logging rules from the DBC.RulesV table on Teradata. These rules govern two crucial tables in the Teradata system: dbqlogtbland dbqsqltbl (which will be called logtbl and sqltbl from here on out). Understanding these rules is essential for understanding the amount of sql statements that will be collected as part of the qInsight collection script. Looking at these two source tables, the logtbl holds query metadata and a short preview of a query's text, although the length of this preview depends on a value set by the DBA (by default this value is set to 200 characters). The sqltbl is the more verbose of the two tables and can contain querytexts of an unlimited length. For sqltbl, a DBA or admin can enable and disable logging rules on a user or application level. It is these rules that are collected by the qInsight validation script and they can be found either under the rulesv section of the validation summary output or within the actual DBC.RulesV table on Teradata.

 

Question: What if SQL Logging is disabled for all users or has strict rules applied?

Note: To confirm the strictness of the SQL Logging rules, refer to the rulesv section of the validation output. If TypeOfRule is set to “Logging Disabled” or SQL is set to “F” for All or most users, this would be considered strict SQL logging rules.

Option 1: Provide a modified collection script from the qInsight portal that collects querytext data from the logtbl. This will produce more limited analysis due to possible truncations in the shorter logtbl querytexts. Any statement longer than the varchar limit on the Querytext field will not be properly analyzed or factored into qScore.

Option 2: Request that the customer change their SQL Logging rules to allow all or key in scope users to have their querytexts collected for a period of 1-4 weeks. This option will require buy-in from the security or DBA team and will delay the collection by a few weeks.

 

Question: What if the logtbl and sqltbl counts are vastly different?

This typically indicates that there are sqltbl logging rules that are in place or were put in place during the date range selected for collection. In this case, one can proceed with a partial collection from the sqltbl , wait for more statements to be logged in the sqltbl (if the rules allow it), or proceed with collection from the logtbl.

 

Question: What if no rows are found for the logtbl or sqltbl?

It is unlikely that these tables will contain absolutely zero data. In this case, verify with the customer that the logtbl and sqltbl values provided correspond to the actual location of the data. Customers will sometimes export or set a different location for these tables.

Have more questions? Submit a request

0 Comments

Please sign in to leave a comment.