CHANGE (C)

The CHANGE command finds a data value to be changed and replaces it with a new data value. It is valid only in Edit. The CHANGE command cannot be used in ZOOM or ZOOMH mode.

When you specify a column name, you limit the search only to data in the specified column. When you specify positions within the column, you further restrict the search. If no column name is entered, all columns in the table are searched for the data. However, the CHANGE command searches only certain column types based on the data type of the find-string. The following table describes which columns are searched.

Columns Searched by Data Type

By default, the CHANGE command searches both excluded and non-excluded rows as well as both hidden and non-hidden columns. You can limit the search to only excluded rows and hidden columns or only non-excluded rows and non-hidden columns.

Find and Change Strings

The first parameter for the CHANGE command is always the find-string, the second parameter the change-string. Enclose the strings in either single or double quotes when:

• 
  
  • The find-string or change-string contains blanks
  • The find-string or change-string matches the characters of reserved words, for example NULL, but you only want to change words with these characters in them. Valid reserved words for the CHANGE command are:

• 
  
  • The find-string or change-string contains a single quote (’), you must enclose it in double quotes
  • The find-string or change-string contains double quotes ("), you must enclose it in single quotes

find-string

Value to be changed. The find-string value is not case sensitive, unless enclosed in quotes and prefaced by C.

ANY

Change any value. Allows you to change data in a column without any regard to its current value. Specify ANY as the find-string, but do not enclose it in single or double quotes. You must also specify a column name when using the reserved word ANY as a find-string.

If ANY is too general as a wildcard, you may want to substitute picture strings for ANY as the find-string.

P

Specifies a picture string. P’picture’ may contain one or a combination of the following:

change-string

New value. The change-string replaces the find-string. Lowercase characters only change to uppercase when CAPS ON is in effect. See CAPS.

C

Specifies a case sensitive string. Enclose the string in quotes and preface it with a C. For example, if you want to search for a value of SMITH in all capital letters, specify c'SMITH'. To change a value to Smith in mixed case, specify c'Smith'.

X

Specifies that the value is in hexadecimal format. When specifying a hexadecimal value, you must enclose the find and change strings in single or double quotes.

NULL

Specifies a search for null values or a change to null values.

column-name

Name of the Db2 column in which you want to restrict the change. You can enter a partial column name. If more than one column name matches the partial name, a window displays all matching column names, allowing you to select one. The following figure shows a sample column selection window. See Partial-Column-Names for more information.

Column Selection window

Start position

Starting position within the specified column name in which you want to restrict the change. If only one position is specified, it is processed as a starting position.

End position

Ending position within the specified column name in which you want to restrict the change. If only one position is specified, it is processed as a starting position.

Modifiers

Specify one of the following parameters to modify the search order.

NEXT

Default. Starts a search for the next occurrence of the find-string at the beginning of the first line being displayed (if the cursor is still in the COMMAND field), or at the cursor location (if the cursor is in the data part of the display). If you specify both a column name and the NEXT parameter, the next occurrence in the specified column of the find-string is changed.

ALL

Changes all occurrences of the find-string starting at the beginning of the first line of the data and continues to the bottom of data. If you specify both a column name and the ALL parameter, all occurrences in the specified column of the find-string are changed.

PREV

Starts a search for the previous occurrence of the find-string starting at the end of the line preceding the first line being displayed (if the cursor is still in the COMMAND field), or at the cursor location (if the cursor is in the data display). If you specify both a column name and the PREV parameter, the previous occurrence in the specified column of the find-string is changed.

FIRST

Starts a search for the first occurrence of the find-string starting at the beginning of the first line of the data and continues until the string is found or the bottom of data is reached. If you specify both a column name and the FIRST parameter, the first occurrence in the specified column of the find-string is changed.

LAST

Starts a search for the last occurrence of the find-string starting at the end of the last line of the data and continues backward until the string is found, or the top of data is reached. If you specify both a column name and the LAST parameter, the last occurrence in the specified column of the find-string is changed.

Rows/Columns to Search

Specify one of the following parameters to determine which rows or columns to search.

Blank

Default. Searches for the string in all rows and columns.

NX

Searches for the string only in those rows that are not excluded and columns that are not hidden.

X

Searches for the string only in those rows that are excluded and columns that are hidden.

CHANGE Command Window

The CHANGE command window (see the following figure) prompts you for the information required to complete your change request. It is only displayed when you enter CHANGE with no parameters. Use the CHANGE command window to specify special change requests, such as replace ANY information in a column or increment the change string to create unique values. After you provide the required change information, press Enter on this screen to process the change and exit the window. Press END to cancel the CHANGE command and exit the window.

CHANGE Command window

Find and Change Strings

For general description see Find and Change Strings.

Find-string

Value to be changed.

To specify any value, enter the reserved word ANY. ANY replaces any value in the specified column positions regardless of its current content.

Change string

New value (maximum 40 characters, including quotes).

When you also fill in a value in the Increment by field, the change string represents the initial value that is to be incremented. For example: 100 is the change string for the Employee Number. This value is used as the first replacement string. Then the Increment by value, for example 10, modifies the initial value. Thus, the following Employee Number values of a multiple change are 100 (initial), 110, 120, 130, etc. The new value must fit the required format and length for the selected column, or you receive an error message.

Increment by

Increment to modify alphabetic (a through z) and numeric characters (0 through 9) of the change string. Other characters (e.g %, #, =, etc.) are not changed by the increment value. The default increment is blank (no increment). The increment is always a numeric value. A negative increment, for example (-1), is valid and decreases from the initial value (100 initial, 99, 98, 97, etc.).

Positive increment is assumed and must be entered without a sign.

The column data type determines how increments are interpreted. Date and time actually have additional pop-up displays (see Timestamp Pop-up Window, Time Pop-up Window, and Date Pop-up Window).

Specify an increment when you want to change a range of records with one CHANGE command and avoid duplicate change strings.

When the change string is an alpha character, for example A, and the increment is 2, the resulting string is A, C, E, G, I, K, etc.

When the change string is alphanumeric, for example AB3, and the increment is 4, the resulting string is AB3 (initial), AB7, AC1, AC5, AC9, AD3, etc.

Alphabetic and numeric data values wrap when they reach the maximum allowable value for the column, starting over at 0 (numeric) or A (alphabetic). Alphabetic data includes all letters of the alphabet in upper and lower case. Integers wrap at their maximum values, for example, increment -1 wraps from -2147483648 to 2147483647, increment 1 wraps from 2147483647 to 0.

Column name

Name of the Db2 column in which you want to restrict the change. Entering an asterisk (*) presents a list of columns from which you select a column name. You can also enter partial column names. If more than one column name matches the criteria, a window displays all matching columns, allowing you to select one. See Column Selection window for a sample window. If you do not specify a column name, the change is applied to all columns.

The column data types DATE, TIME, TMSTMP, and TMESTZ actually have additional pop-up displays to perform date and time arithmetic or incremental changes (see Timestamp Pop-up Window, Time Pop-up Window, and Date Pop-up Window).

Start position

Starting position within the specified column name in which you want to restrict the change. If no end position is specified, the search continues until the end of the column.

End position

Ending position within the specified column name in which you want to restrict the change. If no start position is specified, the search begins at the beginning of the column.

Modifier

NEXT is the default, unless you have specified a search range with the CC line markers. In that case, ALL is the default within the range. See Modifiers for an explanation of valid values.

Rows/Columns to search:

See Rows/Columns to Search: for an explanation of valid values.

Timestamp Pop-up Window

When the column data type in the CHANGE command window is TMSTMP or TMESTZ, the Timestamp Specification window (see the following figure) displays. You can either apply increments to the change-string for rapid data generation or perform date and time arithmetic (add or subtract) on the find-string for data aging purposes. After you provide the operator and increment information, press Enter on this screen to process the change and exit the window. Press END to cancel the CHANGE command and exit the window.

CHANGE Command Window with Timestamp Increment

Find string

Prefilled with the value you specified in Change Command window. You are allowed to change the prefilled Find string.

Change string

Blank or prefilled with the value you specified in Change Command window.

The change-string must be blank, if you want to perform data aging you entered a + or - character in the Operator field.

The change-string must be a complete timestamp value if you want to generate incremental timestamp value(s) (Operator field is blank).

Column name

Prefilled with the value you specified in Change Command window. To change the column press END to return to the Change Command window.

Operator

Determine how the date and time should be changed.

Blank

The change-string is incremented as specified by the increment values, after the initial replacement when multiple records are replaced with the same CHANGE command.

Month Increment

Increment to modify the month portion of the timestamp. The default increment is blank (no increment). Enter the desired month increment (1 to 99).

Day Increment

Increment to modify the day portion of the timestamp. The default increment is blank (no increment). Enter the desired day increment (1 to 999).

Year Increment

Increment to modify the year portion of the timestamp. The default increment is blank (no increment). Enter the desired year increment (1 to 9999).

Hour Increment

Increment to modify the hour portion of the timestamp. The default increment is blank (no increment). Enter the desired hour increment (1 to 99).

Minutes Increment

Increment to modify the minutes portion of the timestamp. The default increment is blank (no increment). Enter the desired minutes increment (1 to 99).

Seconds Increment

Increment to modify the seconds portion of the timestamp. The default increment is blank (no increment). Enter the desired seconds increment (1 to 99).

Microseconds Increment

Increment to modify the microseconds (precision) portion of the timestamp. The default increment is blank (no increment). Enter the desired precision increment (1 to 999999).

Modifier

Prefilled with the value you specified in Change Command window. You are allowed to change the prefilled Modifier value.

See Modifiers for an explanation of valid values.

Rows/Columns to search

Prefilled with the value you specified in Change Command window. You are allowed to change the prefilled search restriction.

See Rows/Columns to Search: for an explanation of valid values.

Date Arithmetic and Increment Example

The following table is an example that illustrates the difference between date arithmetic and incremental.

Date Arithmetic Example

Date Increment Example

Time Pop-up Window

When the column data type in the CHANGE command window is TIME, the Time Specification window (see the following figure) displays. You can either apply increments to the change-string for rapid data generation or perform date and time arithmetic (add or subtract) on the find-string for data aging purposes. After you provide the operator and increment information, press Enter on this screen to process the change and exit the window. Press END to cancel the CHANGE command and exit the window.

CHANGE Command Window with Time Increment

The Time Specification window is similar to the Timestamp Specification window, however, it only includes the increment fields for hour, minutes, and seconds. See Timestamp Pop-up Window for a description of the fields.

Date Pop-up Window

When the column data type in the CHANGE command window is DATE, the Time Specification window (see the following figure) displays. You can either apply increments to the change-string for rapid data generation or perform date arithmetic (add or subtract) on the find-string for data aging purposes. After you provide the operator and increment information, press Enter on this screen to process the change and exit the window. Press END to cancel the CHANGE command and exit the window.

CHANGE Command window with Date Increment

The Date Specification window is similar to the Timestamp Specification window, however, it only includes the increment fields for month, day, and year. See Timestamp Pop-up Window for a description of the fields.