Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2007
    Posts
    72

    Unanswered: UDFScalar error during build. Can you help?

    Hi all,
    I am getting this error when I tried to build a User defined Function. The code and error message is as follows:

    CODE:
    CREATE FUNCTION fnIsVowel
    (
    c CHAR(1)
    )
    RETURNS SMALLINT
    SPECIFIC fnIsVowel
    BEGIN ATOMIC
    IF ( c = 'A' ) OR ( c = 'E' ) OR ( c = 'I' ) OR ( c = 'O' ) OR ( c = 'U' ) OR ( c = 'Y' ) THEN
    RETURN 1;
    END IF;
    RETURN 0;
    END

    CREATE FUNCTION fnSlavoGermanic
    (
    Word CHAR(50)
    )
    RETURNS SMALLINT
    SPECIFIC fnSlavoGermanic
    BEGIN ATOMIC

    --Catch NULL also...
    IF ( LOCATE('W', Word) > 0 ) OR ( LOCATE('K', Word) > 0 ) OR ( LOCATE('CZ', Word) > 0 ) THEN
    RETURN 1;
    END IF;

    RETURN 0;
    END
    ERROR MESSAGE
    :
    DB2ADMIN.fnIsVowel - Build started.
    Create user-defined function returns -104.
    DB2ADMIN.fnIsVowel: 20: [IBM][CLI Driver][DB2/LINUX] SQL0104N An unexpected token "CHAR" was found following "Word ". Expected tokens may include: ".". LINE NUMBER=20. SQLSTATE=42601

    DB2ADMIN.fnIsVowel - Build failed.
    DB2ADMIN.fnIsVowel - Roll back completed successfully.

    Can someone point out what is wrong with this statements?

    Thanks

  2. #2
    Join Date
    May 2003
    Posts
    113
    not sure which db2 you are using.

    I am pretty sure that for db2 z/OS, you cannot put logic in a scalar udf. That is, no support for such "IF --then " stmt yet.

    There is a case-expression that can serve simple IF-THEN logic.

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    I would also use a CASE expression instead. The DB2 optimizer has a much better chance on optimizing code that does not contain procedural logic - as an SQL PL UDF would introduce.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Mar 2007
    Posts
    72
    Thank you guys. But this is being developed in a UDBv8.2 and v9.1environment.
    So are does this mean that the error is misleading because it does not appear to like the first create function statement? That's where this problem is coming from...it like the first create function but not the second or does it? Why (see error message)? They are practically the same statement except for size of "c" and "word". This is the aggravating part of our job...something seemingly very clear is not!

    CREATE FUNCTION fnIsVowel
    (
    c CHAR(1)
    )
    RETURNS SMALLINT
    SPECIFIC fnIsVowel
    BEGIN ATOMIC
    IF ( c = 'A' ) OR ( c = 'E' ) OR ( c = 'I' ) OR ( c = 'O' ) OR ( c = 'U' ) OR ( c = 'Y' ) THEN
    RETURN 1;
    END IF;
    RETURN 0;
    END

    CREATE FUNCTION fnSlavoGermanic
    (
    Word CHAR(50)
    )
    RETURNS SMALLINT
    SPECIFIC fnSlavoGermanic
    BEGIN ATOMIC

    --Catch NULL also...
    IF ( LOCATE('W', Word) > 0 ) OR ( LOCATE('K', Word) > 0 ) OR ( LOCATE('CZ', Word) > 0 ) THEN
    RETURN 1;
    END IF;

    RETURN 0;
    END

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Now we're slowly getting to the point where you tell us what is happening. So we are on DB2 for Linux, and the error occurs both in V8 and V9.1? Do you get the error on the first CREATE FUNCTION statement or the second or both?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Mar 2007
    Posts
    72
    Hi Knut,
    Good morning...I did find out that I needed to add a statement terminator after END in the first create function. That seem to have solved that problem...however I do have perhaps a more difficult error to resolve and I will be posting in another thread. Please visit the thread as I think you might be able to help...thanks

Posting Permissions

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