(r8) WhyDoMyOutOfTheBoxETLsTakeSoLongToRUN < Deployment

Engineering Lifecycle Management Wiki - Deployment

Community information and contribution guidelines

~~EditAttach~~Printable

TWiki > Deployment Web > DeploymentTroubleshooting > PerformanceTroubleshooting > WhyDoMyETLsTakeSoLongToRun > WhyDoMyOutOfTheBoxETLsTakeSoLongToRUN

Revision 8 - 2013-04-24 - 01:13:17 - Main.sbeard

<sticky><div id="container" >
<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%">
---+!! Why do my out of the box 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 determine causes where out of box ETLs (Extract, Transform, Load) take significantly longer to run than expected.
|IN PROGRESS|
---++ What is my ETL doing?
See the parent topic [[https://jazz.net/wiki/bin/view/Deployment/WhyDoMyETLsTakeSoLongToRun][Why do my ETLs Take so Long to Run?]] for an explanation of ETL activity.
---++ Initial Assessment
See the parent topic [[https://jazz.net/wiki/bin/view/Deployment/WhyDoMyETLsTakeSoLongToRun][Why do my ETLs Take so Long to Run?]] for an exaplanation of how to do initial assessment on ETLs.
---++ Recommended analysis steps
---+++ Determine that the ETL jobs have successfully completed. 
Jobs can be started from JTS but each application server, including the JTS, will only display the status of the local jobs. To see the status of a specific data collection job, you must use the Administration page of the application, select the Reports page, and choose the Data Collection Job Status link. The status of the data collection jobs is shown in the order in which they are run. The Data Collection Job Status page under the Server Administration (JTS, CCM, and QM applications) can be accessed directly using
    <verbatim>https://<servername>:<port>/<app context>/admin#action=com.ibm.team.reportsManagement.etlStatus</verbatim>
 <img src="%ATTACHURLPATH%/DataCollectionJobStatus.jpg" alt="DataCollectionJobStatus.jpg" width="526" />
 <p>Errors may be indicated by a ‘Failed’ status on the Data Collection job Status page
---+++ Determine that the User ID's are correct
   * Assure that the correct ids are used to run the ETLs and that those ids have the correct and enabled licenses.
      * The ‘Jazz Reporting’ should be configured as a Consumer (Inbound) for each application (JTS, CCM, and QM) with a username. Out of box, this username is identified as etl_user.
           <br><img src="%ATTACHURLPATH%/ConsumerKey.jpg" alt="ConsumerKey.jpg" width="660" height="54" />
      * The consumer Key should also be listed as the consumer key on the Data Collection Jobs page (screenshot above).
      * This user should have the Data Collector Client Access License assigned.
---+++ Disabled Jobs   
Look for Jobs that have been disabled. 
      This can be done on the Data Collection Jobs page under the Server Administration (JTS, CCM, and QM applications)
     <verbatim>https://<servername>:<port>/<app context>/admin#action=jazz.viewPage&id=com.ibm.team.reports.reportsManagementPage</verbatim>
     The screenshot below shows that all Data Collection Jobs for the JTS have been enabled.<p><img src="%ATTACHURLPATH%/DataCollectionJobs.jpg" alt="DataCollectionJobs.jpg" width="459" />
---+++ Review the Log files
View 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 Logs]] for more information.

---+++ Advanced Analysis
----++++ Debugging using the ETL SELECT statements
ETL steps often contain SQL SELECT statements. The result set from these steps can be useful for problem determination as well as the length of time a certain transaction should take.  Keep in mind that with ETL, the SQL Statements will only encompass the 'Extract' phase. When you run the SELECT statements manually, this will only show the transactional time and should not be used as a baseline for how long the ETL should take as the data will still transform and load into the Data Warehouse database. See [[http://www-01.ibm.com/support/docview.wss?uid=swg21116474][Technote 1116474]] for more information.
----++++ Reviewing the Active Services
When the ETL is running, you can check the Active Services page on the JTS to determine the exact service which is being run, and for how long. This will provide more information than the Data Collection Job Status page.
*NOTE:* When an ETL job is started manually, the name of the service will differ than if the ETL job is started automatically through the nightly service.
Below is a screenshot of the Active Services when running the ETL jobs manually:
     <img src="%ATTACHURLPATH%/ActiveService_Manual.jpg" alt="ActiveService_Manual.jpg" width="1010" height="137" />


---++ Possible Causes and Solutions
After reviewing the above information on assessment and analysis, you may or may not have found an error which correlates to the long running ETL. If so, navigate to the following pages:
<p> *[[https://jazz.net/wiki/bin/view/Deployment/LongRunningETLError][Yes, I found an error which could be causing the Long Running ETL]]* OR
*[[https://jazz.net/wiki/bin/view/Deployment/LongRunningETLNoError][No, I have not found any error causing the Long Running ETL]]*

---++!! Keywords to aid searching
ETL extract transform load data collection job star common RTC RQM Insight JTS time delay report data long length slow freeze frozen response XDC error exception disable variable STAR datamanager custom 

---++ Comments
%COMMENT{type="below"}%

---++ For further reference
Still need help troubleshooting your performance issue?  Refer to [[PerformanceTroubleshooting][Performance Troubleshooting]] for additional topics.

---+++++!! Related 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 ETL with error]], [[LongRunningETLNoError][Long running ETL with no error]]

---+++++!! External links:
   * http://en.wikipedia.org/wiki/Extract,_transform,_load

---+++++!! Product specific links:
   * 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

<sticky></div></div></sticky>

Attachments

Topic attachments
I	Attachment	Action	Size	Date	Who	Comment
jpg	ActiveService_Manual.jpg	manage	42.9 K	2013-04-09 - 17:04	UnknownUser	Active Services - Manual run of ETL
jpg	ConsumerKey.jpg	manage	12.9 K	2013-03-06 - 18:50	UnknownUser	Consumer Key
jpg	DataCollectionJobStatus.jpg	manage	121.4 K	2013-03-06 - 18:51	UnknownUser	Job Status
jpg	DataCollectionJobs.jpg	manage	74.3 K	2013-03-06 - 18:51	UnknownUser	Data Collection Job

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.