Configure a column exclusion list

When we are preparing to load data with the Data Load utility, we can specify a list of table columns to exclude from the loading process. A column exclusion list provides you the ability to exclude columns from being overwritten if the columns are known to already be populated with data.

About this task

Configure a column exclusion list causes the Data Load utility to avoid loading data into every column of a table. By default, when you run the Data Load utility in replace mode, the utility assumes that the input data you load is always the master copy of the data. Because of this behavior, when you run the Data Load utility, the utility replaces the data for all columns that are defined for a business object. If no data is included in the input file for a column, the utility replaces the existing database column value with the configured default value. If no default value is configured, the utility erases the current database column value, leaving the value to be null.

For example, if we are loading catalog data to update a catalog entry long description, and you do not include a value for the short description within the input file. When the utility runs in replace mode, the long description is updated and the short description in the database is set to null. If you do not want the short description to change, include the current value for the short description in the input file, or configure the column to be excluded. If you do not configure a column exclusion list and run the utility to update data, ensure that the input file includes data for all columns.

We can exclude a database column that is either nullable or not null with a default value. A column can be nullable (contains a null value), or the column can be not null and has a default value. If you exclude a non-nullable column, then inserting new rows would fail.

Consider configuring a column exclusion list to include columns that have a default value that can change the behavior of a business object that can be set by business users. For example, if we are loading attribute dictionary attribute data, we can exclude the column 'displayable'. If you do not include a value for this columns, the utility sets the value for the columns to be null. This changes can affect the storefront display of catalog entries assigned the attribute. With this change attributes that are set to not display for catalog entries are now set to be displayable. (DB2)

• All generated columns, both default and custom, are excluded automatically from the loading process if the columns are already populated with data. You do not need to configure any column exclusion list to include custom columns.

To add columns for exclusion, we must first add the <_config:ColumnExclusionList> and <_config:table> elements after the <_config:DataLoader> element in the business object configuration file. Following these element tags, we can then list the columns for exclusion.

• The <_config:ColumnExclusionList> identifies that the configuration file columns a list of columns that are to be excluded from the loading process. This element contains a list of <_config:table> elements.

• The <_config:table> element identifies the table name in the database containing the columns for exclusion.

When you configure an exclusion list, we can configure the list to apply only when you run the Data Load utility to update records. For instance, we can configure the utility to exclude columns that might include a value set by a business user that is different from the input file value. We can configure this conditional exclusion list with a forUpdateOnly="true" property. When you run the utility to insert records, however, this type of exclusion list is ignored and the utility loads data into all columns. By using this forUpdateOnly property, we can avoid the need to change the columns within exclusion lists when you switch between inserting and updating data.

Procedure

1. Open the wc-loader-object.xml business object configuration file for editing, where object is the object we are loading data about. A sample of this file is in the following directory:

   • (Linux) utilities_root/samples/DataLoad/Catalog

   • (Developer) WCDE_installdir/samples/DataLoad/Catalog

   directory.

2. In the file, locate the <_config:DataLoader> element.

3. Add the <_config:ColumnExclusionList> element after the <_config:DataLoader> element to identify that columns are to be excluded. For example:

   <_config:DataLoader className="com.ibm.commerce.foundation.dataload.BusinessObjectLoader">
   <_config:ColumnExclusionList></_config:ColumnExclusionList>

4. Add the <_config:table name=" " columns=" "/> element after the <_config:ColumnExclusionList> element. Identify the table columns for exclusion. For example:

   <_config:DataLoader className="com.ibm.commerce.foundation.dataload.BusinessObjectLoader" >
     <_config:ColumnExclusionList>
       <_config:table name="CATENTRY" columns="MFNAME,MFPARTNUMBER, FIELD4" />    
       <_config:table name="CATENTDESC" columns="LONGDESCRIPTION" />
     </_config:ColumnExclusionList>

   Note: If we want to set a column exclusion list to apply only when the Data Load utility is updating records in a database table, include the forUpdateOnly parameter with the value set to true:

       <_config:table name="CATENTDESC" columns="PUBLISHED" forUpdateOnly="true" />

   If you configure an exclusion list with this parameter set to true, when you update a table record with an input file that includes changes for excluded columns, the utility does not update the value for the excluded columns with the input file value. If the same load operation includes data to create records in the table, the utility ignores the exclusion list and inserts the input file values into all columns for the new records. We can use a comma-separated list to indicate whether for each column whether the forUpdateOnly property value is "true" or "false". For example, the following exclusion list, sets the property value to "true" for the "BUYABLE" and "FIELD2" columns and "false" for the "FIELD1" column. When the utility runs to update records, the "BUYABLE", "FIELD2", and "FIELD1" columns are excluded. When the utility is inserting data to create records in the table, only the "FIELD1" column is excluded.

       <_config:table name="CATENTRY" columns="BUYABLE, FIELD1, FIELD2" forUpdateOnly="true, false, true" />

   If you include multiple columns in the value for the name attribute, but do not set an equal number of values for the forUpdateOnly property, the final value set for the forUpdateOnly property applies to any remaining columns. For example, in the following configuration the column "FIELD1" is excluded when the utility is updating or inserting records. The columns, "BUYABLE", "FIELD2", "FIELD3", and "FIELD4" are associated with the property value "true" and are excluded only when the utility is updating existing records.

       <_config:table name="CATENTRY" columns="BUYABLE, FIELD1, FIELD2, FIELD3, FIELD4" forUpdateOnly="true, false, true" />

5. Save and close the file. Your column exclusion list is defined and the Data Load utility is configured to exclude these columns when you run the utility in replace mode to load your data.

Previous topic: Configure the data load order
Next topic: Running the Data Load utility command syntax