Editing Considerations

There are several things to consider when you use Edit. The following topics include situations specific to Db2 and some general rules for keeping your data in order.

• Obscured Columns
• Column Snap when Scrolling
• Pending State
• Maximum Rows to Select
• Memory Utilization
• Locking a Table During Edit
• Method of Updating Db2
• Editing Primary Column in a View
• Columns with Defined Default Values
• Columns with BLOB, CLOB, DBCLOB, or XML Data Types
• Unicode Table Data
• EBCDIC Tables With UNICODE Columns

Obscured Columns

Obscured columns are Db2 columns that are only partly visible on the screen. They are displayed highlighted and cannot be edited. This feature prevents you from changing a column for which you see only a portion of the available data. For example, you may see a 1 that is actually part of the number 122. File-AID for Db2 displays the following formatted data types as obscured columns:

• Smallint (small integer)
• Integer
• Decimal
• Float
• Time
• Date
• Timestamp (TMSTMP and TMESTZ)
• DECFLOAT

To edit an obscured column, the entire column must be visible on the screen. To view an entire column, scroll left or right, or issue the ROW or ZOOM command. A column snap feature when scrolling reduces the frequency of obscured columns. See Column Snap when Scrolling for details.

File-AID for Db2 does not display the following data types as obscured columns. The data in these columns can be edited when the columns are only partly visible.

• Char (character)
• Varchar (variable character)
• Long Varchar
• Graphic
• Vargraphic (variable graphic)
• Long Vargraphic

Column Snap when Scrolling

If scrolling left or right causes a column on the far left side of the screen to become obscured (see Obscured Columns), File-AID for Db2 will shift the display to unobscure the column. This feature is called a column snap.

File-AID for Db2 performs a column snap under all circumstances except the following:

• The scroll amount is set to an integer.
• The scroll amount is set to MAX.
• The scroll amount is set to CSR and the cursor is in the scrollable area of the screen.
• Scrolling right results in displaying the far right data in the table.

Pending State

When you repeat or copy a row using the Edit function, the new row is placed into a pending state and a PEND flag displays on the table mode screen in place of the row’s line number. When you insert or repeat a row in row mode, a row pending message displays under the command line for the new row. The rows are not actually added to the table until you save them. You can do one of the following to make pending rows a part of your table:

• Make a change to the new row and press Enter with Auto Commit ON.
• Issue the SAVE primary command.
• END the Edit session with AUTOSAVE ON.

Setting new rows into the pending state prevents you from adding exact copies of columns that are supposed to be unique. You can revise the columns before they are included in the table.

Although they are not part of the table, pending rows are treated just like all other rows. You can perform any operation on them, such as delete, copy, or exclude. The FIND and CHANGE primary commands affect them in the usual way, but the RESET command does not clear them from the table.

The only way to change a row from pending to permanent is to save it by one of the methods listed above.

Maximum Rows to Select

The MAXIMUM ROWS TO SELECT field displays on the Edit Selection Template screen. Entering a value in it limits the number of rows to be displayed. Because File-AID for Db2 uses a Db2 cursor to fetch all Db2 information shown on the screen, limiting the number of rows eliminates reading more rows than necessary.

File-AID for Db2 adds a FETCH FIRST n ROWS clause into the internal generated SQL SELECT statement, using the specified number in MAXIMUM ROWS TO SELECT field. This improves the performance of the query and uses less memory than if the SQL SELECT statement were issued without the limiting clause. If an existing criteria is used and it includes FETCH FIRST n ROWS clause in it, File-AID for Db2 replaces this field value by the number in the FETCH FIRST n ROWS clause.

When you have set a MAXIMUM ROWS TO SELECT value and the limit is satisfied, File-AID for Db2 replaces the BOTTOM OF DATA marker with a MAXIMUM SELECTED ROWS marker and a row of plus signs. This message tells you that you are at the end of the selected data area but more of the object exists. The default value for this field is set through Option 0, Defaults and User Parms (see Browse-Edit-SQL-Analysis-Display-Options) or through the PROFILE primary command (see PROFILE-PRO).

Memory Utilization

At the beginning of an edit or browse session, File-AID for Db2 loads as many rows as possible into memory, up to the value specified in MAXIMUM ROWS TO SELECT field on the Edit Options or Browse Options screen (Edit (or Browse) Options screen). During the edit or browse session, File-AID for Db2 always obtains memory to process the commands you enter. The amount of memory needed varies, depending on the actions you plan to take during the edit or browse session. The product default settings normally will allow you to execute the required commands.

If you experience problems having enough memory to edit or browse successfully, you can take the following actions to make more memory available:

• Limit the number of rows of data processed during the edit or browse session by specifying Selection Criteria or specifying a lower value for Maximum Rows to Select.
• End any split-screen sessions.
• Increase the Region size specified on the TSO LOGON panel. File-AID for Db2 requires a minimum region size of 8196.

If, after taking these actions, you still experience problems, then you can use the Memory Utilization window to make more memory available. For more information about this window, refer to Memory Utilization Window.

Locking a Table During Edit

The LOCK TABLE option on the Edit Options screen lets you lock a table while you edit it. This prevents other users from accessing and updating the table until you are finished. File-AID for Db2 accomplishes this by issuing the following SQL:

Since the lock is released and immediately reacquired whenever you issue a SAVE command, it is possible for another user to access the table during the release.

The lock is also released and reacquired whenever you press Enter with Auto Commit ON. For this reason, you may want to set Auto Commit OFF so you have exclusive access to your table between SAVE commands. The Auto Commit option can be set through Option 0, Defaults and User Parms (see Browse-Edit-SQL-Analysis-Display-Options) or through the PROFILE primary command (see PROFILE-PRO).

Method of Updating Db2

When you update data, File-AID for Db2 builds the appropriate SQL statement to reflect the changed row in the table. The only columns included in the statement are the ones in which a value has changed. However, the WHERE clause identifies the old values for all columns to ensure that the correct row gets updated.

If the table has a primary key or a unique index with the RETRIEVE UNIQUE KEY INFO option set to ON (see PROFILE-PRO), File-AID for Db2 produces the following statement:

If there is no unique index for the table or the RETRIEVE UNIQUE KEY INFO option is OFF and no primary key exists, File-AID for Db2 produces the following statement:

Thus, if multiple rows match the WHERE clause, File-AID for Db2 updates only the first one it finds.

If Db2 returns a +100 code Row not found for UPDATE File-AID for Db2 displays the Concurrent Update Intercept window (Concurrent Update Intercept Window) for you to decide how to continue.

Editing Primary Column in a View

When browsing or editing a view with primary key columns, the primary key columns are not identified as primary unless the Profile option RETRIEVE UNIQUE KEY INFO had been set to ON. In order to update a primary key column in a view, the Profile option RETRIEVE UNIQUE KEY INFO must be ON before you start the edit session. Edit the view in a single table/view edit session. Unless the view is the only edit object, or the first in a multiple table/view edit session, updates to the primary key columns fail (Db2 returns -535). See also Method of Updating Db2 and Retrieve UNIQUE KEY information prior to edit and RETRIEVE UNIQUE KEY INFO.

Columns with Defined Default Values

For character columns with defined default values the Profile option “SPACES IMPLY NULL” must be ON so File-AID for Db2 can fill in the default values for blank columns when inserting or updating the column.

Columns with BLOB, CLOB, DBCLOB, or XML Data Types

When browsing or editing a table that has a column with BLOB, CLOB, DBCLOB, or XML data types, the MAXLOB parameter (see Maximum size LOB data retrieved) determines the maximum size (4096) of LOB data that can be retrieved and displayed.

BLOB, CLOB, and DBCLOB Data Types

• Column Data with a length within the MAXLOB parameter value can be edited in place.
• Column Data with a length exceeding the MAXLOB parameter value is protected and cannot be edited in place, but can be browsed or edited with the XLOB primary command when in BROWSE or EDIT ROW mode. The XLOB command (see XLOB) retrieves the entire data record of the target LOB column (CLOB, DBCLOB, or BLOB) into a temporary work file and starts a File-AID/MVS browse or edit session to browse or edit the work file data. Upon exiting the File-AID/MVS browse or edit session, the target LOB column is updated in with the work file contents (edit only) and the work file is deleted (browse or edit).
  In Browse, these fields are displayed with a different color attribute.

Unicode Table Data

File-AID for Db2 Browse and Edit functions support Unicode table data. The Browse and Edit functions retrieve the native Unicode data from Db2. Then, File-AID for Db2 internally calls the IBM z/OS Conversion Services program, CUNLCNV, to convert data from Unicode to terminal CCSID EBCDIC-based data. The converted EBCDIC-based data is displayed on the Browse/Edit data screens.

In an Edit session, you can edit the converted data in the Unicode columns. After the data is edited, File-AID for Db2 calls the program CUNLCNV to convert the EBCDIC-based data to Unicode.

In row mode with HEX ON, File-AID for Db2 displays native Unicode data in hexadecimal format, in addition to the converted EBCDIC-based data. You can then edit the Unicode data in hexadecimal format. See Editing Unicode Data in Row Mode and Hexadecimal Format for more information.

You can expand a column data of single row by E or V command in Row mode. These commands help you to edit the Unicode data. See E and V for more information.

EBCDIC Tables With UNICODE Columns

Db2 Version 11 EBCDIC tables with individually defined UNICODE columns are supported in Browse and Edit. UNICODE columns in EBCDIC tables display like UNICODE columns in UNICODE tables.