Troubleshooting ETL2

ETL2 Trace Log

When the Phase 2 Loader runs, it writes trace data to the ETL2_STAT_DTL table. If you experience problems with the Phase 2 Loader, you can view the trace data using this query:

SELECT * FROM ETL2_STAT_DTL WHERE JOB_ID = $JOBID ORDER BY DTL_ID;

Replace $JOBID with the actual job id.

Troubleshooting Performance Issues

Performance issues are typically caused by bad query plans in the database. If the Phase 2 Loader is performing poorly, stop the load. Then update database statistics and restart the load. The Phase 2 Loader is written so that loads may be stopped and restarted at any time. In addition, the ETL2 Trace Log contains record counts and elapsed times for each step in the trace.

ETL2 Locking Tables

Each step of the Phase 2 loader has a critical section that can only be run by one job at a time. Each critical section does the following:

Join ETL2 table to target table to identify which ETL2 records need to be inserted and which need to be updated.
Insert records into the target table.

If two concurrent jobs perform steps 1 and 2 at the same time, they could both attempt to insert the same records and either collide or create duplicates. To avoid this problem, the Phase 2 loader has a lock table for each ETL2 table; e.g. ETL2_CUSTOMER_MASTER_LOCK for ETL2_CUSTOMER_MASTER. Before entering the critical section, the Phase 2 loader acquires a table lock on the lock table. Then if the lock table is empty, it writes a record into the lock table. If the lock table is not empty, it drops the table lock and tries again after a configurable delay.

If you stop a data load, you may also need to manually break the lock. You break the lock by deleting the record from the appropriate lock table. Any other jobs waiting on the lock will then be able to make progress.