1. Analytical Application Infrastructure User Guide
  2. System Configuration and Identity Management
  3. System Configuration
  4. Database Details
  5. Adding Database Details for DB Server

Adding Database Details for DB Server

You can add a new database by specifying the name, Schema name, DB properties and connection details. Ensure that the Server Details are specified and the database is created before adding the database details.

You should not create Database details with Hive Server1 and Hive Server2 in the same setup since Hive Server 1 and Hive Server 2 drivers cannot run at the same time in the same JVM. Loading both drivers at the same time causes Hive Server 2 connection failure. This issue will be addressed in a future release of the Hive driver.

You cannot configure multiple Database details using different Hive Drivers in a single OFSAA setup. That is, multiple Data Sources using different Hive Drivers is not supported.

To add a new database:

  1. Click the Add button from the toolbar in the Database Master window.

    Figure 13-8 Database Master window


    This illustration shows the Database Master window, which displays the pre-configured database details. The window has the following panes, Database Master, DB Details, DB Property, Connection Details, and User Info. In the Database Master pane, you can specify the DB server, Name, and Schema Name. In the DB Details pane, you can specify the DB Type and Audit Type. In the BD Property, you can specify the Alias Name, Alias Type, TNS Entry String, Date Format, JDBC Connection String, JDBC Driver, and JNDI Name. In the User Info pane, displays the Created By, Last Modified By, Creation Date, and Last Modification Date.

  2. Enter the Database details as tabulated.

    The following table describes the fields in the Database Master window.

    Table 13-8 Fields in the Database Master window and their Descriptions

    Field Description
    DB Server

    Select the Database IP Address from the drop-down list.

    This list displays the database server IP address defined during the set-up.
    Name

    Enter the database Name. Ensure that there are no special characters and extra spaces.

    Note that, for Oracle database, the TNS (Transparent Network Substrate) database name should be same as SID.

    The Name should not exceed 20 characters.
    Schema Name Enter the Schema name for the database.
    DB Type

    The available options are ORACLE, MSSQL,DB2UDB, and HIVE.

    For Information Domain creation, only Oracle and Hive Database types are supported. For DI source creation, MSSQL and DB2UDB are also supported.

    You can create Hive Database instance for a single Hive server/ CDH. Multiple data sources pointing to different Hive servers are not supported.
    Auth Type

    Select the authentication typefrom the drop-down list. Based on the Database you have selected, the drop-down list displays the supported authentication mechanisms.

    Select Default for DB2UDB, ORACLE, and MSSQL databases.

    If DB Type is HIVE, then KERBEROS, KERBEROS_WITH_KEYTAB, LDAP, and Default are supported.

    If the Auth Type is configured as KERBEROS_WITH_KEYTAB for the Hive database, then you must use the Keytab file to login to Kerberos. The Keytab and Kerberos files should be copied to $FIC_HOME/conf and $FIC_WEB_HOME/webroot/conf of OFS AAAI Installation Directory.
    Connection Details
    Alias Name

    This field is not applicable for HIVE DB with Auth Type as Default.

    Select the Alias name (connection) used to access the database from the drop-down list.

    Click the Add Database icon to add a new database connection/atomic schema user. The Alias Details window is displayed.

    • Auth Alias- Enter a name for the database connection.
    • User/Principal Name- Enter the atomic schema User ID to access the database. The system authenticates the specified User ID before providing access.
    • Auth String- Enter the password required to access the database/schema. The system authenticates the specified password before providing access. The maximum length allowed is 30 characters. Special characters are not allowed.

    Note:

    If Authentication type is KERBEROS_WITH_KEYTAB, Auth String (Password) is not required. Since the Auth String is set as mandatory field, enter a dummy password.
    Auth Type Displays the Authentication Type. This field is read-only.
    TNS Entry String

    This field is applicable only for ORACLE DB with Auth Type as Default.

    TNS is the SQL*Net configuration file that defines database address to establish connection.

    Enter the TNSNAME created for the Information Domain.
    Date Format Enter the date format used in the Database server. You can find this in nls_date_format entry for the database. This date format will be used in all the applications using date fields.
    JDBC Connection String

    The default JDBC Connection String is auto populated based on the database type selected. This is the JDBC (Java Database Connectivity) URL configured by the administrator to connect to the database.

    • For ORACLE DB type it is jdbc:oracle:thin:@<<DB Server Name>>:<<Port Number>>:<<Oracle SID>>
    • For MSSQL DB type it is jdbc:microsoft:sqlserver://<<DB Server Name>>:<<Port Number>>
    • For DB2 DB type it is jdbc:db2://<<DB Server Name>>:<<Port Number>>/<<Database Name>>
    • For HIVE DB type, it is jdbc:hive2://<<DB Server Name>>:10000/default

    You need to specify the appropriate details corresponding to the information suggested in brackets. For example, in ORACLE DB you can specify the Port number as 1521 and the SID as ORCL.
    JDBC Driver Name

    The default JDBC Driver Name is auto populated based on the database type selected.

    • For ORACLE DB type it is oracle.jdbc.driver.OracleDriver.
    • For MSSQL DB type it is com.microsoft.jdbc.sqlserver.SQLServerDriver.
    • For DB2 DB type, it is com.ibm.db2.jcc.DB2Driver.
    • For Hive with Auth type as Kerberos with Keytab, it is com.cloudera.hive.jdbc4.HS2Driver.

    In case of modification, ensure that the specified driver name is valid since the system does not validate the Driver Name.

    Multiple data sources pointing to different Hive servers are not supported.
    JNDI Name

    This field is applicable and mandatory for ORACLE DB.

    Enter the JNDI Name.

    JNDI name should be entered if you want to create information domain for this DB schema. If the DB schema is for Data Sources, you can use any dummy data for this field.

    Key Tab File Name

    This field is applicable for Authentication Type selected as KERBEROS WITH KEYTAB.

    Enter the name of the Key Tab file.
    REALM File Name

    This field is applicable for Authentication Type selected as KERBEROS and KERBEROS WITH KEYTAB.

    Enter the name of the Kerberos Realm file.
    KERBEROS KDC Name

    This field is applicable for Authentication Type selected as KERBEROS.

    Enter the name of Kerberos Key Distribution Center (KDC).
    KERBEROS REALM Name

    This field is applicable for Authentication Type selected as KERBEROS.

    Enter the name of the Kerberos Realm file.
    JAAS File Name

    This field is applicable for Authentication Type selected as KERBEROS.

    Enter the name of the Java Authentication and Authorization Service (JAAS) file.
  3. Click Save to save the Database Details for DB Server.