ACTIVITY_COUNT | VantageCloud Lake - ACTIVITY_COUNT Result Code Variable - Teradata Vantage

Teradata® VantageCloud Lake
Deployment
VantageCloud
Edition
Lake
Product
Teradata Vantage
Published
January 2023
Language
English (United States)
Last Update
2024-04-03
dita:mapPath
phg1621910019905.ditamap
dita:ditavalPath
pny1626732985837.ditaval
dita:id
phg1621910019905

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

ACTIVITY_COUNT 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 to run and is updated during runtime after each executable SQL statement is processed. You must write your own code to test the returned count.

There can be 2 different limits of activity count, depending on the client application and the server capability:
  • 4-byte limit that is 2^32-1 rows
  • 8-byte limit that is 2^64-1 rows

If the client application requests the 8-byte activity count and the server can return 8-byte activity count, you do not get the numeric overflow warning. The activity count is a true activity count (not activity count modulo 2^32).However, if the client application does not request the 8-byte activity count or the server cannot return 8-byte activity count, you get numeric overflow behavior.

When to Test ACTIVITY_COUNT

Test ACTIVITY_COUNT after each execution of an SQL statement for which you must know the number of rows affected to make sure of 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 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 SQL stored procedure and embedded SQL applications.

    There can be 2 different limits of activity count, depending on the client application and the server capability:
    • 4-byte limit that is 2^32-1 rows
    • 8-byte limit that is 2^64-1 rows

Related Information