r1 - 2013-11-22 - 17:55:27 - StephanieBagotYou are here: TWiki >  Deployment Web > DeploymentTroubleshooting > PerformanceTroubleshooting > WhyDoMyETLsTakeSoLongToRun > ETLAssessmentAndAnalysis

new.png Why do my ETLs take so long to run?

Authors: GeraldMitchell, StephanieBagot
Build basis: CLM 4.x
This situation is to help assess and analyze data to assist in the investigation and debugging of long running Java and Insight ETLs (Extract, Transform and Load) with IBM Rational Collaborative Lifecycle Management (CLM) applications.

Initial assessment

After you have completed the Initial troubleshooting, the first item to identify the cause for long running ETLs is to monitor the ETL progress and determine which of the ETLs are taking too long to complete. Keep in mind that 'too long' is relative to the user's perception. This could be affected by your system resources or other environmental impacts, which might cause longer running ETLs. Your ETLs might simply be running 'too long' due to limitations in your environment or configuration.

Some questions to help analyze this information are as follows:

Symptoms

  • How did you discover that the ETL is running too long?
  • Which applications are the ETL involved with?
  • Is the ETL eventually completing?
  • If the ETL completes, is the data there and as expected? This can be tested by running any report and validating that the data is as expected. The report parameters should also show all the associated team areas, timelines, etc.

Impact / scope

  • Does it effect multiple ETLs?
  • Are the ETL problems across all applications?

Timing (when?)

  • When did the long running ETLs start to occur, or did it always take long to run?
  • Is the long running ETL continuous or repeatable or intermittent?
  • If the ETL is run manually, does it complete in sufficient time?

Environmental changes

  • Have the data collection jobs been changed?
  • Have any projects been archived, deleted or renamed?
  • Have any applications or reporting solutions been added or removed?
  • Has the data warehouse changed? (Such as location, JDBC connection information)
  • Have the ETLs been changed or customized?

Recommended analysis steps

All ETLs (Out-of-the-box (OOTB) ETLs)

This topic is described further for OOTB ETLs in Why do my out of the box ETLs take so long to load?

Custom ETLs

This topic is described further for custom ETLs in Why do my custom ETLs take so long to load?

Reporting specific logs

  • Review the ETL logs and look for the information on the data collection jobs. Check the application log files (qm-etl.log, jts-etl.log, ccm-etl.log, jazz-etl.log) in the following log directory:
    <server install directory>/logs/*-etl.log 
    See How do I read the ETL log files for more information.
  • Data manager logs in the reporting tool
    • The default location of the data manager logs are at /cognos/datamanager/log
    • Check the log named Build_job_type.log, to see whether there are failures or how many records have been delivered.
  • Data log load: /cognos/datamanager/data
  • Review the XML data configuration (.xdc). Make sure that the correct/current version catalog is used.
  • The mustgather information for Rational Insight is available from Technote 1409882 - Data Collection: Rational Custom Report MustGather List. This information may also be valuable if you are trying to analyze the ETLs and are using Insight.
    • Rational Insight ETL logs for mustgather:
      • The default location of the Rational Insight JDBC logs and Rational Insight Data Manager logs are at
        C:/Documents and Settings/<user>/logs
      • You can change the default location by modifying the register of the jdbclogpath parameter in the registry:
        HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI/IBM Rational insight XML ODBC Driver 
      • Rational Insight Data Manager logs are located at
        <rational_insight_installation_directory>/cognos/datamanager/log 
      • Rational Insight Data Manager logs for rejected data are located in
        <rational_insight_installation_directory>/cognos/datamanager/data 

Possible causes and solutions

After reviewing the above information on assessment and analysis, you might find an error that correlates to the long running ETL. Navigate to one of the following pages:

Related topics:

External links:

Additional contributors: None

Questions and comments:

Edit | Attach | Printable | Raw View | Backlinks: Web, All Webs | History: r1 | More topic actions
 
This site is powered by the TWiki collaboration platformCopyright © by the contributing authors. All material on this collaboration platform is the property of the contributing authors.
Contributions are governed by our Terms of Use. Please read the following disclaimer.
Ideas, requests, problems regarding the Deployment wiki? Create a new task in the RTC Deployment wiki project