Results 1 to 5 of 5

Thread: UDF & MQTs

  1. #1
    Join Date
    Feb 2008
    Posts
    6

    Question Unanswered: UDF & MQTs

    Hi,
    I am new to DB2 UDB. How can I call a UDF in a MQT ?

  2. #2
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    An MQT is defined via a SELECT statement. You can simply embed a UDF in the SELECT statement. However, the message SQL20058 lists the following restrictions:
    4 The fullselect must not contain references to functions that:

    o depend on physical characteristics of the data, for example
    DBPARTITIONNUM, HASHEDVALUE
    o are defined as EXTERNAL ACTION
    o are defined as LANGUAGE SQL, CONTAINS SQL, READS SQL DATA or
    MODIFIES SQL DATA
    So you could use a UDF with LANGUAGE C and NO SQL, for example.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  3. #3
    Join Date
    Feb 2008
    Posts
    6
    I am not able to use a UDF (ISNUMERIC) using the following. I tried declaring the UDF with EXTERNAL C but got
    ----------
    42601(-491)[IBM][CLI Driver][DB2/AIX64] SQL0491N The definition of routine "CUST.ISNUMERIC" must have a RETURNS clause, and one of: the EXTERNAL clause (with other required keywords); the RETURN statement; or the SOURCE clause. LINE NUMBER=48. SQLSTATE=42601
    (0.02 secs)
    ------

    CREATE TABLE TEST ( ALPHANUMERIC VARCHAR(10) );

    INSERT INTO TEST VALUES '1';
    INSERT INTO TEST VALUES 'A';
    INSERT INTO TEST VALUES '2';
    INSERT INTO TEST VALUES 'S';
    INSERT INTO TEST VALUES 'B';

    MQT :
    -----
    CREATE TABLE TAB1 (NUM_FIELD)
    AS (
    SELECT
    CASE WHEN CUST.ISNUMERIC( ALPHANUMERIC) = 1 THEN ' NUMERIC '
    ELSE ' ALPHA '
    END AS COL1
    FROM TEST
    )
    DATA INITIALLY DEFERRED REFRESH DEFERRED



    USER DEFINED FUNCTION :
    -----------------------
    CREATE FUNCTION ISNUMERIC
    (SOURCE VARCHAR(40))
    RETURNS INTEGER
    RETURN CASE WHEN TRANSLATE(SOURCE,'','0123456789.-+') <> '' THEN 0
    WHEN POSSTR(LTRIM(SOURCE),'-') > 1 OR POSSTR(LTRIM(SOURCE),'+') > 1 THEN 0
    WHEN LENGTH(RTRIM(LTRIM(TRANSLATE(SOURCE,'','0123456789 .-+')))) > 1 THEN 0
    WHEN POSSTR(LTRIM(RTRIM(TRANSLATE(SOURCE,'','-+'))),' ') > 0 THEN 0
    WHEN TRANSLATE(SOURCE,'','.-+') = '' THEN 0 ELSE 1
    END

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    The function that you are using is implicitly LANGUAGE SQL because you use SQL statements inside the function body. You have to write some external code like C code to implement your function. Another alternative is to encode the function body directly into the MQT definition and not encapsulate this logic inside a UDF.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Feb 2008
    Posts
    6
    Thank-you somuch.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •