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