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 re-created.
The object file is renamed to avoid library caching in DB2.
db2path/sqllib/function/SAS
directory,
where db2path
is
the path that was defined during installation. This directory is accessible
to all database partitions.
db2path/sqllib/function/SAS
directory,
the publishing macros use the database, schema, and model name as
the object filename to avoid potential naming conflicts.
modelname_EM_outputvarname
mymodel
and
the DB2 installation instance is located in /users/db2v9
.
The first line of code executes a db2profile script. The script sets
the DB2 environment variables so that the DB2 command line processor
(CLP) can execute.
>./users/db2v9/sqllib/db2profile >db2 db2 => connect to database user username using password db2 => select * from syscat.functions where funcname like '%MYMODEL%'
mymodel
.
proc sql noerrorstop; connect to db2 (user=username pw=password db=database); select * from connection to db2 (select * from syscat.functions where funcname like '%MYMODEL%'); disconnect from db2; quit;
allmush1_intab
that
is populated with a unique integer from 1 to n. n is
the number of rows in the table.
allmush1
.
drop table allmush1_outtab; create table allmush1_outtab( id integer ,"EM_CLASSIFICATION" varchar(33) ,"EM_EVENTPROBABILITY" float ,"EM_PROBABILITY" float ); insert into allmush1_outtab( id ,"EM_CLASSIFICATION" ,"EM_EVENTPROBABILITY" ,"EM_PROBABILITY" ) select id, allmush1_em_classification("BRUISES" ,"CAPCOLOR" ,"GILLCOLO" ,"GILLSIZE" ,"HABITAT" ,"ODOR" ,"POPULAT" ,"RINGNUMB" ,"RINGTYPE" ,"SPOREPC" ,"STALKCBR" ,"STALKROO" ,"STALKSAR" ,"STALKSHA" ,"VEILCOLO") as "EM_CLASSIFICATION", allmush1_em_eventprobability("BRUISES" ,"CAPCOLOR" ,"GILLCOLO" ,"GILLSIZE" ,"HABITAT" ,"ODOR" ,"POPULAT" ,"RINGNUMB" ,"RINGTYPE" ,"SPOREPC" ,"STALKCBR" ,"STALKROO" ,"STALKSAR" ,"STALKSHA" ,"VEILCOLO") as "EM_EVENTPROBABILITY", allmush1_em_probability("BRUISES" ,"CAPCOLOR" ,"GILLCOLO" ,"GILLSIZE" ,"HABITAT" ,"ODOR" ,"POPULAT" ,"RINGNUMB" ,"RINGTYPE" ,"SPOREPC" ,"STALKCBR" ,"STALKROO" ,"STALKSAR" ,"STALKSHA" ,"VEILCOLO") as "EM_PROBABILITY" from allmush1_intab ;
%let indconn = server=db2base user=user1 password=open1 database=mydb; %indb2_publish_model( dir=C:\SASIN\baseball1, modelname=baseball1);
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'
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;