Commands

This section lists the commands:

- conf_dir

Specify the absolute path of the conf directory. This is only required when running ETL outside of the bin directory. The ETL_HOME variable within the ETL start up script (etl.sh or etl.bat) must be in absolute path when using this parameter.

- deploy_tables

Deploy tables specified in the create scripts. This executes the data specified in the files including drop, create tables or views and add constraints or indices. This is used mainly for the initial full load to create the reporting database tables. The default is set to no (N).

- deploy_specified_tables

Deploy a specified list of tables. Use a colon to separate the table list. This parameter is used in conjunction with the dst_startDate and dst_endDate parameters. Example: etl.sh -deploy_specified_tables "EXPENSE_REPORT:EXPENSE_REPORT_LINE_ITEM" -dst_startDate "01-15-1970" -dst_endDate "01-15-2002"

- dst_startDate

The start date used to deploy the tables specified in the deploy_specified_tables parameter. This parameter is used only with the deploy_specified_tables parameter. Specify the date in the following format: "MM-dd-yyyy". Example: 01-15-1970. The date will be converted to GMT time as time data stored within the database are in GMT format. Daylight savings time will be used in the conversion. The time within the start date will be set to the beginning of the day, (i.e., MM-dd-yyyy 00:00:00).

- dst_endDate

The end date used to deploy the tables specified in the deploy_specified_tables parameter. This parameter is used only with the deploy_specified_tables parameter. Specify the date in the following format: "MM-dd-yyyy". Example: 01-15-1970. The date is converted to GMT as the time data stored within the database is in GMT format. Daylight savings time is used in the conversion. The time within the end date will be set to the end of the day, (i.e., MM-dd-yyyy 23:59:00).

- startDate

The start date used in the transformation, overriding the date stored in the database. Specify the date in the following format: "MM-dd-yyyy". Example: 01-15-1970. The date is converted to GMT time as time data stored within the database are in GMT format. Daylight savings time is used in the conversion. The time within the start date is set to the beginning of the day (i.e., MM-dd-yyyy 00:00:00).

- endDate

The end date used in the transformation, overriding the date stored in the database. Specify the date in the following format: "MM-dd-yyyy". Example: 01-15-2002. The date is converted to GMT time as time data stored within the database are in GMT format. Daylight savings time is used in the conversion. The time within the end date is set to the end of the day, (i.e., MM-dd-yyyy 23:59:00).

- locale

Specify the locale used for key to string translation of specified columns during transformation. If this is not specified, 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"

- manual_lock_rdb

Manually lock/unlock the reporting database. This sets a flag (to the tracker table) in the reporting database to lock/unlock the reporting database. Use this command with caution as this allows another ETL instance to be executed if you unlock the reporting database while another instance of ETL is writing to the reporting database. Specify Y to lock the database and N to unlock the database. Example: etl.sh -manual_lock_rdb N to unlock the reporting database. This command is not valid when using ETL to write to an ASCII file.

- mapping_classes

The Java classes that are executed. Multiple classes can be specified by using comma separators. The classes will be appended to the list specified in the ETL.xml file.

-etl_sqlTrace

Note: DO NOT USE THIS with the –deploy_tables parameter in the initial creation of the reporting database. This is currently not supported since this is not useful in troubleshooting since the –deploy_tables drop all reporting tables and repopulate the tables without any incremental work such as delete, and so on.

Generate the ASCII delimited SQL file during the database to database transformation for debugging purposes. This file can be useful to troubleshoot various problems (for example, unique key violation during insert, and so on) during a database to database run as this provides the SQL output that are used in the delete and insert conversions. The valid value for this parameter is either “true” or “Y”. This debug parameter must be used in conjunction with the following parameters: sql_end_delimeter and to_file_db_type. The output is stored in the directory where the ETL is executed and has the following file name output convention: etl_sqlTrace_<currentTimeStamp> (e.g. etl_sqlTrace_2008_8_15_14_50.txt). The key parameter that differentiates between a direct transactional database to reporting database or transactional database to an ascii file is the -tofile parameter. If this parameter is specified at the command line, the run is from the transactional database to the ascii file. If this parameter is not specified, the run is a direct transactional database to a reporting database and with the option of ascii file as the output for debugging purposes by specifying the etl_sqlTrace parameter.

Example: (database 2 database run and the enabling of the etl_sqlTrace parameter to output the ascii file)

etl.sh -etl_sqlTrace "true" -sql_end_delimeter "GO" -to_file_db_type "mssql"

- to_file

Write transformed data to a sequential ASCII delimited file instead of the reporting database. The data will contain SQL statements including delete and insert statements.

The following parameters are required when using this command:

to_file, sql_end_delimeter, startDate, to_file_db_type

Example:


etl.sh -to_file "full_path_filename"
-sql_end_delimeter "GO" -startDate "01-15-1970" 
-to_file_db_type "mssql"

sql_end_delimeter

Specify the delimiter used to signal the end of a SQL statement, e.g. \"GO\". This parameter is used only with the to_file parameter. For Windows, use the string \"SEMICOLON\" instead of \";\"");

to_file_db_type

Specify the database type that the output SQL data file should be formatted to. Valid values are: mssql, oracle, and db2. This parameter is to be used only with the to_file parameter.

to_file_begin_tx

Specify the begin transaction command (e.g. begin transaction), if any; otherwise, ignore this parameter. This parameter is used only with the to_file parameter.

Note: Windows uses the hexadecimal representation of characters that are DOS delimiters. For example, use %20 in place of space. Type in the 'begin%20transaction' to specify the 'begin transaction' argument value.

to_file_commit_cmd

Specify the commit command string (e.g. commit), if any; otherwise, ignore this parameter. This parameter is used only with the to_file parameter.

-to_file_output_dir

Optionally specify the output file directory path; otherwise, it would go into the current directory if this parameter is not specified.

- version

Display the ETL version information.

Command argument example:

-startDate "01-15-1970"