IBM Data Management magazine Issue 2, 2009

Database compression isn’t new, but Storage Optimization automates key tasks to make it easier to use. For example, it builds the compression dictionary automatically, instead of requiring the DBA to manually specify the patterns to be compressed.

Fred TX

10000 500

Plano

20000 Plano 500 24385 John TX

Fred

10000 (01)

(02)

… (02) 20000 (01) John

Informix also provides guidelines
ee sidebar, “Resources”) for when
optimization would be least useful,
such as for small tables that fit into
memory without compression and for temporary tables and
system catalogues. But even in those cases, the CPU cycles
needed to compress the data and the table scans required to re-
pack and shrink data have little impact on overall performance.

c c c (s

Figure 1: Saving repeating patterns as shorter character strings allows Storage Optimization to achieve very high compression ratios.

While compression and decompression do require CPU ycles, “We’re finding the price you pay in CPU overhead is outweighed by the benefit you get from having fewer pages and fewer I/Os,” says IDS Chief Architect Kevin Brown. “If before, your database couldn’t fit in memory, and now it can, you’re going to see a really big performance boost.”

Feature V11.50 includes the SQL Warehouse (SQW) Client with Design Studio for data modeling, schema design, data transformation design, and data flow design; SQW Warehouse Server, with an administration console to schedule and manage data flows; and SQL Warehouse runtime to perform data transformation within the IDS data server.

The Informix Warehouse infrastructure also supports ntegrated external tooling for BI analytics from IBM Cognos, for managing data growth with IBM Optim, and for data transformation and cleansing with IBM InfoSphere

In cases where compression might compromise appli-ation performance, administrators can specify which fragments to compress. “If a table has 10 fragments, for example, you might want to leave the most recently created fragments uncompressed for performance purposes, and compress the older fragments, since they’re less likely to be accessed,” says Brown. A recompress option allows administrators to rebuild the compression dictionary to ensure optimal compression of the tables, which is useful if the data within them has changed significantly over time.

d p i

DataStage and InfoSphere QualityStage.

With the new IDS data warehousing tools, DBAs can erform business analysis on their operational data stores to ensure access to the most current data. They can also create separate data warehouses within IDS to prevent analytic queries from slowing production applications or run a “shared disk” configuration in which separate servers access the same data for OLTP and business analysis.

DBAs can use any or all of the Storage Optimization apabilities while the database is running, thus preventing interruption to either transactions or queries. They can also manage Storage Optimization from within the same IDS OpenAdmin Tool (OAT) they use to manage the size of volumes and to monitor performance. Graphical tools show administrators or business users how much space they can save by running any of the functions within Storage Optimization. Storage Optimization can also be controlled through a command-line interface, which is useful for the many ISVs that bundle IDS within their own applications.

The bottom line: businesses that have been using IDS for ata warehousing and BI now have better, more targeted tools to achieve their goals. “We give organizations the choice so they can make the best use of their data and infrastructure based on their technical and business requirements,” says Brown.

RESOURCES

… 24385

Dept 500

Plano, TX

24385 … … (01)

(02)

Bob Scheier is a veteran IT writer based in Swampscott, Massachusetts.

The benefits of the bundle

Storage Optimization is available in a new data warehouse platform package called IBM Informix Warehouse, which includes IDS, its new Storage Optimization Feature, and the IBM Informix Warehouse Feature V11.50. Informix Warehouse