Modifying Tables Without Primary Keys (DB2 for VM)

DB2 for VM lets you modify tables with duplicate rows. Such tables cannot possibly have a primary key since no combination of column values can make a given row unique.

The DB2 for VM Data Adapter provides a way of maintaining tables with duplicate rows. You describe these tables with the attribute KEYS = 0 in the Access File, and the data adapter invokes special SQL syntax to maintain them. It adds the WHERE CURRENT OF cursor_name clause to the SQL UPDATE and DELETE statements it generates from MODIFY procedures. (cursor_name is a data adapter-generated value.) This syntax causes the RDBMS to keep track of the rows being updated.

Note:

• Maintain does not support modifying unkeyed tables.
• When the Access File describes a table with a primary key to FOCUS, the NEXT command retrieves rows in a known, logical sequence, by primary key in ascending or descending order.

When KEYS = 0, FOCUS does not request the rows in a particular order. As a result, rows retrieved with the NEXT command arrive unordered. Moreover, the RDBMS determines the retrieval sequence, and it may vary each time the MODIFY procedure executes.

• The FOCURRENT variable is not supported (see The FOCURRENT Variable); its value is always 0 for tables without primary keys. The AUTOCOMMIT ON CRTFORM option (discussed in Change Verify Protocol: AUTOCOMMIT ON CRTFORM) is not available for unkeyed tables.
• To guarantee the integrity of the row on which the select cursor is positioned, the data adapter adds a FOR UPDATE OF clause to the SELECT statement. This places an update lock on the selected rows.