Searched and Positional Methods
RC/Edit offers two editing methods: the Searched Update method and the Positional Update method.
RC/Edit offers two editing methods: the Searched Update method and the Positional Update method. The Searched Update method uses a different algorithm to fetch and update the data than the Positional Update method. The following lists situations when the Searched Update method could be used for editing data.
- Two or more people want to edit the same table simultaneously. The Searched Update method allows multiple users to edit the same tables. An explicit lock is never issued when using the Searched Update method.
- To make global changes to a column. To make global changes to all rows or to selected rows, the SET command in the Searched Update method is quicker than CHANGE ALL in the Positioned Update.
- When data does not contain NULL values. When there are no NULL values in the data, Searched editing uses fewer resources than Positional editing, regardless of the types of changes being made.
- To refrain from changing a row that has been changed by another process. If the row was changed by another user since the row was fetched, the update will not be allowed.
When using the Searched Update method, data can be changed throughout the whole set of retrieved data. Be sure to know what data will be affected before using the Searched Update method.
The following are situations when the Positional Update method could be used for editing data.
- The data contains NULL values. Positional editing uses fewer resources than Searched editing when making changes one row at a time to data that contains NULL values.
- To update, delete, or insert rows in a table containing a LONG VARCHAR type column.
- To lock the table for some reason. When using the Searched Update method, the table cannot be locked for the edit.
Identical Rows in Retrieved Data
When data is changed in a row with the Searched Update method of editing, the data in all identical rows will be changed. Many tables do not allow identical rows, but this possibility does exist for some users. The Positional Update method only affects the actual row edited.
Editing, Locking, and Table Contention
The Positional method permits table locking; the Searched method does not. If you want to edit a table using the Searched Update method and another user is editing the data using the Positional Update method, you will be locked out of the table. A message will appear indicating the table is in use by user X. That user will receive a message indicating that your user ID is waiting to edit the table (unless both are in a data sharing environment, and the other user is in another MVS).
Two or more people using the Searched method of editing on the same data can encounter table contention if they both try to save their changes at the same time. Common SQL errors are +100 ROW NOT FOUND, or -911 ROLLBACK DUE TO DEADLOCK OR TIMEOUT.
+100 indicates that another user has changed the data after you fetched. The data in the editor will have to be refreshed and the changes made again.
-911 while using the SAVE or SET command indicates someone else is committing data at the same time. Wait a moment and try again. The deadlock should be over.
NULL Value Considerations
If the table being edited contains a number of columns that have NULL values, the Searched Update method of editing should only be used for global changes using the SET command or to permit simultaneous editors. The NULL values cause regular row updates to be processed slower than the Positional Update method.
Positional Method Update Order
When RC/Edit applies updates using the Positional Update method, they are applied in fetch order, not in the current display order. The order of updates is inconsequential except for the case of unique indexes on columns. For example, a user can delete a row that has a primary key value of 5, then update another row, assigning the same primary key of 5. If the delete does not occur before the update, a duplicate error occurs.
Consequently, some updates involving unique indexes might need to be made by using multiple edit sessions or issuing the SAVE command to commit the index changes during the edit session.
Searched Method Update Order
The Searched Update method updates the rows in the order they are displayed on the screen. The Searched method uses only half as many fetches as the Positional method.
Performing Sort During Edit and Browse Processing
When using CA RC/Update to edit a table, you can specify an option (D) for the Searched Update method. This option invokes a processing sequence that can help improve performance. First, this method initiates the sorting process in DB2. Then, if you specified a row limit, this method applies a data retrieval limit to the sorted data.
This processing sequence is helpful when you do not specify extended data queries and you apply the row limit. Having the sort processing occur before the limit processing helps ensure that the correct data is retrieved.
This processing sequence is the default behavior when browsing tables.
Example: Generate a DB2 Sort and Fetch the First 20 Table Rows
This example demonstrates invoking an editing method that generates a DB2 sort (ORDER BY) first and then retrieves a limited number of rows (LIMIT). Using this method can help improve performance through more efficient background processing.
- Choose a table to edit (from the CA RC/Update Main Menu), and then complete fields as follows on the RC/Edit Options panel:
The Data Query Edit panel appears.
- TypeYin the Where field (to create a data query).
- Type20in the Row Limit field.
- TypeD(for Searched Update)
- Type1Dnext to a column (to retrieve the 20 rows in descending order), and press the END key.Retrieval results appear, showing 20 rows arranged as instructed.
Positional Update Warning Screen
The following informational screen displays when Positional Update is selected without specifying locked tables.
This screen never appears if you set the Default Update Method (UMETHOD) in the DEFAULTS parmlib member to P to force locking with the Positional Update method. An error message is generated if this method is requested, but locking cannot be used. This occurs when the table is a remote table.
To suppress display of this panel in the future when editing tables in Positional Update mode when locked tables are not specified, enter
Y(yes) in the Suppress display of this panel in the future field at the bottom of the screen. This screen will not display again. Use this option with caution. From this point on when editing a table in Positional Update mode without specifying locked tables, this panel will not display and no warning will display. If N (no) is specified, the next time a table is edited in Positional Update mode without specifying locked tables, this warning panel will be displayed.