Publishing Scoring Model Files in DB2

The %INDB2_PUBLISH_MODEL macro creates the files that are needed to build the scoring functions and publishes the scoring functions with those files to a specified database in DB2. Only the EM_ output variables are published as DB2 scoring functions. For more information about the EM_ output variables, see Fixed Variable Names.
Note: Secure File Transfer Protocol (SFTP) is used to transfer the source files to the DB2 server during the publishing process. Certain software products that support SSH-2 or SFTP protocols must be installed before you can use the publishing macros. For more information, see Setting up SSH Client Software in UNIX and Windows Environments for use with the SFTP Access Method located at http://support.sas.com/techsup/technote/ts800.pdf.
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:
  • takes the score.sas and score.xml files and produces the set of .c and .h files. These .c and .h files are necessary to build separate scoring functions for each of a fixed set of quantities that can be computed by the scoring model code.
  • if a format catalog is available, processes the format catalog and creates an .h file with C structures. These files are also necessary to build the scoring functions.
  • produces a script of the DB2 commands that are used to register the scoring functions on the DB2 database.
  • transfers the .c and .h files to DB2 using SFTP.
  • calls the SAS_COMPILEUDF function to compile the source files into object files, links to the SAS formats library, and copies the new object files to db2path/sqllib/function/SAS, where db2path is the path that was defined during installation. The object filename is dbname_schemaname_modelname_segnum, where segnum is a sequence number that increments each time the model is replaced or recreated. The object file is renamed to avoid library caching in DB2.
  • calls the SAS_DELETEUDF function to remove existing object files.
  • uses the SAS/ACCESS Interface to DB2 to run the script to create the scoring functions with the object files.
The scoring functions are registered in DB2 with shared object files, which are loaded at run time. These functions are stored in a permanent location. The SAS object files and the SAS formats library are stored in the db2path/sqllib/function/SAS directory, where db2path is the path that was defined during installation. This directory is accessible to all database partitions.
DB2 caches the object files after they are loaded. Each time that the updated objects are used, the database must be stopped and restarted to clean up the cache, or the object files need to be renamed and the functions reregistered with the new object filenames. The SAS publishing process automatically handles the renaming to avoid stopping and restarting the database.
Note: You can publish scoring model files with the same model name in multiple databases and schemas. Because object files for the SAS scoring function are stored in the db2path/sqllib/function/SAS directory, the publishing macros use the database, schema, and model name as the object filename to avoid potential naming conflicts.