Creating a BMC Helix Data Manager user

Before you begin

• Make sure that you have registered the source and target systems.
  See Registering source and target systems.

• If you are using a PostgreSQL database, make sure that you have installed the uuid-ossp, hstore, and plpgsql module.

  Important

  If the uuid-ossp, hstore, and plpgsql module is not present, creating a BMC Helix Data Manager user will fail, as these extensions are created by BMC Helix Data Manager Database Integration.

  1. Run the following command to check if the uuid-ossp, hstore, and plpgsql extensions are installed:

     SELECT * FROM pg_extension

  2. Check the extname column in the output.
     If the extname column contains the uuid-ossp, hstore, and plpgsql extensions, you do not need to install any extensions, and you can create the BMC Helix Data Manager user.
     If the extname column does not contain any one or all of the extensions, perform steps 3 and 4 to install the extensions.

  3. Install the modules by using any one of the following methods:

     • You can use the postgres-contrib module to install the necessary modules and extensions on your system.
       Run the following command on your PostgreSQL database server:

       yum install postgres*contrib

     • To install any of the modules individually, refer to the respective PostgreSQL or Managed database service provider documentation.

To automatically create a BMC Helix Data Manager user

1. Select Configuration > Register & Configure Systems.
2. Select the system and select Action > Create HDM Database Integration.
   
   
   
3. Enter the System user name and password.
4. (PostgreSQL only) Enter the AR Admin User Name.
   If you are using the latest hotfix version of BMC Helix Data Manager for a PostgreSQL database, you get an additional option to enter the AR Admin User Name:
   
   
   
5. Enter a HDM User Password, and click Create Database Integration.
   A progress tracker appears and then displays a success or failure message.
6. Click Close.

(Optional) Using scripts to create a BMC Helix Data Manager user

The following sections describe the procedure to create a BMC Helix Data Manager user by using scripts:

To create the user with Microsoft SQL scripts

The following Microsoft SQL scripts for manual integration are provided:

• create_hdm_server_login.sql
• drop_hdm_server_loging.sql
• drop_hdm_database_user.sql
• create_hdm_database_user_self_contained.sql
• run_as_hdm.sql

The files are stored in the <HelixDataManagerInstallDirectory>\Engine\artifacts\manual_integration\mssql folder.

Perform the following steps:

1. Use the create_hdm_server_login.sql and drop_hdm_server_loging.sql files to create and drop the Microsoft SQL Server login for the BMC Helix Data Manager user. You only need to apply this once per server.
2. Use the create_hdm_database_user_self_contained.sql and drop_hdm_database_user.sql files to drop and create the BMC Helix Data Manager user and associated objects from a specific database on a Microsoft SQL Server.
3. Run the run_as_hdm.sql file as a BMC Helix Data Manager user. This file creates BMC Helix Data Manager related objects like functions, procedures, and views for a specific database on a Microsoft SQL Server.
4. Apply these scripts to each database in the Microsoft SQL Server. The database users that these scripts create are mapped to the BMC Helix Data Manager login created above.
5. Change the USE [xxx] statement at the top of the database specific scripts to apply these to the correct database. For example, changing this to use [ARSYSTEM] will create or drop the BMC Helix Data Manager user and its objects from the ARSYSTEM database.
6. If you have multiple databases on the same server, amend the database name and apply the create_hdm_database_user_self_contained.sql and run_as_hdm.sql script to each database.

To create the user with Oracle scripts

The following Oracle scripts for manual integration are provided:

• run_as_system.sql
• run_as_hdm.sql

The files are stored in <HelixDataManagerInstallDirectory>\Engine\artifacts\manual_integration\oracle folder.

Execute the run_as_system.sql file as a user with DBA privileges. The file will drop the existing BMC Helix Data Manager user and all schema objects, create the BMC Helix Data Manager user, and apply the correct privileges.

Run the run_as_hdm.sql file as the BMC Helix Data Manager user. The file will create some BMC Helix Data Manager helper functions in the BMC Helix Data Manager schema.

To create the user with PostgreSQL scripts

The following PostgreSQL scripts for manual integration are provided:

• run_as_hdm.sql
• postgres_setup.sql
• drop_hdm_user.sql

The files are stored in <HelixDataManagerInstallDirectory>\Engine\artifacts\manual_integration\postgres folder.

Perform the following steps:

1. Run the postgres_setup.sql file as a PostgreSQL database SUPERUSER. This file creates the BMC Helix Data Manager user and grants the privileges to the created user.
2. Run the run_as_hdm.sql file as a BMC Helix Data Manager user. This file creates BMC Helix Data Manager related objects, for example, functions, procedures, views.

To remove the integration, run the drop_hdm_user.sql file as a PostgreSQL user. 

You may get the following error during integration:

ERROR: syntax error at or near "$" LINE 5: userid varchar := $(USERID); ^ SQL state: 42601 Character: 37

To resolve this error, perform the following steps:

1. Edit the postgres_setup.sql file and replace actual values for the following parameters:
   

   declare 
       userid varchar := $(USERID);
       pwd varchar := $(PWD);
       dbname varchar := $(DBNAME);
       dbschema varchar := $(DBSCHEMA);
   	aradminuser varchar := $(ARADMINUSER);

   where,
   $(USERID) - User ID of the database.
   $(PWD) - Password of the database.
   $(DBNAME) - Name of the database.
   $(DBSCHEMA) - Schema name of the database.
   $(ARADMINUSER) - If you are running the script on a Remedy/DWPC/IS database, set the required ARADMIN user.
   If you are running the script on an RDBMS system, set the value as NULL.
   

   Add the actual values in single quotes, for example - 'hdm'
   
   For example:
   

   declare 
       userid varchar := 'hdm';
       pwd varchar := 'password';
       dbname varchar := 'ARSystem';
       dbschema varchar := 'public';
    	aradminuser varchar := 'ARAdmin'; 

2. Save the file.
3. Run the postgres_setup.sql file as a PostgreSQL user again.

Where to go from here

Discovering data dictionaries