Running the %INDB2_PUBLISH_MODEL Macro

%INDB2_PUBLISH_MODEL Macro Run Process

To run the %INDB2_PUBLISH_MODEL macro, complete the following steps:

Create a scoring model using SAS Enterprise Miner.
Use the SAS Enterprise Miner Score Code Export node to create a score output directory and populate the directory with the score.sas file, the score.xml file, and (if needed) the format catalog.
Start SAS 9.3 and submit the following commands in the Program Editor or Enhanced Editor:
```
%indb2pm;
%let indconn = server=yourserver user=youruserid password=yourpwd
    database=yourdb schema=yourschema serveruserid=yourserveruserid;
```
For more information, see the %INDB2PM Macro and the INDCONN Macro Variable.
Run the %INDB2_PUBLISH_MODEL macro.

For more information, see %INDB2_PUBLISH_MODEL Macro Syntax.

Messages are written to the SAS log that indicate the success or failure of the creation of the scoring functions.

%INDB2PM Macro

The %INDB2PM macro searches the autocall library for the indb2pm.sas file. The indb2pm.sas file contains all the macro definitions that are used in conjunction with the %INDB2_PUBLISH_MODEL macro. The indb2pm.sas file should be in one of the directories listed in the SASAUTOS= system option in your configuration file. If the indb2pm.sas file is not present, the %INDB2PM macro call (%INDB2PM; statement) issues the following message:

macro indb2pm not defined

INDCONN Macro Variable

The INDCONN macro variable is used to provide credentials to connect to DB2. You must specify server, user, password, and database information to access the machine on which you have installed the DB2 database. The schema name and the server user ID are optional. You must assign the INDCONN macro variable before the %INDB2_PUBLISH_MODEL macro is invoked.

Here is the syntax for the value of the INDCONN macro variable for the %INDB2_PUBLISH_MODEL macro:

SERVER=server USER=user PASSWORD=password
DATABASE=database <SCHEMA=schema>
<SERVERUSERID=serveruserid>

Arguments

SERVER=server: specifies the DB2 server name or the IP address of the server host.

Requirement:The name must be consistent with the way the host name was cached when PSFTP server was run from the command window. If the full server name was cached, you must use the full server name in the SERVER argument. If the short server name was cached, you must use the short server name. For example, if the long name, disk3295.unx.comp.com, is used when PSFTP was run, then server=disk3295.unx.comp.com must be specified. If the short name, disk3295, was used, then server=disk3295 must be specified. For more information about running the PSFTP command, see DB2 Installation and Configuration Steps in the SAS In-Database Products: Administrator's Guide.

USER=userid: specifies the DB2 user name (also called the user ID) that is used to connect to the database.

PASSWORD=password: specifies the password that is associated with your DB2 user ID.

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

DATABASE=database: specifies the DB2 database that contains the tables and views that you want to access.

Requirement:The scoring model functions are created as Unicode functions. If the database is not a Unicode database, then the alternate collating sequence must be configured to use identity_16bit.

SCHEMA=schema: specifies the schema name for the database.

Default:If you do not specify a value for the SCHEMA argument, the value of the USER argument is used as the schema name.

SERVERUSERID=serveruserid: specifies the user ID for SAS SFTP and enables you to access the machine on which you have installed the DB2 database.

Default: If you do not specify a value for the SERVERUSERID argument, the value of the USER argument is used as the user ID for SAS SFTP.

Note: The person who installed and configured the SSH software can provide the SERVERUSERID (SFTP user ID) and the private key that need to be added to the pageant.exe (Windows) or SSH agent (UNIX). Pageant must be running for the SFTP process to be successful.

Tip

The INDCONN macro variable is not passed as an argument to the %INDB2_PUBLISH_MODEL macro. This information can be concealed in your SAS job. You might want to place it in an autoexec file and set the permissions on the file so that others cannot access the user ID and password.

%INDB2_PUBLISH_MODEL Macro Syntax

%INDB2_PUBLISH_MODEL

(DIR=input-directory-path, MODELNAME=name
<, DATASTEP=score-program-filename>
<, XML=xml-filename>
<, DATABASE=database-name>
<, FMTCAT=format-catalog-filename>
<, ACTION=CREATE | REPLACE | DROP>
<, MODE=FENCED | UNFENCED>
<, INITIAL_WAIT=wait-time>
<, FTPTIMEOUT=timeout-time>
<, OUTDIR=diagnostic-output-directory>
);

Arguments

DIR=input-directory-path: specifies the directory where the scoring model program, the properties file, and the format catalog are located.

This is the directory that is created by the SAS Enterprise Miner Score Code Export node. This directory contains the score.sas file, the score.xml file, and, if user-defined formats were used, the format catalog.

Requirement:You must use a fully qualified pathname.

Interaction:If you do not use the default directory that is created by SAS Enterprise Miner, you must specify the DATASTEP=, XML=, and, if needed, FMTCAT= arguments.

See:Special Characters in Directory Names

MODELNAME=name: specifies the name that is prepended to each output function to ensure that each scoring function name is unique on the DB2 database.

Restriction:The scoring function name is a combination of the model and output variable names. A scoring function name cannot exceed 128 characters. For more information, see Scoring Function Names.

Requirement:The model name must be a valid SAS name that is 10 characters or fewer. For more information about valid SAS names, see the topic on rules for words and names in SAS Language Reference: Concepts.

Interaction: Only the EM_ output variables are published as DB2 scoring functions. For more information about the EM_ output variables, see Fixed Variable Names and Scoring Function Names.

DATASTEP=score-program-filename: specifies the name of the scoring model program file that was created by using the SAS Enterprise Miner Score Code Export node.

Default: score.sas

Restriction: Only DATA step programs that are produced by the SAS Enterprise Miner Score Code Export node can be used.

Interaction: If you use the default score.sas file that is created by the SAS Enterprise Miner Score Code Export node, you do not need to specify the DATASTEP= argument.

XML=xml-filename: specifies the name of the properties XML file that was created by the SAS Enterprise Miner Score Code Export node.

Default: score.xml

Restrictions:Only XML files that are produced by the SAS Enterprise Miner Score Code Export node can be used.

The maximum number of output variables is 128.

Interaction:If you use the default score.xml file that is created by the SAS Enterprise Miner Score Code Export node, you do not need to specify the XML= argument.

DATABASE=database-name: specifies the name of a DB2 database to which the scoring functions and formats are published.

Requirement:The scoring model functions are created as Unicode functions. If the database is not a Unicode database, then the alternate collating sequence must be configured to use identity_16bit.

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 %INDB2_PUBLISH_MODEL Macro Run Process.

FMTCAT=format-catalog-filename: specifies the name of the format catalog file that contains all user-defined formats that were created by the FORMAT procedure and that are referenced in the DATA step scoring model program.

Restriction:Only format catalog files that are produced by the SAS Enterprise Miner Score Code Export node can be used.

Interactions:If you use the default format catalog that is created by the SAS Enterprise Miner Score Code Export node, you do not need to specify the FMTCAT= argument.

If you do not use the default catalog name (FORMATS) or the default library (WORK or LIBRARY) when you create user-defined formats, you must use the FMTSEARCH system option to specify the location of the format catalog. For more information, see PROC FORMAT in the Base SAS Procedures Guide.

ACTION=CREATE | REPLACE | DROP

specifies one of the following actions that the macro performs:

CREATE: creates a new function.

REPLACE: overwrites the current function, if a function by the same name is already registered.

DROP: causes all functions for this model to be dropped from the DB2 database.

Default:CREATE

Tip:If the function has been previously defined and you specify ACTION=CREATE, you will receive warning messages from DB2. If the function has been previously defined and you specify ACTION=REPLACE, no warnings are issued.

MODE=FENCED | UNFENCED: specifies whether the running code is isolated in a separate process in the DB2 database so that a program fault does not cause the database to stop.

Default: FENCED

Tip:After the SAS scoring functions are validated in fenced mode, you can republish them in unfenced mode. You might see a performance advantage when you run in unfenced mode.

See:Modes of Operation

INITIAL_WAIT=wait-time: specifies the initial wait time in seconds for SAS SFTP to parse the responses and complete the SFTP -batchfile process.

Default:15 seconds

Interactions: The INITIAL_WAIT= argument works in conjunction with the FTPTIMEOUT= argument. Initially, SAS SFTP waits the amount of time specified by the INITIAL_WAIT= argument. If the SFTP -batchfile process is not complete after the initial wait time, retries occur until the wait time is equal to or greater than the time-out value specified by the FTPTIMEOUT= argument. All retries double the previous wait time. SAS SFTP fails after the time-out value is reached or exceeded, and an error message is written to the SAS log.

For example, assume you use the default values. The initial wait time is 15 seconds. The first retry waits for 30 seconds. The second retry waits for 60 seconds. The third retry waits for 120 seconds. This is the default time-out value. So, the default initial wait time and time-out values enable four possible tries—the initial try, and three retries.

See: FTPTIMEOUT= argument

FTPTIMEOUT=time-out-value: specifies the time-out value in seconds if SAS SFTP fails to transfer the files.

Default: 120 seconds

Interactions:The FTPTIMEOUT= argument works in conjunction with the INITIAL_WAIT= argument. Initially, SAS SFTP waits the amount of time specified by the INITIAL_WAIT= argument. If the SFTP -batchfile process is not complete after the initial wait time, retries occur until the wait time is equal to or greater than the time-out value specified by the FTPTIMEOUT= argument. All retries double the previous wait time. SAS SFTP fails after the time-out value is reached or exceeded and an error message is written to the SAS log.

For example, assume you use the default values. The initial wait time is 15 seconds. The first retry waits for 30 seconds. The second retry waits for 60 seconds. The third retry waits for 120 seconds. This is the default time-out value. So the default initial wait time and time-out values enable four possible tries—the initial try, and three retries.

Tip:Use this argument to control how long SAS SFTP waits to complete a file transfer before timing out. A time-out failure could indicate a network or key authentication problem.

See:INITIAL_WAIT= argument

OUTDIR=diagnostic-output-directory: specifies a directory that contains diagnostic files.

Files that are produced include an event log that contains detailed information about the success or failure of the publishing process and sample SQL code (SampleSQL.txt). For more information about the SampleSQL.txt file, see Scoring Function Names.

Tip:This argument is useful when testing your scoring models.

See:Special Characters in Directory Names

Modes of Operation

The %INDB2_PUBLISH_MODEL macro has two modes of operation: fenced and unfenced. You specify the mode by setting the MODE= argument.

The default mode of operation is fenced. Fenced mode means that the scoring function that is published is isolated in a separate process in the DB2 database when it is invoked, and an error does not cause the database to stop. It is recommended that you publish the scoring functions in fenced mode during acceptance tests.

When the scoring function is ready for production, you can run the macro to publish the scoring function in unfenced mode. You could see a performance advantage if the scoring function is published in unfenced mode.

Model Publishing Macro Example

%indb2pm;
%let indconn = server=db2base user=user1 password=open1 database=mydb;
%indb2_publish_model( dir=C:\SASIN\baseball1, modelname=baseball1);

The %INDB2_PUBLISH_MODEL macro produces a text file of DB2 CREATE FUNCTION commands as shown in the following example.

Note: This example file is shown for illustrative purposes. The text file that is created by the %INDB2_PUBLISH_MODEL macro cannot be viewed and is deleted after the macro is complete.

CREATE FUNCTION baseball1_EM_eventprobablility
(
"CR_ATBAT" float,
"CR_BB" float,
"CR_HITS" float,
"CR_HOME" float,
"CR_RBI" float,
"CR_RUNS" float,
"DIVISION" varchar(31),
"LEAGUE" varchar(31),
"NO_ASSTS" float,
"NO_ATBAT" float,
"NO_BB" float,
"NO_ERROR" float,
"NO_HITS" float,
"NO_HOME" float,
"NO_OUTS" float,
"NO_RBI" float,
"NO_RUNS" float,
"YR_MAJOR" float
)
RETURNS varchar(33)
LANGUAGE C
NO SQL
PARAMETER STYLE SQL
DETERMINISTIC
FENCED THREADSAFE
NO EXTERNAL ACTION
ALLOW PARALLEL
NULL CALL
EXTERNAL NAME '/users/db2v9/sqllib/function/SAS/
   dbname_username_baseball1.so!baseball1_em_ eventprobablility '

After the scoring functions are installed, they can be invoked in DB2 using SQL, as illustrated in the following example. Each output value is created as a separate function call in the select list.

select baseball1_EM_eventprobability
( 
"CR_ATBAT",
"CR_BB",
"CR_HITS",
"CR_HOME",
"CR_RBI",
"CR_RUNS",
"DIVISION",
"LEAGUE",
"NO_ASSTS",
"NO_ATBAT",
"NO_BB",
"NO_ERROR",
"NO_HITS",
"NO_HOME",
"NO_OUTS"
) as homeRunProb from MLBDB2;

DB2 Permissions

You must have DB2 user permissions to execute the SAS publishing macros to publish the scoring functions. Some of these permissions are as follows.

EXECUTE user permission for functions that were published by another user
READ user permission to read the SASUDF_COMPILER_PATH and SASUDF_DB2PATH global variables
CREATE_EXTERNAL_ROUTINE user permission to the database to create functions
CREATEIN user permission for the schema in which the scoring functions are published if a nondefault schema is used
CREATE_NOT_FENCED_ROUTINE user permission to create functions that are not fenced

Permissions must be granted for each user that needs to publish a scoring function and for each database that the scoring model publishing uses. Without these permissions, publishing of the scoring functions fails.

The person who can grant the permissions and the order in which permissions are granted is important. For complete information and examples, see the installation and configuration instructions in the SAS In-Database Products: Administrator's Guide.