The Loader offers the following options for processing data using the Load command:
- Loading
- Importing
- Using the SQL import feature
Before loading data, you should determine which method of processing would produce the best results.
Load method
Consider the load method in the following situations:
- If the database is a local DB2 database
- If you know that the data is clean, and if the database does not contain any data
- If you know that the data is clean, and if you know the database does not contain the data that is being loaded
- If you know that the data is clean, if the targeted tables do not contain any primary keys, and if you know that the database does not contain the data that is being loaded
- If the load time is your primary concern
With the load method, data is loaded into the database. If the data already exists, the command fails as a result of a duplicate-key error and a duplicate-error message displays.
Import method
With the import method for DB2, data is also loaded into the database. If the data already exists, it is not deleted but is updated with new values. Consider this method in any of the following situations:
- If the database management system is DB2
- If you do not know whether the data is clean
- If you have to update large sets of homogeneous data at a column level
- If the load time is not your primary concern
- If the table into which data is imported has primary keys
With the import method, data is also loaded into the database. If the data already exists, it is not deleted but is updated with new values. Consider this method in any of the following situations:
- If you do not know whether the data is clean
- If the data already exists in the database
- If the load time is not your primary concern
- If the table into which data is imported has primary keys
SQL import method
With the SQL import method, JDBC or SQL statements are used to update or insert data into the database. Data is inserted if it does not already exist, and existing data is updated. Consider this method in any of the following situations:
- If you are updating existing data and require column-level updates
There is better error reporting on constraint violations and data-type errors with this method.
- If you know that some of the data is not clean
- If database integrity is your primary concern
- If the database is not local
- If you are using Product Advisor search-space synchronization
createonly method
To improve performance during instance creation, use the createonly method. Use the createonly method to create mass-load data (MLD) files without loading the data into the database. You can later use your native database load utility to load the MLD files that you created into a WebSphere Commerce database by running the Load command using the loadonly method.
loadonly method
Use the loadonly method to load MLD files that were created using the createonly method. Use the loadonly method only for instance creation. If you use it at any other time, the result may not be desirable.
Other considerations
Restrictions on using the load method
- With the load method, only new records are inserted to the database; existing records are not updated.
The load method can be used only for local and not for remote databases.
The load method cannot insert or update data in bit data fields or DBCLOB fields.
Restrictions on using the import method
The import method cannot insert or update data in bit data fields.
With the import method, the Loader only inserts or updates tables that have primary keys defined on them; the import method cannot insert or update data in tables that do not have a primary key. If the input record only has values for columns that are primary, the record is rejected.
The import method cannot insert or update data in bit data fields or DBCLOB fields. The import method can be used only on local databases
Comparison of the SQL import and load methods
The SQL import method checks for data consistency, including foreign references, and allows you to update existing data. The load method does not.
Comparison of the import and SQL import methods
The import and SQL import methods perform similar functions. The import method is typically faster, but it requires disk space for temporary files.
The import method can only insert or update tables that have primary keys defined on them; whereas, the SQL import method does not require that tables have primary keys on them.
Further considerations
The delete method is used to delete data that is in the input XML document from the database. The element must contain the values for the primary key or the unique index for the table. If the data being deleted has dependencies to data in another table with "cascade on delete" enabled, the dependent data is also deleted.
If you are using Product Advisor search-space synchronization, you must use the SQL import method for loading data.
The import and load methods use native utilities that are optimized for DB2, while the SQL import method uses JDBC calls (which are generic to many database products).