E
dit
A
ttach
P
rintable
r1 - 2013-11-22 - 17:55:27 - Main.sbagot
You are here:
TWiki
>
Deployment Web
>
DeploymentTroubleshooting
>
PerformanceTroubleshooting
>
WhyDoMyETLsTakeSoLongToRun
>
ETLAssessmentAndAnalysis
<div id="header-title" style="padding: 10px 15px; border-width:1px; border-style:solid; border-color:#FFD28C; background-image: url(<nop>https://jazz.net/wiki/pub/Deployment/WebPreferences/TLASE.jpg); background-size: cover; font-size:120%"> ---+!! <img src="https://jazz.net/wiki/pub/Deployment/WebPreferences/new.png" alt="new.png" width="50" height="50" align="right"/> Why do my ETLs take so long to run? %DKGRAY% Authors: Main.GeraldMitchell, Main.StephanieBagot <br> Build basis: CLM 4.x %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> 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 [[https://jazz.net/wiki/bin/view/Deployment/InitialTroubleshootingInvestigation][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 [[https://jazz.net/wiki/bin/view/Deployment/WhyDoMyOutOfTheBoxETLsTakeSoLongToRUN][Why do my out of the box ETLs take so long to load?]] ---+++Custom ETLs This topic is described further for custom ETLs in [[https://jazz.net/wiki/bin/view/Deployment/WhyDoMyCustomETLsTakeSoLongToRun][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: <verbatim><server install directory>/logs/*-etl.log </verbatim> See [[https://jazz.net/wiki/bin/view/Deployment/HowToReadETLLogFile][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 [[http://www.ibm.com/support/docview.wss?uid=swg21409882][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 <verbatim>C:/Documents and Settings/<user>/logs</verbatim> * You can change the default location by modifying the register of the jdbclogpath parameter in the registry: <verbatim>HKEY_LOCAL_MACHINE/SOFTWARE/ODBC/ODBCINST.INI/IBM Rational insight XML ODBC Driver </verbatim> * Rational Insight Data Manager logs are located at <verbatim><rational_insight_installation_directory>/cognos/datamanager/log </verbatim> * Rational Insight Data Manager logs for rejected data are located in <verbatim><rational_insight_installation_directory>/cognos/datamanager/data </verbatim> ---++ 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: </br> * [[https://jazz.net/wiki/bin/view/Deployment/LongRunningETLError][Yes, I found an error that could be causing the long running ETL]] * [[https://jazz.net/wiki/bin/view/Deployment/LongRunningETLNoError][No, I have not found any error causing the long running ETL]] ---+++++!! Related topics: * Still need help troubleshooting your performance issue? Refer to [[PerformanceTroubleshooting][Performance Troubleshooting]] for additional topics. * [[WhyDoMyOutOfTheBoxETLsTakeSoLongToRUN][Why Do my OOB ETLs take so long to run?]] * [[WhyDoMyCustomETLsTakeSoLongToRun][Why do my custom ETLs take so long to run?]] * [[HowToReadETLLogFile][How to read ETL log files]] * [[LongRunningETLError][Long-running ETLs with error]] * [[LongRunningETLNoError][Long-running ETLs without errors]] ---+++++!! External links: * http://en.wikipedia.org/wiki/Extract,_transform,_load * http://pic.dhe.ibm.com/infocenter/clmhelp/v4r0m1/topic/com.ibm.rational.reporting.admin.doc/topics/t_running_the_data_collection_jobs.html * http://pic.dhe.ibm.com/infocenter/clmhelp/v4r0m1/topic/com.ibm.rational.reporting.admin.doc/topics/c_data_collection.html ---+++++!! Additional contributors: None ---+++++!! Questions and comments: %COMMENT{type="below" target="WhyDoMyETLsTakeSoLongToRunComments" button="Submit"}% %INCLUDE{"WhyDoMyETLsTakeSoLongToRunComments"}% <sticky></div></sticky>
E
dit
|
A
ttach
|
P
rintable
|
V
iew topic
|
Backlinks:
We
b
,
A
l
l Webs
|
H
istory
: r1
|
M
ore topic actions
Deployment
Deployment web
Planning and design
Installing and upgrading
Migrating and evolving
Integrating
Administering
Monitoring
Troubleshooting
Community information and contribution guidelines
Create new topic
Topic list
Search
Advanced search
Notify
RSS
Atom
Changes
Statistics
Web preferences
NOTE: Please use the Sandbox web for testing
Status icon key:
To do
Under construction
New
Updated
Constant change
None - stable page
Smaller versions of status icons for inline text:
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
.