ETL Parameters

This section lists the ETL Parameters.

This table shows all the Infor ETL Parameters that can be configured and implemented using Infor Expense Management System Administration Tool.

Parameter Meaning and Values
etl.ADCTablesKeyTranslationCols (Parm_ID=3146830) A comma (,) separated string of column numbers that is used by ETL ADC generator in SAT to specify which columns in the generated ADC tables that need to be translated from resource keys to strings. You have these options: 2,3,4,5,6,7,8,9,10,11
etl.ADCGenDefaultColumn.alco_address A db column of alco_address to be used when adc generator cannot find the db column referencing to the bob element.e.g. descriptiveNameForColumn.
etl.ADCGenDefaultColumn.alco_location A db column of alco_location to be used when adc generator cannot find the db column referencing to the bob element.e.g. locationName.
etl.guestADCKeyTranslationCols (Parm_ID=3146831) A comma (,) separated string of column numbers that is used by ETL ADC generator in SAT to specify which columns in the generated LINE_ITEM_GUEST_ADC table that need to be translated from resource keys to strings. You have these options: 2,3,4,5,6,7
etl.reportingUser (Parm_ID=3152403) The ETL Reporting DB Username Default value is null
etl.reportingPassword (Parm_ID=3152404)
etl.reportingDBName (Parm_ID=3152405) The ETL Reporting DB Password Default value is null The ETL Reporting DB Name Default value is null
The ETL Reporting DB Password etl.reportingDBType (Parm_ID=3152406) The ETL Reporting DB Type Default value is null
etl.reportingDBServer (Parm_ID=3152407) The ETL Reporting DB Server IP and protocol. For example 10.93.114.188:1433 Default value is null
etl.EXCLUDE_ADMIN_LOG_USERS(parm_id=3159273) Use this parameter to specify a comma separated list of user ids that are excluded from ADMIN_LOG table. To disable ADMIN_LOG transport, set this value to ALL.

The default value is 3060427(HRBkg)

Is customer editable.

etl.tableModifiedList(parm_id=3159280) Null

The default values are alco_user,alco_project_number,alco_cost_center,alco_proxy

Is not customer editable.

etl.IGNORE_SESSION_LOCK_IF_OLDER_THAN(parm_id=3159089) Null

The default value is 8

Is not customer editable.

etl.TABLE_HINT(parm_id=3158277) Null

Is not customer editable.

Note: Table attributes such as, key_translation_cols, key_decrypt_cols, and timezone_adjustment_cols are defined in the header of each table in the ETL.xml file. You can use application parameters, in this pattern, to define the header values of the tables in the ETL.xml file.

etl.<TABLE_NAME>.<attribute_name>.

Value is a comma separated list of column number. It takes precedence over the attributes defined in ETL.xml.

For SaaS customers

Note: If you are a SaaS customer, on AWS , and you do not have access to ETL.xml , you can add these parameters instead of changing the properties file.
Parameter Meaning and values
etl.adc_conversion_maps

Use this parameter to specify the ADC conversion mapping file name, if any.

The default value is Not specified.

etl.mapping_db_vendor_prefix Use this parameter to 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 specified in the source database connection.
etl.tbl_creation_db_vendor_prefix Use this parameter to 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

Use this parameter to specify the threshold for messages to display in the log. Levels are inclusive; that is, WARNING level displays log message marked as warning, or error. The default level is INFORMATION.

verbosityLevel values can be:

  • 1 - ERROR
  • 2 - WARNING
  • 3 - INFORMATION
  • 4 - DEBUG

The default value is 3.

etl.log_directory Use this parameter to specify the directory to output this log.
etl.log_max_size

Use this parameter to define the log maximum size. The max size units is K bytes.

The default value is 1000.

etl.exec_conversion_classes Use this parameter to 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

Use this parameter to specify whether or not you want to execute any SQL conversion specified in the mapping file. The possible values can be:

  • ALL - to execute all SQL conversions.
  • NONE - to execute none of the SQL conversions.

The default values is ALL.

etl.doc_type.<doc_type>

Use this parameter to specify the data types to transform. The <doc_type> can be ER, PR, TP, TS, VAT, PERM, NOTE.

VAT, PERM and NOTE are not application types but are additional data types that you can specify to transform.

The possible values can be:

  • true
  • false

Only NOTE is commented out by default. The rest are set to be transformed.

etl.exclude_doc_in_activities

Use this parameter to specify the transform documents that are NOT in the activities below. For example, you may want to transform only those documents that have made it past create. Value is a list of comma separated activity numbers.

The default value is Not set.

etl.delete_batch_size

Use this parameter to specify the batch delete size. The value indicates the number of records to be executed as a batch.

The default value is 100.

etl.delete_line_size_for_file

Use this parameter to 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.

The default value is 5000.

etl.commit_frequency

Use this parameter to specify the destination database's commit frequency. This value controls how often data is committed to the destination database. This value also instructs the database to do a commit after the specified number of records have been inserted in a table.

The default value is 4000.

etl.DISPLAY_INSERT_SQL

Use this parameter to debug flag. Specify whether or not you want to see the insert SQL statements that the ETL tool is processing.

The default value is OFF.

etl.DISPLAY_TBL_DEPLOYMENT_SQL

Use this parameter to debug flag. Specify whether or not you want to see the DDL (data definition language) that are read in and processed by the ETL tool when the destination database is being deployed.

The default value is OFF.

etl.TIME_ADJUSTMENT

Use this parameter to specify the "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 time is adjusted to Pacific Time : <TIME_ADJUSTMENT>America/Los_Angeles</TIME_ADJUSTMENT>
  • if time is adjusted to China Standard Time : <TIME_ADJUSTMENT>Asia/Shanghai</TIME_ADJUSTMENT>

- Or -

Use this parameter to specify the number of hours that the local time is ahead or behind (negative number) the GMT time.

Note: No DST will be done automatically if number of hours is specified.

For example,

  • if time is adjusted to Pacific Time : <TIME_ADJUSTMENT>-8</TIME_ADJUSTMENT>
  • if time is adjusted to China Standard Time : <TIME_ADJUSTMENT>8</TIME_ADJUSTMENT>

The default value is 0.

etl.adjustTimeToEndDate

Use this parameter to specify the number of minutes to subtract from the ETL end date (endDate parameter) used in the transformation.

The default value is 15.

etl.TRANSACTION_READ_UNCOMMITTED

Use this parameter to specify whether or not you want to set the Transaction Isolation Level to TRANSACTION_READ_UNCOMMITTED.

By setting this parameter to 'ON', isolation level will allow:

  • Dirty Reads - This happens when a transaction reads data that is being modified by another transaction that has not yet been committed.
  • Non-Repeatable Reads - This 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 data that a transaction is reading.
  • Phantom Reads - Records that appear in a set being read by another transaction. Phantom reads can occur when other transaction insert rows that would satisfy the WHERE clause of another transaction's statement.

when you use this parameter, tables in the transactional database are not locked during an ETL run and does not block the Infor Expense Management application from accessing the same table concurrently.

The possible values:

  • ON - Set isolation level to TRANSACTION_READ_UNCOMMITTED
  • OFF(default) - No isolation level is set by ETL

The default value is OFF.

etl.TEXT_LENGTH_LIMIT

Use this parameter to specify the maximum length of the note entry send to reporting database. The rest of the text will be truncated.

The default value is 500.

The maximum value is 4000.

etl.ETL_RUN_TRACKER

Use this parameter to track ETL command in the ETL_RUN table and INSERT/DELETE table activities in ETL_RUN_RESULT table.

The default value is OFF.

etl.FULL_LOAD_TABLE

Use this parameter to load the specified table in full or incremental run.

The possible values:

  • WORK_ITEM - full load WORK_ITEM table
  • an empty string - no table to be full load
etl.adcAllowOverMax Set the value of this application parameter to True, if the ETL ADC script generator must handle mapping of ADC column beyond the default maximum numbers.
The default maximum number of each data type are:
  • MAX_STRINGS = 10
  • MAX_DATES = 5
  • MAX_INTS = 3
  • MAX_CURRENCIES = 3
Note: For additional ADC fields, columns with the same data type must be added to the ADC table in the reporting database.