Query Acceleration for Business Using IBM Informix Warehouse Accelerator

252 Pages·2013·3.65 MB·English

Front cover
IBM Information Management Software
®
Query Acceleration for
Business Using IBM Informix
Warehouse Accelerator
Technology and architecture of
Informix Warehouse Accelerator
Data mart design considerations
and examples
IBM Cognos Business
Intelligence integration
Whei-Jen Chen
Nigel Campbell
Martin Fuerderer
Frederick Ho
Holger Kirstein
Keshava Murthy
Ramanathan Sivaguru
ibm.com/redbooks

International Technical Support Organization
Query Acceleration for Business Using IBM
Informix Warehouse Accelerator
November 2013
SG24-8150-00
Note: Before using this information and the product it supports, read the information in
“Notices” on pageix.
First Edition (November 2013)
This edition applies to IBM Informix Version 12.10.
© Copyright International Business Machines Corporation 2013. All rights reserved.
Note to U.S. Government Users Restricted Rights -- Use, duplication or disclosure restricted by GSA ADP
Schedule Contract with IBM Corp.
Contents
Notices . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .ix
Trademarks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . x
Preface . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Authors. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xi
Acknowledgements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .xiv
Now you can become a published author, too! . . . . . . . . . . . . . . . . . . . . . . . . xv
Comments welcome. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Stay connected to IBM Redbooks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . xv
Chapter 1. Introduction to IBM Informix Warehouse Accelerator. . . . . . . . 1
1.1 Problems that Informix Warehouse Accelerator solves for its customers. . 2
1.2 History of Informix Warehouse Accelerator. . . . . . . . . . . . . . . . . . . . . . . . . 2
1.3 Key Informix Warehouse Accelerator technologies. . . . . . . . . . . . . . . . . . . 4
1.3.1 Informix Warehouse Accelerator architecture. . . . . . . . . . . . . . . . . . . 5
1.3.2 Informix and Informix Warehouse Accelerator data mart definition and
data loading . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 5
1.3.3 Query execution in Informix and Informix Warehouse Accelerator. . . 6
1.3.4 Data synchronization in both Informix and Informix Warehouse
Accelerator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 6
1.4 Informix Warehouse Accelerator architectural options . . . . . . . . . . . . . . . . 7
1.5 Components of Informix Warehouse Accelerator . . . . . . . . . . . . . . . . . . . . 8
1.5.1 Coordinator node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.5.2 Worker node . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.6 Informix Warehouse Accelerator tools . . . . . . . . . . . . . . . . . . . . . . . . . . . . 9
1.7 Business intelligence tools that are used with Informix and Informix
Warehouse Accelerator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10
1.8 Informix Warehouse Accelerator editions . . . . . . . . . . . . . . . . . . . . . . . . . 11
1.9 Informix Warehouse Accelerator positioning. . . . . . . . . . . . . . . . . . . . . . . 12
Chapter 2. Designing data marts. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 15
2.1 Relational database design overview . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2.1.1 Designs for OLTP . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 16
2.1.2 Designs for OLAP and dimensional modeling. . . . . . . . . . . . . . . . . . 19
2.2 Dimensional modeling for Informix Warehouse Accelerator. . . . . . . . . . . 23
2.2.1 Degree of denormalization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
2.2.2 Composing a data mart. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 24
2.2.3 Subsets in a snowflake schema . . . . . . . . . . . . . . . . . . . . . . . . . . . . 26
2.2.4 Overlapping snowflake schemas . . . . . . . . . . . . . . . . . . . . . . . . . . . 28
© Copyright IBM Corp. 2013. All rights reserved. iii
2.2.5 Types of relationships between tables . . . . . . . . . . . . . . . . . . . . . . . 30
2.3 Designing Informix Warehouse Accelerator data marts . . . . . . . . . . . . . . 32
2.3.1 Supported data types . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
2.3.2 Data compression. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 32
2.3.3 Dimension tables and multiple Informix Warehouse Accelerator worker
nodes . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 33
2.3.4 Data mart metadata in the Informix server . . . . . . . . . . . . . . . . . . . . 34
Chapter 3. Designing and sizing an IBM Informix Warehouse Accelerator
environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 35
3.1 Architectural considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
3.1.1 SMP environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 36
3.1.2 Cluster environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 40
3.1.3 Virtual machine environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42
3.1.4 Changing an existing configuration. . . . . . . . . . . . . . . . . . . . . . . . . . 43
3.2 Continuous data mart availability . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.3 Sizing. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 44
3.3.1 General resource considerations . . . . . . . . . . . . . . . . . . . . . . . . . . . 45
3.3.2 Sizing for an SMP environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 48
3.3.3 Sizing for a cluster environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . 51
3.3.4 Sizing for a VM environment. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 53
3.3.5 Network connectivity. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 54
Chapter 4. IBM Informix Warehouse Accelerator installation and
configuration. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 55
4.1 Installation of the Informix Warehouse Accelerator server . . . . . . . . . . . . 56
4.1.1 Installation of Informix Warehouse Accelerator: Single computer. . . 56
4.1.2 Installation of Informix Warehouse Accelerator: Cluster. . . . . . . . . . 57
4.2 Configuration tasks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 58
4.2.1 Configuring Informix Warehouse Accelerator. . . . . . . . . . . . . . . . . . 58
4.2.2 Operating system setup requirements . . . . . . . . . . . . . . . . . . . . . . . 62
4.2.3 Configuring the Informix server. . . . . . . . . . . . . . . . . . . . . . . . . . . . . 64
4.3 Setting up and starting the accelerator server. . . . . . . . . . . . . . . . . . . . . . 65
4.3.1 Setting up Informix Warehouse Accelerator . . . . . . . . . . . . . . . . . . . 65
4.3.2 Starting Informix Warehouse Accelerator. . . . . . . . . . . . . . . . . . . . . 65
4.3.3 Connecting Informix server and Informix Warehouse Accelerator . . 67
Chapter 5. Creating IBM Informix Warehouse Accelerator data marts . . 69
5.1 Data marts as objects in Informix Warehouse Accelerator. . . . . . . . . . . . 70
5.2 Identifying workloads in your business environments. . . . . . . . . . . . . . . . 71
5.3 Data mart creation by using workload analysis. . . . . . . . . . . . . . . . . . . . . 72
5.3.1 Workload analysis methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 73
5.3.2 Workload Analysis by using OAT . . . . . . . . . . . . . . . . . . . . . . . . . . . 74
5.3.3 Data mart deployment by using OAT . . . . . . . . . . . . . . . . . . . . . . . . 79
iv Query Acceleration for Business Using IBM Informix Warehouse Accelerator
5.4 Interactive data mart design with IBM Smart Analytics Optimizer Studio . 84
5.5 Embedded data mart creation by using sysdbopen() . . . . . . . . . . . . . . . . 94
5.6 Designing a data mart that is based on SQL trace filtering. . . . . . . . . . . . 96
5.7 Using Informix TimeSeries data in an Informix Warehouse Accelerator
environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 97
Chapter 6. Query execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 103
6.1 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 104
6.2 Query execution flow. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 105
6.2.1 Step 1: Submitting SQL on the application or BI tools . . . . . . . . . . 107
6.2.2 Step 2: Optimizing and matching the query . . . . . . . . . . . . . . . . . . 108
6.2.3 Step 3: Local execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
6.2.4 Step 4: Accelerating SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 109
6.2.5 Step 5: Query processing on the coordinator node. . . . . . . . . . . . . 110
6.2.6 Step 6: Query processing on worker nodes . . . . . . . . . . . . . . . . . . 111
6.2.7 Step 7: Results sent to Informix and DRDA over TCP/IP. . . . . . . . 112
6.2.8 Step 8: Post processing on the Informix server . . . . . . . . . . . . . . . 112
6.2.9 Step 9: Returning results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 112
6.3 Enabling query execution . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 113
6.4 Query matching. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 116
6.4.1 Checklist for query (query block) qualification. . . . . . . . . . . . . . . . . 118
6.4.2 Examples for query qualification. . . . . . . . . . . . . . . . . . . . . . . . . . . 130
6.5 Monitoring query execution. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 139
6.6 Summary. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 141
Chapter 7. Managing and refreshing an IBM Informix Warehouse
Accelerator data mart. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 143
7.1 Overview. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 144
7.1.1 IBM Informix Open Admin Tool. . . . . . . . . . . . . . . . . . . . . . . . . . . . 145
7.1.2 The Informix and Informix Warehouse Accelerator Stored Procedure
API . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 146
7.1.3 IBM Smart Analytics Optimizer Studio . . . . . . . . . . . . . . . . . . . . . . 147
7.2 Data synchronization methods . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 148
7.3 Data mart states and transitions . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 149
7.3.1 Stage A: Informix tables or data marts . . . . . . . . . . . . . . . . . . . . . . 150
7.3.2 Stage B: Data mart design and definition . . . . . . . . . . . . . . . . . . . . 152
7.3.3 Stage C: Loading the pending data mart (disabled) . . . . . . . . . . . . 154
7.3.4 Stage D: (Enabled) Loaded data mart in use . . . . . . . . . . . . . . . . . 156
7.3.5 Stage E: Data mart disabled. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 162
7.3.6 Stage F: Data mart deleted. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
7.4 Schema changes on Informix . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 163
Chapter 8. IBM Informix Warehouse Accelerator server: Administration
tasks. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 165
Contents v
8.1 Administering the Informix Warehouse Accelerator server by using the ondwa
utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
8.1.1 ondwa setup . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 166
8.1.2 ondwa start . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
8.1.3 ondwa stop . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 167
8.1.4 ondwa reset. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
8.1.5 ondwa clean . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 168
8.2 Access token generation. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 169
8.3 Monitoring Informix Warehouse Accelerator. . . . . . . . . . . . . . . . . . . . . . 170
8.4 Monitoring operating system usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
8.4.1 Monitoring memory usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 171
8.4.2 Monitoring processor usage . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
8.4.3 Monitoring the network . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 176
8.4.4 Monitoring disk space usage. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
8.5 Informix database server administration objectives . . . . . . . . . . . . . . . . 177
8.5.1 Accelerated query tables. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 177
8.5.2 Cleanup of data mart metadata. . . . . . . . . . . . . . . . . . . . . . . . . . . . 179
Chapter 9. Use of IBM Cognos Business Intelligence with IBM Informix
Warehouse Accelerator . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 183
9.1 IBM Cognos Business Intelligence . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 184
9.2 Metadata model. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
9.3 Relationships. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 185
9.3.1 Dimensions and member preservation. . . . . . . . . . . . . . . . . . . . . . 189
9.4 Multiple fact queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 191
9.5 Filtering data . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 193
9.6 Data types. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 194
9.7 Data driven prompts and dimension browses. . . . . . . . . . . . . . . . . . . . . 196
9.8 Connection command blocks . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 196
9.9 Monitoring SQL statements. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 198
Chapter 10. IBM Informix Warehouse Accelerator proof of concept . . . 201
10.1 Assessing qualification and compatibility for Informix Warehouse
Accelerator. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 202
10.2 Assessing customer pain for analytic queries. . . . . . . . . . . . . . . . . . . . 204
10.2.1 Feasibility assessment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 205
10.2.2 Business value assessment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
10.3 Document of understanding . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 207
10.4 Operating system, environment, and client application readiness . . . . 208
10.5 Preparing the server for Informix Warehouse Accelerator . . . . . . . . . . 209
10.6 Sizing the environment . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 210
10.6.1 Informix database server requirements. . . . . . . . . . . . . . . . . . . . . 210
10.7 Defining an accelerator and creating a data mart. . . . . . . . . . . . . . . . . 211
vi Query Acceleration for Business Using IBM Informix Warehouse Accelerator
10.7.1 Probing queries. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 212
10.8 Accelerating SQL queries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
10.8.1 Controlling query acceleration. . . . . . . . . . . . . . . . . . . . . . . . . . . . 213
10.8.2 Query performance. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 214
10.8.3 Compiling results. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 215
10.9 Proof of technology. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 216
Appendix A. Tools for IBM Informix Warehouse Accelerator . . . . . . . . . 217
A.1 Quick guide to Informix Warehouse Accelerator tools and interfaces . . 218
A.2 The ondwa utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 219
A.3 The ondwachk utility. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 221
A.4 Stored procedure API. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 222
A.5 IBM OpenAdmin Tool for Informix (OAT) . . . . . . . . . . . . . . . . . . . . . . . . 227
A.6 IBM Smart Analytics Optimizer Studio . . . . . . . . . . . . . . . . . . . . . . . . . . 228
Related publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Other publications . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Online resources . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 229
Help from IBM . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 230
Contents vii
viii Query Acceleration for Business Using IBM Informix Warehouse Accelerator