Logging Row Counts for DML Statements - Analytics Database - Teradata Vantage

SQL Data Manipulation Language

Deployment
VantageCloud
VantageCore
Edition
Enterprise
IntelliFlex
VMware
Product
Analytics Database
Teradata Vantage
Release Number
17.20
Published
June 2022
Language
English (United States)
Last Update
2024-04-05
dita:mapPath
pon1628111750298.ditamap
dita:ditavalPath
qkf1628213546010.ditaval
dita:id
esx1472246586715
lifecycle
latest
Product Category
Teradata Vantage™

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}