Install the Repository

The Repository (gtrep) is a database for storing  data. The Repository is necessary for TDM to function.
tdm45
The Repository (
gtrep
) is a database for storing 
Test Data Manager
data. The Repository is necessary for TDM to function.
You can install the repository in one of the following ways:
  • Use the repository installer utility.
  • Use native database management capabilities.
 We recommend that you use the database installer utility to install the repository.
Use your
database
credentials to install the repository. Do not use your Windows credentials.
Use the Database Installer Utility to Install the Repository
The database installer utility provides an automated method for installing various 
Test Data Manager
 databases, including the repository. The utility is available in the repository installation kit that you download as a part of the product image.
  1. Download and extract the files in the repository installation kit.
  2. Open a command prompt, and navigate to \DB-install-kit-<
    version>
    \ca-tdm-db-install-kit
    .
  3. Run a command similar to the following using valid values for your database server:
    Microsoft SQL Server
    ca-tdm-db-installer install --dbname gtrep --dbms sqlserver --server
    mysqlserver.acme.com
    --dbmsuser sa --dbmspassword 
    gtsecret123
    --dbuser gtrep --dbpassword
    12ABcd!%
    Oracle
    ca-tdm-db-installer install --dbname gtrep --dbms oracle --server
    myoracle.acme.com
    --dbmsuser system --dbmspassword
    gtsecret123
    --servicename 
    myoracle.acme.com
    --dbuser gtrep --dbpassword
    12ABcd!%
    The tool installs the main repository database on the database server that you specify. In this example, it installs the database with a new user gtrep. The --force flag is not set, so if the database already exists, the tools fails and returns
    1
    . You can use the --testdb command to test for database existence first.
Database Installer Utility Command Line Syntax
The following parameters are available when you use the install action:
ca-tdm-db-installer install
  [--server server[\instance]]
  [--dbmsuser name ] [--dbmspassword pw]
  [--dbname <name of db to install> ]
  [--dbuser name] [--dbpassword pw]
  [--force ]
  [--servicename <oracle service name>]  [--port n ]
  [--role <login role>] [--tnsname <tns name>]
  [-? -h help ?  ]
server
Specifies the server name where you want to install the repository. If you omit this property, the installer uses the local host by default.
dbms
Specifies the database type for the repository.
Values:
 oracle, sqlserver.
dbmsuser, dbmspassword
Specifies a user with administrative rights to access the database where you want to install the repository. If you omit this property, the installer attempts to log in to the database with the current user privileges.
Example:
If you configure Microsoft SQL Server to use Windows authentication while logged in as an administrator, the current user has the required privileges. Enter the password for this user in the dbmspassword property.
 Unified login only works for Microsoft SQL Server and not Oracle.
 For Oracle, verify that the specified
dbmsuser
has the following roles and privileges:
Roles
  • SELECT_CATALOG_ROLE
System privileges
  • ALTER ANY PROCEDURE
  • ALTER ANY TABLE
  • CREATE ANY INDEX
  • CREATE ANY PROCEDURE
  • CREATE ANY SEQUENCE
  • CREATE ANY TABLE
  • CREATE ANY VIEW
  • CREATE SESSION
  • CREATE USER
  • DROP USER
  • GRANT ANY PRIVILEGE
  • GRANT ANY ROLE
  • INSERT ANY TABLE
  • LOCK ANY TABLE
dbuser, dbpassword
Specifies the user name for the database to be created. Enter the password for this user in the dbpassword property. For Oracle, the dbuser user is usually the same as the database name, but need not be. In fact, several repositories may be installed under different user names.
 Microsoft SQL Server and Oracle users differ as follows
  • In Microsoft SQL Server, a user is an independent entity which can access databases depending on permissions granted by the dba. For example, fred and jim are users and gtrep1 and gtrep2 are databases.
  • In Oracle, a user owns tables and other entities. Users and schemas are synonymous. For example, users fred and jim can both own separate repository tables.
dbname
Specifies the name of the directory in the kit that contains the repository, which also becomes the name of the TDM repository. The name of the repository folder in the kit is
gtrep
.
For SQL Server installations (i.e. where the value of --dbms is
sqlserver
), this value must be
gtrep
.
force
Drops and re-installs the database if the database already exists. Omit this property unless you upgrade or replace an existing repository.The force option must never be used to upgrade an existing repository because its purpose is to destroy the existing one and replace it. The installer does not support upgrade. Upgrade your repository through Datamaker.
tnsname 
(Oracle only) Specifies the tns name of the Oracle database. This is used as an alternative to server, service and port.
role 
(Oracle only) Specifies the role that the Oracle user is to take. If the user logs as system then this can be omitted as system already has the necessary roles. If  the user logs as some other user, then specify --role sysdba. 
servicename
 
(Oracle only) Specifies the target Oracle database service name.
port
Specifies the port for the target Oracle or Sqlserver database:
Default: 
1521 (Oracle)
Default: 
1433 (Microsoft SQL Server)
Install the Repository Manually
Install the Repository Manually for Microsoft SQL Server
You can install the repository for SQL Server manually using a SQL Server database restore operation.
Before you start, ensure that you have an installed Microsoft SQL Server and that you have privileges to create the following items:
  • A database
  • Tables
  • Views
  • Functions
  • Procedures
  • Indexes
  • Primary and foreign keys 
  • Constraints
Also ensure that you have access to Microsoft SQL Server Management Studio or Enterprise Manager.
By default, when recreating the GTREP repository from backup, only the SA user and other authorized database users have access. To create a user named GTREP, copy the contents of the SQL script file gtrep_user.sql saved in CATDM-DBInstaller\artifacts\DB-install-kit\SQL_Server_Install_Kit\Databases\DM_Repository directory and change the password as appropriate before executing the query in SQL Server Management Studio.
You can change these credentials in the user creation script that you run in Step 10, or ask your database administrator to create a separate login for the repository.
  1. Download and extract the files in the repository installation kit.
  2. Navigate to the DB-install-kit-
    version
    \SQL_Server_Install_Kit\Databases\DM_Repository directory, and copy the gtrep.bak file to your SQL Server file system backup directory.
    Example:
    C:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup
  3. Open Microsoft SQL Server Management Studio.
  4. Right-click
    Databases
    and select
    Restore Database
    .
    The Restore Database dialog opens.
  5. Select gtrep in the
    To Database
    field as the database to restore to.
  6. Select
    From Device
    , click the button to the right of this option, select File as the Backup Media. Click
    Add
    .
    A file selection dialog opens.
  7. Select the gtrep.bak file that you copied into the Backup directory, click
    Ok
    , and click
    Ok
    on the next dialog.
  8. Ensure that the
    Restore
    check box is selected on the
    Restore Database
    dialog, and click
    Ok
    .
    SQL Server Management Studio restores the gtrep database. The repository is now installed.
  9. Expand Databases in SQL Server Management Studio and verify that the gtrep database exists.
  10. Return to the DB-install-kit-
    version
    \SQL_Server_Install_Kit\Databases\DM_Repository directory and run the gtrep_user script in SQL Server Management Studio.
    The GTREP user is created for the repository.
 Other databases are provided in the SQL_Server_Install_Kit\Databases directory that provide optional source and target databases for testing. Install these the same way you installed the repository.
Install the Repository Manually for Oracle
You can install the repository for Oracle manually.
Before you start, ensure that you have an installed Oracle, SQL Plus (sqlplus), and Data Pump Import (impdmp) and that you have privileges to create the following items:
  • A database
  • Tables
  • Views
  • Functions
  • Procedures
  • Indexes
  • Primary and foreign keys 
  • Constraints
Install the Oracle Repository on Windows
This section describes how to configure and install the repository on an Oracle database on a Windows system.
Oracle Windows Repository Requirements
A mismatch of Oracle versions is a common problem during the initial setup of the repository on Windows. Use the following steps to check for an Oracle instance installed on the path:
  • From a command prompt, issue the command PATH. Look for an existing Oracle in the path variable.
  • Issue a sqlplus command and see if you are prompted for a username and password.
  • Issue a tnsping command to see if you can ping an existing Oracle instance.
You must meet the following requirements for an Oracle Windows repository:
  • Test Data Manager
     requires a 32-bit client called Oracle Instant Client. This is provided with the product installation.
  • Modify the TNSNAMES.ORA file to include the databases that you plan to connect to. Alternatively, you can use EZConnect strings (//server:port/dbservice).
  • A 32-bit client is required for ODBC. The manager for this is located in %windir%\sysWOW64.
  • DB2 Connect on 64-bit sets up ODBC sources that do not work with
    Test Data Manager
    . These sources must be recreated with a unique name using %windir%\syswow64\odbcad32.exe, instead of %windir%\system32\odbcad32.exe. After you create the source,  move over all of the advanced settings.
  • In some cases, the sqlnet.ora file needs to be updated to work with the installation batch scripts. Locate and open the sqlnet.ora file in the oracle directory. Change the SQLNET.AUTHENTICATION_SERVICES = (NTS) to SQLNET.AUTHENTICATION_SERVICES = (NONE).
Installation
Complete the following steps to install the repository on an Oracle Windows machine. You must be logged in as an administrator to complete these steps.
  1. Download and extract the files in the repository installation kit, and move the files to the repository system.
  2. Navigate to the DB-install-kit-
    version
    \Oracle_Server_Install_Kit\Databases\DM_Repository directory, right-click the repository.bat file and select Edit.
  3. Change the following variables as needed, and save the file:
    • GT_SYS_USER
      Specifies the system user for the Oracle instance.
    • GT_SYS_PASSWORD
      Specifies the password for the Oracle system user.
    • GT_TNS
      Specifies the TNS alias for the target database.
    • GT_REP_USER
      Specifies the repository user name.
    • GT_REP_USER_UC
      Specifies the repository user name in uppercase.
    • GT_REP_PASSWORD
      Specifies the repository user password.
  4. Run the repository.bat file.
    The utility creates the repository database in the Oracle instance.
  5. Check the repository.log file to verify that the repository was created successfully.
 Other databases are provided in the Oracle_Install_Kit\Databases directory that provide optional source and target databases for testing. Install these the same way you installed the repository.
Install the Oracle Repository on UNIX/Linux
Complete the following steps to install the repository on an Oracle UNIX/Linux system.
  1. Download and extract the files in the repository installation kit, and move the files to the UNIX/Linux repository system.
  2. Verify that the oracle user has full permissions to the installation kit directory. Use a command such as the following to grant the permissions if necessary:
    chmod 777 /home/DM_repository
  3. Determine the same user name and password information that is described in Step 3 of the Windows procedure.
    You will require to substitute these values for variables in the ensuing commands.
  4. Navigate to the DM_Repository directory from the UNIX/Linux terminal.
  5. Run the following command:
    sqlplus ${GT_SYS_USER}/${GT_SYS_PASSWORD}${GT_TNS}  @gtrep.ddl  ${GT_REP_USER}  ${GT_REP_USER_UC}  /home/DM_repository
  6. Run the following command:
    sqlplus ${GT_SYS_USER}/${GT_SYS_PASSWORD}${GT_TNS}  @grants.ddl  ${GT_REP_USER}  ${GT_REP_USER_UC}
  7. Run the following command:
    impdp ${GT_SYS_USER}/${GT_SYS_PASSWORD}${GT_TNS}  remap_schema=gtrep:${GT_REP_USER} directory=GTPUMP  dumpfile=gtrep.dmp
  8. Run the following command:
    sqlplus ${GT_REP_USER}/${GT_REP_PASSWORD}${GT_TNS} @compile.sql
  9. Run the following command:
    sqlplus ${GT_REP_USER}/${GT_REP_PASSWORD}${GT_TNS}
  10. Run the following query from the SQL Plus prompt:
    select count(*) from gtrep_data;
You can also check the connection to the Oracle database server from a remote machine with the Oracle client. For example, assuming that the UNIX/linux system name is oracle_server_1, then run the following command:
sqlplus system/[email protected]_server_1
If a connection is made, this should show a command prompt.
 Other databases are provided in the Oracle_Install_Kit\Databases directory that provide optional source and target databases for testing. Install these the same way you installed the repository.