invoking mining directly from Cognos

[zviko]

hi guys, i need urgent help

i run the following  in warehouse design studio to deploy a stored procedure to db2

CREATE PROCEDURE OKMART_ASSOC_PROC ( IN SUPPORT INTEGER,
                                     IN LENGTH INTEGER )
   DYNAMIC RESULT SETS 1
------------------------------------------------------------------------
-- SQL Stored Procedure
   -- SUPPORT
   -- LENGTH
------------------------------------------------------------------------
P1: BEGIN
   -- Declare cursor
   DECLARE cursor1 CURSOR WITH RETURN FOR
      SELECT * FROM OKMART_BI.OKMART_RULES;
      
      
      SET  SCHEMA "OKMARTASSOC";

CREATE VIEW "IWSCHEMA9B5CCD".INPUT_04_0(
  "opcode",
  "sale_key",
  "DayKey",
  "sale_time",
  "cashier_num",
  "ticket_num",
  "item_code",
  "quantity_sold",
  "item_price",
  "amount_taken"
) AS
SELECT Q348."opcode" AS "opcode", Q348."sale_key" AS "sale_key",
       Q348."DayKey" AS "DayKey", Q348."sale_time" AS "sale_time",
       Q348."cashier_num" AS "cashier_num", Q348."ticket_num" AS "ticket_num",
       Q348."item_code" AS "item_code", Q348."quantity_sold" AS "quantity_sold",
       Q348."item_price" AS "item_price", Q348."amount_taken" AS "amount_taken"
  FROM OKMART_BI.UNIT_LINE_ITEM_SLS_FACT Q348;

CREATE VIEW "IWSCHEMA9B5CCD".NAMEMAP_04_0(
  "item_code",
  "dept_id",
  "subdept_id",
  "description",
  "rcpt_desc",
  "internal_id"
) AS
SELECT Q380."item_code" AS "item_code", Q380."dept_id" AS "dept_id",
       Q380."subdept_id" AS "subdept_id", Q380."description" AS "description",
       Q380."rcpt_desc" AS "rcpt_desc", Q380."internal_id" AS "internal_id"
  FROM OKMART_BI.PRODUCT_DIM Q380;

CALL IDMMX.BuildRuleModel('OKMart_Analytics.IM_ASSOC_04',
                          'INPUT_04_0',
                          'sale_key',
                          'SUPPORT',
                          25.0,
                          'LENGTH',
                          'DM_addNmp(''nameMap'',''NAMEMAP_04_0'',''item_code'',''description''),DM_setFldNmp(''item_code'',''nameMap''),DM_setFldUsageType(''amount_taken'',2),DM_setFldUsageType(''ticket_num'',2),DM_setFldUsageType(''DayKey'',2),DM_setFldUsageType(''sale_time'',2),DM_setFldUsageType(''quantity_sold'',2),DM_setFldUsageType(''item_code'',1),DM_setFldUsageType(''cashier_num'',2),DM_setFldUsageType(''item_price'',2),DM_setFldUsageType(''opcode'',2),DM_setMaxNumRules(10000),DM_setAlgorithm(''SIDE'',''<NumBins>5</NumBins>'')');

CREATE VIEW $MODELPORT$INPUT_07_0 (
  $LOC$MODEL,
  $LOC$TABLENAME,
  $LOC$MODELCOLNAME,
  $LOC$IDCOLNAME,
  $LOC$ID) AS
  (SELECT MODEL, 'IDMMX.RULEMODELS', 'MODEL', 'MODELNAME', MODELNAME
     FROM IDMMX.RULEMODELS
     WHERE MODELNAME = 'OKMart_Analytics.IM_ASSOC_04');

CREATE VIEW INPUT_09_0 AS
  (SELECT
     RESULT.ID,
     RESULT.BODYID,
     RESULT.HEAD,
     RESULT.HEADNAME,
     RESULT.LENGTH,
     SUBSTR(RESULT.BODYTEXT,1,1024) AS BODYTEXT,
     RESULT.SUPPORT,
     RESULT.CONFIDENCE,
     RESULT.LIFT
   FROM $MODELPORT$INPUT_07_0 AS MODEL,
        TABLE (IDMMX.DM_GETRULES(MODEL.$LOC$MODEL)) RESULT);

INSERT INTO "OKMART_BI"."OKMART_RULES" (
    "ID",
    "BODYID",
    "HEAD",
    "HEADNAME",
    "LENGTH",
    "BODYTEXT",
    "SUPPORT",
    "CONFIDENCE",
    "LIFT")
SELECT
    INPUT_09_0."ID" AS "ID",
    INPUT_09_0."BODYID" AS "BODYID",
    INPUT_09_0."HEAD" AS "HEAD",
    INPUT_09_0."HEADNAME" AS "HEADNAME",
    INPUT_09_0."LENGTH" AS "LENGTH",
    INPUT_09_0."BODYTEXT" AS "BODYTEXT",
    INPUT_09_0."SUPPORT" AS "SUPPORT",
    INPUT_09_0."CONFIDENCE" AS "CONFIDENCE",
    INPUT_09_0."LIFT" AS "LIFT"
FROM INPUT_09_0;

DROP VIEW INPUT_09_0;

DROP VIEW $MODELPORT$INPUT_07_0;

DROP VIEW "IWSCHEMA9B5CCD".NAMEMAP_04_0;

DROP VIEW "IWSCHEMA9B5CCD".INPUT_04_0;

      

   -- Cursor left open for client application
   OPEN cursor1;
END P1


i want to intergrate this procedure with cognos, so i define parameters in the framework manager that will be used as input by the user. but when i  try to invoke the stored procedure i get the following error

RQP-DEF-0177 An error occurred while performing operation 'sqlExecute' status='-51'.
UDA-SQL-0144 An arithmetic exception was detected.
[IBM][CLI Driver][DB2/NT64] SQL0420N Invalid character found in a character string argument of the function "DOUBLE". SQLSTATE=22018


--------------------------------------------------------------------------------
would you please help me.

[blom0344]

First question would be:  Does the stored procedure work when invoked from DB2?  In other words: Is this a Cognos issue or a DB2 issue? 

[zviko]

i really had not put that into consideration,thanks for pointing it out. so i ran the sql from the database whilst in warehouse design studio and it gives me the same error

OKMARTASSOC.OKMART_ASSOC_PROC - Run started.
Data returned in result sets is limited to the first 50 rows.
Data returned in result set columns is limited to the first 100 bytes or characters.
OKMARTASSOC.OKMART_ASSOC_PROC - Calling the stored procedure.
OKMARTASSOC.OKMART_ASSOC_PROC - Exception occurred while running:
A database manager error occurred.SQLCODE: -420, SQLSTATE: 22018 - Invalid character found in a character string argument of the function "DOUBLE".. SQLCODE=-420, SQLSTATE=22018, DRIVER=4.11.77

OKMARTASSOC.OKMART_ASSOC_PROC - Roll back completed successfully.
OKMARTASSOC.OKMART_ASSOC_PROC - Run failed. 

so i think it is a DB2 error.