Table of Contents

Search

  1. Preface
  2. Performance Tuning Overview
  3. Bottlenecks
  4. Optimizing the Target
  5. Optimizing the Source
  6. Optimizing Mappings
  7. Optimizing Transformations
  8. Optimizing Sessions
  9. Optimizing Grid Deployments
  10. Optimizing the PowerCenter Components
  11. Optimizing the System
  12. Using Pipeline Partitions
  13. Appendix A: Performance Counters

Performance Tuning Guide

Performance Tuning Guide

Optimizing the Database Schema

Optimizing the Database Schema

You can improve repository performance on IBM DB2 and Microsoft SQL Server databases when you enable the Optimize Database Schema option for the Repository Service in the Administration Console. The Optimize Database Schema option causes the Repository Service to store varying length character data in Varchar(2000) columns instead of CLOB columns wherever possible. Using Varchar(2000) columns improves repository performance in the following ways:
  • Reduced disk access.
    The PowerCenter repository stores Varchar data directly in columns within a database table. It stores CLOB data as references to another table. To retrieve CLOB data from the repository, the Repository Service must access one database table to get the reference, and then access the referenced table to read the data. To retrieve Varchar data, the Repository Service accesses one database table.
  • Improved caching.
    The repository database buffer manager can cache Varchar columns, but not CLOB columns.
Optimizing the database schema can improve repository performance for the following operations:
  • Backing up a repository
  • Restoring a repository
  • Exporting repository objects
  • Listing dependencies among objects
  • Deploying folders
In general, performance improves proportionally as repository database and page sizes increase. Therefore, optimizing the database schema provides greater performance improvements in larger PowerCenter repositories.
You can optimize the database schema when you create repository contents or back up and restore an existing repository. To optimize database schema, the repository database must meet the following page size requirements:
  • IBM DB2.
    Database page size 4 KB or greater. At least one temporary tablespace with page size 16 KB or greater.
  • Microsoft SQL Server.
    Database page size 8 KB or greater.