Tips & Techniques > Systems Management

Managing BI with IBM Workload Manager

Many people utilize data-warehouse and business-intelligence (BI) applications in an enterprise. Senior executives study reports to make strategic decisions. Mid-level management inspects report data for more focused decisions. Analysts submit ad-hoc queries to gain business insight and perform deep analytics to look for trends. With the advent of operational BI, actions are pushed down to customer-facing personnel and even customers themselves. Service representatives submit lightweight queries to build a profile about a specific customer in real time.

These diverse workloads consume different system resources and demand different priorities. It’s important to have a process in place to monitor and manage these workloads. Operational BI queries tend to be less taxing and retrieve only a few rows from a database. On the other hand, ad-hoc queries present unpredictable resource consumption and at times could access multiple years of data–necessitating scans of billions of rows. In between operational BI and ad-hoc queries lie static reports, which present a more repeatable resource requirement, but still need to be completed.

Eliminating CPU Monopolization

One of the biggest BI workload-management challenges is eliminating query CPU monopolization. A large query taking up all of the available CPU cycles affects everyone and will surely have a negative impact on business. Large queries can be managed using two approaches. First, a database administrator (DBA) can screen all of the queries ahead of time looking for CPU-intensive queries. These large queries are then scheduled to run off shift. A disadvantage of this approach is screening queries can be time consuming and isn’t fool proof. Occasionally, a large query gets by the screening process and enters the system during peak hours.

The second technique takes a more passive approach. Instead of screening the queries prior to execution, a DBA sets up a governor (e.g., a DB2* resource limit facility) to cancel a query after it consumes a specific number of CPU cycles. This is less labor intensive, but the downside is precious CPU cycles are wasted.

IBM’s z/OS* Workload Manager (WLM) offers a capability that doesn’t come with the disadvantages of either of the aforementioned approaches. It doesn’t require a DBA to spend tedious amounts of time pre-screening queries, nor does it squander valuable CPU resources without accomplishing useful work. Instead, it constantly monitors workload-resource consumption and makes adjustments based on goals defined during installation. A three-step approach for assessing and refining the WLM service definition with regard to mixed BI workloads is recommended. Optimally, the z/OS and DB2 administrators would work together to complete these steps.

1. Construct a summary table view of the BI workloads, their importance to the business, objectives and the current WLM and DB2 schemes.
2. Collect WLM and DB2 performance data for your BI workloads.
3. Review WLM recommendations and evaluate performance results versus WLM service definition and business goals.

The value of this exercise, at minimum, better prepares the performance administrator for discussions and decisions surrounding BI workload resource management and resource growth. At maximum, it results in better alignment of system resources with business goals, yielding a more efficient system and happier end users.

Next page: >>