Db2 Rowset Data Modification: Positioned UPDATE, DELETE, and Multi-Row INSERT

Validate row counts after rowset changes.

A rowset positioning cursor can fetch several rows into COBOL host-variable arrays with one FETCH. After that fetch, Db2 can modify the current rowset with positioned UPDATE or DELETE statements, or target one row inside the rowset. That power is useful, but it also makes row-count validation non-negotiable.

This guide explains how to modify data with rowset positioning in Db2 for z/OS, including positioned update, positioned delete, single-row targeting inside a rowset, multi-row insert, ATOMIC versus NOT ATOMIC, and SQLCA checks for COBOL programs.

What rowset modification means

A rowset cursor fetches a block of rows instead of one row at a time. The program can then process arrays in working storage. For updateable rowset cursors, Db2 can apply positioned updates or deletes to the current rowset, or to a single row within that rowset.

The important distinction is scope. WHERE CURRENT OF cursor-name can affect the current rowset. FOR ROW n OF ROWSET targets one row in that rowset. A production program should make that choice explicit and then verify the affected-row count.

Rowset modification options

Operation	Scope	What to check
Positioned UPDATE ... WHERE CURRENT OF	Can update all rows in the current rowset.	Expected rowset size, row count, and whether every fetched row should change.
Positioned DELETE ... WHERE CURRENT OF	Can delete all rows in the current rowset.	Business rule, audit requirement, and commit/rollback scope.
FOR ROW n OF ROWSET	Targets one row in the current rowset.	Host variable row number is within the fetched row count.
INSERT ... FOR n ROWS	Inserts multiple rows from host-variable arrays.	ATOMIC or NOT ATOMIC, diagnostics, and failed-row handling.

Positioned update for the current rowset

Use this form only when every row in the current rowset should receive the change. If the cursor fetched 50 rows, design and test the program as a 50-row update path, not as a single-row path.

EXEC SQL
   UPDATE EMP
      SET SALARY = :WS-NEW-SALARY
    WHERE CURRENT OF C1
END-EXEC

IF SQLCODE = 0
   PERFORM CHECK-ROWSET-UPDATE-COUNT
ELSE
   PERFORM WRITE-DB2-ERROR
END-IF

Do not assume that SQLCODE = 0 is enough. Check the row count available for your statement and Db2 level, and compare it with the number of rows the program intended to update.

Update one row inside a rowset

When only one row in the fetched rowset should change, use rowset row targeting. The row number can be a host variable, which lets the program select the matching array element after validation.

EXEC SQL
   UPDATE EMP
      SET SALARY = :WS-NEW-SALARY
    FOR ROW :WS-ROW-NO OF ROWSET
    FOR CURSOR C1
END-EXEC

Validate WS-ROW-NO before the SQL statement. It should be greater than zero and less than or equal to the number of rows fetched into the current rowset.

Delete with rowset positioning

A positioned delete can remove every row in the current rowset or one selected row, depending on the syntax used. Treat this as a high-risk path in batch programs because the wrong rowset size can delete more rows than expected.

EXEC SQL
   DELETE FROM EMP
    WHERE CURRENT OF C1
END-EXEC

EXEC SQL
   DELETE FROM EMP
    FOR ROW 3 OF ROWSET
    FOR CURSOR C1
END-EXEC

For production deletes, write audit data before commit. Include the key values from the host-variable arrays, not just the rowset number.

Multi-row insert with host-variable arrays

Db2 can insert multiple rows from host-variable arrays with FOR n ROWS. This is useful for bulk insert paths where the COBOL program has already filled arrays with validated data.

EXEC SQL
   INSERT INTO EMP_TBL
          (EMP_NO, EMP_NAME, SALARY)
   FOR :WS-INSERT-COUNT ROWS
   VALUES (:HV-EMP-NO,
           :HV-EMP-NAME,
           :HV-SALARY)
   ATOMIC
END-EXEC

With ATOMIC, Db2 treats the insert set as one unit for success or failure. With NOT ATOMIC, individual rows can succeed or fail, so the program must inspect diagnostics and handle partial success.

ATOMIC versus NOT ATOMIC

Choice	Behavior	When it fits
ATOMIC	The multi-row statement succeeds or fails as a unit.	Use when partial inserts would break the business rule.
NOT ATOMIC	Rows can succeed or fail independently.	Use only when the program has diagnostics handling for failed rows.

SQLCA and diagnostics checks

Rowset processing needs stronger checks than a one-row cursor loop. At minimum, validate SQLCODE, SQLSTATE, affected-row count, fetched-row count, and diagnostics for partial success cases.

• Check SQLCODE after every rowset update, delete, or insert.
• Compare affected rows with the intended number of rows.
• Use diagnostics when NOT ATOMIC can produce per-row results.
• Log key values from arrays when a row fails, not only the array index.
• Commit only after the full rowset operation passes business validation.

Common mistakes

Using WHERE CURRENT OF when only one row should change

In rowset processing, that can affect the whole current rowset. Use row targeting when the business rule is one row.

Not validating the row number

A host variable used in FOR ROW n OF ROWSET must be checked against the number of rows actually fetched.

Using NOT ATOMIC without diagnostics handling

Partial success is only useful when the program can identify failed rows and decide whether to continue, retry, or roll back.

Related Db2 topics

Use this guide with Db2 Rowset Positioning Cursor, Db2 SQL Execution Validation, Db2 SQLCODE and SQLSTATE, Db2 GET DIAGNOSTICS, and Db2 Application Environment.

FAQ

Can WHERE CURRENT OF affect more than one row with a rowset cursor?

Yes. With rowset positioning, a positioned update or delete can affect all rows in the current rowset. Use row targeting when only one row should be changed.

How do I update one row in a Db2 rowset?

Use FOR ROW n OF ROWSET with the cursor name and validate that n is within the number of rows fetched into the current rowset.

When should I use NOT ATOMIC for multi-row insert?

Use NOT ATOMIC only when partial success is acceptable and the program checks diagnostics for each failed row.