Setting up the databases for TrueSight Server Automation - Data Warehouse

Before installing TrueSight Server Automation - Data Warehouse, you must set up the required databases. The database administrator can set up the required databases.

If you want to use TrueSight Server Automation - Data Warehouse in non-English locales, see To configure TrueSight Server Automation databases for a non-English locale .

General prerequisites

• Download the product files from the  TrueSight Server Automation - Data Warehouse  BMC Electronic Product Distribution (EPD) location.

• Ensure that the database names:

  • Contain only Latin alphabet letters, numbers, and underscores (_).
  • Do not begin with a number.
  • Do not contain hyphens (-) in the names. Database names with hyphens work in TrueSight Server Automation. However, they do not work in TrueSight Server Automation - Data Warehouse.
• Ensure that the database passwords:
  
  • Do not begin with an equal sign (=) character.
  • Do not begin with an integer.
  • Do not contain spaces.

• Ensure that the data warehouse schema type aligns with the TrueSight Server Automation database in one of the following ways:

  • If you have one or more TrueSight Server Automation databases that use the CHAR schema, the data warehouse must use the CHAR schema.

  • If you have one or more TrueSight Server Automation databases that use the NCHAR schema, the data warehouse must use the NCHAR schema.

  • If you have multiple TrueSight Server Automation databases and some use the CHAR schema and others use the NCHAR schema, the data warehouse must use the NCHAR schema. 

     To determine the schema type used by the TrueSight Server Automation database, do the following:

    1. Log in to SQL Plus as any user (for example, system).

    2. Run the following command:

        select data_type from user_tab_columns where table_name = 'SYSTEM_PROPERTY' and column_name like 'NAME' 

       
       For the CHAR type schema, VARCHAR2 is returned as the output. For the  NCHAR type schema, NVARCHAR2 is returned as the output.

     To determine the schema type used by the TrueSight Server Automation - Data Warehouse database, do the following:

    1. Log in to SQL Plus as any user (for example, system).

    2. Run the following command:

       select * from user_tab_columns where table_name = 'BL_SITE' and column_name like 'NAME'

       For the CHAR type schema, VARCHAR2 is returned as the output. For the  NCHAR type schema, NVARCHAR2 is returned as the output.

• The character set of the schema type of the TrueSight Server Automation database and data warehouse must match. For example, for SQL Server database, if the TrueSight Server Automation database uses LATIN-1, the data warehouse must use the same character set or its subset character set. For Oracle database, if the TrueSight Server Automation database uses AL32UTF8, the data warehouse must use the same character set or its subset character set. 

• Users of non-English databases (such as Asian customers) must use the NCHAR character set. Users of English databases can use the CHAR  character set.

Prerequisites for SQL Server database 

Ensure that the SQL Server databases meet the following requirements:

• The SQL Server data warehouse must use the TCP/IP protocol.
• The SQL Server collation sequence for data warehouse must be case-insensitive. For example, SQL_Latin1_General_CP1_CI_AS is a case-insensitive collation sequence. TrueSight Server Automation - Data Warehouse does not support collation with a case-sensitive schema.
• The SQL Server collation sequence for the TrueSight Server Automation database and the data warehouse must be the same and be case-insensitive, regardless of whether the product is installed in an English or a non-English locale.
• SQL Server databases do not have any Unicode requirements.
• Configure the SQL Server remote query timeout by performing the following steps:
  1. On SQL Server database instance, right-click the instance name, and select Properties.
  2. In the left pane, click Connections.
  3. In the Remote server connections area, in the Remote query timeout field, enter 0 as the query timeout period.
  4. Click OK.

Prerequisites for Oracle database 

Ensure that the Oracle databases meet the following requirements:

• The recommended setting for the NLS_LENGTH_SEMANTICS parameter for the data warehouse schema is CHAR.

• If you are using NCHAR schema, you must use a Unicode value of AL16UTF16 (NLS_NCHAR_CHARACTERSET=AL16UTF16). 

   To verify that the database is Unicode, do the following:

  1. Log in to SQL Plus as any user (for example, system).

  2. Run the following command:

     select parameter, value from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET'

     Verify that a Unicode value is returned (AL32UTF8 or AL16UTF16). If a non-Unicode value is returned, create a new Unicode database and specify a Unicode value of AL32UTF8 or AL16UTF16. For instructions, see the database software documentation.

• The global_names parameter for the Oracle instance where TrueSight Smart Reporting for Server Automation is located must be set to false. This is to ensure that the database link name generated by the configuration process is resolved correctly. If the global_names parameter is not set to false, the primary site installation fails. After you install the product, you can set this parameter to true if needed.

   To determine the value of the global_names parameter, do the following:

  1. Log in to SQL Plus as sysdba user on the Oracle database instance where the TrueSight Server Automation user is located.

  2. Run the following command:

     SQL> SELECT NAME, VALUE FROM V$PARAMETER WHERE NAME = 'global_names';

• The TrueSight Server Automation database user must have Execute permission on the DBMS_JOB and DBMS_LOCK Oracle packages.

   To ensure that the TrueSight Server Automation database user has the Execute permission on these packages, do the following:

  1. Log in to SQL Plus as SYS on the Oracle database instance where the TrueSight Server Automation user is located.

  2. Run the following commands:

     Grant execute on dbms_job to <TrueSightServerAutomationDatabaseUser>

     Grant execute on dbms_lock to <TrueSightServerAutomationDatabaseUser>

     <TrueSightServerAutomationDatabaseUser> is the TrueSight Server Automation database user name.

• Ensure that the tnsnames.ora file on the data warehouse database server contains the TNS entry of the TrueSight Server Automation database instance and the TNS name matches with the Service name.

Supported scenarios for secondary TrueSight Server Automation sites

If you plan to use a system arrangement in which a secondary site uses a different character set, refer to the following scenarios for guidelines about supported setups.

The following setup is supported:

• Application Server 1 uses CHAR .
• Application Server 2 uses NCHAR .

• TrueSight Server Automation - Data Warehouse server 1 uses NCHAR .

TrueSight Server Automation - Data Warehouse server 1 cannot use CHAR because you cannot run ETL from an NCHAR (Application Server 2 database) database to a CHAR database (TrueSight Server Automation - Data Warehouse server 1 data warehouse).

Where to go from here

Now that the prerequisites are met, do one of the following: 

• Create databases and users on SQL Server
• Create tablespaces and users on Oracle