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

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)
5 REPLIES 5
Highlighted
Ambassador

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
Ambassador

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

Ambassador

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
Fan

Re: Teradata Activity Count Implementation

Thanks Everyone for your reply but i requirement is to capture the log  only the Batch Job actvity. For example 

 

Could you please tell me how can i capture the Activityu count. 

1) suppose..if i have a Audit table like FTTP_SRC.ZZZ_Audit(JobName, TableName, ActCount, LoadDate, LoadTime); 

2) Now i have script(**bleep**.bteq) like below 

logon **bleep**/**bleep**,**bleep**; 
select * from DBX.dept; 
insert into FTTP_SRC.ZZZ_Audit('**bleep**.bteq', 'DBX.Dept', ActivityCount, Current_date, Current_time); 

select * from DBX.Emp; 
insert into FTTP_SRC.ZZZ_Audit('**bleep**.bteq', 'DBX.Emp', ActivityCount, Current_date, Current_time); 

logoff; 

If I have script file like above to Capture the Audit info including ActivityCount of each of table. This will give problem. 

Please tell me what i need to use to insert ActivityCount into FTTP_SRC.ZZZ_Audit. 

Supporter

Re: Teradata Activity Count Implementation

Vijay,

 

For your specific example;

 

logon **bleep**/**bleep**,**bleep**;
select * from DBX.dept;
insert into FTTP_SRC.ZZZ_Audit
select '**bleep**.bteq', 'DBX.Dept', COUNT(*) ActivityCount, Current_date, Current_time
from DBX.dept;

select * from DBX.Emp;
insert into FTTP_SRC.ZZZ_Audit
select '**bleep**.bteq', 'DBX.Emp', COUNT(*) ActivityCount, Current_date, Current_time
from DBX.Emp;

logoff;

 

Please clarify other cases you have.

 

Cheers,

Teradata Frank, Certified Master