264 Enhance Your Business Applications: Simple Integration of Advanced Data Mining Functions
-- when: set UP ONCE
-----------------------------------------------------------------------
delete from IDMMX.ClusTasks
where id='Connection_Segmentation_Task';
insert into IDMMX.ClusTasks
select
'Connection_Segmentation_Task',
IDMMX.DM_clusBldTask()..dm_defClusbldTask(d.miningdata,s.settings)
from IDMMX.MiningData D,
IDMMX.ClusSettings S
where d.id='Connection'
and s.id='Connection_Segmentation';
-----------------------------------------------------------------------
-- Purpose: Call the Stored Procedure to run the Clustering Task.
-- When: to be put in a DB2 script in a batch job
-----------------------------------------------------------------------
call IDMMX.DM_BuildClusModelcmd('IDMMX.CLUSTASKS','TASK','ID',
'Connection_Segmentation_Task',
'IDMMX.CLUSTERMODELS','MODEL','MODELNAME',
'ConnectionSegmentationModel');
Script to score the data
Example C-3 shows the SQL script generated and updated for the business case
in Chapter 5, Fraud detection example on page 75.
Example: C-3 SQL script generated by IDMMKSQL and modified for the example
-------------------------------------------------------------------------------
--- Purpose:
--- Load model in file clustermodel.pmml into table IDMMX.CLUSTERMODELS,
--- using modelname ConnectionSegmentationModel.
--- Modelname and tablename get default values.
--- You can change these values if necessary.
--- When: set up ONCE
-------------------------------------------------------------------------------
--INSERT INTO ###IDMMX.CLUSTERMODELS### VALUES
('ConnectionSegmentationModel', IDMMX.DM_impClusFile('###ABSOLUTE_PATH###
clustermodel.pmml'));
Appendix C. SQL scripts for the fraud detection scenario 265
-------------------------------------------------------------------------------
--- Purpose: Create the table ALLOCATED_CLUSTERS
--- when: Set up ONCE
-------------------------------------------------------------------------------
DROP TABLE ALLOCATED_CLUSTERS;
CREATE TABLE ALLOCATED_CLUSTERS(
premium_id char(12),
caller_id char(12),
Clus_id INTEGER,
Score FLOAT
);
------------------------------------------------------------------------------
--- Purpose:
--- Start Scoring Services with REC2XML.
--- Create temporary view Resultview.
---
--- When:
--- View created once, used every batch run
------------------------------------------------------------------------------
DROP VIEW SCORING_ENGINE;
CREATE VIEW SCORING_ENGINE( premium_id, caller_Id , Result ) AS
SELECT
data.Premium_id, data.caller_id,
IDMMX.DM_applyClusModel(models.model,
IDMMX.DM_impApplData(
REC2XML(1,'COLATTVAL','',
data."NO_CALLS",
data."NO_CLRS",
data."SUM_DUR",
data."REL_DUR",
data."SUM_COST",
data."MAX_DUR",
data."VAR_DUR")))
FROM IDMMX.CLUSTERMODELS models, connection_table data
WHERE models.MODELNAME= 'ConnectionSegmentationModel';
------------------------------------------------------------------------------
--- Purpose:
--- Use view Resultview to score data and
--- write the results into table Resulttable
---

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.