Data Analyzer Repository Database Requirements
The Data Analyzer repository stores metadata for schemas, metrics and attributes, queries, reports, user profiles, and other objects for the Reporting Service.
You must specify the Data Analyzer repository details when you create a Reporting Service. The Reporting Service provides the Data Analyzer repository with the metadata corresponding to the selected data source.
The Data Analyzer repository supports the following database types:
- •IBM DB2 UDB
- •Microsoft SQL Server
- •Oracle
- •Sybase ASE
Allow 60 MB of disk space for the database.
IBM DB2 Database Requirements
Use the following guidelines when you set up the repository on IBM DB2:
- •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 on Microsoft SQL Server:
- •If you create the repository in Microsoft SQL Server 2005, Microsoft SQL Server must be installed with case-insensitive collation.
- •If you create the repository in Microsoft SQL Server 2005, the repository database must have a database compatibility level of 80 or earlier. Data Analyzer uses non-ANSI SQL statements that Microsoft SQL Server supports only on a database with a compatibility level of 80 or earlier.
To set the database compatibility level to 80, run the following query against the database:
sp_dbcmptlevel ‘<DatabaseName>’, 80
Or open the Microsoft SQL Server Enterprise Manager, right-click the database, and select Properties > Options. Set the compatibility level to 80 and click OK.
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 CONNECT, RESOURCE, and CREATE VIEW privileges.
- •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.
The database for the Data Analyzer repository requires a page size of at least 8 KB. If you set up a Data Analyzer database on a Sybase ASE instance with a page size smaller than 8 KB, Data Analyzer can generate errors when you run reports. Sybase ASE relaxes the row size restriction when you increase the page size.
Data Analyzer includes a GROUP BY clause in the SQL query for the report. When you run the report, Sybase ASE stores all GROUP BY and aggregate columns in a temporary worktable. The maximum index row size of the worktable is limited by the database page size. For example, if Sybase ASE is installed with the default page size of 2 KB, the index row size cannot exceed 600 bytes. However, the GROUP BY clause in the SQL query for most Data Analyzer reports generates an index row size larger than 600 bytes.
- •Verify the database user has CREATE TABLE and CREATE VIEW privileges.
- •Set "allow nulls by default" to TRUE.
- •Enable the Distributed Transaction Management (DTM) option on the database server.
- •Create a DTM user account and grant the dtm_tm_role to the user.
The following table lists the DTM configuration setting for the dtm_tm_role value:
DTM Configuration | Sybase System Procedure | Value |
|---|
Distributed Transaction Management privilege | sp_role "grant" | dtm_tm_role, username |