ETL through XMDBTOOL - run_etl.bat/sh

Functionality

You can generate ASCII files having SQL queries through XMDBTool, using the ETL tool. Run_etl.bat is supplied with the data range. Based on this date range, it generates the text file which is saved to the user specified folder.

Server Settings

Customers have their own unique configuration settings for running the ETL tool in the server directory <server-root>/ETL. In this folder, there is a subfolder for each customer specified by the tenant ID. In the customer specific folder, a conf folder includes the ETL.xml file, which is specific to that particular customer, along with other required config folders. This conf folder is similar to the conf folder in the ETL_Tool folder in the standalone ETL tool.

Note:  If customers want to use the ETL tool, they must have a tenant ID folder in the <server-root>/ETL folder with the config files. For example, if a tenant has the tenant ID of t1, then the <server-root>/ETL is:
  • <server-root>/ETL/t1/conf/ETL.xml
  • <server-root>/ETL/t1/conf/conversionScripts
  • <server-root>/ETL/t1/conf/createScripts

All these folders are automatically created.

Configuration Parameters

  • startDate: This is a mandatory parameter which specifies the date for the ETL to start to retrieve data. Format must be MM-DD-YYYYY HH:MM:SS. When dates are specified in the command line, add a % between the data and the time part. For example, MM-DD-YYYYY%HH:MM:SS.
  • endDate: This is a mandatory parameter and is the date for the ETL to end retrieving data. Format must be MM-DD-YYYYY HH:MM:SS. When dates are specified in the command line, add a % between the data and the time part. For example, MM-DD-YYYYY%HH:MM:SS.
  • toFile: This is a mandatory parameter and is the user-specified file name for the ETL generated ASCII file. The user also needs to specify the absolute path of the directory.
  • sql_end_delimiter: This is an optional parameter and is used to specify the delimiter that is used to signal the end of a SQL statement, e.g. \"GO\". By default this value is set to “GO”.
  • db_type: This is an optional parameter. Specify the database type that the output SQL data file must be formatted to. Valid values are: mssql, oracle, and db2. By default, the value is mssql.
  • deploy_table - To run full ETL this parameter is to be set to Y. If not running full ETL this parameter is not required
  • mapping_classes - To run full ETL, this parameter must be set to USER_HIERARCHY. If not running full ETL is parameter is not required.
  • locale - Specify the locale used for key to string translation of specified columns during transformation. Else, the locale is defaulted to English-US (en_US). When using this parameter, the entire locale string must be specified with the language, country, and variant (optional) separated by underscores. Language is always lower case, and country is always upper case. Example: "de_DE", "en_US_WIN"
  • 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.