Tuning Repository Performance

Repository Analyze

The PowerCenter repository has more than 170 tables, and almost all tables use at least one index to speed up queries. Most databases keep and use column distribution statistics to determine which index to use to execute SQL queries optimally. Database servers do not update these statistics continuously.
 
In frequently used repositories, these statistics can quickly become outdated, and SQL query optimizers may not choose the best query plan. In large repositories, choosing a sub-optimal query plan can have a negative impact on performance. Over time, repository operations gradually become slower.
 
PowerCenter identifies and updates the statistics of all repository tables and indexes when you copy, upgrade, and restore repositories. You can also update statistics using the pmrep UpdateStatistics command.


Syntax: pmrep UpdateStatistics

Increasing Repository Copy, Backup, and Restore Performance

Large repositories may contain a lot of log and history information that slows down repository performance. You have the option to skip the information that is not essential to the function of the repository. When you back up, restore, or copy a repository, you can choose to skip the following types of information:

• Workflow and session logs  
• Deploy group history 
• MX data 

By skipping this information, you reduce the time it takes to copy, back up, or restore a repository.
You can also skip this information when you use the pmrep commands.