Running ETL as a background process

ETL can be run using the XMDBToolBkg.ETL background process. The ETL server setup remains the setup required to run ETL from XMDBTool client.

The startDate and endDate parameters must be specified through the background process parameters to the XMDBToolBkg.ETL background process. The file name of the generated ETL file can be specified using the toFile parameter. If the toFile parameter is not specified, the default name of the ETL file is etl___<date_timestamp>.txt. This txt file can be e-mailed, if you specify the e-mail address in the e-mail background process parameter.

The ETL log file can be e-mailed to the customer, if the e-mail parameter is provided. This log file is saved in the exportFileLocation\tenant\etl\log folder.

These are the optional parameters:

  • db_type: This is used to specify the type of the database. This can be mssql, oracle, db2, or sybase. If this is left blank, the default value for db_type is mssql.
  • sql_end_delimiter: This is used to set the sql delimiter. The default value is GO.
  • skip_specified_tables - This parameter enables you to suppress specific ETL table data to Reporting Database or Script file when running an incremental ETL job.
  • exportFileLocation -This parameter creates the etl txt file. The location of the generated etl txt file is <exportFileLocation>/<tenantID>/etl. This folder is mandatory.

The log file for the etl run is saved in the <exportFileLocation>/<tenantID>/log folder. The log file is saved as etl__<datetimestamp>.log.

For ETLs that must be run daily on an incremental basis, you can use the parameter incremental. If this parameter is set to true, incremental = true, then the startDate and endDate are calculated.

  • For the first run with the incremental flag set to true, the database table alex_xmdbtool_etl is selected. The alex_xmdbtool_etl is an xmdbtool-etl specific table, created in the Expense Management database. In most cases, no entry is specified if this is the first run. If no data is retrieved from the database table, then the startDate and endDate parameters are retrieved from the background process parameters and the ETL runs accordingly. After the run is complete, the next startDate is calculated as the current endDate as retrieved from the parameter specified in the database table with the value of isActive flag is 1. The endDate is not specified in the database table.
  • For subsequent runs with the incremental flag set to true, the database table alex_xmdbtool_etl is selected and the row with the isActive flag value of 1 is selected. If the row is not found, then the startDate and endDate are retrieved from the background process parameter. If the row is found, then the startDate is retrieved from the database table and is the value for the startDate. The current Date is the value of the endDate and the ETL run continues. After the run is complete, the new startDate is the value of the current endDate. This start date is specified in the database table with the isActive flag value of 1. Before this value is specified, the isActive value for all the earlier entries in the database table is 0 (zero). This ensures that only the correct start date is retrieved.

The full ETL file and the incremental file is now compressed using the background parameter zip=true.

Note:  The functionality to compress the full and the incremental ETL file is only available for ETL runs that are started using the background process XMDBToolBkg.ETL.

The ETL log file is scanned to find the No space left on device message. If the message is found in the log file, the ETL file is not transferred to the SFTP server. The file is also not downloaded to client side, if the ETL is initiated from the client side.

To avoid the scanning of the ETL file, you can set the value of the checkReqd application parameter to false. By default, the value is set to true.

You can also specify words or sentences in the xmdbtool.etKeyWord application parameter that must be found in the log file. By default, No space left on device message is scanned.