6 Registering Locations and Connections

This chapter contains the following topics:

• About Remote Locations and Connections
  To use an Oracle-technology source data system adapter (the general Oracle Tables and Views adapter and the Oracle Clinical adapters) you must create a database link and define one or more Remote Locations in the Oracle Life Sciences Data Hub user interface for each actual remote location, and define one or more Connections giving a valid username and password for each Remote Location. All passwords are encrypted in Oracle LSH.
• Defining Remote Locations
  If you need to load data into Oracle LSH from another system, you must define a Remote Location.
• Defining Connections
  A Connection provides a username and password that Oracle Life Sciences Data Hub uses to access a remote database.
• Searching for Remote Locations and Connections
  To find existing Connections, you must go to the Remote Location for which they are defined. To find existing Remote Locations, do the following:

About Remote Locations and Connections

To use an Oracle-technology source data system adapter (the general Oracle Tables and Views adapter and the Oracle Clinical adapters) you must create a database link and define one or more Remote Locations in the Oracle Life Sciences Data Hub user interface for each actual remote location, and define one or more Connections giving a valid username and password for each Remote Location. All passwords are encrypted in Oracle LSH.

A user must select both a Remote Location and Connection to define and to run an Oracle-technology Load Set.

There are two types of Connections:

• User Connections are created by users to external databases for which they have a valid username and password. Users can see and use only the User Connections they have created.
• Shared Connections are created by an administrator. They also require a valid username and password to the external database, but are available for use to any user who belongs to a user group assigned to the Adapter Area for which the Remote Location is defined.

All the users in a user group assigned to an Adapter Area have access to any Remote Locations defined for that adapter. They can then define their own User Connection based on their personal username and password on the external database, or use a Shared Connection that the administrator has defined; see Defining Connections.

For example, if a user is a member of a user group assigned to the general Oracle Tables and Views Adapter Area and the user has Read privileges on a particular Oracle database, then the user can create a Connection to the Remote Location defined for that database. The user can also use any shared Connections the administrator has defined for the same Remote Location.

The CDR_SYS_ADMIN function role is required to define Remote Locations and Connections.

You can add Remote Locations and Connections at any time.

This section contains the following topic:

• Security
  Remote Locations and Connections are defined objects with the same security requirements as other defined objects.

Security

Remote Locations and Connections are defined objects with the same security requirements as other defined objects.

To see a Remote Location and Connection during Load Set definition or execution, a user must be a member of a user group with access to the Remote Location and the Connections defined for it.

By default, Remote Locations and Connections inherit the user group assignments of their Adapter Area; see Setting Up Security for Adapters.

In addition, Oracle Life Sciences Data Hub displays user-specific Connections only to the user who created the Connection.

Note:

The tnsnames.ora file in your Oracle LSH database tier must include each remote location.

Defining Remote Locations

If you need to load data into Oracle LSH from another system, you must define a Remote Location.

A Remote Location links the following as a pair:

• One of the Oracle-technology adapters used for loading data into Oracle Life Sciences Data Hub (Oracle Tables and Views, or one of the set of Oracle Clinical adapters)
• One database from which the adapter can load data into Oracle Life Sciences Data Hub

Oracle LSH also uses Remote Locations and Connections for storing user credentials for external systems with adapters. See:

• Creating an Informatica Remote Location and Connection
• Searching for Remote Locations and Connections

To define a Remote Location:

1. Click the Remote Location subtab under the Administration tab. The Maintain Remote Locations screen opens.
2. Click Add Remote Location. The Create Remote Location screen appears.
3. Enter values in the following fields:
   • Name. Enter a name for the Remote Location. It does not necessarily need to be the same as the name of the database.

     Note:

     Do not use spaces, slashes, or special characters such as single quotes ('). If you use these characters the Remote Location you define does not appear in the list of values where it is needed
   • Description. Enter a description of the Remote Location.
   • DBLINK Prefix. The name of the database link. If another DBLINK Prefix with the same name exists in the database, the system adds an additional string to make it unique. The DBLINK_NAME is usually the global name or the TNS name of the remote database.

     Note:

     To create a database link to a database whose name is different from the Oracle LSH database, you must set the global_names parameter of both databases to FALSE. To set the parameter, do one of the following:

     • Edit the init.ora file to change the parameter value to:

       global_names=FALSE.

     • From a SQL prompt, enter:

       SQL> alter system set global_names=false;

   • Connect String. The name of the string that Oracle LSH must use in the USING clause of the create database link SQL statement.
   • Adapter. Select the Adapter type from the drop-down list.

   • Conversion Multiplier. (Optional) If you are defining a remote location for a database that uses a character set other than UTF8, you may need to enter a number such as 1.2. Oracle LSH uses this multiplier to adjust the length of VARCHAR2 data type columns in Load Set Table Descriptors.

     This may be necessary because when you transfer VARCHAR2 data from a non-UTF8 character set database to Oracle LSH, which is a UTF8 character set database, the data can be longer in the UTF8 database. For example, 60 characters of data from a Western European (WE) database might convert to 62 characters in UTF8. If you have row values equal to or near the defined column length, loading data into Oracle LSH may fail if you do not specify a conversion multiplier greater than 1.

     If you enter a multiplier of 1.2, Oracle LSH creates Table Descriptors as follows:

     Table 6-1 Example of Column Length Conversion Using a Conversion Multiplier of 1.2

     Table Columns in Non-UTF8 Database Table X	Table Columns in Table Descriptor X
     VARCHAR2(60)	VARCHAR2(72)
     VARCHAR2(10)	VARCHAR2(12)
     NUMBER(10)	NUMBER(10)

     The multiplier can be any number between 1.00 and 3.00, with a maximum of 2 decimal places. Columns of the Number and Date data types are not affected. If you do not enter a value, the system interprets it as a 1.

     Note:

     • If the value of the Conversion Multiplier causes an uploaded Table Column to have a length greater than 4000, which is the maximum length for Oracle columns of data type VARCHAR2, Oracle LSH automatically creates the column with a length of 4000. If the actual data written to the column in any job exceeds 4000 characters, the job fails and the log file gives the reason.
     • Your database NLS Length Semantics parameter must be set to BYTE. If it is set to CHAR it may automatically perform conversions of 3x. To check the setting of the parameter, do the following query in SQL*Plus:

       select * from V$NLS_PARAMETERS where parameter ='NLS_LENGTH_SEMANTICS';

       However, it is required to use character semantics. To do so, set a profile value; see Using Character Semantics for Work Area Table Installation.

     • The following types of Load Sets do not use conversion multipliers: SAS, Text, Oracle Clinical Labs, and Oracle Clinical Randomization. The length of VARCHAR2 columns in OC Labs and Randomization Load Set Table Descriptors is automatically increased.
4. In the Classification section, select the following for both the definition and the instance:
   • Subtype. Select a subtype according to your company's policies.
   • Classification Values. Specify classifications according to your company's policies, to enable users to search for the Remote Location by classification. For instructions, see "Classifying Objects" in the Oracle Life Sciences Data Hub Application Developer's Guide.
5. Click Apply to save your work. The system opens the main screen for the new Remote Location.

   You can change the security and classifications of the Remote Location through the items in the Actions drop-down list. By default, the Remote Location inherits the user group assignments of the Adapter Area.

Defining Connections

A Connection provides a username and password that Oracle Life Sciences Data Hub uses to access a remote database.

Oracle LSH encrypts the passwords.

To use a Remote Location, there must be at least one Connection defined for it. You, the administrator, can define shared Connections available to all users with normal security access to the defined Remote Location. In addition, any Oracle LSH user with an account and password on the remote location can define his or her own Connection through the Preferences link (see "Setting Preferences" in the Oracle Life Sciences Data Hub User's Guide for instructions). User-defined Connections are available only to the user who defined them.

To define a Connection:

1. In the main screen for the Remote Location for which you want to create a Connection, click Create Connection. The Create Connection screen opens.
2. Enter values in the following fields:
   • Name. Enter a name for the Connection.
   • Description. Enter a description of the Connection.
   • User Name. Enter a valid user name for an account on the remote location. Oracle LSH will use this user name to connect to the remote location.
   • Password. Enter the password required for the same user account. Oracle LSH encrypts the password for security.
   • Connection Type. Select the Connection Type from the drop-down list.
     • User. The User option allows only the person who creates the Connection to use it.
     • Shared. The Shared option allows multiple users to use the Connection. Security is determined by user group assignment to the Connection.
   • Remote Location. The system populates the field with the name of the Remote Location for which you are defining this Connection. See Defining Remote Locations .
3. In the Classification section, select the following for both the definition and the instance:

   • Subtype. Select a subtype according to your company's policies.

   • Classification Values. Specify classifications according to your company's policies, to enable users to search for the Connection by classification. For instructions, see "Classifying Objects" in the Oracle Life Sciences Data Hub Application Developer's Guide.

4. Click Apply to save your work. The system displays the main screen for the new Connection.

You can change the security and classifications of the Connection through the items in the Actions drop-down list. By default, the Connection inherits the user group assignments of the Remote Location. If the user group assignments for the Remote Location and Connection are different, the user must have access to both in order to use the Remote Location/Connection combination.

Searching for Remote Locations and Connections

To find existing Connections, you must go to the Remote Location for which they are defined. To find existing Remote Locations, do the following:

1. Go to the Remote Location subtab under the Administration tab.
2. Enter a value in one or both of the following fields:
   • Adapter Type. From the drop-down list, select the Remote Location's adapter type.
   • Remote Location Name (Optional). Enter the Remote Location's name or part of its name plus the % wildcard.
3. Click Go. The system displays all Remote Locations that match these criteria in the lower part of the screen.

To see the Connections defined for the Remote Location or to define additional Connections, click the Remote Location's hyperlink in the Name column.