Configuring a Database

SAS Embedded Process Publish Method
Scoring Function Publish Method
Finding the JDBC JAR Files

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.
    For information about the permissions that are required for specific databases, see the following topics:
  3. SAS Model Manager requires that the following database and table permissions be set at minimum. Verify that the permissions you have set on your database include these permissions.
    1. GRANT CREATE, DROP, EXECUTE, and ALTER permissions for functions and procedures.
    2. GRANT CREATE and DROP permissions for tables. With these permissions, users can validate the scoring results when publishing scoring model files using SAS Model Manager.
    3. Run the database-specific macro to create a table in the database to store the published model scoring files. The value of the MODELTABLE= argument in the macro should match the specification of the In-Database Options for SAS Model Manager in SAS Management Console. For more information, see In-Database Options.
      If the Use model manager table option is set to No, then the model-table-name should be sas_model_table. Otherwise, it should be sas_mdlmgr_ep.
      Here is an example of the create model table macro for Teradata: 
      %INDTD_CREATE_MODELTABLE(DATABASE=database-name, MODELTABLE=model-table-name, 
ACTION=CREATE);
      For more information about creating a table for a specific database, see the SAS In-Database Products: User’s Guide.

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.
    For information about the permissions that are required for specific databases, see the following topics:
  3. SAS Model Manager requires that the following database and table permissions be set at minimum. Verify that the permissions you have set on your database include these permissions.
    1. GRANT CREATE, DROP, EXECUTE, and ALTER permissions for functions and procedures.
    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.
  4. Navigate to the \sasinstalldir\SASModelManagerInDatabaseScoringScripts\14.2\Utilities directory to find the Create Tables and Drop Tables scripts for your database. Then, follow these 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.
  5. 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 SAS Web Application Server are the following:
    single server install and configuration
    \sasconfigdir\Levn\Web\WebAppServer\SASServer1_1\lib
    This is an example of the directory path: C:\SAS\Config\Lev1\Web\WebAppServer\SASServer1_1\lib
    multiple server install and configuration
    \sasconfigdir\Levn\Web\WebAppServer\SASServer11_1\lib
    This is an example of the directory path: C:\SAS\Config\Lev1\Web\WebAppServer\SASServer11_1\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.
  6. Restart the SAS servers on the web application server.

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 https://jdbc.postgresql.org/download.html. An example of a JDBC driver name is postgresql-9.2-1002.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 Supportthen selectDownloadsthen selectDeveloper Downloads, and then click JDBC Driver in the table.
For more information about the database versions that are supported, see the SAS Foundation system requirements documentation for your operating environment.
Copyright © SAS Institute Inc. All Rights Reserved.
Last updated: February 9, 2017