Configuring ETL properties

All the ETL properties in ETL.xml are overwritten by their corresponding application parameters in the database when ETL is run via XMDBTool.

The ETL properties of the application parameters are:

etl.adc_conversion_maps Specify the ADC conversion mapping file name, if any. Default: Not specified
etl.mapping_db_vendor_prefix Specify the conversion mapping file type that the ETL tool must use. The value must be a database vendor type and generally is the same as the one specified in the source database connection.
etl.tbl_creation_db_vendor_prefix Specify the database creation file type that the ETL tool must use. The value must be a database vendor type and it must be the same as the one specified in the destination database connection.
etl.verbosity_level Threshold for which types of messages are displayed in the log. Levels are inclusive; that is, "WARNING" level displays any log message marked as warning or error. Default level is INFORMATION.

verbosityLevel values are:

  • 1 - ERROR
  • 2 - WARNING
  • 3 - INFORMATION
  • 4 - DEBUG
etl.log_directory The directory to which to output this log.
etl.log_max_size Maximum log size. Maximum size of the unit is K byte. Default: 1000
etl.exec_conversion_classes Specify any conversion classes that you want to process. The class alias can be found in the conversion mapping file. Multiple conversion classes can be separated by commas.
etl.conversion_sql Specify whether or not you want to execute any SQL conversion specified in the mapping file. Possible values are:
  • ALL - to execute all SQL conversions.
  • NONE - to execute none of the SQL conversions.
Default: ALL
etl.doc_type.<doc_type> Specify the data types to transform. doc_type can be ER, PR, TP, TS, VAT, PERM, NOTE VAT, PERM, and NOTE. These are not application types but are additional data types that you can specify to transform. Possible values are:
  • true
  • false
Only NOTE is commented out by default. The rest are set to be transformed.
etl.exclude_doc_in_activities Specify to transform documents that are NOT in the activities below, for example, you want to transform only those documents that are created in past. Value is a list of activity numbers separated by comma. Default: Not set
etl.delete_batch_size Specify the batch delete size. The value indicates the number of records to be executed as a batch. Default: 100
etl.delete_line_size_for_file Specify the length of the line to be written to the file when ETL is exporting to a file. If the length exceeds 20000 chars, oracle sqlplus cannot read from the file. Default: 5000
etl.commit_frequency Specify the destination database's commit frequency. This value controls how often data is committed to the destination database; it tells the database to do a commit after the specified number of records are inserted in a table. Default: 4000
etl.DISPLAY_INSERT_SQL Debug flag. Specify whether the inserted SQL statements being processed by the ETL tool can be viewed. Default: OFF
etl.DISPLAY_TBL_DEPLOYMENT_SQL Debug flag. Specify whether the DDL (Data Definition Language) syntax that is read and processed by the ETL tool when the destination database is being deployed can be viewed. Default: OFF
etl.TIME_ADJUSTMENT Specify 'local' or the time zone id (see ETL_User_Guide for the complete list of time zone id). For example,
  • If the time is adjusted to the Local timezone: <TIME_ADJUSTMENT>local</TIME_ADJUSTMENT>
  • If the time is adjusted to Pacific Time: <TIME_ADJUSTMENT>America/Los_Angeles</TIME_ADJUSTMENT>
  • If the time is adjusted to China Standard Time: <TIME_ADJUSTMENT>Asia/Shanghai</TIME_ADJUSTMENT>
- Or -

Specify the number of hours that the local time is ahead or behind (negative number) the GMT time.

Note: Daylight Savings Time is not applied automatically when the number of hours are specified.

For example, Default: 0

  • if the time is adjusted to Pacific Time: <TIME_ADJUSTMENT>-8</TIME_ADJUSTMENT>
  • if the time is adjusted to China Standard Time: <TIME_ADJUSTMENT>8</TIME_ADJUSTMENT>
etl.adjustTimeToEndDate Specify the number of minutes to subtract from the ETL end date parameter(endDate) used in the transformation. Default: 15
etl.TRANSACTION_READ_UNCOMMITTED Specify whether the Transaction Isolation Level can be set to TRANSACTION_READ_UNCOMMITTED. By setting this parameter to 'ON', isolation level is allowed:
  • Dirty Reads - Happens when a transaction reads data that is being modified by another transaction that is not yet committed.
  • Non-Repeatable Reads - Happens when a query returns data that is different if the query were repeated within the same transaction. Non-repeatable reads can occur when other transactions are modifying the data that a transaction is reading.
  • Phantom Reads - Records that appear in a set being read by another transaction. Phantom reads occur when other transaction insert rows that would satisfy the WHERE clause of another transaction's statement.
This means tables in the transactional database are locked during an ETL run and do not block the Expense Management application from accessing the same table concurrently. The possible values are:
  • ON - Set isolation level to TRANSACTION_READ_UNCOMMITTED
  • OFF(default) - No isolation level is set by ETL
Default: OFF
etl.TEXT_LENGTH_LIMIT Specify the maximum length that the note entry is sent to reporting database. The rest of the text is truncated.

Default: 500

Maximum: 4000

etl.ETL_RUN_TRACKER Track ETL command in ETL_RUN table and INSERT/DELETE table activities in ETL_RUN_RESULT table. Default: OFF
etl.FULL_LOAD_TABLE Full load the specified table in full or incremental run. The possible values are:
  • WORK_ITEM - full load WORK_ITEM table
  • an empty string - no table to be full load
migrate.rptdb.sqlBatchInsertSize Specify the batch size that has to be updated/inserted. Default: 5000
migrate.rptdb.outFile Specify the path of the output file for db-to-file (on-premise customers only). Default: CATALINA_HOME\tool\etl\migrateRptDb_yyyyMMdd.sql

The reporting database information is defined as application parameters when ETL is run via XMDBTool. These parameters can be defined in alss_parameters in the central database if the central database is enabled. Otherwise, they are defined as application parameters in these transactional databases:

  • etl.reportingDBName
  • etl.reportingDBServer
  • etl.reportingDBType
  • etl.reportingPassword
  • etl.reportingUser