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
• 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
  • process_permissions and update_cognos_model script failures
  • Errors while updating the Cognos model
  • Issues running ETL with non-English locales
• Related topic

Resolving general errors

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 BMC Decision Support for Server Automation 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 (site_siteID_timeStamp_run_etl.log) in the BDSSAInstallationDirectory/logs/ETL/timeStamp for the exact cause of the failure. 
    If you find inadequate error logging in the log file, it is likely that ETL was run with the logging argument -d specified at a low level of detail (less than 3). Specify –d at a level of 3 or higher and run the ETL script again. For more information, see Running-ETL-using-an-NSH-script.
    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 BMC Decision Support for Server Automation Console (see Obtaining-ETL-error-details-in-the-BMC-Decision-Support-for-Server-Automation-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. For more information, see Recovering-from-an-ETL-hang-or-failure.

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 BMC 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:

  BDSSA 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 BMC 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 BMC 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.

process_permissions and update_cognos_model script failures

If the process_permissions.nsh script or the update_cognos_model_properties.nsh script fails, check for the following issues:

• Ensure that the default logon configuration was not changed from SRP.
• Verify that the SRP logon for the product was not disabled from the Application Server Administration console (also called blasadmin utility).

These settings are required for successful ETL runs.

_{Back to top}

Errors while updating the Cognos model

If the ETL Detailed Status page in the BMC Decision Support for Server Automation Console shows that the 4_UPDATE_COGNOS scenario has failed, check the error message. See the error messages listed in the following table for possible actions that you can take to solve the issue.

Overriding the update_cognos script lock

The update_cognos_model_properties.nsh script has a locking mechanism that prevents another process from running and updating the reports model when one of these processes is already running (you can only run one script to update the model at a time). If you attempt to run the script when an instance of the script is already running, one of the following error messages appears:

or

You might run into a situation in which a script is between processes and you see one of these error messages. In this case, complete the following steps:

1. Check whether update_cognos_model_properties.nsh is running by using the following command:

   ps -ef | grep update_cognos_model_properties.nsh
2. If update_cognos_model_properties.nsh is running, wait until the process finishes.
3. If the process takes longer than 30 minutes, kill the process and navigate to the BDSSAInstallationDirectory/odi/shared/bin directory and delete the props.lock file.
4. Run the update_cognos_model_properties.nsh script again.

_{Back to top}

Issues running ETL with non-English locales

If ETL fails to run on installations using Japanese or Chinese languages, use the procedure in this topic to resolve the issue for your database type.

If ETL fails on the 1_MASTER scenario portion of the process, with the following error message, use the procedure in this topic to prevent the issue for your database type.

To resolve the ETL issue on Oracle systems

1. Log on to SQL Plus as BSARA_ETL_WORK (or the name you chose for this user).

2. Run the following script on the reports data warehouse:

   Sqlplus BSARA_ETL_WORK/<password>@<OracleSID>

   In this script, variables are defined as follows:

   • <password> is the password for the BSARA_ETL_WORK user.
   • <OracleSID> is the Oracle system ID.

3. After the script runs successfully, log on to the BSARA_ETL_WORK schema and run the following script:

   start upgrade_work_repository_for_localization_oracle.sql

To resolve the ETL issue on Microsoft SQL Server systems

1. Log on to the BSARA_ETL_WORK database.

2. Run the following script:

   sqlcmd -S <server>\<instance> -d BSARA_ETL_WORK -U <user> -P <password> -i upgrade_work_repository_for_localization_sqlserver.sql -o upgrade_work_repository_for_localization_sqlserver.out

   In this script, variables are defined as follows:

   • <server> is the ETL work repository database server ID.
   • <instance> is the name of the database instance.
   • <user> is the BSARA_ETL_WORK user.
   • <password> is the password for the BSARA_ETL_WORK user.

After completing this procedure, re-run ETL.

_{Back to top}

Related topic

Running-ETL