Monday, March 18, 2013

DB2 Table Editors

In today's blog post I want to briefly discuss one of the more useful, yet often ignored, DB2 tools available on the market -- Table Editors...

Typically, the only method of updating DB2 data (indeed, any data stored in a relational database) is with  SQL data manipulation language statements DELETE, INSERT, and UPDATE (or with a database load). Because these SQL statements operate on data a set at a time, multiple rows -- or even all of the rows -- can be affected by a single SQL statement. Coding SQL statements for every data modification required during the application development and testing phase can be time-consuming and error-prone.

A table editing tool can reduce the time needed to make simple data modifications by providing full-screen edit capability for database tables. The user specifies the table to edit and is placed into an edit session. The data is presented to the user as a series of rows, with the columns separated by spaces. A header line indicates the column names. The data can be scrolled up and down as well as left and right. To change data, the user simply types over the current data.

This type of tool is ideal for supporting the application development process. A programmer can make quick changes without coding SQL. Also, if properly implemented, a table editor can reduce the number of erroneous data modifications made by beginning SQL users.

When a table editor is used, all columns are available for update. Thus, if a table editor is used to change production data, a simple mis-keying can cause unwanted updates. Native SQL should be used if you must ensure that only certain columns are updated.

One final note: Tested SQL statements and application programs are characterized by their planned nature. These modification requests are well thought out and tested. This is not true for changes implemented through a table editor, so always exercise caution when using a table editor.

Examples of DB2 Table Editors include: