Mapping file guidelines

You can create a mapping file if you are familiar with the source data and the destination tables. This mapping defines which source columns are inserted into which destination columns. Mapping files can be simple or complex.

Use these guidelines when you create a mapping file. It is a good idea to view and test sample mapping files when you are learning about the mapping file structure and syntax. See Data Import sample files.

Optional data conversions can be specified on a per-column basis. You can use the same mapping to import different data multiple times, but the format of the input data must not change. Importing by using a JDBC connection has the same restrictions. You can import multiple sets of data, if the source data columns do not change names or types.

The mapping file syntax for CSV data sources and JDBC data sources is almost identical. The only difference is that a JDBC mapping must contain the source table and column, while a CSV mapping needs only the source column.

You do not need to provide mappings for the following instances:
  • For any destination columns that have auto-generated values.
  • For any destination columns that can have null values.
  • For any source columns that are missing. (Data Import ignores blank columns. Map only the columns that you require.)
You must provide mappings for the following instances:
  • For any columns that cannot be null.
  • For any columns that are not auto-generated and are not null.
  • For any data conversions that you want to occur. (You can perform data conversions using standard SQL statements, including SQL functions. You can also perform data conversions using custom functions that are incorporated into the ICFM baseline configuration.)
Example:
As an example of a data conversion, consider importing a time stamp value from a CSV file. The CSV data represents the time in the form MM/DD/YYYY. The following mapping file line illustrates how USING ACTION can be used to convert the input data into a DB2 TIMESTAMP value:
ORGANIZATION.ESTABLISHED_DATE = Since USING ACTION "TIMESTAMP_FORMAT( %1,'MM/DD/YYYY')";

Note that other data conversion can be applied using the same paradigm.

Keep the following information about mapping statements in mind when you create your mapping file.

Mapping statements:
  • Can be grouped by using BEGIN and END lines.
  • Can span lines by using the backslash ( \ ) line continuation character. (Note that you must use the backslash ( \ ) character; otherwise, Data Import does not recognize the continuation line.)
  • Can refer to multiple input columns by using positional parameters, such as %1...%9.
  • Can specify a custom SQL string through the USING ACTION statement modifier.
  • Can update or insert through the USING ACTION_MATCH statement modifier.
A JDBC mapping file uses the following mapping format: 
destTable.destColumn = sourceTable.sourceColumn USING ACTION "valid_sql_modifier_for_conversion"
A CSV mapping file uses the following mapping format: 
destTable.destColumn = sourceColumn USING ACTION "valid_sql_modifier_for_conversion"

The mapping file format supports comments that start with the # symbol. It is recommended to comment your mapping files.

Source columns can be referenced multiple times in the same block. However, destination columns can be referenced only once per block. In other words, a single source column can be the source for more than one destination column. In this case, create two mapping lines with different destination columns.