Performance Tuning Guide

10.5
- 10.5.6
- 10.4.0

Back Next

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.

Rename Saved Search

Table of Contents

Performance Tuning Guide

Performance Tuning Guide

Optimizing the Database Schema

Optimizing the Database Schema