Using the SAS Embedded Process to Run Scoring Models :: SAS(R) 9.4 In-Database Products: User's Guide, Fifth Edition

How to Run a Scoring Model with the SAS Embedded Process

The integration of the SAS Embedded Process and DB2 allows scoring code to run directly using the SAS Embedded Process on DB2.

Note: The SAS Embedded Process might require a later release of DB2 than function-based scoring. For more information, see the SAS Foundation system requirements documentation for your operating environment.

To run the scoring model using the SAS Embedded Process, follow these steps.

Create a table to hold the scoring files.

The %INDB2_CREATE_MODELTABLE macro creates a table that holds the scoring files for the model that is being published.

For more information, see Creating a Model Table.
Run the %INDB2_PUBLISH_MODEL to create the scoring files.

The %INDB2_PUBLISH_MODEL macro uses some of the files that are created by the SAS Enterprise Miner Score Code Export node: the scoring model program (score.sas file), the properties file (score.xml file), and (if the training data includes SAS user-defined formats) a format catalog.
The %INDB2_PUBLISH_MODEL macro performs the following tasks:
- translates the scoring model into the sasscore_modelname.ds2 file that is used to run scoring inside the SAS Embedded Process.
- takes the format catalog, if available, and produces the sasscore_modelname_ufmt.xml file. This file contains user-defined formats for the scoring model that is being published.
- uses the SAS/ACCESS Interface to DB2 to insert the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files into the model table that was created using the %INDB2_CREATE_MODELTABLE macro.
For more information, see Running the %INDB2_PUBLISH_MODEL Macro and DB2 Scoring Files.
Use the ANALYZE_TABLE function in the FROM clause in any SQL expression to run the scoring model.

For more information, see ANALYZE_TABLE Function.

Creating a Model Table

Overview

When using the SAS Embedded Process to publish a scoring model in DB2, you must create a table to hold the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files. You must run the %INDB2_CREATE_MODELTABLE macro to create the table before you run the %INDB2_PUBLISH_MODEL macro.

The model table contains the following columns. The ModelName column is the table key. The table is referenced by the two-level name schema-name.model-table-name.

Column Name	Description	Specification
ModelName	contains the name of the model	VARCHAR(128) NOT NULL PRIMARY KEY
ModelDS2	contains the sasscore_modelname.ds2 file	BLOB(4M) NOT NULL
ModelFormats	contains the sasscore_modelname_ufmt.xml file	BLOB(4M)
ModelMetadata	Reserved by SAS for future use	BLOB(4M)
ModelUUID1	contains the UUID of the source model	VARCHAR (36)
Notes1	contains additional information that describes the source model	VARCHAR (512)
1This column is for use by SAS Model Manager. If you have a model table that was created prior to SAS 9.4 and you want this column in your model table, you must run the %INDB2_CREATE_MODELTABLE macro to re-create your model table.

%INDB2_CREATE_MODELTABLE Run Process

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

Start SAS and submit the following command in the Program Editor or Enhanced Editor:
```
%let indconn = server=yourserver user=youruserid password=yourpwd
    database=yourdb schema=yourschema;
```
For more information, see the INDCONN Macro Variable.
Run the %INDB2_CREATE_MODELTABLE macro.

For more information, see %INDB2_CREATE_MODELTABLE Macro Syntax.

%INDB2_CREATE_MODELTABLE Macro Syntax

%INDB2_CREATE_MODELTABLE

(TS_PRIMARYPAR=tablespace-name
<, DATABASE=database-name>
<, MODELTABLE=model-table-name>
<, ACTION=CREATE | REPLACE | DROP>
);

Arguments

TS_PRIMARYPAR=tablespace-name

specifies the name of the tablespace that resides in the primary partition.

Tip	You can get the name of the tablespace from your database administrator.

DATABASE=database-name

specifies the name of a DB2 database where the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files are held.

Default

The database specified in the INDCONN macro variable or your current database

MODELTABLE=model-table-name

specifies the name of the table that holds the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files.

Default	sas_model_table
Requirements	The maximum table name length is 128 characters and it must be a valid DB2 table name.
Requirements	The table name that you specify for this macro must be the same table name that is used in the %INDB2_PUBLISH_MODEL macro.
See	%INDB2_PUBLISH_MODEL Macro Syntax

ACTION = CREATE | REPLACE | DROP

specifies one of the following actions that the macro performs:

CREATE

creates a new table.

Tip	If the table has been previously defined and you specify ACTION=CREATE, an error is issued.

REPLACE

overwrites the current table, if a table with the same name is already registered.

Tip	If you specify ACTION = REPLACE, and the current table contains sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml files, the files are deleted and an empty table is re-created.

DROP

causes all models in this table to be dropped.

Default

CREATE

ANALYZE_TABLE Function

Overview of the ANALYZE_TABLE Function

The ANALYZE_TABLE function is the interface for running the scoring model inside DB2 with the SAS Embedded Process. The ANALYZE_TABLE function uses the information that is stored in the model table. The ANALYZE_TABLE function is a built-in DB2 function.

Using the ANALYZE_TABLE Function

You can use the ANALYZE_TABLE function using explicit pass-through and PROC SQL or you can use other DB2 query tools such as the Command Line Processor. Use the ANALYZE_TABLE function in the FROM clause in any SQL expression to run the scoring model.

Tip

Look at the SampleSQL.txt file that is produced when the %INDB2_PUBLISH_MODEL macro is successfully run. This file can be found in the output directory (OUTDIR argument) that you specify in the %INDB2_PUBLISH_MODEL macro. The SampleSQL.txt file contains basic SQL code that can be used to run your score code inside DB2. Please note that you must modify the sample code before using it. Otherwise, the sample code returns an error.

Note: Before using the ANALYZE_TABLE function with the SAS Embedded Process, you must create the model table with the %INDB2_CREATE_MODELTABLE macro. Then, you must publish the files to the model table with the %INDB2_PUBLISH_MODEL macro. For more information, see Creating a Model Table and Running the %INDB2_PUBLISH_MODEL Macro.

Here is an example using PROC SQL.

proc sql;
 connect to db2 (user=userid password=xxxx database=mydatabase);
 create table work.sas_score_out1 as select * from connection to db2
    (WITH T1 as (SELECT * from SCORE_INPUT_TABLE where X1 < 1.0)
      SELECT * from T1 ANALYZE_TABLE
         (IMPLEMENTATION 'PROVIDER=SAS;
            ROUTINE_SOURCE_TABLE=myschema.SAS_PUBLISH_MODEL;
            ROUTINE_SOURCE_NAME="Intr_Tree";') );
 disconnect from db2;
quit;

ANALYZE_TABLE Function Syntax

The syntax of the ANALYZE_TABLE function is as follows:

FROM input-table-name ANALYZE_TABLE (IMPLEMENTATION 'PROVDER=SAS’;
ROUTINE_SOURCE_TABLE=schema.model-table-name;
ROUTINE_SOURCE_NAME="model-name"; ')

Arguments

input-table-name

specifies the input table that is used by the ANALYZE_TABLE function.

schema

specifies the name of the schema where the scoring model files are published.

model-table-name

specifies the name of the model table where the sasscore_modelname.ds2 and sasscore_modelname_ufmt.xml scoring files were published with the %INDB2_CREATE_MODELTABLE macro.

Requirement

The table name that you specify for this function must be the same table name that is used in the %INDB2_CREATE_MODELTABLE macro. For more information, see %INDB2_CREATE_MODELTABLE Macro Syntax.

model-name

specifies the name of the model.

DB2 Scoring Files

When using the SAS Embedded Process, the %INDB2_PUBLISH_MODEL macro produces two scoring files for each model:

sasscore_modelname.ds2. This file contains code that is executed by the ANALYZE_TABLE function.
sasscore_modelname_ufmt.xml. This file contains user-defined formats for the scoring model that is being published. This file is used by the ANALYZE_TABLE function.

These files are published to the model table that you specify in the %INDB2_PUBLISH_MODEL macro. See Scoring File Examples for an example of each of these files.

A message that indicates whether the scoring files are successfully or not successfully created is printed to the SAS log.

Although you cannot view the scoring files directly, there are two ways to see the models whose files are created:

Run this query from the DB2 command line processor:

db2> connect to databasename user userid using password
db2> select modelname from sasmodeltablename

Run a PROC SQL query from SAS.

proc sql;
 connect to db2 (user=userid password=xxxx database=mydatabase);
  select * from connection to db2
 (select modelname from sas_model_table);
 disconnect from db2; 
quit;

You can also use the SASTRACE and SASTRACELOC system options to generate tracing information. For more information about these system options, see the SAS System Options: Reference.