massload utility (Server environment)

This utility loads an XML input file into a target database. Loading the XML file updates the WebSphere Commerce database. The massload utility allows column-level updates to a table. You can also use this utility to delete data from a database.

For more information about the Data Load utility, see Overview of the Data Load utility

WebSphere Commerce Developer For information about the massload utility in the WebSphere Commerce development environment, see, massload utility (Development environment).

Note: The massload utility loads only one file at a time.

The massload utility requires valid and well-formed XML as input to the database. In this input, element names correspond to table names and attributes of the element correspond to column names in the table.

The massload utility includes the following features:

Error reporter
The massload utility includes an error reporter that generates an exception document if there is an error.

Before you run this utility, ensure that you complete the required configuration tasks:

  1. Configure the loading utilities.
  2. Configure tracing and logging for the loading utilities.
  3. Configure the massload utility.
  4. Optional: Configure the MassLoadCustomizer.properties file.

In addition to the trace log and message log for the loading utilities, this utility produces the following log file:

  • DB2 WC_installdir/logs/massload.db2.log
  • WC_installdir/logs/massload.oracle.log

AIXLinux Run this utility as the non-root WebSphere Commerce user ID. Do not run this command as root.

massload utility syntax diagram. See the list that is called Parameter values for the applicable parameters.

Utility command

The massload utility has the following file name:

  • For IBM i OS operating systemAIXLinux massload.sh
  • Windows massload.cmd

Parameter values

-dbname
  • DB2 Name of the target database.
  • For IBM i OS operating systemDepending on the JDBC driver you use to connect to the database, specify one of the following values:
    Native JDBC driver
    The database name as displayed in the relational database directory (WRKRDBDIRE).
    IBM Toolbox for Java JDBC driver
    The fully qualified host name of the database system. If you are using the IBM Toolkit for Java JDBC driver, you must specify the -schemaname parameter.
    Note: If your database is on a remote IASP and the database name is different from the hostname, the value of dbname that is passed to a utility is the following value:
    -dbname "hostname/schemaName;database name=db_Name;cursor hold=false"
    
    For example,
    -dbname "TORASCAT.yourcompany.com/demo;database name=CATDB;cursor hold=false"
  • Oracle Required: The Oracle TNS name for the database.
DB2
Note: For DB2 UDB databases, the DB2 Type 4 JDBC driver is used, where the Type 4 database name is prefixed with the database server and port. For example, db_server:db_port/db_name.

The Type 2 database name is deprecated, where the database names do not contain a prefix.

-dbuser
  • DB2 Name of the user that is connecting to the database. Ensure that the user has database-level load authority and table-level INSERT privileges for native load methods.
  • For IBM i OS operating system The ID of the instance user.
  • Oracle Oracle user ID connecting to the database.
-dbpwd
Password for the user that is connecting to the database.
-method
Mode of operation for the massload utility to use when the database is modified with input data. The default method is import.

Specify one of the following methods:

import
The import method uses the native database import or update functions, if they are available from the database vendor. If the import or update functions are not available, the import method uses SQL statements by using JDBC to update the database.

If the data that is being loaded exists in the database, the data is updated with new values from the XML file.

Restriction:
  • This method does not support column-level updates.
  • This method can insert or update only the tables that have primary keys that are defined on them. Tables with keys that consist entirely of foreign keys cannot be loaded by using this method.
  • The import method cannot insert or update data in bit data fields.
  • DB2 The import method only inserts or updates tables that have primary keys that are defined on them. The import method cannot insert or update data in tables that do not have a primary key. If the input record has values only for primary columns, the record is rejected.
  • For IBM i OS operating system You cannot use the import method for remote databases.
  • For IBM i OS operating system The import method does not support bit data or DBCLOB fields.
  • Oracle This method is not available for Oracle database.
load
The load method uses the native database loading functions from the database vendor (DB2 Load or SQLLoad).
Note: The DB2 load command requires the user to have database-level load authority and table-level INSERT privileges.

The load method expects your data and the target database to have the following properties:

  • The data is clean. The data contains no conflicts or foreign reference problems.
  • The target database tables do not contain any of the data that is being loaded. If the data exists in the database, the massload utility fails with a duplicate key error.
Restriction:
  • This method does not support column-level updates.
  • When you use this method, the massload utility does not check data integrity, including foreign references.
  • You cannot update existing data by using the load method.
  • DB2 You cannot use the load method for remote databases.
  • For IBM i OS operating system The load method does not support bit data or DBCLOB fields.

DB2AIXLinuxThe massload utility sets the permissions of the WCS/temp directory to 755. Revert the permissions back to 750 after the load finishes, as these permissions are the default permissions of the WCS/temp directory.

sqlimport

The sqlimport method uses JDBC calls to insert and update data for local and remote databases. This method allows column-level updates for you to use to update existing data.

The sqlimport method also ensures that records meet the database schema constraints that make the sqlimport method safer than the import or load methods.

delete
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 that is being deleted has dependencies to data in another table with "cascade on delete" enabled, the dependent data is also deleted.
createonly
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.

AIXLinuxWindowsThe files that are created when you use this method (.mld and .cmd files) are placed in a directory named "MassLoadOutputFiles". This directory is created as a subdirectory under the WCS/temp directory.

You can load the MLD files that you created into a WebSphere Commerce database by running the massload utility by using the loadonly method.

For IBM i OS operating systemWhen you create an instance, the MassLoadOutputFiles directory is created in the temp directory, under the instance root directory. The default location of the directory is WC_userdir/instances/instance_name/temp/MassLoadOutputFiles.

loadonly
Use the loadonly method to load MLD files that were created by using the createonly method. When you use the loadonly method, you must also use the -directory parameter.
Note: The -directory parameter replaces the -infile parameter that you would specify when you were using any method other than loadonly.

For the value of the -directory parameter, you must specify the fully qualified path of the MassLoadOutputFiles directory that was created by using the createonly method.

Here is an example of running the massload utility by using the loadonly method (and the required -directory parameter):
./massload.sh -dbname mall -dbuser db2admin -dbpwd db2admin -method
loadonly -directory 
WC_installdir\temp\MassLoadOutputFiles
-schemaname wcsadmin

Always specify the name of the target database schema by using the -schemaname parameter when you run the massload utility by using this method. Otherwise, the program uses the name of the database schema that is obtained when the MassLoadOutputFiles directory and its files were originally created.

When you use the loadonly method, errors and other messages are saved in files that have a .log extension. These log files are written to the MassLoadOutputFiles directory specified for the -directory parameter.

DB2AIXLinuxThe massload utility sets the permissions of the WCS/temp directory to 755. Revert the permissions back to 750 after the load finishes, as these permissions are the default permissions of the WCS/temp directory.

Use the loadonly method only for instance creation. If you use it at any other time, the result might not be desirable.

-infile
Name of the input XML file.

Do not specify this parameter when you use the loadonly method.

-directory
Full path of the MassLoadOutputFiles directory that was created by using the createonly method.

You can use this parameter with only the loadonly method.

-noprimary
Optional: Action the massload utility must take when the primary key is missing for a record in the input file. The following are valid values for the -noprimary parameter:
error
The error option indicates that it should report the missing primary key as an error and terminate.
insert
The insert option tries to process (insert or delete) the data.

If you do not specify this parameter, the -noprimary parameter is set to error.

-contentcontext
Optional: Tells the utility to use the base schema (production-ready data). This parameter cannot be specified with the -schemaname or the -workspaces parameters.
-schemaname
Name of the target database schema.

This parameter is required if there are multiple schemas in the database into which you are loading data.

If this parameter is not specified when you run the utility, the utility looks for a name=value pair in the customizer property file that specifies the value of SchemaName. If this pair is present in the property file, the utility uses the value that is specified.

For IBM i OS operating system If neither a command-line, nor a property-file specification for the -schemaname parameter exists, the utility defaults to the value of the -dbuser parameter.

-workspcname
This parameter can only be used when loading data into a workspace on an authoring server. This parameter cannot be used when loading data on a staging server or a production server.
Optional: The workgroup code, which is the system generated identifier for the workspace, not the name that is assigned to the workspace by the Workspace Manager. Specify this parameter if you want the massload utility to load a workspace. When you load data into a workspace, the massload utility respects the locking policy set in the workspace. For more information about workspaces locking policies, see Workspaces locking policies. If you specify the workspace parameter, you must specify the following parameters:
-taskgrp
The task group code, which is the system generated identifier for the task groups, not the name that is assigned to the task group by the Workspace Manager.
-task
The task code, which the system generated identifier for the task, not the name that is assigned to the task by the Workspace Manager.
-commitcount
Optional: Number of records that are processed before the database commit occurs when you use the SQL update method of operation.

If you do not specify this parameter, the -commitcount parameter is set to 1. Transactions are committed for every update or insert into the database.

To improve the performance of the massload utility for large input files, the commit count should be increased. After you consider the size of the input XML file, you might use a commit count larger than the number of records in your file. This commit count enables rollback of the entire input XML file if an error occurs.

Do not set the -commitcount value as large as the number of elements in the input file for the following reasons:

  • A high -commitcount value causes high memory consumption.
  • When the -commitcount value is smaller than the number of elements in the input file, some data is written to the database. Depending on the value of -maxerror, a smaller value for -commitcount ensures that some data is written to the database before the maximum number of errors is exceeded and the tool terminates. The default value for -maxerror is 1.
-maxerror
Optional: Number of errors after which the massload utility terminates in the SQL update method of operation.

If you do not specify this parameter, the -maxerror parameter is set to 1.

-customizer
Name of the customizer property file to be used for your WebSphere Commerce database. When you specify the customizer property file with this parameter, omit the ".properties" file extension.

Specify one of the following customizer values:

  • DB2 (Not required) Do not specify this parameter.
  • For IBM i OS operating system Required: Specify one of the following customizer files:
    ISeries_LODWCSDTA_Customizer
    Specify this customizer value if you are using the native system i JDBC driver.

    When you specify this value, the massload utility uses the values that are specified in the following file:

    WC_installdir/properties/ISeries_LODWCSDTA_Customizer.properties

    Toolbox_LODWCSDTA_Customizer
    Specify this customizer value if you are using the IBM Toolkit for Java JDBC driver.

    When you specify this value, the massload utility uses the values that are specified in the following file:

    WC_installdir/properties/Toolbox_LODWCSDTA_Customizer.properties

    If you specify this customizer value, you must specify the hostname as the -dbname parameter. The following is an example of invoking the massload.sh script:

    
    ./massload.sh -dbname MY.HOSTNAME.COM -dbuser instance -dbpwd mypass -method sqlimport
    -customizer Toolbox_LODWCSDTA_Customizer -infile /path/file.xml -schemaname instance 

    This customizer properties file is in the WC_installdir/properties directory.

    z/OS Required: DB2390ConnectionCustomizer

    When you specify this value, the massload utility uses the values that are specified in the following file:

    WC_installdir/properties/DB2390ConnectionCustomizer.properties

  • Oracle (Not required) Do not specify this parameter if you are using Oracle database. By default, the massload utility uses the Oracle thick JDBC driver. If you want to use the thin JDBC driver, see Configuring the massload utility.

If you do not specify this parameter, the -customizer parameter is set to MassLoadCustomizer.properties.

passwordFile
Optional: The full path to the password.properties file that contains the encrypted password for the user who is connecting to the database. For instance, C:\password.properties. By default, the massload utility requires the user password to be included in plain-text directly as a command-line parameter when you run the utility. When you include the password as a command-line parameter it can be possible for other users to view the password. Instead, you can use the passwordFile parameter to identify the password file that includes the encypted user password.
The password.properties file contains the following content:

dbUserPassword=encrypted_pwd
Where encrypted_pwd is the password that is encrypted with the wcs_encrypt utility.