Results 1 to 2 of 2
  1. #1
    Join Date
    Jun 2002
    Posts
    5

    Question Unanswered: problem in creating function in db2 version 7 FP3

    Hi,

    I am tying to create follwing function,

    CREATE function TEST() RETURNS INTEGER NOT DETERMINISTIC LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE num INTEGER;
    SET num = 1;
    return num;
    END

    and I got following error.

    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0491N The definition of routine "DB2ADMIN.TEST" must have a RETURNS clause,

    and one of: the EXTERNAL clause (with other required keywords); the RETURN

    statement; or the SOURCE clause. LINE NUMBER=1. SQLSTATE=42601



    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 "BEGIN

    ATOMIC". Expected tokens may include: "END". LINE NUMBER=1. SQLSTATE=42601



    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 "num" was found following "DECLARE ". Expected

    tokens may include: "JOIN <joined_table>". SQLSTATE=42601



    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 "num" was found following "SET ". Expected

    tokens may include: "JOIN <joined_table>". SQLSTATE=42601



    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 "num" was found following "return ". Expected

    tokens may include: "JOIN <joined_table>". SQLSTATE=42601



    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


    Can u help me please. Thanks in advance.

    -Paresh

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: problem in creating function in db2 version 7 FP3

    create a file, say /usr/home/db2inst1/create-fn.sql which looks like

    CREATE function TEST() RETURNS INTEGER NOT DETERMINISTIC LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE num INTEGER;
    SET num = 1;
    return num;
    END @

    Then from the command line issue

    db2 -td@ -vf create-fn.sql

    This should work ....

    Cheers

    Sathyaram


    Originally posted by pareshpaladiya
    Hi,

    I am tying to create follwing function,

    CREATE function TEST() RETURNS INTEGER NOT DETERMINISTIC LANGUAGE SQL
    BEGIN ATOMIC
    DECLARE num INTEGER;
    SET num = 1;
    return num;
    END

    and I got following error.

    DB21034E The command was processed as an SQL statement because it was not a

    valid Command Line Processor command. During SQL processing it returned:

    SQL0491N The definition of routine "DB2ADMIN.TEST" must have a RETURNS clause,

    and one of: the EXTERNAL clause (with other required keywords); the RETURN

    statement; or the SOURCE clause. LINE NUMBER=1. SQLSTATE=42601



    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 "BEGIN

    ATOMIC". Expected tokens may include: "END". LINE NUMBER=1. SQLSTATE=42601



    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 "num" was found following "DECLARE ". Expected

    tokens may include: "JOIN <joined_table>". SQLSTATE=42601



    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 "num" was found following "SET ". Expected

    tokens may include: "JOIN <joined_table>". SQLSTATE=42601



    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 "num" was found following "return ". Expected

    tokens may include: "JOIN <joined_table>". SQLSTATE=42601



    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


    Can u help me please. Thanks in advance.

    -Paresh

Posting Permissions

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