Data Load from Oracle or DB2 database fails

Document ID : KB000021876
Last Modified Date : 14/02/2018
Show Technical Document Details

Problem:

Oracle or DB2 data loads fails during data load and this will occur if the Data Manager user name is not the same as the database schema owner name.

To avoid this problem, specify the user name and password that is the owner of the objects in the target database. If this is not possible, perform one of the following actions:

Solution:

  1. Solution for DB2

    Specify the schema name within the Data Manager user interface when adding or editing a DB2 data source, as follows:

    1. In the Data Manager user interface, select Manage Data Sources.

    2. Add a new data source or edit an existing data source.

    3. In the details section, specify the schema name in the JDBC URL(Optional) field in the following format:

      jdbc:db2://hostName:port/database_name:currentSchema=SCHEMA_NAME;

      For example,

      jdbc:db2://host1.devlab.hyperformix.com:50000/TDW:currentSchema=ITMUSER;

      Important: Be aware of the following information

      • The schema name is case sensitive and must be specified with all capital letters.

      • The semicolon at the end of the statement is required.

      • The parameter currentSchema is case sensitive as shown.

  2. Solution for Oracle

    Perform one of the following solutions.

    These solutions are listed in order of preference, with the simplest and best solution shown first.

    1. Solution 1

      Create a logon trigger at the source database that supplies the schema name when the user logs on. (The source data base is not the Data Manager database.)

      Note: This method does not alter any permissions or privileges for the user. After logging on, the user is able to use the schema objects in the target schema without needing to fully qualify them with the schema name. The user can access only objects for which read permission is granted.

      Create the trigger as follows:

      CREATE OR REPLACE TRIGGER db_logon
      AFTER logon ON DATABASE WHEN (USER = 'USER_NAME')
      BEGIN
      execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = Target_Schema';
      END;

      Note: The user name is case sensitive and must be specified with all capital letters.

    2. Solution 2

      Modify the Data Manager Data Adapter XML files and qualify the table names with schema names (schemaName.tableName). For assistance with this method, contact CA Technical Support.

    3. Solution 3

      Create public or private synonyms for the user at the source database which point to corresponding objects in the target schema. For assistance with this method, contact CA Technical Support.

  3. To connect to Data Manager (for example, from Capacity Manager or Performance Optimizer), users must provide the Data Manager schema owner user name.

    For security reasons or to be able to identify individual users via the Oracle audit logs, you might want to create read-only user accounts for business users. You must perform additional steps to enable these accounts to connect to Data Manager.

    To enable read-only user accounts to connect to Data Manager, perform one of the following solutions. These solutions are listed in order of preference, with the simplest and best solution shown first.

    1. Solution 1

      Create a logon trigger at the Data Manager database that supplies the schema name when the user logs on.

      Note: This method does not alter any permissions or privileges for the user. After logging on, the user is able to use the schema objects in the target schema without needing to fully qualify them with the schema name. The user can access only objects for which read permission is granted.

      Create the trigger as follows:

      CREATE OR REPLACE TRIGGER db_logon
      AFTER logon ON DATABASE WHEN (USER = 'USER_NAME')
      BEGIN
      execute immediate 'ALTER SESSION SET CURRENT_SCHEMA = Target_Schema';
      END;

      Note: The user name is case sensitive and must be specified with all capital letters.

    2. Solution 2

      Create public or private synonyms for the user which point to corresponding objects in the target schema.

      This is not an ideal solution because you must manage the database objects used by each application, and these objects can change between releases. For assistance with this method, contact CA Technical Support.