17.05 - Logging Row Counts for DML Statements - Advanced SQL Engine - Teradata Database

Teradata Vantage™ - SQL Data Manipulation Language

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
Release Date
January 2021
Content Type
Programming Reference
Publication ID
B035-1146-175K
Language
English (United States)

You can log the row counts of DML statements in DBQLogTbl for single and multistatement requests when you specify BEGIN QUERY LOGGING. See Teradata Vantage™ - SQL Data Definition Language Syntax and Examples, B035-1144.

Row counts for insert, update, and delete operations are logged in the StmtDMLRowCount column of the DBC.QryLogV view and DBC.QryLogV_SZ security zone view, including stored procedures, external stored procedures, Teradata Parallel Transporter, MultiLoad, and FastLoad operations. The counts are stored in JSON format. You can use JSON functions to extract data from the views. See Teradata Vantage™ - JSON Data Type, B035-1150.

Example: Viewing Row Counts for DML Operations

Here is the table definition for the example.

CREATE TABLE t1 (a1 INTEGER, b1 INTEGER, c1 INTEGER);

You use this statement to begin logging queries.

BEGIN QUERY LOGGING ON ALL;

The following statements perform insert, update, and delete operations:

INSERT INTO t1 VALUES (1,2,3);
UPDATE t1 SET C1 = 10;
DELETE FROM t1;

This statement ends query logging.

END QUERY LOGGING ON ALL;

Now, you can query the StmtDMLRowCount column of the view QryLogV in the DBC database to display a count of the rows that were changed.

SELECT StmtDMLRowCount FROM DBC.QryLogV;
StmtDMLRowCount
---------------------------------------
{"Insert":1}
{"Update":1}
{"Delete":1}