Editing data with the table data editor

Open the table data editor in edit mode to edit the data and save the changes to the database for a table, view, alias, or materialized query table.

Before you begin

You can edit the data in tables, views, aliases, and user-maintained materialized query tables for DB2® for Linux, UNIX, and Windows Version 9.5 and later. You can edit the data in tables and views for DB2 for z/OS® Version 9 and later. You must have the appropriate privileges to select, update, insert, or delete the data.

Procedure

To edit data in the table data editor:

  1. In the Administration Explorer, click a folder to display the list of objects in the Object List. For example, click the Tables folder to see the list of tables.
  2. In the Object List, right-click an object and click Edit Data in the context menu.

    The table data editor opens in the editor area of the workbench. The title at the top of the table data editor indicates the name of the object. If filtering conditions have been set in the Table Data Filter dialog, the name of the object is appended with the label [Filtered].

    For columns that have a LOB data type or any user-defined data type that is based on a LOB data type, only the first 100 bytes of the data are shown.

  3. Make your changes to the data.
    • To edit the value of a table cell:
      1. Double-click the table cell. As an alternative, you can right-click the table cell and click Edit Value.
      2. Change the contents of the cell and press Enter.
      Restriction: Columns that have a LOB data type or any user-defined data type that is based on a LOB data type cannot be edited.
    • To insert a new row into the table:
      1. Right-click anywhere in the table data editor and click Insert Row.
      2. Enter data in the new row.
    • To delete a row in the table, right-click the row and click Delete Row.
    • To set a value to null, right-click the nullable column in the row and click Set Null.
  4. To commit the changes to the database, click the Graphic of the icon for the Commit data changes to the database button button (Commit the data changes to the database) on the editor's toolbar.
    Tip: The table data editor relies on the row identifiers of the selected rows. The row identifiers are used together with the selected values when the changes are committed to the database to uniquely identify a row and to identify if the data in the database has been changed after the data was retrieved in the table data editor. If the data in the database has changed, the changes cannot be committed. In general, row identifiers can be reused if a row is deleted from the database. Row identifiers can also be updated if a table is reorganized.

    When the data is successfully committed to the database, a dialog is displayed to indicate that the data was successfully committed. The dialog also shows the statements that were run to make the data changes. You can also review the status in the SQL Results view.

    If any of the changes cannot be committed, none of the changes are committed.

    When the data is not successfully committed, a dialog is displayed with an error message. The dialog also shows the statements that were attempted to be run. Resolve the error and take the necessary steps to commit the changes to the database again. For example, the changes are not committed when:
    • Another application changed data values in the database (except for LOB and XML columns) while you were editing the same data values in the table data editor.
    • The table was reorganized after you opened the table data editor.
    • The database returns an error.

What to do next

You can open multiple table data editors on the same or different objects. You can also filter or refresh the displayed data.

Feedback