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

Prerequisites

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

The SAS Scoring Accelerator for Netezza requires a certain version of the Netezza client and server environment. For more information, see http://www.sas.com/technologies/analytics/datamining/scoring_acceleration/#section=5.

Overview of the In-Database Deployment Package for Netezza

This section describes how to install and configure the in-database deployment package for Netezza (SAS Formats Library for Netezza 2.1).

The in-database deployment package for Netezza must be installed and configured before you can perform the following tasks:

Use the %INDNZ_PUBLISH_FORMATS format publishing macro to create or publish the SAS_PUT( ) function and to create or publish user-defined formats as format functions inside the database.
Use the %INDNZ_PUBLISH_MODEL scoring publishing macro to create scoring model functions inside the database.

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

The in-database deployment package for Netezza contains the SAS formats library and two additional publishing macros.

The SAS formats library is a run-time library that is installed on your Netezza system. This installation is made so that the SAS scoring model functions or the SAS_PUT( ) function can access the routines within the run-time library. The SAS formats library contains the formats that are supplied by SAS.

The %INDNZ_PUBLISH_JAZLIB macro registers the SAS formats library. The %INDNZ_PUBLISH_COMPILEUDF macro registers a utility function in the database. The utility function is then called by the format and scoring publishing macros. You must run these two macros before you run the format and scoring publishing macros.

Function Publishing Process in Netezza

To publish the SAS scoring model functions, the SAS_PUT( ) function, and format functions on Netezza systems, the format and scoring publishing macros perform the following tasks:

Create and transfer the files, using the Netezza External Table interface, to the Netezza server.

Using the Netezza External Table interface, the source files are loaded from the client to a database table through remote ODBC. The source files are then exported to files (external table objects) on the host. Before transfer, each source file is divided into 32K blocks and converted to hexadecimal values to avoid problems with special characters, such as line feed or quotation marks. After the files are exported to the host, the source files are converted back to text.
Compile those source files into object files using a Netezza compiler.
Link with the SAS formats library.
Register those object files with the Netezza system.

Netezza Installation and Configuration Steps

If you are upgrading from or reinstalling a previous version, follow the instructions in Upgrading from or Reinstalling a Previous Version.
Move and unpack the SAS formats library and binary files for the SAS_COMPILEUDF function.

For more information, see Installing the SAS Formats Library and Binary Files.
Run the %INDNZ_PUBLISH_JAZLIB macro to publish the SAS formats library as an object.

For more information, see Running the %INDNZ_PUBLISH_JAZLIB Macro.
Run the %INDNZ_PUBLISH_COMPILEUDF macro.

For more information, seeRunning the %INDNZ_PUBLISH_COMPILEUDF Macro.
If you plan to use SAS Model Manager with the SAS Scoring Accelerator for in-database scoring, perform the additional configuration tasks provided in Configurations for SAS Model Manager.

Upgrading from or Reinstalling a Previous Version

If you are upgrading from or reinstalling a previous version of the SAS formats library and binary files, follow these steps.

Run the %INDNZ_PUBLISH_JAZLIB macro with ACTION=DROP to remove the SAS formats library as an object.

For more information, see Running the %INDNZ_PUBLISH_JAZLIB Macro.
Run the %INDNZ_PUBLISH_COMPILEUDF macro with ACTION=DROP to remove the SAS_COMPILEUDF, SAS_DIRECTORYUDF, and SAS_HEXTOTEXTUDF functions.

For more information, seeRunning the %INDNZ_PUBLISH_COMPILEUDF Macro.
Navigate to the /nz/extensions directory and delete the SAS directory under /nz/extensions.

Note: Under the SAS directory, the installer for the SAS Formats Library and binary files creates a directory under the SAS directory. This directory is named SASFormatsLibraryForNetezza. If you delete everything under the SAS directory, the SAS Formats Library and the binary files are removed.
Continue the installation instructions in Installing the SAS Formats Library and Binary Files.

Installing the SAS Formats Library and Binary Files

The SAS formats library and the binary files for the SAS_COMPILEUDF function are contained in a self-extracting archive file. The self-extracting archive file is located in the SAS-install-directory/SASFormatsLibraryforNetezza/2.1/Netezza32bitTwinFin/ directory.

To move and unpack the self-extracting archive file, follow these steps:

Using a method of your choice, transfer the accelnetzfmt-2.1-n_lax.sh to your Netezza system.

n is a number that indicates the latest version of the file. If this is the initial installation, nhas a value of 1. Each time you reinstall or upgrade, n is incremented by 1.
After the accelnetzfmt-2.1-n_lax.sh file has been transferred to the Netezza machine, log on as the user who owns the Netezza software (usually the “nz” ID).

Use the following commands at the UNIX prompt to unpack the TAR file.

mkdir –p /nz/extensions
chmod 755 /nz/extensions
cd /nz/extensions
chmod 755 accelnetzfmt-2.1-n_lax.sh
path_to_self-extracting_tar_file/accelnetzfmt-2.1-n_lax.sh

After the script runs and the files are unpacked, the target directories should look similar to these.

/nz/extensions/SAS/SASFormatsLibraryForNetezza/2.1-n/bin/InstallAccelNetzFmt.sh
/nz/extensions/SAS/SASFormatsLibraryForNetezza/2.1-n/lib/SAS_CompileUDF.o_spu10
/nz/extensions/SAS/SASFormatsLibraryForNetezza/2.1-n/lib/SAS_CompileUDF.o_x86
/nz/extensions/SAS/SASFormatsLibraryForNetezza/2.1-n/lib/libjazxfbrs_spu10.so
/nz/extensions/SAS/SASFormatsLibraryForNetezza/2.1-n/lib/libjazxfbrs_x86.a

There also is a symbolic link such that /nz/extensions/SAS/SASFormatsLibraryForNetezza/2.1 points to the latest version.

Running the %INDNZ_PUBLISH_JAZLIB Macro

Overview of Publishing the SAS Formats Library

The SAS formats library is a shared library and must be published and registered as an object in the Netezza database. The library is linked to the scoring and format publishing macros through a DEPENDENCIES statement when the scoring model functions or formats are created.

You must run the %INDNZ_PUBLISH_JAZLIB macro to publish and register the SAS formats library. The %INDNZ_PUBLISH_JAZLIB macro publishes and registers the SAS formats library in the database as the sas_jazlib object.

%INDNZ_PUBLISH_JAZLIB Macro Run Process

To run the %INDNZ_PUBLISH_JAZLIB macro follow these steps:

Start SAS 9.3 and submit the following commands in the Enhanced Editor or Program Editor:
```
%indnzpj;
%let indconn=SERVER=yourservername USER=youruserid PW=yourpwd DB=database;
```
For more information, see %INDNZPJ Macro and INDCONN Macro Variable.
Run the %INDNZ_PUBLISH_JAZLIB macro. For more information, see %INDNZ_PUBLISH_JAZLIB Macro Syntax.

%INDNZPJ Macro

The %INDNZPJ macro searches the autocall library for the indnzpj.sas file. The indnzpj.sas file needs to be called before calling the %INDNZ_PUBLISH_JAZLIB macro. The indnzpj.sas file should be in one of the directories listed in the SASAUTOS= system option in your configuration file. If the indnzpj.sas file is not present, the %INDNZPJ macro call (%INDNZPJ; statement) issues the following message:

macro indnzpj not defined

INDCONN Macro Variable

The INDCONN macro variable is used to provide credentials to connect to Netezza. You must specify server, user, password, and database information to access the machine on which you have installed the Netezza data warehouse. You must assign the INDCONN macro variable before the %INDNZ_PUBLISH_JAZLIB macro is invoked.

The value of the INDCONN macro variable for the %INDNZ_PUBLISH_JAZLIB macro has this format:

SERVER=<'>server<'> USER=<'>userid<'> PASSWORD=<'>password<'>
DATABASE=<'>database<'>

SERVER=<'>server<'>

specifies the server name or IP address of the server to which you want to connect. This server accesses the database that contains the tables and views that you want to access. If the server name contains spaces or nonalphanumeric characters, enclose the server name in quotation marks.

USER=<'>userid<'>

specifies the Netezza user name (also called the user ID) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, enclose the user name in quotation marks.

PASSWORD=<'>password<'>

specifies the password that is associated with your Netezza user name. If the password contains spaces or nonalphanumeric characters, enclose the password in quotation marks.

Tip	You can use only PASSWORD=, PASS=, or PW= for the password argument. PWD= is not supported and causes an error.

DATABASE=<'>database<'>

specifies the name of the database on the server that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, enclose the database name in quotation marks.

%INDNZ_PUBLISH_JAZLIB Macro Syntax

%INDNZ_PUBLISH_JAZLIB

(<DATABASE=database>
<, ACTION=CREATE | REPLACE | DROP>
<, OUTDIR=diagnostic-output-directory>
);

Arguments

DATABASE=database

specifies the name of a Netezza database to which the SAS formats library is published as the sas_jazlib object.

Default	SASLIB
Interaction	The database that is specified by the DATABASE= argument takes precedence over the database that you specify in the INDCONN macro variable.
Tip	The object name for the SAS formats library is `sas_jazlib`

ACTION=CREATE | REPLACE | DROP

specifies that the macro performs one of the following actions:

CREATE

creates a new SAS formats library.

Note	The SAS formats library is created as a library database object named `sas_jazlib`. If the SAS formats library was published previously and you specify ACTION=CREATE, you receive warning messages that the library already exists and be prompted to use REPLACE.

REPLACE

overwrites the current SAS formats library, if a SAS formats library by the same name is already registered, or creates a new SAS formats library if one is not registered.

Note The SAS formats library is created as a library database object named sas_jazlib. Formats and models that use published formats create database dependencies on the sas_jazlib object. When you specify ACTION=REPLACE, the SAS formats library is replaced with the latest version. As a precautionary practice, you might need to republish any format or model that uses published formats to ensure that the latest version of the SAS formats library is referenced.

DROP

causes the SAS formats library to be dropped from the Netezza database.

Note	The SAS formats library is created as a library database object named `sas_jazlib`. Formats and models that use published formats create database dependencies on the `sas_jazlib` object. If you previously published user-defined formats or if you previously published models that have formats associated with them, you might not be able to drop the SAS formats library. Dropping the SAS formats library does not work until all dependencies on the library object are released from the database. You can use ACTION=REPLACE instead of ACTION=DROP.
Tip	If you specify ACTION=DROP and the SAS formats library does not exist, you receive an error message.
See	Note under REPLACE

Default

CREATE

OUTDIR=diagnostic-output-directory

specifies a directory that contains diagnostic files.

Tip	Files that are produced include an event log that contains detailed information about the success or failure of the publishing process.

Running the %INDNZ_PUBLISH_COMPILEUDF Macro

Overview of the %INDNZ_PUBLISH_COMPILEUDF Macro

The %INDNZ_PUBLISH_COMPILEUDF macro creates three functions:

SAS_COMPILEUDF. This function facilitates the scoring and format publishing macros. The SAS_COMPILEUDF function compiles the scoring model and format source files into object files. This compilation uses a Netezza compiler and occurs through the SQL interface.
SAS_DIRECTORYUDF and SAS_HEXTOTEXTUDF. These functions are used when the scoring and format publishing macros transfer source files from the client to the host using the Netezza External Tables interface. SAS_DIRECTORYUDF creates and deletes temporary directories on the host. SAS_HEXTOTEXTUDF converts the files from hexadecimal back to text after the files are exported on the host. For more information about the file transfer process, see Function Publishing Process in Netezza.

You have to run the %INDNZ_PUBLISH_COMPILEUDF macro only one time.

The SAS_COMPILEUDF, SAS_DIRECTORYUDF, and SAS_HEXTOTEXTUDF functions must be published before the %INDNZ_PUBLISH_FORMATS or %INDNZ_PUBLISH_MODEL macros are run. Otherwise, these macros fail.

Note: The %INDNZ_PUBLISH_COMPILEUDF macro is needed only if you plan to use scoring functions to run the scoring models.

Note: To publish the SAS_COMPILEUDF, SAS_DIRECTORYUDF, and SAS_HEXTOTEXTUDF functions, you must have the appropriate Netezza user permissions to create these functions in either the SASLIB database (default) or in the database that is used in lieu of SASLIB. For more information, see Netezza Permissions.

%INDNZ_PUBLISH_COMPILEUDF Macro Run Process

To run the %INDNZ_PUBLISH_COMPILEUDF macro to publish the SAS_COMPILEUDF, SAS_DIRECTORYUDF, and SAS_HEXTOTEXTUDF functions, follow these steps:

Create either a SASLIB database or a database to be used in lieu of the SASLIB database.

This database is where the SAS_COMPILEUDF, SAS_DIRECTORYUDF, and SAS_HEXTOTEXTUDF functions are published. You specify this database in the DATABASE argument of the %INDNZ_PUBLISH_COMPILEUDF macro. For more information about how to specify the database that is used in lieu of SASLIB, see %INDNZ_PUBLISH_COMPILEUDF Macro Run Process.
Start SAS 9.3 and submit the following commands in the Enhanced Editor or Program Editor.
```
%indnzpc;
%let indconn = server=yourserver user=youruserid password=yourpwd
   database=database;
```
For more information, see %INDNZPC Macro and INDCONN Macro Variable.
Run the %INDNZ_PUBLISH_COMPILEUDF macro. For more information, see %INDNZ_PUBLISH_COMPILEUDF Macro Syntax.

After the SAS_COMPILEUDF function is published, the model or format publishing macros can be run to publish the scoring model or format functions.

%INDNZPC Macro

The %INDNZPC macro is an autocall library that initializes the %INDNZ_PUBLISH_COMPILEUDF macro.

INDCONN Macro Variable

The INDCONN macro variable provides the credentials to make a connection to Netezza. You must specify the server, user, password, and database information to access the machine on which you have installed the Netezza database. You must assign the INDCONN macro variable before the %INDNZ_PUBLISH_COMPILEUDF macro is invoked.

The value of the INDCONN macro variable for the %INDNZ_PUBLISH_COMPILEUDF macro has this format.

SERVER=<'>server<'> USER=<'>userid<'> PASSWORD=<'>password<'>
DATABASE=SASLIB | <'>database<'>

SERVER=<'>server<'>

specifies the server name or IP address of the server to which you want to connect. This server accesses the database that contains the tables and views that you want to access. If the server name contains spaces or nonalphanumeric characters, enclose the server name in quotation marks.

USER=<'>userid<'>

specifies the Netezza user name (also called the user ID) that you use to connect to your database. If the user name contains spaces or nonalphanumeric characters, enclose the user name in quotation marks.

PASSWORD=<'>password<'>

specifies the password that is associated with your Netezza user name. If the password contains spaces or nonalphanumeric characters, enclose the password in quotation marks.

Tip	You can use only PASSWORD=, PASS=, or PW= for the password argument. PWD= is not supported and causes an error.

DATABASE=SASLIB | <'>database<'>

specifies the name of the database on the server that contains the tables and views that you want to access. If the database name contains spaces or nonalphanumeric characters, enclose the database name in quotation marks.

Default

SASLIB

Interaction

If the SAS_COMPILEUDF function is published in a database other than SASLIB, then that database name should be used instead of SASLIB for the DBCOMPILE argument in the %INDNZ_PUBLISH_FORMATS and %INDNZ_PUBLISH_MODEL macros. Otherwise, the %INDNZ_PUBLISH_FORMATS and %INDNZ_PUBLISH_MODEL macros fail when calling the SAS_COMPILEUDF function during the publishing process. If a database name is not specified, the default is SASLIB. For documentation on the %INDNZ_PUBLISH_FORMATS and %INDNZ_PUBLISH_MODEL macros, see the Documentation for Publishing SAS Formats and Scoring Models in Netezza.

%INDNZ_PUBLISH_COMPILEUDF Macro Syntax

%INDNZ_PUBLISH_COMPILEUDF

(<DATABASE=database-name>
<, ACTION=CREATE | REPLACE | DROP>
<, OUTDIR=diagnostic-output-directory>
);

Arguments

DATABASE=database-name

specifies the name of a Netezza database to which the SAS_COMPILEUDF is published.

Default	SASLIB
Interaction	The database that is specified by the DATABASE= argument takes precedence over the database that you specify in the INDCONN macro variable. For more information, see %INDNZ_PUBLISH_COMPILEUDF Macro Run Process.

ACTION=CREATE | REPLACE | DROP

specifies that the macro performs one of the following actions:

CREATE

creates a new SAS_COMPILEUDF function.

REPLACE

overwrites the current SAS_COMPILEUDF function, if a SAS_COMPILEUDF function by the same name is already registered, or creates a new SAS_COMPILEUDF function if one is not registered.

DROP

causes the SAS_COMPILEUDF function to be dropped from the Netezza database.

Default	CREATE
Tip	If the SAS_COMPILEUDF function was published previously and you specify ACTION=CREATE, you receive warning messages that the function already exists and be prompted to use REPLACE. If you specify ACTION=DROP and the SAS_COMPILEUDF function does not exist, you receive an error message .
See	REPLACE

OUTDIR=diagnostic-output-directory

specifies a directory that contains diagnostic files.

Tip	Files that are produced include an event log that contains detailed information about the success or failure of the publishing process.

Netezza Permissions

There are two sets of permissions involved with the in-database software.

The first set of permissions is needed by the person who publishes the SAS formats library and the SAS_COMPILEUDF, SAS_DIRECTORYUDF, and SAS_HEXTOTEXTUDF functions. These permissions must be granted before the %INDNZ_PUBLISH_JAZLIB and %INDNZ_PUBLISH_COMPILEUDF macros are run. Without these permissions, running these macros fails.

The following table summarizes the permissions that are needed by the person who publishes the formats library and the functions.

Permission Needed	Authority Required to Grant Permission	Examples
CREATE LIBRARY permission to run the %INDNZ_PUBLISH_JAZLIB macro that publishes the SAS formats library (`sas_jazlib` object)	System Administrator or Database Administrator Note: If you have SYSADM or DBADM authority, then you have these permissions. Otherwise, contact your database administrator to obtain these permissions.	GRANT CREATE LIBRARY TO fmtlibpublisherid
CREATE FUNCTION permission to run the %INDNZ_PUBLISH_COMPILEUDF macro that publishes the SAS_COMPILEUDF, SAS_DIRECTORYUDF, and the SAS_HEXTOTEXTUDF functions		GRANT CREATE FUNCTION TO compileudfpublisherid

The second set of permissions is needed by the person who runs the format publishing macro, %INDNZ_PUBLISH_FORMATS, or the scoring publishing macro, %INDNZ_PUBLISH_MODEL. The person who runs these macros is not necessarily the same person who runs the %INDNZ_PUBLISH_JAZLIB and %INDNZ_PUBLISH_COMPILEUDF macros. These permissions are most likely needed by the format publishing or scoring model developer. Without these permissions, the publishing of the scoring model functions and the SAS_PUT( ) function and formats fails.

Note: Permissions must be granted for every format and scoring model publisher and for each database that the format and scoring model publishing uses. Therefore, you might need to grant these permissions multiple times. After the Netezza permissions are set appropriately, the format and scoring publishing macros can be run.

Note: When permissions are granted to specific functions, the correct signature, including the sizes for numeric and string data types, must be specified.

The following table summarizes the permissions that are needed by the person who runs the format or scoring publishing macro.

Permission Needed	Authority Required to Grant Permission	Examples
EXECUTE permission for the SAS 9.3 Formats Library	System Administrator or Database Administrator Note: If you have SYSADM or DBADM authority, then you have these permissions. Otherwise, contact your database administrator to obtain these permissions.	GRANT EXECUTE ON SAS_JAZLIB TO scoringorfmtpublisherid
EXECUTE permission for the SAS_COMPILEUDF function		GRANT EXECUTE ON SAS_COMPILEUDF TO scoringorfmtpublisherid
EXECUTE permission for the SAS_DIRECTORYUDF function		GRANT EXECUTE ON SAS_DIRECTORYUDF TO scoringorfmtpublisherid
EXECUTE permission for the SAS_HEXTOTEXTUDF function		GRANT EXECUTE ON SAS_HEXTOTEXTUDF TO scoringorfmtpublisherid
CREATE FUNCTION, CREATE TABLE, CREATE TEMP TABLE, and CREATE EXTERNAL TABLE permissions to run the format and scoring publishing macros		GRANT CREATE FUNCTION TO scoringorfmtpublisherid GRANT CREATE TABLE TO scoringorfmtpublisherid GRANT CREATE TEMP TABLE TO scoringorfmtpublisherid GRANT CREATE EXTERNAL TABLE TO scoringorfmtpublisherid

Note: If you plan to use SAS Model Manager with the SAS Scoring Accelerator for in-database scoring, additional permissions are required. For more information, see Configurations for SAS Model Manager.

Documentation for Publishing SAS Formats and Scoring Models in Netezza

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