In-Database Deployment Package for Oracle :: SAS(R) 9.3 In-Database Products: Administrator's Guide, Second Edition

Prerequisites

SAS Foundation and the SAS/ACCESS Interface to Oracle must be installed before you install and configure the in-database deployment package for Oracle.

Overview of the In-Database Package for Oracle

This section describes how to install and configure the in-database deployment package for Oracle (SAS Embedded Process 9.31).

The in-database deployment package for Oracle must be installed and configured before you can use the %INDOR_PUBLISH_MODEL scoring publishing macro to create scoring files inside the database.

The scoring publishing macros are included in the SAS/ACCESS Interface to Oracle. For more information about using the scoring publishing macros, see the SAS In-Database Products: User's Guide.

The in-database deployment package for Oracle includes the SAS Embedded Process. The SAS Embedded Process is a SAS server process that runs within Oracle to read and write data. The SAS Embedded Process contains macros, run-time libraries, and other software that is installed on your Oracle system so that the SAS scoring files created in Oracle can access the routines within the SAS Embedded Process’s run-time libraries.

Oracle Installation and Configuration Steps

If you are upgrading from or reinstalling a previous release, follow the instructions in Upgrading from or Reinstalling a Previous Version before installing the in-database deployment package.
Install the in-database deployment package.

For more information, see Installing the In-Database Deployment Package for Oracle.
Create the required users and objects in the Oracle server. For more information, see Creating Users and Objects for the SAS Embedded Process.

Upgrading from or Reinstalling a Previous Version

You can upgrade from or reinstall a previous version of the SAS Embedded Process. Before installing the In-Database Deployment Package for Oracle have the database administrator (DBA) announce to the user community that there will be an upgrade of the SAS Embedded Process. The DBA should then alter the availability of the database by restricting access, or by bringing the database down.

Installing the In-Database Deployment Package for Oracle

Overview

The in-database deployment package for Oracle is contained in a self-extracting archive file named tkindbsrv-9.31-n_lax.sh. n is a number that indicates the latest version of the file. If this is the initial installation, n has a value of 1. Each time you reinstall or upgrade, n is incremented by 1.

The self-extracting archive file is located in the SAS-install-directory/SASTKInDatabaseServer/9.31/OracleDatabaseonLinuxx64/ directory.

Move the SAS Embedded Process Package to the Oracle Server

To move and copy the Oracle in-database deployment package, follow these steps:

Using a method of your choice, move the tkindbsrv-9.31-n_lax.sh file to directory of your choice. It is suggested that you create a SAS directory under your home directory. An example is /u01/pochome/SAS.
Copy the tkindbsrv-9.31-n_lax.sh file onto each of the RAC nodes using a method of your choice (for example, DCLI, SFTP, SCP, or FTP).
Note: This might not be necessary. For RAC environments with a shared Oracle Home, you can also use one of these methods:
- Copy the extracted directories from a single node.
- Copy the self-extracting archive file to a directory common to all the nodes.
- If the file system is not a database file system (DBFS), extract the file in one location for the whole appliance.

Unpack the SAS Embedded Process Files

For each node, log in as the owner user for the Oracle software using a secured shell, such as SSH. Perform the following steps:

Change to the directory where the tkindbsrv-9.31-n_lax.sh file is located.
If necessary, change permissions on the file to enable you to execute the script and write to the directory.
```
chmod +x tkindbsrv-9.31-n_lax.sh
```
Use this command to unpack the self-extracting archive file.
```
./tkindbsrv-9.31-n_lax.sh
```
After this script is run and the files are unpacked, a SAS tree is built in the current directory. The content of the target directories should be similar to the following, depending on the path to your self-extracting archive file. Part of the directory path is shaded to emphasize the different target directories that are used.
```
/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/bin

/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/misc

/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/sasexe

/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/utilities

/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/admin

/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/logs
```
For non-shared Oracle home systems, update the contents of the $ORACLE_HOME/hs/admin/extproc.ora file on each node. For shared Oracle home systems, you can update the file in one location that is accessible by all nodes.
1. Make a backup of the current extproc.ora file.
2. Add the following settings to the file making sure to override any previous settings.
  SET EXTPROC_DLLS=ANY SET EPPATH=/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/ SET TKPATH=/path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/sasexe
Note: Ask your DBA if the ORACLE_HOME environment variable is not set.
For non-shared Oracle home systems, update the contents of the $ORACLE_HOME/network/admin/sqlnet.ora file on each node. For shared Oracle home systems, you can update the file in one location that is accessible by all nodes.
1. Make a backup of the current sqlnet.ora file. If the file does not exist, create one.
2. Add the following setting to the file.
  DIAG_ADR_ENABLED=OFF

Creating Users and Objects for the SAS Embedded Process

After the In-Database Deployment Package for Oracle is installed, the DBA must create the users and grant user privileges, before the SAS administrator can create the objects for the Oracle server. The users and objects are required for the SAS Embedded Process to work. The steps to create users and objects is not required for an upgrade or reinstall, unless you want to create and grant privileges for additional users.

Note: SQLPLUS or an equivalent SQL tool can be used to submit the SQL statements in this topic.

To create the user accounts for Oracle, the DBA must perform the following steps:

Connect as SYS, using the following command:
```
sqlplus sys/<password> as sysdba
```

To create and grant user privileges for the SASADMIN user, submit the following statements:

create user SASADMIN identified by sasadmin;

grant connect, resource to SASADMIN;
grant create table to SASADMIN;
grant create view to SASADMIN;
grant create library to SASADMIN;
grant create any directory to SASADMIN;
grant drop any directory to SASADMIN;
grant create public synonym to SASADMIN;
grant drop public synonym to SASADMIN;
grant create any context to SASADMIN

To create and grant user privileges for other users, submit the following SQL statements:

# demo, model and nlsmodel are examples of users
create user demo identified by demo;
create user model identified by model;
create user nlsmodel identified by nlsmodel;

grant connect to demo;
grant connect, resource to model;
grant connect, resource to nlsmodel;

To create the objects and the SASEPFUNC table function that are needed to run the scoring model, the SAS administrator (SASADMIN) must perform the following steps:
1. Change the directory to /path_to_sh_file/SAS/SASTKInDatabaseServerForOracle/9.31/admin.
2. Connect as SASADMIN, using the following command:
  sqlplus sasadmin/<password>
3. Submit the following SQL statements:
  @create_sasepfunc.sql; @create_sasepcontext.sql; @create_sasepcfg_table.sql; @create_saseplog_table.sql;

Oracle Permissions

The person who runs the %INDOR_CREATE_MODELTABLE needs CREATE permission to create the model table. Here is an example.

GRANT CREATE TABLE TO userid

The person who runs the %INDOR_PUBLISH_MODEL macro needs INSERT permission to load data into the model table. Here is an example.

GRANT INSERT ON modeltablename TO userid

Note: The RESOURCE user privilege that was granted in the previous topic includes the permissions for CREATE, DELETE, DROP, and INSERT.

Documentation for Scoring Models in Oracle

For information about how to publish SAS scoring models, see the SAS In-Database Products: User's Guide, located at http://support.sas.com/documentation/onlinedoc/indbtech/index.html.