ACTIVITY_COUNT | Teradata Vantage - ACTIVITY_COUNT - Advanced SQL Engine

The ACTIVITY_COUNT status variable returns the number of rows affected by an SQL DML statement in an embedded SQL or stored procedure application.

It provides the same function as the Activity Count word in the SQLERRD array of SQLCA for embedded SQL applications.

ANSI Compliance

ACTIVITY_COUNT is a Teradata extension to the ANSI/ISO SQL:2011 standard.

When ACTIVITY_COUNT Is Set

ACTIVITY_COUNT is initialized to 0 when a stored procedure or embedded SQL application begins execution and is updated during runtime after each executable SQL statement is processed. You must write your own code to test the count it receives.

There can be 2 different limits of activity count, depending on the client application and the server's capability:

4-byte limit that is 2^32-1 rows
8-byte limit that is 2^64-1 rows

If the client application is coded to request for the 8-byte activity count and the server is capable of returning 8-byte activity count, you will no longer see the numeric overflow warning. This means the activity count is always a true activity count (not activity count modulo 2^32). If, however, the client application is not coded to request for 8-byte activity count or the server is not capable of returning 8-byte activity count, you will still see numeric overflow behavior.

When to Test ACTIVITY_COUNT

Test ACTIVITY_COUNT after each execution of an SQL statement for which you need to know the number of rows affected to ensure proper error handling.

You must write your own code to handle error processing based on ACTIVITY_COUNT values.

Usage Constraints on ACTIVITY_COUNT

The following usages of ACTIVITY_COUNT are valid within a stored procedure or embedded SQL application:

ACTIVITY_COUNT can be specified as the operand of a SET statement.
For example, the following statement is valid.
```
SET h1 = h1 + ACTIVITY_COUNT;
```
ACTIVITY_COUNT can be specified as an expression in an SQL statement.
For example, the following statements are valid.
```
INSERT INTO table_1 (column_1)
VALUES (:ACTIVITY_COUNT);
UPDATE table_1
SET column_1 = column_1 + :ACTIVITY_COUNT;
```

The following usages of ACTIVITY_COUNT are not valid:

ACTIVITY_COUNT cannot be declared explicitly within a stored procedure.
ACTIVITY_COUNT cannot be SET to a value or an expression.
For example, the following statement is not valid.
```
SET ACTIVITY_COUNT = h1 + h2;
```
ACTIVITY_COUNT cannot be specified in the INTO clause of a SELECT statement.
For example, the following statement is not valid.
```
SELECT column_1 INTO :ACTIVITY_COUNT FROM table_1;
```
ACTIVITY_COUNT cannot be specified in place of the INOUT and OUT parameters of a CALL statement in a stored procedure.
If the activity count for a query exceeds a limit of 232-1 rows, the system returns the true activity count modulo 232 along with the following warning message:
```
Numeric overflow has occurred internally. The number of rows returned is actual number of rows returned, modulo 2^32.
```
To determine the actual activity count in this situation, you must add the modulo 232 value returned to 232 as follows:

True activity count = returned_value + 232

This is true for both SQL stored procedure and embedded SQL applications.
There can be 2 different limits of activity count, depending on the client application and the server's capability:
- 4-byte limit that is 2^32-1 rows
- 8-byte limit that is 2^64-1 rows

ACTIVITY_COUNT | Teradata Vantage - ACTIVITY_COUNT - Advanced SQL Engine - Teradata Database

SQL Stored Procedures and Embedded SQL

ANSI Compliance

When ACTIVITY_COUNT Is Set

When to Test ACTIVITY_COUNT

Usage Constraints on ACTIVITY_COUNT

Related Topics