Preparing a Database for Use with SAS Model Manager

Prerequisites

SAS Foundation, the SAS/ACCESS Interface, and the in-database deployment package for the database must be installed and configured before you can prepare a database for use with SAS Model Manager. For more information, see the chapter for your type of database in this guide. Here are the databases that can be used with SAS Model Manager:

Overview of Preparing a Database for Use with SAS Model Manager

Additional configuration steps are required to prepare a database for publishing and scoring in SAS Model Manager if you plan to use the scoring function (MECHANISM=STATIC) publish method or the SAS Embedded Process (MECHANISM=EP) publish method. If you want to store the scoring function metadata tables in the database, then the SAS Model Manager In-Database Scoring Scripts product must be installed before the database administrator (DBA) can prepare a database for use with SAS Model Manager.
During the installation and configuration of SAS 9.3 products, the SAS Model Manager In-Database Scoring Scripts product is installed on the middle-tier server or another server tier if it is included in the custom plan file.
The location of the SAS installation directory is specified by the user. Here is the default installation location for the SAS Model Manager In-Database Scoring Scripts product on a Microsoft Windows server: C:\Program Files\SASHome\SASModelManagerInDatabaseScoringScripts
The script installation directory includes a directory that specifies the version of SAS Model Manager (currently 12.1). The files and subdirectories that are needed to prepare a database for use by SAS Model Manager are located in the version directory. The Utilities subdirectory contains two SQL scripts for each type of database: a Create Tables script and a Drop Tables script. The DBA needs these SQL scripts to create the tables needed by the SAS Model Manager to publish scoring functions.
Note: The database tables store SAS Model Manager metadata about scoring functions.

Configuring a Database

SAS Embedded Process Publish Method

To enable users to publish scoring model files to a database from SAS Model Manager using the SAS Embedded Process, follow these steps:
  1. Create a separate database where the tables can be stored.
  2. Set the user access permissions for the database.
    1. GRANT CREATE, DROP, EXECUTE, and ALTER permissions for functions and procedures.
      For more information about permissions for the specific databases, see the following topics:
    2. GRANT CREATE and DROP permissions for tables. With these permissions, users can validate the scoring results when publishing a scoring model files using SAS Model Manager.

Scoring Function Publish Method

To enable users to publish scoring functions to a database from SAS Model Manager, follow these steps:
  1. Create a separate database where the tables can be stored.
  2. Set the user access permissions for the database.
    1. GRANT CREATE, DROP, EXECUTE, and ALTER permissions for functions and procedures.
      For more information about permissions for the specific databases, see the following topics:
    2. GRANT CREATE and DROP permissions for tables. With these permissions, users can validate the scoring results when publishing a scoring function using SAS Model Manager.
    3. GRANT SELECT, INSERT, UPDATE, and DELETE permissions for SAS Model Manager metadata tables.
    4. GRANT SELECT permission for the following views to validate the scoring function names:
      • syscat.functions for DB2
      • pg_catalog.pg_proc for Greenplum
      • dbc.functions for Teradata
      • _v_function for Netezza
    Note: If scoring input tables, scoring output tables, or views exist in another database, then the user needs appropriate permissions to access those tables or views.
  3. Navigate to the \sasinstalldir\SASModelManagerInDatabaseScoringScripts\12.1\Utilities directory to find the Create Tables and Drop Tables scripts for your database. Then, perform the following steps:
    1. Verify the statements that are specified in the Create Tables script. Here are the names of the scripts for each type of database:
      • DB2 SQL scripts: createTablesDB2.sql and dropTablesDB2.sql
      • Greenplum SQL scripts: createTablesGreenplum.sql and dropTablesGreenplum.sql
      • Netezza SQL scripts: createTablesNetezza.sql and dropTablesNetezza.sql
      • Teradata SQL scripts: createTablesTD.sql and dropTablesTD.sql
    2. Execute the Create Tables script for a specific type of database.
  4. Download the JDBC driver JAR files and place them in the \lib directory on the web application server where the SAS Model Manager Web application is deployed.
    The default directory paths for the web application servers are the following:
    JBoss
    \JBoss_Home\server\SASServer1\lib
    This is an example of the directory path: C:\JBoss4.3.0.GA\server\SASServer1\lib
    WebLogic
    \sasconfigdir\Lev#\Web\SASDomain\lib
    This is an example of the directory path: C:\SAS\Config\Lev1\Web\SASDomain\lib
    WebSphere
    WebSphere_HOME\lib
    This is an example of the directory path: C:\Program Files\IBM\WebSphere7\AppServer\lib
    Note: You must have Write permission to place the JDBC driver JAR files in the \lib directory. Otherwise, you can have the server administrator download them for you.
    For more information, see Finding the JDBC JAR Files.
  5. Restart the SAS servers on the web application server.
    • JBoss: Use JBoss services or commands to restart the SAS servers.
    • WebLogic: Use the WebLogic Administration Console or commands to restart the SAS servers.
    • WebSphere: Use the WebSphere Admin Console or commands to restart the SAS servers.

Finding the JDBC JAR Files

The DB2 JDBC JAR files are db2jcc.jar and db2jcc_license_cu.jar. The DB2 JDBC JAR files can be found on the server on which the database client was installed. For example, the default location for Windows is C:\Program Files\IBM\SQLLIB\java.
The Greenplum database uses the standard PostgreSQL database drivers. The PostgreSQL JDBC JAR file can be found on the PostgreSQL – JDBC Driver site at http://jdbc.postgresql.org/download.html. An example of a JDBC driver name is postgresql-9.1-901.jdbc4.jar.
The Netezza JDBC JAR file is nzjdbc.jar. The Netezza JDBC JAR file can be found on the server on which the database client was installed. For example, the default location for Windows is C:\JDBC.
The Teradata JDBC JAR files are terajdbc4.jar and tdgssconfig.jar. The Teradata JDBC JAR files can be found on the Teradata website at http://www.teradata.com. Select Support & Downloadsthen selectDownloadsthen selectTeradta JDBC Driver.
For more information about the database versions that are supported, see the SAS Scoring Accelerator system requirements at http://www.sas.com/technologies/analytics/datamining/scoring_acceleration/#section=5.
Copyright © SAS Institute Inc. All rights reserved.