Database Tier Settings

This section includes information about opa_settings on UNIX and Windows.

For more information , see:

• OPA Settings
  
• Changing opa_settings on UNIX
  
• Setting TNS_ADMIN on UNIX
  
• Changing opa_settings.bat on Windows
  

OPA Settings

You can define values for the environment variables in opa_settings (UNIX) or in opa_settings.bat (Windows). On UNIX you can limit the scope of the environment variable setting to a single instance or to a single user. See Defaulting, Adding, and Customizing Values for details.

On UNIX some of these settings are in opa_setup (Korn or Bourne shell) and copa_setup (C shell).

Most of these environment variables affect job execution on back end servers.

For more information , see:

• NLS_DATE_FORMAT
  
• NLS_LANG
  
• RXC_BATCH_QUEUE
  
• RXC_BDL_DIR
  
• RXC_DEBUG_BUFFER_SIZE
  
• RXC_IMMED_QUEUE
  
• RXC_LOG
  
• RXC_MAA_TAB_SPACE
  
• RXC_NOW_STRING
  
• RXC_PRINTER
  
• RXC_SAS_BATCH_QUEUE
  
• RXC_SAS_ROOT
  
• RXC_SAS_VIEW
  
• RXC_USER
  
• SASORA
  
• TEMP
  
• USER_BV_JOB
  

NLS_DATE_FORMAT

This variable determines the format used for displaying dates and converting characters to dates. The default value is "DD-MON-RRRR". The year must be specified as RRRR.

You can modify this variable using db_env_setting records in the opa_settings file.

NLS_LANG

This variable specifies the language and character set settings used by Oracle RDBMS to read from and write to the database.

For Oracle Clinical and RDC Onsite, Oracle strongly recommends that you use the AL32UTF8 character set (the default value set during installation). However, these applications support UTF8, US7ASCII, WE8ISO8859P1, or any single byte character set.

Oracle Clinical and RDC use the NLS_LANG environment variable to control the language, territory, and character set used for database connections. The NLS_LANG variable concatenates the three components as LANGUAGE_TERRITORY.CHARSET.

• You must set the CHARSET component of the NLS_LANG variable to match the character set of the database.

• You must use the same character set on the database tier and the application tier. If you select US7ASCII for the database tier and AL32UTF8 or UTF8 for the application tier, Oracle Clinical stores some special characters incorrectly in the database.

• For PSUB to work correctly for an AL32UTF8 character set database, the opa_settings file must have the following setting:

  db_env_setting:database:NLS_LANG:american_america.AL32UTF8

The default value for this variable is:

db_env_setting:_DEFAULT_:NLS_LANG:american_america.AL32UTF8

You can modify this variable using db_env_setting records in the opa_settings file.

Note:

The default settings for all databases or the specific settings for a particular database, such as NLS_LANG, must be correct in the opa_settings file.

RXC_BATCH_QUEUE

This the batch queue for nonblocking PSUB jobs, on UNIX only.

If you want PSUB to use a batch queue other than the default for running user requests, redefine the setting for rxc_batch_queue. You can define it globally for all users, or individually by placing the command in the user's login script.

Default is a.

RXC_BDL_DIR

This is the spool directory for batch data load.

When a user requests Prepare to Completion for a given data file group, and does not specify otherwise in the submission form, any resulting reloadable data files are written to the directory specified by RXC_BDL_DIR. If there are no reloadable files, the completed files are placed in RXC_LOG.

RXC_DEBUG_BUFFER_SIZE

This sets the output buffer size for executing procedures.

RXC_DEBUG_BUFFER_SIZE controls the size of the buffer space used for running a Validation or Derivation Procedure in debug mode. The installed default value is 200000; you may want to increase it to 1000000.

RXC_IMMED_QUEUE

Batch queue for blocking PSUB jobs, on UNIX only

If you want PSUB to use a batch queue other than the default to process user requests for blocking jobs (such as default layout and generate procedure), redefine the setting for RXC_IMMED_QUEUE. It may be necessary to send blocking jobs to another batch queue so that they are not held up by other system activity, such as long-running reports.

Set this variable through db_env_setting records in the opa_settings file.

RXC_LOG

The directory where the system saves the log files of various processes.

RXC_MAA_TAB_SPACE

Oracle Clinical's Data Extract functionality requires a privileged Oracle user so that Oracle schemas can be created to hold Data Extract Views. The Oracle account for this purpose is RXC_MAA (Maintain Access Accounts).

RXC_MAA_TAB_SPACE specifies the name of the Oracle tablespace defined by RXC_MAA as the temporary tablespace when these schemas are created. During installation RXC_MAA_TAB_SPACE is set to TEMP1 with a size of 10Mb.

Set through db_env_setting records in the opa_settings file.

RXC_NOW_STRING

Defines the string for "now" that is used by the at command in the local language environment. This is effective only for 3GL and PL/SQL jobs submitted in immediate mode to run on UNIX back end servers. Default value is "now". To see your current "now" string, enter:

% echo $LANG

If LANG is undefined or is equal to "C", you have finished. The RXC_NOW_STRING is simply "now". Otherwise, do this:

% cd /usr/lib/nls/$LANG
dumpmsg at.cat

The string for "now" is the third item in the third set of output.

Set through db_env_setting records in the opa_settings file.

RXC_PRINTER

This is the environment variable to which PSUB refers when the user chooses RXC_PRINTER from the list of values for printing a PSUB job. It refers to the default printer for Oracle Clinical.

RXC_SAS_BATCH_QUEUE

This references to the default PSUB batch queue for SAS job, on UNIX only.

If you want PSUB to use a batch queue other than the default for running users' SAS requests, redefine the setting for RXC_SAS_BATCH_QUEUE, globally for all users, or individually by placing the command in the user's initialization file.

RXC_SAS_ROOT

Set this variable to the full path of the database subdirectory of the sas_view directory: OPA_Home/sas_view/database_Oracle_SID. For example:

c:/home/opapps/sas_view/example_db

RXC_SAS_VIEW

Set this variable to the full path of the database subdirectory of the sas_view directory: OPA_Home/sas_view/database_Oracle_SID. For example:

c:/home/opapps/sas_view/example_db

Note:

For historical reasons, two environment variables identify the same directory. Both RXC_SAS_VIEW and RXC_SAS_ROOT identify the root directory for all the input and output files associated with the Data Extract jobs that run in your installation. See SAS_VIEW Directory Tree. (This is true with the exception of the .log and .out files now stored in the database).

The Installer automatically populates these variables in UNIX and Windows, whether the database is local to the machine or remote.

RXC_USER

This is the root directory for creating SAS files during data extract. For example, if RXC_USER is defined as /u01/oc, and ORACLE_SID is prod, then the data extract files go in /u01/oc/prod/… directory.

Note:

It is possible to set a different value for RXC_USER for each database, if you wish, overriding this default.

SASORA

When SAS is installed on a UNIX database, this environment variable must be defined when you run SAS Access against an Oracle database. The default value shipped in opa_settings is V9.

When PSUB and the SAS server are both on Windows, you must comment out the setting of SASORA in opa_settings.bat:

rem set SASORA=V9

TEMP

This is the default temporary directory for SFTP and FTP processes.

USER_BV_JOB

This environment variable specifies the name and location of a user-defined script to be executed as the last step of batch validation.

For example:

UNIX (in .oclrc):

USER_BV_JOB=/dir1/dir2/dir3/filename
export USER_BV_JOB

The full pathname of the file must be specified.

At batch validation run time, the environment variable is evaluated and the corresponding script is submitted for execution via PSUB. The script is called with two arguments: clinical_study_id and clinical_study_version_id.

Changing opa_settings on UNIX

This section has information on UNIX settings as mentioned below:

• Setting Up UNIX Environments
  
• Changing Configuration Settings on UNIX Database Servers
  
• Defaulting, Adding, and Customizing Values
  
• Constraints on the opa_settings File
  
• Checking for Errors in the opa_settings File
  

Setting Up UNIX Environments

On UNIX systems, you run the selection script, which checks the arguments you provide to define a configuration against the settings file. If the arguments are valid, the script applies the appropriate values to the corresponding environment variables in the current shell. The syntax for calling the selection script depends on whether you use the C shell or Bourne/Korn shells. For all shells, the selection script accepts at least one argument and an optional second:

Argument	Description
database	Indicates the database to be used. This can be: the Oracle SID of a database on the PSUB server connect string of a database instance on a machine other than the PSUB server - (minus sign). The script sets the code environment, but preserves the existing database context, if any.
code_env	Optional. An Oracle Clinical code environment designator that must refer to a code environment defined in the opa_settings file on the PSUB server.

Note that using a - (minus) for the first argument neither updates nor creates a database context.

Arguments Specified		Resulting Behavior
database	~	With no code_env specified, the script sets only Oracle-level environment variables needed for applications to access database.
- (minus)	code_env	With no database specified, the script sets only environment variables needed for jobs to run. That is, it sets PATH to include RXC_PSUB and RXC_BIN, and defines the RXC_* environment variables.
database	code_env	With both database and code_env specified, the script executes both sets of commands.

• In Bourne and Korn shells:

  • p1=database
  • p2=code_env
  • .opa_setup

  For example:

  $ p1=test
  $ p2=52
  $ . opa_setup
  

• In C shell:

  • copa_setup database code_env

  For example:

  %copa_setup test 52

Changing Configuration Settings on UNIX Database Servers

The configurations are defined in the opa_settings file. The Oracle Universal Installer creates all necessary entries in this file during installation of software and creation or upgrade of databases. The most common reason to modify the opa_settings file is to customize the values set for various environment variables during execution of back end jobs. You may also need to modify the file to delete databases that are no longer available and enable the use of additional code environments against a database.

Each line in the file defines a particular type of environment information:

record_type_key:field_1[:field_2]...

Starting with an identifier of the type of information (record), the line also contains a colon (:) separator, followed by fields that contain the information for that record, each separated by colons. Table B-1 lists and describes each record type.

Defaulting, Adding, and Customizing Values

Use db_env_setting entries to define the value you want environment variables to assume during execution of back end jobs. You can add an entry for any environment variable you want to define; the definition will be in effect for any database if you set field 1 to _DEFAULT_. To limit the environment variable setting so that it affects only those jobs associated with a particular database, use the database's SID as the value for field 1.

The environment variable settings in opa_settings affect all users. To set a value for an environment variable for just one user, place a statement in that user's .oclrc script—for example, RXC_DEBUG=TRUE; export RXC_DEBUG.

If you want an environment variable setting to affect all jobs that run against a particular database, add or modify a database-specific entry in opa_settings—for example, db_env_setting:test:SQL_TRACE:TRUE.

Finally, if you want the setting to affect all jobs run against any database, add or modify a _DEFAULT_ entry for that environment variable—for example, db_env_setting:_DEFAULT_:RXC_SAS_BATCH_QUEUE:b

The last example in Table B-1 shows how to override a system-wide default setting with a database-specific setting.

Note:

The default settings for all databases or the specific settings for a particular database, such as NLS_LANG, must be correct in the opa_settings file.

Constraints on the opa_settings File

Oracle recommends that you use the defaults where possible, and add overrides only as needed. In addition, note the following constraints if you edit this file.

• In the opa_settings file, there should be exactly one each of these record types:

  • opa_home

  • oratab_filespec

  • tnsnames_filespec

• For each database instance appearing in a db_code_pair record, a value must be defined for each of the database environment settings (record type key db_env_setting). The setting may be made either through a generic _DEFAULT_ record, or through a database-specific record.

Checking for Errors in the opa_settings File

If you modify the opa_settings file, run the script ~opapps/bin/check_opa_settings.sh to check the settings file for errors. The syntax is:

check_opa_settings.sh [-nowarn] settings-file-name

The script generates an error message if it finds any duplicate record_type key values. These would cause an error if present when opa_setup is run. (In fact, opa_setup calls check_opa_settings.sh to preclude this. However, opa_setup does not check for warnings. See below).

Unless the -nowarn argument is provided, check_opa_settings.sh will also generate a warning for multiple db_code_pair entries for a single database. While multiple db_code_pair entries are not invalid, they may represent a condition you do not want to allow. For instance, if you upgrade database 'x' from 5.1 to 5.2, opa_settings would include:

db_code_pair:x:51
db_code_pair:x:52

In this case, check_opa_settings.sh warns you. Remove the line enabling the 5.1 code environment against the 5.2 database, so you don't mistakenly start up a PSUB process in that configuration.

You might want to disregard other warnings. For example, if you had a code tree for testing patches, as well as a production code tree. Then opa_settings might have:

db_code_pair:x:52
db_code_pair:x:52patchtest

You would disregard the warning check_opa_settings.sh would give, since both pairs are valid.

Table B-1 List and Description of the Records in the opa_settings File

Record Type Key	Description	Example
oratab_filespec	Location of the file oratab on the server Field 1: Fully specified path to the OPA directory	oratab_filespec:/etc/oratab
tnsnames_filespec	Location of the file tnsnames.ora on this server. Ensure that this file has an entry for each connect string (that is, a reference to a remote database) that is required for OPA applications. The record provides information about accessing the database over the network. Field 1: Fully specified path to the file tnsnames.ora	tnsnames_filespec:/etc/tnsnames.ora
opa_home	Location of Oracle Health Sciences (formerly known as Oracle Pharmaceutical Applications) products on the server. Field 1: Fully specified path to the OPA directory.	opa_home:/pharm/home/opapps
remote_db_home	Location of an available remote database, to which ORACLE_HOME should be set. Field 1: Net8 connect string of the remote database. Field 2: ORACLE_HOME value that is used while accessing the current database	remote_db_home:hpx1:/u01/app/oracle/product/9.2.0
code_environment	Location of the code for a version of an OPA application. Field 1: A code environment designator, for example, OC52 for the Oracle Clinical 5.2 code Field 2: The fully specified path to the root directory for the version of the application software	code_env: oc52:/pharm/home/opapps/52
db_code_pair	Indicates that a particular code environment can be used with a particular database. Field 1: The system identifier (SID) of a local database instance, or the connect string of a remote database instance. Field 2: A code environment designator	db_code_pair:prod:oc52
db_env_setting	Provides either a default or database-specific setting for an environment variable. The following environment variables must have at least default settings: NLS_DATE_FORMAT NLS_LANG RXC_BATCH_QUEUE RXC_NOW_STRING SASORA These settings are assigned default values at install time. Field 1: The database SID, or connect string, if this is a database-specific setting for the environment variable; or _DEFAULT_, if this is a default setting across databases for this environment variable. Field 2: Name of an environment variable Field 3: Value to be assigned to the environment variable	db_env_setting:_DEFAULT_:SASORA:V9 db_env_setting:TEST:SASORA:V9

Setting TNS_ADMIN on UNIX

The TNS_ADMIN environment variable is located in the .cshrc file in the opapps Home directory. It must point to the location where sqlnet.ora exists. The Installer puts sqlnet.ora in the opapps Home directory. slqnet.ora contains the path of the Oracle Wallet that contains the credentials for OCPSUB.

To set the value to the opapps Home:

.chsrc:

setenv TNS_ADMIN $HOME

Changing opa_settings.bat on Windows

During installation of the server code, the Installer creates the file opa_settings.bat, located in the opapps\bin directory. File opa_settings.bat contains the commands to set environment variables at startup and execution of the PSUB process.

Below is a list of the Oracle Clinical environment variables that must be defined in opa_settings.bat.

• NLS_DATE_FORMAT

  set NLS_DATE_FORMAT=DD-MON-RRRR

  NLS_DATE_FORMAT determines the format in which client applications running on the Windows server transfer date information to and from the database. The format must specify the year as RRRR to be Year 2000 compliant.

• NLS_LANG

  set NLS_LANG=american_america.AL32UTF8

  NLS_LANG determines which language settings Oracle uses when it reads and writes values into the database. The NLS_LANG entry in your registry for your iSuites Oracle Home must be consistent with the NLS_LANG setting in the Oracle Home and your databases.

  opa_settings must have the following setting for PSUB to work correctly for a AL32UTF8 character set database. See NLS_LANG for more information.

• RXC_MAA_TAB_SPACE

To change or add environment variable settings active during back end job execution, edit the opa_settings.bat file with a text editor. Each line must be in the following format:

set variable_name=value