Skip to main content

Preface

You plan to buy compression software for your DB2 tables and you are evaluating our INFOPAK software.

We have gathered in this guide the main elements that can help you in making the right choice by collecting quantified information to assist you in preparing the final report.

Test Methodology

Product Installation​

The first step consists of installing the product as delivered on the demo tape. The procedure for unloading the tape is described in the manual that comes with the tape.

Let us remind you that this tape contains:

  • TPAKDB2, a simulation tool that reads the tables and runs the compression algorithm on every row without any modification to existing tables.

  • INFOPAK, the compression routine itself.

You will notice the ease of installation and use of this product: it can be immediately used without any previous step such as reading existing tables and without building specific compression tables.

TPAKDB2​

This is the first step in evaluating compression. It helps you in determining a compression ratio that is near actual as it uses INFOPAK directly (it cannot take into account physical parameters such as free space or the various prefixes that can be found in table rows or data pages).

TPAKDB2 gives you percentages of benefit, but it is the absolute value that is meaningful and allows you to add the number of bytes saved by INFOPAK on the tables that are the candidates for compression. Thus, it can be interesting to compare the space savings gained from compressing a large table with only a 50% benefit versus a small table that may achieve an 85% compression benefit.

Selecting the test sample​

If you wish to run a comparison of performance with and without compression, you must define a test sample and you must be sure that the comparisons are meaningful.

We advise you to first create a reference sample of non compressed tables, eventually by copying real tables. These tables must be isolated in a specific tablespace (only one table per tablespace).

We suggest you define a significant set of DB2 requests that suits your operations profile and assign a relative importance for each one (x% for update in TP, y% for read-only batches, and so on).

The size of the sample must be near (or equal to) the size of your real tables: one reason is that the DB2 optimizer draws his access strategy from the volumes recorded in the catalog, and so the access path can be very different depending on the size of the tables); another reason is that the fixed times (initialization for instance) are important, and can mask the results if the size of the sample is too small.

Create table for compression​

The EDITPROC parameter in the CREATE TABLE statement allows you to specify the INFOPAK compression routine for a table.

We suggest you copy the definition of the reference (non compressed) tables, add the EDITPROC parameter, and remain with one table in a tablespace.

If you have one or several indexes on these tables, you must also copy their definition.

At this point, you have a definition of tables without and with compression. You can submit these definitions under SPUFI to add these tables to the DB2 catalog. The comparison can start.

Test session

Table loading​

The initial load of the tables can be run.

We suggest you submit, in the same operating environment, the jobs for initial load by using the LOAD utility. Note the CPU and elapsed times for these jobs. The CPU time for loading the tables with compression will be greater than the one for the same tables without compression and will reflect the maximum overhead (because INFOPAK runs more instructions in compression than in decompression).

Full image copy​

After the LOAD, it is necessary to make an image copy of the tables, especially if you used the LOG NO option of the LOAD utility.

The CPU and elapsed times will be noted. You will see a decrease in CPU and elapsed times for the compressed tables, reflecting the compression ratio.

RUNSTATS and STOSPACE​

The next step is to run the RUNSTATS and STOSPACE on the tables (compressed and not compressed). The function of these utilities is twofold:

  • first, they let you know the real profile of the loaded tables (number of data pages, occupation ratio, and so on),

  • then, they collect data used by the DB2 optimizer for finding the best access path.

After they have been processed, you can query the DB2 catalog to know the number of pages for every table:

   SELECT NPAGES  FROM SYSIBM.SYSTABLES
   WHERE  CREATOR = 'creator-name'
     AND  NAME    = 'table-name' ;

The above request gives the number of pages occupied by a table.

   SELECT NACTIVE FROM SYSIBM.SYSTABLESPACE
   WHERE  CREATOR = 'creator-name'
     AND  NAME    = 'tablespace-name' ;

The above request gives the number of active pages in a tablespace.

Reorganisation​

A comparison between the reorganization times can also be done.

Let us remind you that several options are available for the REORG utility, especially the default option UNLOAD CONTINUE that unloads and reloads a tablespace without decompression and recompression, and yields better performance (CPU and elapsed) for a compressed table.

Comparison of the requests​

First, we advise you write test programs and not measure directly under SPUFI or QMF: that is because, with these tools, statements are dynamically pre-compiled, and you add catalog times, access path selection times and pre-compiler times that reflect neither compression impact nor day-to-day processing. These programs must be processed by the BIND operation after RUNSTATS and STOSPACE has been run on the accessed tables (to be sure that the access path chosen takes into account the actual space values stored in the DB2 catalog).

Secondly we suggest that you sort DB2 requests by access type to measure the right effects and to draw significant conclusions. This is because, depending on the program and the type of request, several access types can be generated by DB2:

  • direct access by using a unique index key: for this type of request, that corresponds to the majority of real time accesses, you will not see differences between a compressed and uncompressed table for tables with more than ten to twenty pages. The access will be done by reading as many index pages as index levels, and only the selected row will be read and decompressed.

  • index access without data reference: this type of access leads to a partial or total scan of the leaf pages of an index; as the indexes are not compressed, you will not see differences between compressed and uncompressed tables.

  • index access with data reference: this kind of access leads to a partial or total scan of leaf pages of an index, and then reads (with decompression) only some rows of the table; the difference in performance between compressed and uncompressed tables cannot be foreseen; compression can increase or decrease the CPU time, depending on the number of rows accessed, the type of index (clustered or not), the compression ratio, the disorganization of the table, and so on

  • total scan of a table: in this type of access, (tablespace scan), all the rows of the table are retrieved and decompressed. You must have less I/O with compression (provided that the table is not already in buffers), and more CPU. But you must wonder whether this kind of access is really needed (sequential batch scan) or if it is a non-optimized request (do you intend to make a tablespace scan in a real time transaction?)

The EXPLAIN instruction can tell you what kind of access path has been chosen by DB2 (in a complex request involving several tables, you can have a combination of access strategies).

Before every set of comparisons, you must stop and start the databases to be sure that they do not already exist in buffers, that will lead to wrong results.

Conclusions

Impact on space​

The savings on disk occupation due to INFOPAK are the easiest to justify. They are the most important, because they are permanent. Space is saved 24 hours a day!

Immediate benefits​

You must add absolute value figures of space savings not only for production tables but also for test tables, information center tables, full and incremental copies or work tables.

These savings can immediately be converted into dollars for permanent storage (disks) or temporary storage (disks or tapes).

Future benefits​

If you know the annual rate of growth for your tables (or the future production volume if you start applications with DB2), you can forecast the benefits for the next few years.

Impact on elapsed time​

As compression saves disk space, it also saves time as fewer pages are read from external storage to main storage.

With INFOPAK, elapsed times are usually shorter. The consequences are:

  • shorter stop time for TP applications,

  • shorter processing times for batch programs,

  • shorter times for security processing (image copies)

  • easier operations.

Impact on CPU time​

Real time​

As in real time, you ordinarily read only one row at a time (or a small number of rows in a table), you will not see significant a increase due to compression.

Batch processing​

As batches usually read a great number of rows, it is obvious that these rows have to be decompressed, and that the CPU time will probably increase when you add INFOPAK.

To measure the impact, you must add: absolute values (ie seconds) and not percentages, that have no meaning (a program that only reads a table without processing will yield a greater CPU increase in percentage than a program that does a lot of things, decompressing the same rows).

You must also look at the time when a CPU increase is noticed: if a program running during the night spents less elapsed time and more CPU time, is CPU increase relevant as the overall balance is positive.

Impact on other resources​

The other benefits drawn from INFOPAK are:

  • lower channel load as I/O is decreased,

  • better buffer occupation and a greater probability of finding a requested page in the buffer.

INFOPAK references

North America​

  • Alverno (Beech Grove)

  • American General Services Company (Houston)

  • Ameritech (Brookfield)

  • Ameritech (Chicago)

  • Ameritech (Waukesha)

  • Army/Air Force Exchange Service (Dallas)

  • Associated Bureau (Minneapolis)

  • BC Hydro and Power Authority (Vancouver) Canada

  • Bellsouth Services (Atlanta)

  • Bellsouth Services (Birmingham)

  • Beth Israel Hospital (Boston)

  • Blue Cross / Blue Shield of Florida (Jacksonville)

  • Blue Cross / Blue Shield of Massachussets (Boston)

  • Blue Cross / Blue Shield of Michigan (Detroit)

  • Blue Cross / Blue Shield of Nebraska (Omaha)

  • Blue Cross / Blue Shield of New Jersey (Florham Park)

  • Blue Cross of North Carolina (Durham)

  • Blue Cross of South Carolina (Columbia)

  • Boeing Computer Services (Seattle)

  • Boise State University (Boise)

  • California State Lottery (Sacramento)

  • Canadian Tire Acceptance Ltd (Welland) Canada

  • City of New York (Brooklyn)

  • Clark Information Technology (South Bend)

  • Coca-Cola Company (Atlanta)

  • Community Mutual Insurance Company (Cincinnati)

  • CoreStates Financial Corporation

  • County of Los Angeles (Los Angeles)

  • Deere & Company (Chicago)

  • Depository Trust Company (New York)

  • Financial Information Services Agency (New York)

  • Fisher Camuto (Stamford)

  • GAB Services (New Jersey)

  • General American Life (St Louis)

  • General RE Services (Stamford)

  • Global Travel Company (Toronto) Canada

  • GTE (Tampa)

  • Holiday Inn (Memphis)

  • Imperial Oil Limited (Willowdale) Canada

  • Independance Blue Cross (Philadelphia)

  • Indiana University (Indianapolis)

  • Iowa Public Service Company (Sioux City)

  • Kay Bee toy and Hobby Shops (Pittsfield)

  • Kentucky Utilities (Lexington)

  • L'industrielle Service Technique (Montreal) Canada

  • Lakeland Regional Medical Center (Lakeland)

  • Manufacturers Hanover (New York)

  • Manulife Financial (Toronto) Canada

  • Mc Donnell Douglas (Long Beach)

  • MCI Telecommunications (Rancho Cordova)

  • MCI Telecommunications (Rockville)

  • Michigan Bell (Southfield)

  • National Wildlife (Vienna)

  • North Carolina Farm Bureau Insurance Company (Raleigh)

  • Northeast Utilities (Nethersfield)

  • Northwest Pipeline (Tulsa)

  • Palomar Pomerado Health Systems (Escondido)

  • Parker Hannifin Corporation (Cleveland)

  • Reliance Insurance Company (Philadelphia)

  • Republic Financial Services (Dallas)

  • Reynolds Metals (Richmond)

  • Rolm Systems (Santa Clara)

  • Southwestern Public Service Company (Amarillo)

  • St Johns Hospital (Roseville)

  • State Farm Insurance (Chicago)

  • State University of New York (Stony Brook)

  • Sunlife of Canada (Willowdale) Canada

  • The Glidden Company (Research Triangle Park)

  • Travelers Insurance Company (Hartford)

  • Tucson Medical Center (Tucson)

  • United Data Services (Apopka)

  • United States Steel Corporation (Pittsburgh)

  • University Hospital (North Carolina)

  • US Sprint (Apopka)

  • US Sprint (Irving)

  • US Sprint (Overland Park)

  • Wisconsin Public Service (Greenbay)

  • Workers Compensation Board (Vancouver) Canada

France​

Financial establishments​

  • BRED

  • Banque INDOSUEZ

  • Banque LA HENIN

  • Banque Nationale de Paris

  • Banque Neuflize Schlumberger Mallet

  • Banque Paribas

  • Banque Populaire Région Nord de Paris

  • Banque Populaire du Midi

  • Banque Populaire du Nord

  • Banque SOFINCO

  • Banque WORMS

  • CASDEN

  • CEDICAM Lyon

  • CEDICAM Saint Quentin en Yvelines

  • CETELEM

  • CIC

  • CIO

  • CRCAM Brunoy (SCT)

  • CRCAM Sud-Méditerranée

  • CRCAM de l'Ile de France

  • CRCAM de l'Ille et Vilaine

  • CRCAM de l'Indre

  • CRCAM de l'Isère

  • CRCAM de la Gironde

  • CRCAM de la Haute-Savoie

  • CRCAM de la Haute-Vienne

  • CRCAM de la Loire

  • CRCAM de la Loire Atlantique

  • CRCAM de la Somme

  • CRCAM des Bouches du Rhône

  • CRCAM du Loiret

  • CRCAM du Lot

  • CRCAM du Lot et Garonne

  • CRCAM du Maine et Loire

  • CRCAM du Midi

  • CRCAM du Sud-Est

  • Caisse Centrale des Banques Populaires

  • Caisse d'Epargne de Bordeaux

  • Caisse d'Epargne de Nancy

  • Caisse d'Epargne de Paris

  • Caisse d'Epargne de Rennes

  • Caisse d'Epargne de Rouen

  • Caisse d'Epargne de Seclin

  • Caisse d'Epargne de Toulouse

  • Caisse d'Epargne de l'Isle Adam

  • Crédit Commercial de France

  • Crédit Lyonnais

  • Crédit Mutuel de Bretagne

  • Crédit Mutuel de Strasbourg

  • Crédit Mutuel du Sud Ouest

  • Crédit du Nord

  • DIAC

  • FRANFINANCE

  • GIE AMT

  • GIE ARMONI

  • GIE INFOROUEST

  • GIE LIVE

  • GIE LOGITAINE

  • GIE TECH-INFOR

  • Informatique CDC

  • LOCAFRANCE

  • SFAC

  • SICOVAM

  • SNVB

  • SOVAC

  • Société Lyonnaise de Banque

  • Société Marseillaise de Crédit

Insurance companies​

  • AGRR

  • AXA Assurances

  • CCMSA

  • CIPC

  • CNPBTP

  • Caisse Sociale de Monaco

  • GAN

  • GENACORP - GIE

  • GFA

  • GIA

  • GPA

  • GREPAC

  • GROUPAMA

  • Groupe AZUR

  • Groupe MALAKOFF

  • Groupe MORNAY

  • ICS (La Concorde)

  • ISICA

  • L'Alsacienne d'Assurances

  • MACSF

  • MATMUT

  • Mutualité Française

  • Mutuelles du Mans

  • PFA

  • SMABTP

  • UAP

  • UNI EUROPE

  • VIA Assurances

  • WINTERTHUR Assurances

Other companies​

  • AIR FRANCE

  • AIR LIQUIDE

  • ALSTHOM Belfort

  • ALSTHOM Saint Nazaire

  • ASCINTER OTIS

  • AUTOMOBILES CITROEN

  • AUTOMOBILES PEUGEOT

  • CALBERSON

  • Compagnie Générale des Eaux

  • ESSO SAF

  • GENERAL ELECTRIC

  • GIE GETEXINFO

  • GRACE

  • Groupement Inter Assedic IDF1

  • HOECHST

  • LA REDOUTE

  • LEGRAND

  • NMPP

  • QUELLE

  • RANK XEROX

  • ROUSSELOT

  • Régie RENAULT

  • SEXTANT AVIONIQUE (Valence)

  • SEXTANT AVIONIQUE (Vélizy)

  • SFNGR (Nouvelles Galeries)

  • SITB

  • SNAT (France Telecom)

  • THOMSON Issy les Moulineaux

  • THOMSON Malakoff

  • TURBOMECA

Germany​

  • AGRIPPINA VERSICHERUNGS AG (Köln)

  • ALLIANZ LEBENSVERSICHERUNGS-AG (Stuttgart)

  • ALLIANZ LEBENSVERSICHERUNGS-AG (Hannover)

  • ALLIANZ VERSICHERUNG-AG (München)

  • ALLIANZ VERSICHERUNG-AG (Stuttgart)

  • ALLIANZ VERSICHERUNG-AG (Frankfurt)

  • ALLIANZ VERSICHERUNG-AG (Köln)

  • ALLIANZ VERSICHERUNG-AG (Hamburg)

  • ANDREAS STIHL (Waiblingan)

  • ARMY AND AIR FORCE (München)

  • BAYERISCHE LANDESBANK (München)

  • BAYERISCHE LANDESBAUSPARKASSE (München)

  • BREUNINGER GMBH & CO. (Stuttgart)

  • DEUTSCHE BANK AG (Düsseldorf)

  • DEUTSCHE BANK AG (Eschborn)

  • DEUTSCHE BANK AG (Hamburg)

  • DRESDNER BANK AG (Düsseldorf)

  • DRESDNER BANK AG (Frankfurt)

  • DRESDNER BANK AG (Hamburg)

  • DRESDNER BANK AG (München)

  • GAD-GESELLSCHAFT FÜR AUTOMATISCHE DV EG (BIK) (Münster)

  • HELVETIA VERSICHERUNGEN (Frankfurt)

  • LVM VERSICHERUNGEN (Münster)

  • RBG RZ BAYERISCHER GENOSSENSCHAFTEN EG (BIK) (München)

  • RBG RZ BAYERISCHER GENOSSENSCHAFTEN EG (BIK) (Nürnberg)

  • RWG GMBH, DATENVERARBEITUNGSGESELLSCHAFT (BIK) (Stuttgart)

  • SKF GMBH (Schweinfurt)

  • STADTSPARKASSE (München)

  • TELENORMA (Frankfurt)

Belgium​

  • AMEV

  • ASSUBEL

  • AXA Belgium

  • Banque NAGELMACKERS

  • COCKERILL

  • Crédit Communal de Belgique

  • ELECTRABEL

  • Générale de Banque

  • LA ROYALE BELGE

  • Ministère de L'économie

  • NRB

  • UAP

  • UNMS

  • WINTERTHUR

Switzerland​

  • United Overseas Bank

  • MIGROS Coopérative

England​

  • BAYER

  • EMPIRE STORES

  • Automobiles PEUGEOT

  • RANK XEROX

Spain​

  • Barclays Bank

  • Fimestic S.A.

  • Rank Xerox

  • Robert Bosch S.A

  • Sintel S.A.

  • UAP

Italy​

  • UAP

  • Winterthur

Japan​

  • Daiwa Seiko(Tokyo)

  • Kawasaki Steel(Tokyo)

  • Kobe steel Kakogawa (Tokyo)

  • Kobelco Systems Corporation (Tokyo)

  • Meiji Milk Products (Tokyo)

  • Recruit Company Ltd (Tokyo)

  • Sanwa Bank(Tokyo)

  • Sumimoto Metal Ltd (Tokyo)

Australia​

  • BHP Information Technology

  • BP Australia Limited

  • Composite Buyers Limited

  • Fujitsu Australia Limited

  • National Mutual Computer Services Pty. Ltd.

  • QANTAS Airlines

  • Repco Information System

  • VicRoads

Argentina​

  • BASF

  • Banco de GALICIA

  • RENAULT

  • SIEMENS

New Zealand​

  • Air New Zealand

  • EDS(New Zealand)

Korea​

  • Inchon Iron & Steel(Seoul)

  • Hyundai

Israel​

  • Oil Refineries

Brazil​

  • Brasinpar Ltda