PowerCenter Repository Database Requirements
A PowerCenter repository is a collection of database tables containing metadata. A PowerCenter Repository Service manages the repository and performs all metadata transactions between the repository database and repository clients.
The PowerCenter repository supports the following database types:
- •IBM DB2 UDB
- •Microsoft SQL Server
- •Microsoft Azure SQL Database
- •Oracle
- •PostgreSQL
- •Sybase ASE
Note: To install the PowerCenter Repository Service with the 10.4.0 installer, you can use the Oracle, Microsoft SQL Server, or the PostgreSQL database. If you want to install the PowerCenter Repository Service on any of the other databases, you create the service with the required database after you run the installer.
Allow 35 MB of disk space for the database.
Note: Ensure that you install the database client on the machine on which you want to run the PowerCenter Repository Service.
For more information about configuring the database, see the documentation for your database system.
IBM DB2 Database Requirements
Use the following guidelines when you set up the repository on IBM DB2:
- •To optimize repository performance, set up the database with the tablespace on a single node. When the tablespace is on one node, PowerCenter Client and PowerCenter Integration Service access the repository faster than if the repository tables exist on different database nodes.
Specify the single-node tablespace name when you create, copy, or restore a repository. If you do not specify the tablespace name, DB2 uses the default tablespace.
- •Informatica does not support IBM DB2 table aliases for repository tables. Verify that table aliases have not been created for any tables in the database.
Microsoft SQL Server Database Requirements
Use the following guidelines when you set up the repository:
- •Set the database server page size to 8K or higher. This is a one-time configuration and cannot be changed afterwards.
- •Verify that the database user account has the CONNECT, CREATE TABLE, and CREATE VIEW privileges.
Oracle Database Requirements
Use the following guidelines when you set up the repository on Oracle:
- •Set the storage size for the tablespace to a small number to prevent the repository from using an excessive amount of space. Also verify that the default tablespace for the user that owns the repository tables is set to a small size.
The following example shows how to set the recommended storage parameter for a tablespace named REPOSITORY:
ALTER TABLESPACE "REPOSITORY" DEFAULT STORAGE ( INITIAL 10K NEXT 10K MAXEXTENTS UNLIMITED PCTINCREASE 50 );
Verify or change the storage parameter for a tablespace before you create the repository.
- • Verify that the database user has the following privileges:
- - CREATE SEQUENCE
- - CREATE SESSION
- - CREATE SYNONYM
- - CREATE TABLE
- - CREATE VIEW
- •Informatica does not support Oracle public synonyms for repository tables. Verify that public synonyms have not been created for any tables in the database.
Sybase ASE Database Requirements
Use the following guidelines when you set up the repository on Sybase ASE:
- •Set the database server page size to 8K or higher. This is a one-time configuration and cannot be changed afterwards.
- •Set the Sybase database option "ddl in tran" to TRUE.
- •Set "allow nulls by default" to TRUE.
- •Verify the database user has CREATE TABLE and CREATE VIEW privileges.
- • Set the database memory configuration requirements.
The following table lists the memory configuration requirements and the recommended baseline values:
Database Configuration | Sybase System Procedure | Value |
|---|
Number of open objects | sp_configure "number of open objects" | 5000 |
Number of open indexes | sp_configure "number of open indexes" | 5000 |
Number of open partitions | sp_configure "number of open partitions" | 8000 |
Number of locks | sp_configure "number of locks" | 100000 |
PostgreSQL Database Requirements
Use the following guidelines when you set up the repository on PostgreSQL:
- •Verify that the database user account has CREATE TABLE and CREATE VIEW privileges.
- •Ensure that PostgreSQL has sufficient disk space for the data files. By default, the data files are present in the following location:
<PostgreSQL installation directory>/data
- •On the database, set the configuration parameters.
The following table lists the minimum and recommended values for the configuration parameters that you must set:
Parameter | Minimum Value | Recommended Value |
|---|
max_connections | 200 | 4000 |
shared_buffers | 2 GB | 16 GB |
max_locks_per_transaction | 1024 | 4000 |
- •To install PostgreSQL database for the PowerCenter repository, set values for the PostgreSQL database host, port, and service name for the pg_service.conf file in the following format:
[PCRS_DB_SERVICE_NAME]
host=Database host IP
port=Database port
dbname=PowerCenter Repository Service database service name
To securely connect to PostgreSQL for the PowerCenter repository, set the sslmode to require along with the remaining required database properties in the pg_service.conf file in the following format: sslmode=require
- •Set the PGSERVICEFILE environment variable to the location of the pg_service.conf file in the Informatica installation directory.