Using the SAS Embedded Process to Run Scoring Models :: SAS(R) 9.3 In-Database Products: User's Guide, Second 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. Please refer to the system requirements documentation.

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 Viewing the 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)

%INDB2_CREATE_MODELTABLE Run Process

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

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;
```
For more information, see %INDB2PM Macro and 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.

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.

The syntax of the ANALYZE_TABLE function is as follows:

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

Arguments

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

schema-name: 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.

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;

Viewing the 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.

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

Look at the SampleSQL.txt file that is produced when the %INDDB2_PUBLISH_MODEL macro is successfully run. This file can be found in the output directory (OUTDIR argument) that you specify in the %INDB2_PUBLISH_MODELmacro.
Look at the SAS log. A message that indicates whether the scoring files are successfully or not successfully created is printed to the SAS log.

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;