Results 1 to 6 of 6
  1. #1
    Join Date
    Jul 2014
    Posts
    294

    Answered: The SQL statement is not supported.. SQLCODE=-142 : function mapping

    HI,

    I am totally new to few concepts in db2 & I am growing up my knowledge now.
    I read some docs & tried to apply the function mapping concept in one of my procedure & as far I am getting the below error.

    I have a database d1 & there I created one UDF, bonus().This is working fine.
    Now I am trying to access the same bonus() udf from other database d2.
    From database d1 I created some wrappers & servers to access d2 database.

    UDF on database d1:
    CREATE FUNCTION BONUS ()
    RETURNS DECIMAL(8,2)
    NO EXTERNAL ACTION
    F1: BEGIN
    DECLARE VAR1 INTEGER DEFAULT 100;
    DECLARE VAR2 INTEGER DEFAULT 100;
    DECLARE RES INTEGER;

    SET RES = VAR1 * VAR2;

    RETURN RES;
    END

    Function template statement on database d2:
    CREATE FUNCTION BONUS_1() RETURNS DECIMAL (8,2) AS TEMPLATE DETERMINISTIC NO EXTERNAL ACTION;

    Function mapping statement on database d2:
    CREATE FUNCTION MAPPING FUN111 FOR d2.BONUS_1()
    SERVER TYPE DB2/UDB OPTIONS (REMOTE_NAME 'd1.BONUS()');

    Both are created without errors:
    Now I am trying to call the function from database d2

    VALUES BONUS();

    error:
    The SQL statement is not supported.. SQLCODE=-142, SQLSTATE= , DRIVER=3.67.28

    when I query the SYSCAT.FUNCMAPPINGS table the wrappername & servername columns seems to be null. I am not sure where I have to include the wrappername in function mapping statement.

    Correct me if i am wrong & If possible some one kindly provide me an example for this function mapping concept.

    Million Thanks

  2. Best Answer
    Posted by db2mor

    "If your aim is to get DB2 to run a scalar UDF in a data-source while connected to a different federated database...one way to do it is below, although it's generally much easier/cleaner just to connect to the desired database and run sprocs or queries directly.

    Say database d2 is a DB2 data-source, and it contains the desired scalar UDF called bonus()

    Say the federated database is d1, and it contains all of these:
    1. the drda wrapper
    2. the server called d2 that resolves to database d2 using the drda wrapper, with any options you might need like dbname, fold_id, fold_pw, db2_maximal_pushdown, db2_two_phase_commit etc.
    3. any required 'user mapping' definition(s)
    4. a function template bonus_1 with same signature as d2.bonus()
    5. a function mapping for bonus_1 for server d2 with correct remote_name option
    6. a nickname newdummy for d2.sysibm.sysdummy1 (for example)


    connect to d1;
    select bonus_1() from newdummy;

    results in DB2 running the bonus() function in d2 and returning the desired result
    (which in your example would be the same result as connect to d2; values bonus() )
    while "connect to d1; values bonus_1()" gives SQL0142N for present DB2 LUW versions."


  3. #2
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    The mapped-function works if your query (on d1) references a nickname (that resolves to d2) in addition to the mapped function.

  4. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by HABBIE View Post
    Function mapping statement on database d2:
    CREATE FUNCTION MAPPING FUN111 FOR d2.BONUS_1()
    SERVER TYPE DB2/UDB OPTIONS (REMOTE_NAME 'd1.BONUS()');

    Both are created without errors:
    Now I am trying to call the function from database d2

    VALUES BONUS();
    You seem to be mixing up the concepts of database, server (as in federation), and schema.
    ---
    "It does not work" is not a valid problem statement.

  5. #4
    Join Date
    Jul 2014
    Posts
    294
    Thanks for the reply db2mor.
    Can you kindly make me clear with what you said

  6. #5
    Join Date
    Apr 2012
    Posts
    1,034
    Provided Answers: 18
    If your aim is to get DB2 to run a scalar UDF in a data-source while connected to a different federated database...one way to do it is below, although it's generally much easier/cleaner just to connect to the desired database and run sprocs or queries directly.

    Say database d2 is a DB2 data-source, and it contains the desired scalar UDF called bonus()

    Say the federated database is d1, and it contains all of these:
    1. the drda wrapper
    2. the server called d2 that resolves to database d2 using the drda wrapper, with any options you might need like dbname, fold_id, fold_pw, db2_maximal_pushdown, db2_two_phase_commit etc.
    3. any required 'user mapping' definition(s)
    4. a function template bonus_1 with same signature as d2.bonus()
    5. a function mapping for bonus_1 for server d2 with correct remote_name option
    6. a nickname newdummy for d2.sysibm.sysdummy1 (for example)


    connect to d1;
    select bonus_1() from newdummy;

    results in DB2 running the bonus() function in d2 and returning the desired result
    (which in your example would be the same result as connect to d2; values bonus() )
    while "connect to d1; values bonus_1()" gives SQL0142N for present DB2 LUW versions.

  7. #6
    Join Date
    Jul 2014
    Posts
    294
    Thanks For your explination

Tags for this Thread

Posting Permissions

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