Application Service Guide > Application Service Databases > Workflow Database Requirements

Workflow Database Requirements

The Data Integration Service stores run-time metadata for workflows in the workflow database. Before you create the workflow database, set up a database and database user account for the workflow database.

You specify the workflow database connection when you create the Data Integration Service.

The workflow database supports the following database types:

•IBM DB2 UDB
•Microsoft SQL Server
•Microsoft Azure SQL Database
•Oracle
•PostgreSQL

Allow 200 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 Data Integration Service.

IBM DB2 Database Requirements

Use the following guidelines when you set up the repository on IBM DB2:

•Verify that the database user account has CREATETAB and CONNECT privileges.
•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.
•Set the tablespace pageSize parameter to 32768 bytes.
•Set the NPAGES parameter to at least 5000. The NPAGES parameter determines the number of pages in the tablespace.
•Set the connection pooling parameters.

The following table lists the connection pooling parameters that you must set:

Parameter	Value
Maximum Connection Pool Size	128
Minimum Connection Pool Size	0
Maximum Idle Time	120 seconds

Microsoft Azure SQL Server Database Requirements

Use the following guidelines when you set up the repository:

• Set the allow snapshot isolation and read committed isolation level to ALLOW_SNAPSHOT_ISOLATION and READ_COMMITTED_SNAPSHOT to minimize locking contention.

To set the isolation level for the database, run the following commands:

ALTER DATABASE DatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON

ALTER DATABASE DatabaseName SET READ_COMMITTED_SNAPSHOT ON

To verify that the isolation level for the database is correct, run the following commands:

SELECT snapshot_isolation_state FROM sys.databases WHERE name=[DatabaseName]

SELECT is_read_committed_snapshot_on FROM sys.databases WHERE name = DatabaseName

•The database user account must have the CONNECT, CREATE TABLE, and CREATE VIEW privileges.

Microsoft SQL Server Database Requirements

Use the following guidelines when you set up the repository:

•Verify that the database user account has CONNECT and CREATE TABLE privileges.
•Set the connection pooling parameters.

The following table lists the connection pooling parameters that you must set:

Parameter	Value
Maximum Connection Pool Size	128
Minimum Connection Pool Size	0
Maximum Idle Time	120 seconds

Oracle Database Requirements

Use the following guidelines when you set up the repository on Oracle:

• Verify that the database user has the following privileges:

- ALTER TABLE
- ALTER VIEW
- CREATE SEQUENCE
- CREATE SESSION
- CREATE SYNONYM
- CREATE TABLE
- CREATE VIEW
- DROP TABLE
- DROP 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.
•Set the connection pooling parameters.

The following table lists the connection pooling parameters that you must set:

Parameter	Value
Maximum Connection Pool Size	128
Minimum Connection Pool Size	0
Maximum Idle Time	120 seconds

PostgreSQL Database Requirements

Use the following guidelines when you set up the repository on PostgreSQL:

•Verify that the database user account has CONNECT, CREATE TABLE, and CREATE VIEW privileges.
•Specify the database schema name when you use PostgreSQL as the database.
•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	1024
max_wal_size	1 GB	8 GB
checkpoint_timeout	5 minutes	30 minutes