Positioned Cursors | VantageCloud Lake - Positioned Cursors - Teradata Vantage

The ANSI/ISO SQL standard defines an updatable (positioned) cursor. Therefore, an application can define a cursor for a query and then update results rows using the same cursor.

Using a Positioned Cursor to Update or Delete a Row in a Stored Procedure

Using a Cursor to Update a Row in Preprocessor2

1. Declare a cursor for a SELECT statement.
2. Open the cursor using an OPEN statement.
3. Retrieve a row using the FETCH statement.
4. Update or delete the fetched row using the WHERE CURRENT OF clause with an UPDATE or DELETE statement, respectively.
5. Close the cursor using a CLOSE statement.

Positioned Cursors for SELECT AND CONSUME Statements in Preprocessor2

SELECT AND CONSUME statements in positioned cursors are not valid.

When you select a row from a queue table using a SELECT AND CONSUME statement, the system consumes that row. Therefore, running SELECT AND CONSUME statements in positioned cursors makes no sense, because once selected, a consumed row cannot be deleted or updated. Therefore, any SELECT AND CONSUME statement run from a positioned cursor fails.

Because all cursors coded in a Preprocessor2 application default to being positioned cursors when you precompile the code with the TRANSACT or -tr preprocessor directives set to ANSI, you cannot run SELECT AND CONSUME statements from a cursor in an ANSI-style embedded SQL application.

Features Supporting Positioned Cursors

WHERE CURRENT OF Clause

After you declare a positioned cursor, the WHERE CURRENT OF clause allows the DELETE and UPDATE statements to act on the row pointed to by the cursor.

For example, the following DELETE statement deletes the current customer row from the cursor named x01.

EXEC SQL
DELETE FROM customer
WHERE CURRENT OF x01;

FOR CHECKSUM Clause

Positioned cursors do not recognize resource locking levels. Instead, these cursors assume all actions involving a cursor are done within a single transaction and that terminating that transaction closes any open cursors.

When you do not specify a LOCKING request modifier, all SELECT statements use a READ level lock. Positioned updates and deletes both default to a WRITE severity row hash lock.

The FOR CHECKSUM clause is not supported for stored procedures.

How CHECKSUM Locking Works

CHECKSUM locking is similar to ACCESS locking, but adds checksums to the rows of a results table to allow a test of whether a row in the cursor has been modified by another user or session at the time an update is being made through the cursor.

If an application specifies an ACCESS lock and then issues a cursor UPDATE or DELETE, the row to be changed may have been altered by another application between the time the first application read the row and the time the application issued the cursor UPDATE or DELETE statement.

If the checksum changes because another application updated the row after the current application read the row, the current application gets an error.

CHECKSUM locks are valid only when used with a SELECT statement opened by a positioned cursor.

Example: LOCKING with CHECKSUM

This example uses CHECKSUM locking on the table named t.

LOCKING TABLE t
FOR CHECKSUM
SELECT i, text
FROM t;

Rules for Using Positioned Cursors

Positioned UPDATEs and DELETEs must be in the same transaction as the SELECT that opened the cursor these statements are using.

In a stored procedure, you can specify the FOR UPDATE clause in the DECLARE CURSOR statement to define a positioned cursor. If you do not specify the FOR UPDATE clause, the system returns a warning that the cursor is not updatable.

Multiple UPDATEs of a fetched row or UPDATEs followed by a DELETE of the fetched row are allowed.

Positioned updates and deletes must occur within the same transaction that contains the SELECT statement that defined the cursor.

When the application attempts a positioned UPDATE or DELETE against a row selected by an invalid SELECT, the system returns an error and rolls back the impacted transaction.

When a program attempts to UPDATE or DELETE a row using a WHERE CURRENT OF clause for a non-positioned cursor, the system returns an error stating that the cursor is not updatable.

Performance Optimization Guidelines for Positioned Cursors

Row Hash Locks

Because of the number of row hash locks required to implement cursor updates on large data sets, use positioned updates and deletes only for small data sets. When too many row hash locks are imposed, the transaction fails with a lock table overflow error.

Either avoid long duration transactions when using positioned cursors, or use CHECKSUM locking to avoid locking conflicts that may prevent other applications from reading or updating the tables used by your application. CHECKSUM locking is not supported for stored procedures.

When the number of row hash locks becomes excessive and a lock table overflow error occurs, Teradata Vantage™ issues a transaction level end for any SQL application that gets the error.

Cursor Conflicts

The system returns a cursor conflict warning in all these situations, but runs the requested delete or update.

Related Information

• SELECT AND CONSUME, see the following:
  • SELECT AND CONSUME
  • Example: Creating a Procedure that Consumes a Queue Table
  • Example: Creating a Procedure that Performs a Consume Mode SELECT
  • Example: Using SELECT AND CONSUME on Queue Tables with JSON, ST_GEOMETRY, or XML Columns
• Cursors and Preprocessor2, see "Cursor Rules" in Cursors and Embedded SQL and Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
• Examples of implementing positioned cursors in an embedded SQL client application, see Teradata® Preprocessor2 for Embedded SQL Programmer Guide, B035-2446.
• Examples of implementing positioned cursors in stored procedures, see FOR.