(r3) LongRunningETLNoError < Deployment

Engineering Lifecycle Management Wiki - Deployment

Community information and contribution guidelines

~~EditAttach~~Printable

TWiki > Deployment Web > DeploymentTroubleshooting > PerformanceTroubleshooting > WhyDoMyETLsTakeSoLongToRun > LongRunningETLNoError

Revision 3 - 2013-02-26 - 19:32:17 - Main.geraldmi

<sticky><style type="text/css">
h1, h2, h3, h4, h5, h6 {font-family: arial,sans-serif; color:#595959; background:none; }
h2 {border-bottom-color:#D2D2D2; }
h3, h4, h5, h6 {border:none; }
</style></sticky>

<sticky><div id="container" >
<div id="header-title" style="padding: 10px 15px; border-width:1px; border-style:solid; border-color:#FFD28C; background-color:#FFF0CE; font-size:120%">
---+!! %SPACEOUT{ %TOPIC% }%
%DKGRAY% Authors: Main.GeraldMitchell, Main.StephanieBagot  <br>
Last updated:  %REVINFO{"$day $month $year"}% <br>
Build Basis: Products, Editions and Versions as applicable 
%ENDCOLOR%</div></sticky>

<!-- Page contents top of page on right hand side in box -->
<sticky><div style="float:right; border-width:1px; border-style:solid; border-color:#DFDFDF; background-color:#F6F6F6; margin:0 0 15px 15px; padding: 0 15px 0 15px;">
%TOC{title="Page contents"}%
</div></sticky>

<sticky><div style="margin:15px;"></sticky>
---++ Why do my ETLs take so long to run?
This situation is to help discover the possibilities in correcting ETLs in the situation where the ETLs seem to be taking what is considered a longer time than expected to run. This specific topic will cover instances when __no error occurs__.
<br> If an error has occurred, navigate to the [[https://jazz.net/wiki/bin/view/Deployment/LongRunningETLError][Long Running ETL with Error]] page.
---++ Initial Assessment
After you have completed the [[https://jazz.net/wiki/bin/view/Deployment/InitialTroubleshootingInvestigation][Initial Troubleshooting]], and the [[https://jazz.net/wiki/bin/edit/Deployment/WhyDoMyETLsTakeSoLongToRun][Initial Assessment]] ETL specific questions, you have been unable to identify any errors or other unexpected behaviour which is causing the long running ETL. This page will help to point you to where to investigate next.

---++ Possible Causes and Solutions
Significant improvements have been made to the performance of the ETL processes between 3.x and 4.x. Ensure you are running at least 4.0 to take advantage of these performance increases.

Below are some possible causes and solutions we have experiences with long running ETL jobs:
---+++ 1. There is too much data collected by the Job
Full data collection jobs occur during the initial run of the data collection jobs. These jobs take significantly longer than typical data collection jobs that run 'delta builds' which only collect changes from the last time that job was run. If you are manually initiating Data Collection, ensure that the Full Data Collection Job is not selected.

In addition delivery of data into the data warehouse is slow when projects contain too much data.It is possible to reduce the data delivered by customizing the ETL process to deliver raw data directly to a temporary table and then to separately transform that data into the target table. This requires the creation of a temporary table with the correct privileges, configuring the application server,  customizing the ETL process, and executing the ETL job. See the [[http://www-01.ibm.com/support/docview.wss?uid=swg21455870][Technote 1455870]] for an example of Insight and Requisite Pro, though the general principal applies to all applications and data warehousing.

Note also that the http connection is governed by a timeout default that can be adjusted and sender and receiver such that no timeout may occur but this process would still be relevant for improving performance.

---+++ 2. Too many jobs are running or the jobs are unnecessarily run
The ETL is data dependent and so should be treated as a queue, where only one ETL should be scheduled at a time. Additionally, ETL jobs that are duplicated or run too often can slow performance. Out of the box, the ETLs will subsequently after the other, so running multiple jobs should not be a concern. If you have customized any ETLs to run at the same time, this could cause the long running ETLs. Take a look into your customization to ensure no ETLs are running at the same time. It is best to only run jobs right before they are needed to get the latest information.  It is also best to run with deltas when possible.
<br>*Example:* The ANALYZE_TABLE command is already run by the ETL jobs, so it is redundant to have it run twice. The ANALYZE_TABLE command can be disabled through a new parameter in the Data Warehouse connection page named: Automatically update the database statistics. Source: [[http://www-01.ibm.com/support/docview.wss?uid=swg21590790][Technote 1590790]]

---+++ 3. Not enough System Resources
The ETLs are running and consuming system resources, but there is no Out of Memory or crash occurring. The ETLs may simply be running as quickly as possible based on your current system resources. An option to increase processing time would be to add more RAM or processing power to the server.

---+++ 4. Database Backups
Running a backup process during the ETLs will also add additional load onto the database server, causing the ETLs to run longer. Ensure that no backup is being run during the ETL process.

---+++ 5. Outdated JDBC Driver
The JDBC driver is what allows the JVM to connect to the database and transfer data and therefore directly affects the performance of the database with data loading.  For large datasets, the corrections in recent JDBC drivers for performance will help alleviate problems associated with moving large amounts of data. Make sure that the JDBC driver is the latest recommended version for the Database and is the correct 64 bit ( or 32 bit) version.

---+++ 6. Custom SQL Queries are causing Delays in Running ETLs
Running SQL queries against the database will put extra load on the database server, thus causing the ETLs to run longer. Ensure that there are no custom queries running against the database during the time the ETLs run (by default, this is at 12 AM). 

---+++ 7. Network Delays
If there are too many network hops between the CLM Server and the database server, the latency may cause long running ETLs. Ensure that your Database Server and CLM Server are located on the same network. Wireshark and other network troubleshooting tools can be used to troubleshoot network performance.

     
---+++++!! Related Topics: [[DeploymentWebHome][Deployment Web Home]], [[DeploymentWebHome][Deployment Web Home]]

---+++++!! External Links:          
   * [[https://www.ibm.com][IBM]]

---+++++!! Additional Contributors: Main.TWikiUser, Main.TWikiUser

</div></div>

Copyright © by IBM and non-IBM 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.
Dashboards and work items are no longer publicly available, so some links may be invalid. We now provide similar information through other means. Learn more here.