Why do running reports hang in IBM Rational Insight when the BuildStar job in an ETL is running?
Reports stay in the "Executing" state much longer than they typically do, or they never complete at all.
The BuildStar job involves the use of database reorganization commands that Insight uses to keep the tables of the Insight databases running optimally. These commands create an exclusive lock on the database which will cause an expected and significant performance hit for reports running at the same time.
Resolving the problem
Move the BuildStar job out of the main ETL jobs. Then run it during a maintenance window (off hours) when no reports will be run.
To move the BuildStar job out of the main ETL jobs, use the following steps:
- Launch Data Manager and open the Insight ETL catalog
- Navigate to the Build Star job under Builds and Job Streams -> Jobs -> Star Jobs
- Right-click the Build Star job and select Duplicate
- Re-name the new job Reorg
- In the new job, right click on Cleanup Facts -> Properties, and select Exclude this node from processing, and click OK.
- Repeat step 5 for Dimensions, HistoricalLookups and Facts. At this point, only reorgODS, reorgDim, reorgFact and reorgHistoricalLookups should be enabled.
- Navigate to the original Build Star job and disable the four reorg nodes by right-clicking each one, selecting Properties and selecting Exclude this node from processing.
- At this point you should have the original build star job with the reorg nodes excluded, and a new job called Reorg with nothing but the reorg nodes included. You can schedule the reorg job to run at a time when no other jobs are running, or run it manually during a maintenance window.
Note: The data warehouse performance can degrade over time if the reorg job is not run after adding a large amount of data.
An enhancement (Jazz work Item 79500) has been filed to remove the BuildStar jobs from the daily ETL jobs.