Results 1 to 6 of 6

Thread: Db2 udf

  1. #1
    Join Date
    Jun 2013
    Posts
    4

    Unanswered: Db2 udf

    Hi all,

    Please tell me where i am wrong in DB2 UDF. its showing error:


    CREATE Function getDATEPART(datevar varchar,yearvar varchar)
    returns varchar (4)
    begin
    DECLARE datevar1 varchar (4) ;
    DECLARE datevar2 varchar (4);

    IF datevar = '1' THEN
    datevar2='2'
    end if;
    return datevar2;
    end


    Please help me

  2. #2
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    SET datevar2='2' ;
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  3. #3
    Join Date
    Jun 2013
    Posts
    4
    No, Its not working.

    please check at your end.

    please help me

  4. #4
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    And the error message and SQLCODE is ...
    And you are trying to create this UDF using the tool ...
    And you are working with DB2 version ...
    And you've searched on Google / DB2 doc and you've tried what to fix
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

  5. #5
    Join Date
    Jun 2013
    Posts
    4
    please see below function and its error



    CREATE FUNCTION MY_FUNC(PARAM1 VARCHAR(4000))
    RETURNS INT
    BEGIN
    DECLARE VAR1 INT;
    DECLARE VAR2 INT;
    SET VAR1 = 1;
    SET VAR2 = VAR1 + 2000;
    RETURN VAR2;
    END





    ------

    Error

    ------


    ------------------------------ Commands Entered ------------------------------
    CREATE FUNCTION MY_FUNC(PARAM1 VARCHAR(4000))
    RETURNS INT
    BEGIN
    DECLARE VAR1 INT;
    DECLARE VAR2 INT;
    SET VAR1 = 1;
    SET VAR2 = VAR1 + 2000;
    RETURN VAR2;
    END;
    ------------------------------------------------------------------------------
    CREATE FUNCTION MY_FUNC(PARAM1 VARCHAR(4000))
    RETURNS INT
    BEGIN
    DECLARE VAR1 INT
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "DECLARE
    VAR1 INT". Expected tokens may include: "<psm_semicolon>". LINE NUMBER=4.
    SQLSTATE=42601

    DECLARE VAR2 INT
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "INT" was found following "DECLARE VAR2 ".
    Expected tokens may include: "END-OF-STATEMENT". LINE NUMBER=1.
    SQLSTATE=42601

    SET VAR1 = 1
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "VAR1" is not valid in the context where it is used. SQLSTATE=42703

    SET VAR2 = VAR1 + 2000
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0206N "VAR2" is not valid in the context where it is used. SQLSTATE=42703

    RETURN VAR2
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "RETURN
    VAR2". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    END
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END".
    Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END". Expected tokens may include: "JOIN <joined_table> ".

    Explanation:

    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.

    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.

    This message can be returned when text is passed to the command line
    processor (CLP) in command mode and the text contains special characters
    that are interpreted by the operating system shell, such as single or
    double quotes, which are not identified with an escape character.

    The statement cannot be processed.

    User response:

    Respond to this error in one of the following ways:

    * Examine and correct the statement in the area of the specified token.
    * If you are using the CLP in command mode and there are any special
    characters, such as quotes, in the command, use an escape character,
    such as the backslash character, to cause the operating system shell
    to not take any special action for those special characters. You
    could also issue the statement using CLP in interactive mode or batch
    mode to avoid any processing of special characters by the operating
    system shell.

    sqlcode: -104

    sqlstate: 42601


    Related information
    Command line processor features

  6. #6
    Join Date
    Apr 2008
    Location
    Iasi, Romania
    Posts
    561
    Provided Answers: 2
    1. put the CREATE FUNCTION statement in a file, let's say myfunc.sql
    2. add an @ after the last END
    3. from the command line, run
    db2 -v -td@ -f myfunc.sql
    Florin Aparaschivei
    DB2 9.7, 10.5 on Windows
    Iasi, Romania

Posting Permissions

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