Configuring timestamp and date data handling

When massloading data that contains timestamp and date data, you have two options for handing the timestamp and date data: Specify the timestamp and date data as fixed information in your input file. You can also specify the timestamp and date data relative to when you load the input file into the database.

Feature Pack 6 or later The massload utility is deprecated for WebSphere Commerce Version 7 Feature Pack 6. The Data Load utility is the recommended command-line loading utility. If you are currently using the mass load utility, you are recommended to switch to the Data Load utility to load your CSV and XML input files into your target database. If your system contains scheduled and automated processes that use massload, it is recommended that you update these processes to use the Data Load utility. Other WebSphere Commerce utilities that use the massload utility, such as the acpload utility, continue to use the massload utility in WebSphere Commerce Version 7 Feature Pack 6. If you have business reasons to continue using the massload utility, you can continue to use this utility. For more information about the Data Load utility, see Overview of the Data Load utility. You can switch to the Data Load utility by using the TableObjectMediator to load your data when no business object mediator exists for the data that you are loading. For more about the TableObjectMediator formation, see Data Load utility table-based mediator and builder.

About this task

For either option, the data formats for timestamp and date data in the input file are determined by patterns (masks) that can be customized. You can edit an existing pattern or add more patterns to the existing list of patterns. The first pattern that matches the data is used to convert the data to the target database timestamp format before you load the data it into the database.

When you specify the timestamp and date data relative to the load date and time, the massload utility inserts values into columns with a timestamp data type based the reading of the time-of-day clock when the massload utility processes the input file. For example, the STARTDATE and ENDDATE of an offer in WebSphere Commerce can have values that are based on the time at which the offer is inserted into the table. To support this functionality, the massload utility uses the MLTIME table to keep the timestamp instances.

To configure how the massload utility handles timestamp and date data:

Procedure

Open the following file in the text editor:
- WC_installdir/xml/loader/MassLoadCustomizer.properties
- workspace_dir/wc/xml/loader/MassLoadCustomizer.properties
Perform one of the following steps:
- Set the timestamp masks to handle timestamp and date data as fixed information
  1. Customize the timestamp formats by modifying or adding masks.
    The following input-timestamp masks are provided:
```
  InputTimeStampFormat.1 = yyyy-DD hh:mm:ss.SSSSSS
  InputTimeStampFormat.2 = yyyy-MM-dd hh:mm:ss.SSSSSS
  InputTimeStampFormat.3 = yyyy-DD-hh.mm.ss.SSSSSS
  InputTimeStampFormat.4 = yyyy-MM-dd-HH.mm.ss.SSSSSS
  InputTimeStampFormat.5 = yyyy-MM-dd-hh.mm.ss.SSSSSS
  InputTimeStampFormat.6 = yyyy-MM-dd HH:mm:ss.SSSSSS
  InputTimeStampFormat.7 = yyyy-DD HH:mm:ss.SSSSSS
```
    The default patterns for input-date formats are as follows:
```
  InputDateFormat.1 = MM-dd-yyyy
  InputDateFormat.2 = yyyy-dd-MM
  InputDateFormat.3 = yyyy-MM-dd 
  InputDateFormat.4 = MM/dd/yyyy
  InputDateFormat.5 = yyyy/dd/MM
  InputDateFormat.6 = yyyy-DD
```
    In general, output date and timestamp formats are not customized.
  2. Change or add timestamp and date masks. There are two customizable output-timestamp patterns.
    
    TimeStampFormat.JDBC
    
    Used when the massload utility uses JDBC connections to perform operations.
    The SQL import and delete methods of the massload utility use JDBC connections for updating the database.
    
    TimeStampFormat.Load
    
    Used when the massload utility uses the native utilities.
    The import and load methods of the massload utility use native utilities.
    
    Specify masks in the sequence in which you want them to be compared with the input timestamp. If you add an input timestamp, you must use the next number in the current sequence.
    
    The patterns for formatting input data to the output for timestamp and date are as follows:
```
  TimeStampFormat.JDBC = yyyy-MM-dd hh:mm:ss.SSSSSS
  TimeStampFormat.Load = yyyy-MM-dd-hh.mm.ss.SSSSSS
```
```
  DateFormat.JDBC = yyyy-MM-dd
  DateFormat.Load = yyyy-MM-dd
```
- Set the timestamp masks to handle timestamp and date data as relative information
  1. Modify the values of the properties that are specified in the new massload utility customizer property file:
    WC_Installdir/xml/loader/MassLoadCustomizer.properties.
    
    The name of the table and its columns can be customized by changing the following properties in the massload utility customizer property file:
```
  TimestampTableName = MLTIME
  TimestampIdColumn = INSTANCEID
  TimestampValueColumn = MLTIMESTAMP
```
    The input data for specifying current-timestamp values are based on timestamp string patterns. The following masks are used for specifying the durations for the timestamp:
```
  %D for days
  %M for months
  %Y for years
  %H for hours
  %m for minutes
  %s for seconds
```
    You can customize current-timestamp formats by modifying or adding masks in the massload utility customizer property file. The following input masks are provided:
```
  InputCurrentTimestampFormat.1 = CURRENT TIMESTAMP
  InputCurrentTimestampFormat.2 = CURRENT TIMESTAMP %D DAYS
  InputCurrentTimestampFormat.3 = CURRENT TIMESTAMP %D DAYS %M
MONTHS
  InputCurrentTimestampFormat.4 = CURRENT TIMESTAMP %D DAYS %M
MONTHS %Y YEARS
  InputCurrentTimestampFormat.5 = CURRENT TIMESTAMP %Y YEARS %M
MONTHS %D DAYS
  InputCurrentTimestampFormat.6 = SYSDATE
  InputCurrentTimestampFormat.7 = ADDDAYS(SYSDATE,%D)
  InputCurrentTimestampFormat.8 = ADDDAYS(ADDMONTHS(SYSDATE,%M),%D)

  InputCurrentTimestampFormat.9 =
ADDDAYS(ADDMONTHS(ADDYEARS(SYSDATE,%Y),%M),%D)
```
    Input data for the current timestamp is matched with the specified patterns. If the data matches a specified input pattern, that pattern is used to parse the input data and the massload utility converts the data into the appropriate output format before you insert the data into the database. New patterns can be added to the preceding list provided the subscript numbers are ordered sequentially.
  2. Set the target output formats for specifying current timestamps:
    1. CurrentTimestampFormat.Load is used when the massload utility is operating in load or import mode.
    2. CurrentTimestampFormat.JDBC is used when the massload utility uses JDBC to insert, update, or delete values in the database.
    The default target patterns in the massload utility are as follows:
```
  CurrentTimestampFormat.Load = CURRENT TIMESTAMP %Y YEARS %M
MONTHS %D DAYS 
    %h HOURS %m MINUTES %s SECONDS 
  CurrentTimestampFormat.JDBC = CURRENT TIMESTAMP %Y YEARS %M
MONTHS %D DAYS
    %h HOURS %m MINUTES %s SECONDS
```
    When you customize the CurrentTimestampFormat.Load and CurrentTimestampFormat.JDBC properties, you should make sure that the syntax of the resulting statement is valid for the database management system.
    
    The CurrentTimestampLiteral property is used by the massload utility to make an early determination of whether the value for the timestamp column is in a current-timestamp format, thus avoiding expensive computations to determine that the value is not a string representation of timestamp.
```
  CurrentTimestampLiteral = CURRENT TIMESTAMP
```
    The default value for this property for DB2 is CURRENT TIMESTAMP.
    
    The default value for the Oracle database is SYSDATE.
Save your changes.