Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

254 Solving Operational Business Intelligence with InfoSphere Warehouse Advanced Edition

򐂰 All queries with a priority must be given resources to complete within service

level objectives (SLO).

򐂰 Educate the business as to how queries are identified, grouped, and assigned

resources.

6.4.1 Identify/define query workloads with Optim Performance

Manager

Start by identifying each application that is to access the database and

understand the nature of that application, how queries are generated, and how

many concurrent queries and users you expect.

Queries in a data warehouse environment are less predictable than in traditional

OLTP environments. For example, an interactive business intelligence (BI)

application might generate many unique queries that might be transformed from

multidimensional expression (MDX) to SQL and represent a challenge for both

the DB2 optimizer and for the optimization of your cache hit ratio.

Each query workload is typically aligned to a group or department of users, a BI

application, or a set of reports.

Perform these steps to identify and define your workload:

1. Use the Connections dashboard to review all database connections.

Identify and reconcile individual database connections with expected

workloads.

Optim Performance Manager can capture many connection attributes and not

all are shown by default. Click Choose columns to add additional columns to

the Connection dashboard to further help you identify each connection.

Chapter 6. Managing complex query workloads in an operational warehouse environment 255

Figure 6-15 shows some of the additional connection related data that you

can add to the data grid on the Connections dashboard.

Figure 6-15 Column list of items that appear on the Connections dashboard

2. Use the SQL Statements dashboard to identify queries by connection.

When you identify an individual connection, use the filters in both the

Connections dashboard and the SQL statements dashboard to get a more

complete picture of usage patterns and queries executed.

In the SQL statements dashboard, identify the top five SQL statements

issued by a single connection over a period of time; use the time slider in

conjunction with the Historical analysis mode to achieve this.

Replace the connection filter with the most common SQL statement text used

and establish which other connections also issue the same SQL statement.

Using this approach allows you to establish patterns of use and shared

workloads across different connection types.