ETL run issues

This topic describes how to resolve the extract, transform, and load (ETL) errors that you might encounter during ETL runs.

• Resolving general errors
  • To investigate an ETL command error in the TrueSight Server Automation – Data Warehouse console
• Resolving specific errors
  • ETL fails on Microsoft SQL Server because of query time out
  • ETL fails on Oracle because of mismatching database character sets
  • Error while using Oracle JDBC driver version 11.2.0.1 and SSL
• Resolving specific warnings
  • ETL executed with warnings in SQL Server while using JDBC jar version mssql-jdbc-9.4.1.jre11.jar
• Recovering from an ETL hang or failure
• Resolving the invalid input error for ETL_LOG_FILES_RETENTION_PERIOD parameter
  • Issue scope
  • Issue symptom
  • Workaround
• Related topic

Resolving general errors

If an error occurs while running the extract, transform, and load (ETL) process, you can view error details in the TrueSight Server Automation - Data Warehouse console. You can use the log files to help resolve the error yourself or send the log files to BMC Customer Support for assistance. The ETL process uses the following general error indicators:

• The run_etl command exits with the following message:

  ETL is terminating with an error. Please check the log file: <logFileLocation>.
• A scenario step in the TrueSight Server Automation - Data Warehouse console .

If you encounter these error indicators, you can use the following methods to try to work around the problem:

• Try running the run_etl command again. Sometimes, running the command again resolves the issue and further scenario runs continue without error. 
• If running the command again does not resolve the issue, perform the following tasks in the order shown:
  • If the run_etl command exits with an error message, check the log file (BSA_site_siteID_run_etl.log) in the <TSSA-DWInstallationDirectory>/logs/ETL/timeStamp for the exact cause of the failure. 
    If you cannot solve the issue, contact BMC Customer Support and have the log file available.
  • If a scenario step has an error icon next to it, investigate the error in the TrueSight Server Automation - Data Warehouse console.
    
• If you get a scenario exception that says that the scenario is already running when you try to rerun the command, run the stop_etl.nsh script. 

To investigate an ETL command error in the TrueSight Server Automation – Data Warehouse console

1. Launch the TrueSight Server Automation - Data Warehouse console, as described in Accessing-the-TrueSight-Server-Automation-Data-Warehouse-console.
2. Navigate to the scenario step that indicates an error, click the   icon, and read the error message (if there is no error message, skip to step 4).
3. Perform the appropriate action:
   • If the cause of the error is clear to you, resolve the error, and run the run_etl command again.
   • If the cause of the error is not clear to you, copy the error text into a document, locate the log files for that scenario run in the <TSSA-DWInstallationDirectory>/logs/etl/timeStamp directory, and send them with the document to BMC Customer Support for assistance.
4. Navigate to the <TSSA-DWInstallationDirectory>/logs/shared/bdi/timeStamp directory and locate the log file with the most recent time stamp, BSA_site_siteID_run_etl.log & BSA_site_siteID_exception_run_etl.err.

Resolving specific errors

This section provides information about the specific ETL errors and their resolutions.

ETL fails on Microsoft SQL Server because of query time out

On SQL Server, ETL fails with the following error message:

Resolution:

Complete the following steps to resolve the issue:

1. On SQL Server database instance, right-click the instance name, and select Properties.
2. In the left pane, click Connections.
3. In the Remote server connections area, in the Remote query timeout field, enter 0 as the query timeout period.
4. Click OK.

ETL fails on Oracle because of mismatching database character sets

On Oracle, ETL process fails in the following scenarios. Each scenario has its own solution.

• If the TrueSight Server Automation database instance uses the nchar schema and the reports data warehouse instances uses the char schema, ETL fails with the following error message:

  TSSADW does not support if DW database character set is 'char' and OM database character set is 'nchar'. Please contact system administrator.

  Solution:  On the reports data warehouse instance, create the schema with the nchar character set. 

• The TrueSight Server Automation database and the reports data warehouse instances use the same schema (char or nchar). However, one database schema does not use a character set that is a subset or superset of the character set of the other database schema. In this case, ETL fails with the following error message:

  Collation sequence mismatch between DW database <schemaCharacterSet> and OM database <schemaCharacterSet>. Please contact system administrator.

  Solution: Create the TrueSight Server Automation database and the reports data warehouse Oracle instances that use either the same character set or one character set is subset or superset of the other character set.

For more details about the Oracle database requirements, see Prerequisites for the Oracle database setup.

_{Back to top}

Error while using Oracle JDBC driver version 11.2.0.1 and SSL

ETL fails with the following exception when you use the Oracle JDBC driver version 11.2.0.1 and SSL on the reports server:

Workaround:

This exception occurs when Oracle JDBC driver version 11.2.0.1 cannot connect with SSL to a 11g database. To fix the issue, use the JDBC driver version 11.2.0.2. You can obtain it from the following URL: http://www.oracle.com/technetwork/database/enterprise-edition/jdbc-112010-090769.html.

Resolving specific warnings

This section provides information about the specific ETL warnings and their resolutions.

ETL executed with warnings in SQL Server while using JDBC jar version mssql-jdbc-9.4.1.jre11.jar

In SQL Server, when you upgrade the JDBC jar version to mssql-jdbc-9.4.1.jre11.jar, ETL executes with the following warning message:

Resolution:

This warning occurs when SQL Server is not on the latest service pack for the version of SQL Server you are using. For example, to fix the issue in SQL Server 2014, use the latest service pack 3 on SQL server 2014.

_{Back to top}

_{Back to top}

Recovering from an ETL hang or failure

You can use the stop_etl.nsh script to recover from an extract, transform, and load (ETL) hang or failure, which can be indicated by any of the following circumstances:

• You receive one of the following error messages:
  • SCENARIO EXCEPTION - Scenario is already running.
  • Another instance of the script is already running.
• ETL stops responding (you see no activity for a long time).

After running the stop_etl.nsh script, try running the run_etl.nsh script again.

To stop an ETL run

1. Log on to the reports server on which the ETL failure occurred.
2. From a command line, start the Network Shell.
3. Navigate to the shared/bdi/bin directory.

4. Run the stop_etl.nsh script as follows:

   ./stop_etl.nsh -s <siteID> 

   <siteID> is the site ID for which ETL needs to be stopped. This should be a valid site ID from the BL_SITE table.
   
   For example, to stop the ETL for a site with site ID 2, run the following script:
   

   ./stop_etl.nsh -s 2

    

Resolving the invalid input error for ETL_LOG_FILES_RETENTION_PERIOD parameter

Issue scope

When you give a non-zero or a negative value as an input for the ETL_LOG_FILES_RETENTION_PERIOD parameter from the generic_configuration.properties file.

Issue symptom

The following error message is displayed:

Workaround

Make sure that the retention period value must be numeric and non-zero. The system won't accept if the value is set to zero (0)

For more information, see Viewing-the-log-files-for-ETL-runs.

_{Back to top}

Related topic

Running-ETL