Teradata Activity Count Implementation

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Tourist

Teradata Activity Count Implementation

Hi Teradata Masters,

 

i have the requirement to capture all the DML activity count.

The Number of Row affected by any Sql Statement.

i want to capture username,logdate,timestamp,statementtype,row_count_affected.

for example:whenever anyone fire select * from dbc.tabelsv

then log information shoud be capture and store into logtable with above column as mentioned.

 

can anyone help me how to implement this in teradata.

 

Thanks,

Vijay

Tags (1)
3 REPLIES
Senior Apprentice

Re: Teradata Activity Count Implementation

Hi,

 

If you're looking for this information from "any sql dml" then on current releases of TD you'll have to do some work on the client side (i.e. in your application).

 

For SELECT statements you can get all of this information from the main DBQL table (DBQLogTbl). The main column that you need is 'NumResultRows'.

 

Looking at your requirements, I have mapped them to the columns in that table:

username - UserName

logdate - CAST(STARTTIME AS DATE)

timestamp - StartTime (when the query started - is this what you want?)

statementtype - StatementType

row_count_affected - NumResultRows (but only for SELECT)

 

I've got a feeling that the NumResultRows column will be populated for other dml commands in a later release of TD but can't find my notes on this yet.

 

HTH

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Junior Contributor

Re: Teradata Activity Count Implementation

In TD16.20 Feature Upgrade 1 there's a new column QryLogV.StmtDMLRowCount which contains INSERT/UPDATE/DELETE counts in JSON format.

 

Before this needs StepInfo enabled and then the counts are tracked in QryLogStepsV.RowCount for DEL/INS/UPD steps, but there are several other steps like UpsIns/MRD indicating inserts/updates/deletes and there's also RowCount(2|3) for MRM steps...

Senior Apprentice

Re: Teradata Activity Count Implementation

Thanks to @dnoeth for the clarification on where Ins/Upd/Del counts are.

 

Remember that if you're using step level info that you need to typically use the 'final' Merge/Insert/Delete step from the query. Do not simply sum up all of the individual row counts.

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com