E
dit
A
ttach
P
rintable
r14 - 2015-05-26 - 18:07:24 - Main.sbagot
You are here:
TWiki
>
Deployment Web
>
DeploymentTroubleshooting
>
PerformanceTroubleshooting
>
WhyDoMyETLsTakeSoLongToRun
>
HowToReadETLLogFile
<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/uc.png" alt="uc.png" width="50" height="50" align="right"/> How to read ETL log files %DKGRAY% Authors: Main.StephanieBagot <br> Build basis: CLM 4.x, 5.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 analysis page will help to understand how to read ETL (Extract, Transform, Load) log files when using reporting with IBM CLM applications. ---++ Reading the ETL log file A typical ETL log file will display the following information: <verbatim>2013-02-18 19:07:34,106 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: ***Started Build IterationParent at 2/18/13 7:07 PM*** 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Selected: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Inserted: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Updated: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Ignored: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Inserting: 0ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Updating: 0ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Looking Up: 0ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Fetching Data: Less than 1ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Running: Less than 1ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: ***Finished Build IterationParent at 2/18/13 7:07 PM. The build was successful*** </verbatim> This can be broken down as follows:<br/> <verbatim>2013-02-18 19:07:34,106 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: ***Started Build IterationParent at 2/18/13 7:07 PM*** </verbatim> 1. The first line indicates which build within the ETL is run and the date and timestamp. In the example above, the Build IterationParent was started on 2/18/13 at 7:07 PM. <BR> <verbatim> 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Selected: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Inserted: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Updated: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Records Ignored: 0 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Inserting: 0ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Updating: 0ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Looking Up: 0ms </verbatim> 2. The ETL will first Look Up the data. This is where the data's external IDs are obtained, and compared against the Data Warehouse integer keys. In the example above, this time was 0 ms. Once the look up finds data that matches, it will determine if the records will be inserted (new data) or updated (existing data in the datawarehouse). Time for each of the Update/Inserted steps will be provided in the log, as well as how many records were selected for update/insert. <verbatim> 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Fetching Data: Less than 1ms </verbatim> <BR>3. The next step is Fetching the Data. This will actually bring the data from the application database into the data warehouse. <verbatim> 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: Time Running: Less than 1ms 2013-02-18 19:07:34,216 [ ccm: AsynchronousTaskRunner-3] DEBUG ervice.internal.common.CommonRemoteSnapshotService - ETL: ***Finished Build IterationParent at 2/18/13 7:07 PM. The build was successful*** </verbatim> <BR>4. The Total time running displays the amount of time that the build ran for. ---++ Key areas to look for in the ETL log file ---+++ Errors You may see errors at the end of the ETL indicating that the ETL failed, but errors may also be contained within each build. Oftentimes, if one build fails, the ETL will move onto the next build which may contribute to long running ETLs. Is is important to review each build as its own item to ensure there are no failures. ---+++ Time fetching data If the time to fetch data increases dramatically between ETL runs, this may be indicative of database latency problem as the data is brought from one database into another. The time to run the ETL may also be impacted if there are long delays between the entries. If this occurs, it may be due to a process being waited for, garbage collection, logging allocation, etc. ---+++Baseline and state history jobs The Baseline and State History Jobs will actually move data from the data warehouse. This type of data is used in trend reports and these ETLs are often the slowest. ---++How can reading the ETL log file help to isolate ETL performance? ---++!! 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 ---+++++!! Related topics: * Still need help troubleshooting your performance issue? Refer to [[PerformanceTroubleshooting][Performance Troubleshooting]] for additional topics. * [[HowToReadETLLogFile][How to read ETL log files]] * [[WhyDoMyOutOfTheBoxETLsTakeSoLongToRUN][Why do my OOB ETLs take so long to run?]] * [[WhyDoMyCustomETLsTakeSoLongToRun][Why do my custom ETLs take so long to run?]] * [[LongRunningETLError][Long-running ETLs with error(s)]] * [[LongRunningETLNoError][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 <sticky></div></sticky>
E
dit
|
A
ttach
|
P
rintable
|
V
iew topic
|
Backlinks:
We
b
,
A
l
l Webs
|
H
istory
: r14
<
r13
<
r12
<
r11
<
r10
|
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
.