Virtual fields can be appended to each record as each record is loaded. A virtual field can one of the following
- Constant value
- The schema of the table being loaded (%s%)
- The name of the table being loaded (%t%)
- Path of the file loaded (%p%)
- Name of the file – excluding file path (%f%)
- Sequence number of the record within the file (%N%)
- Byte offset of the record within the file (%O%)
- Byte size of the record within the file (%S%)
- Unique record identity (%I%)
- Date of the load - in the format YYYYMMDD (%D%)
- Time of the load - in the format hhmmss (%T%)
- Unique run identity (%R%)
The tokens in brackets above indicate the symbol used to represent the corresponding value. Virtual fields are appended using the adjoin switch ‘J’. This switch lists the fields to be appended as a string which must contain the field delimiter to separate multiple fields. For example, J”9,%O%,%S%” would append a constant value of 9, the record offset and record size as 3 virtual fields at the end of every record loaded.
Note that the record offset and size relate to the original record content and do not include any virtual fields adjoined to the record.
Also be aware that virtual fields can only be appended to the end of a source record and cannot be interleaved with source fields. If the columns in the table do not correspond with this order then the ‘c’ switch must be used to explicitly define the column order for loading.
If you wish to concatenate the load date and load time to generate a load timestamp then insert a space between then so that the timestamp can be parsed correctly. For example, use “%D% %T%”.
The unique record identity %I% should be loaded into a BIGINT column.