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.
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-IFDo 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-EXECValidate 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-EXECFor 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-EXECWith 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
SQLCODEafter every rowset update, delete, or insert. - Compare affected rows with the intended number of rows.
- Use diagnostics when
NOT ATOMICcan 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.
