276 Enhance Your Business Applications: Simple Integration of Advanced Data Mining Functions
Script for function to build the associations rule model
First you create the functions to build the associations rule model shown in
Example E-1.
Example: E-1 Creating the BuildRulesModel function
-- CREATE PROCEDURE BuildRulesModel (
-- mdlName varchar(40),
-- tablename varchar(40),
-- settings CLOB(10K))
-- - - - - - - - - - - - - - - - - - - - - - - - - - -
-- implementation
CREATE PROCEDURE BuildRulesModel (
mdlName varchar(40),
tablename varchar(40),
settings CLOB(10K))
language SQL
begin
call IDMMX.DM_BuildRuleModel(
IDMMX.DM_RuleBldTask()..
DM_defRuleBldTask( Miningdata(tablename),
IDMMX.DM_RuleSettings()..DM_impRuleSettings(settings))
..DM_expRuleTask(),
cast (null as CHAR(10) FOR BIT DATA),
'IDMMX.RULEMODELS','MODEL','MODELNAME',
mdlname
);
end
@
Script for a function that transforms the resulting rule
model
Then you create the function that transforms the resulting rule model from
IDMMX.RuleModels (model) into a table in DB2 UDB V8. See Example E-2.
Appendix E. SQL scripts for the up-to-date promotion scenario 277
Example: E-2 Function creation
CREATE FUNCTION ListRules ( aRULESMODEL IDMMX.DM_RuleModel)
RETURNS TABLE (Antecedent VARCHAR(100),Consequent VARCHAR(100),
Support DOUBLE,Confidence Double)
LANGUAGE SQL
READS SQL DATA
NO EXTERNAL ACTION
not DETERMINISTIC
RETURN
with xmlmodel(M) as
(
values (DB2XML.XMLCLOB(IDMMX.DM_expRuleModel( aRulesModel ) ) )
),
Item(id,value) as
(
select
substr( DB2XML.extractVarchar(DB2XML.XMLCLOB(RETURNEDCLOB),'/Item/@id'),
1,20) as id,
substr( DB2XML.extractVarchar(DB2XML.XMLCLOB(RETURNEDCLOB),'/Item/@value'),
1,20) as value
from
TABLE(
DB2XML.extractclobs((select M from xmlmodel),
'/PMML/AssociationModel/Item')) AS T
),
Itemset1(id, ref)
as (
select
substr( DB2XML.extractVarchar(DB2XML.XMLCLOB(RETURNEDCLOB),'/Itemset/@id'),
1,20) as id,
substr(
DB2XML.extractVarchar(DB2XML.XMLCLOB(RETURNEDCLOB),'/Itemset/ItemRef/@itemRef')
, 1,20) as ref
from
TABLE(
DB2XML.extractclobs((select M from xmlmodel),
'/PMML/AssociationModel/Itemset[@numberOfItems="1"]'))
AS T
),
Rule(ante,cons,support,confidence)
as (
select
substr(
DB2XML.extractVarchar(DB2XML.XMLCLOB(RETURNEDCLOB),'/AssociationRule/@anteceden
t'), 1,20) as ante,
substr(
DB2XML.extractVarchar(DB2XML.XMLCLOB(RETURNEDCLOB),'/AssociationRule/@consequen
t'), 1,20) as cons,

Get Enhance Your Business Applications: Simple Integration of Advanced Data Mining Functions now with O’Reilly online learning.

O’Reilly members experience live online training, plus books, videos, and digital content from 200+ publishers.