Transcript
Informatica 9.0: Database Requirements
© 2009 Informatica Corporation
Abstract This article describes the requirements for the domain configuration and Model repository databases in Oracle, IBM DB2, and Microsoft SQL Server.
Supported Versions y
Informatica Data Services 9.0
y
Informatica Data Quality 9.0
Overview Informatica application services store metadata in relational database repositories. The domain stores configuration information in a domain configuration database. Before you install Informatica, you must set up the databases and database user accounts for the domain configuration and repositories. The databases for the domain configuration and the Model repository have the same configuration and user account requirements.
Database Configuration Requirements Each database system has its own requirements for the domain configuration and Model repository databases and user accounts. If you do not configure the database with these properties, you can encounter problems such as deployment failures or metadata inconsistencies when you run Informatica services. For more information about configuring the database, see the documentation for your database system. Database commands that change the configuration of a database or database system require DBA privileges. Use the following guidelines when you set up the databases and user accounts: y
The databases must be accessible to all gateway nodes in the Informatica domain.
y
The domain configuration can be in the same database schema as the PowerCenter, Metadata Manager, or Data Analyzer repositories. Likewise, the Model repository can be in the same database schema as the PowerCenter, Metadata Manager, or Data Analyzer repositories. However, the domain configuration and the Model repository cannot be in the same database schema.
Oracle Database Requirements Use the following guidelines when you set up an Oracle database: y
y
Set the following configuration parameter: Parameter
Value
open_cursors
1000
The database user account must have the CONNECT and RESOURCE permissions.
IBM DB2 Database Requirements Use the following guidelines when you set up an IBM DB2 database: y
2
On the IBM DB2 instance where you create the database, set the following parameters to ON: -
DB2_SKIPINSERTED
-
DB2_EVALUNCOMMITTED
y
-
DB2_SKIPDELETED
-
AUTO_RUNSTATS
On the database, set the following configuration parameters: Parameter
Value
applheapsz
8192
appl_ctl_heap_sz
8192
logfilsiz
4000
DynamicSections
1000
maxlocks
98
locklist
50000
auto_stmt_stats
ON Note: DB2 version 9.5 only
y
The tablespace pageSize parameter must be set to 32768. In a single-partition database, specify a tablespace that meets the pageSize requirements. If you do not specify a tablespace, the default tablespace must meet the pageSize requirements. In a multi-partition database, you must specify a tablespace that meets the pageSize requirements.
y
For DB2 version 9.1, generate the database statistics for the DB2 optimizer after you create content in the database. Update the statistics for the domain configuration database after installation. Update the statistics for the Model repository after you create the Model Repository Service and add content to the repository. Use the following command: REORGCHK UPDATE STATISTICS on SCHEMA SchemaName
y
The database user account must have the CREATETAB and CONNECT permissions.
Microsoft SQL Server Database Requirements Use the following guidelines when you set up a Microsoft SQL Server database: y
Set the read committed isolation level to READ_COMMITTED_SNAPSHOT to minimize locking contention. To set the isolation level for the database, run the following command: ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON
To verify that the isolation level for the database is correct, run the following command: SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DatabaseName
If the isolation level is READ_COMMITTED_SNAPSHOT, the command returns 1. y
The database user account must have the CONNECT and CREATE TABLE permissions.
Author Marissa Johnston Staff Technical Writer
Acknowledgements Thanks to Anthony Kilman and Darren Lo for help in completing this article.
3
