Selecting records by record keys (FROMKEY and TOKEY parameters)

 

You can specify record keys to copy only from a keyed database file. Note that you can use this parameter on the CPYF command only. You can copy records:

• From a specified key value (FROMKEY parameter) to a specified key value (TOKEY parameter) OR

• Until a specified number of records (NBRRCDS parameter) is reached

If the command reaches the end of the file before it reaches the specified ending key value or number of records, the copy completes normally.

If no record in the from-file member has a key that is a match with the FROMKEY value, but there is at least one record with a key greater than the specified value, the first record copied is the first record with a key greater than the FROMKEY value. If the specified key value is greater than any record in the member, the command sends an error message and does not copy the member.

You can specify *BLDKEY on the FROMKEY and TOKEY parameters to use a list of character and numeric values in their natural display form for the fields in a key. The command converts each element to the corresponding key field data type. The command then provides the composite key value (a key that is comprised of more than one field) to the database.

If you specify fewer values than the complete database key contains, the command builds a partial key and passes it to the database. If you specify more values than the database key contains, an ending error occurs. The command always applies values to the consecutive fields that are in the extreme left of the key so that it is impossible to skip key fields.

The command pads character fields on the right with blanks. The command adjusts numeric fields to the implied decimal point in the key field with the correct zero padding.

All regular rules for specifying numeric fields in an external character format apply. The command does not allow a floating-point value of *NAN (Not a Number).

It is also important to understand Key string comparisons made by the copy operation in order to interpret various warning messages.

If you use record keys to select records, you cannot use relative record numbers (FROMRCD/TORCD parameters) to select records on the same CPYF command.

You should not specify COMPRESS(*NO) when selecting records by record key from a keyed physical file. Because the keyed access path of a file does not contain deleted records, the copy command never copies them, so the compression is automatic.

Because deleted records are canceled in a copy by this method, it is also possible that the relative record numbers have changed in the new file, even if you have specified MBROPT(*REPLACE).

The user has to input operand equal to the length of the fix field when specifying *BLDKEY on the FROMKEY and TOKEY parameters for fix length binary character keyfields.

• Key string comparisons made by the copy operation
  When the TOKEY value is specified, the check made by the copy operation is a logical character comparison between the key string for each record retrieved. The key strings can be specified explicitly using the first TOKEY parameter format or built implicitly by the copy operation that uses the list of values that are given.

• Example: build-key function
  The example in this topic shows the usages of various parameters of the build-key function.

• Example: using FROMKEY and TOKEY
  In this example, the copy command copies records in the file EMP1 to the file EMP1T. EMP1T is a file in a test library. Because you only need a subset of the records, you specify a from-key value and a to-key value. Both are full key values.

• Variable-length fields used by record keys (FROMKEY and TOKEY)
  When the number of key fields and a value are used to specify the FROMKEY or TOKEY parameter, the string should include the 2-byte length field for each variable-length key field.

• Date, time, and timestamp fields used by record keys (FROMKEY and TOKEY)
  When the number of key fields and a value are used to specify the FROMKEY or TOKEY parameter, no conversion of data occurs if the corresponding key field in the from-file is a date, time, or timestamp field.

• Null-capable fields used by record keys (FROMKEY and TOKEY)
  When you use the number of key fields and a value to specify the FROMKEY or TOKEY parameter, the copy command ignores the null values. The command uses only the buffer default values for values that are actually null for the comparison.

• Different CCSIDs used by record keys (FROMKEY and TOKEY)
  When you use the number of key fields and a value to specify the FROMKEY or TOKEY parameter, the copy command does not make any CCSID conversions to the input string.

• DBCS-graphic fields used by record keys (FROMKEY and TOKEY)
  When the number of key fields and a value are used to specify the FROMKEY or TOKEY parameter, no conversions are done on the input string. The input string is used as is.

 

Parent topic:

Selecting the records to copy

 

Related concepts

Selecting a specified number of records (NBRRCDS parameter)