Why do my out of box ETLs take so long to run?

Authors: GeraldMitchell, StephanieBagot
Build basis: CLM 4.x, 5.x

This situation is to help determine causes where out of box ETLs (Extract, Transform, Load) take significantly longer to run than expected.

What is my ETL doing?

See the parent topic Why do my ETLs Take so Long to Run? for an explanation of ETL activity.

Initial Assessment

See the parent topic 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

https://<servername>:<port>/<app context>/admin#action=com.ibm.team.reportsManagement.etlStatus

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.
    
  • 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)

https://<servername>:<port>/<app context>/admin#action=jazz.viewPage&id=com.ibm.team.reports.reportsManagementPage

The screenshot below shows that all Data Collection Jobs for the JTS have been enabled.

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:

<server install directory>/logs/*-etl.log 

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

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:

Yes, I found an error which could be causing the Long Running ETL OR No, I have not found any error causing the Long Running ETL

Related topics:

• Still need help troubleshooting your performance issue? Refer to Performance Troubleshooting for additional topics.
• How to read ETL log files
• Why do my OOB ETLs take so long to run?
• Why do my custom ETLs take so long to run?
• Long-running ETLs with error(s)
• Long-running ETLs without error(s)

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