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:

• 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.