Results 1 to 4 of 4
  1. #1
    Join Date
    Jan 2010
    Posts
    2

    Unanswered: Create function problem with DB2 V9.5

    Hi guys,

    I am trying to create a function on DB2 version 9.5 (on 64 bit AIX). I have written the following function definition into a file.


    CREATE FUNCTION SMODEL.sampleF
    (
    param1 DECIMAL(4) ,
    param2 CHARACTER(6) ,
    param3 VARCHAR(16) ,
    param4 DECIMAL(9) ,
    param5 CHARACTER(2) ,
    param6 CHARACTER(1) ,
    param7 DECIMAL(11,5) ,
    param8 DATE
    )
    RETURNS DECIMAL(18,4)
    LANGUAGE SQL
    READS SQL DATA
    f1: BEGIN
    DECLARE var1 INTEGER DEFAULT 0;
    RETURN 0;
    END f1
    @



    When I try to create the function from the command line using

    db2 -td@ -f st.sql

    I get the 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:
    SQL0104N An unexpected token "DECLARE" was found following "L DATA f1: BEGIN
    ". Expected tokens may include: "<delim_atomic>". LINE NUMBER=15.
    SQLSTATE=42601



    I also get the same error when I connect to database using TOAD DB2 and run the create command as a script.

    It works fine on windows with DB2 ExpressC 8.1, I made sure that it is not an end of statement issue (i am using @), it is not an dos2unix end of line character issue. I would appreciate your help on this.

    Thanks a lot.
    Last edited by toprak; 01-12-10 at 09:43.

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Look at the documentation for CREATE FUNCTION:

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    and

    IBM DB2 9.5 Information Center for Linux, UNIX, and Windows

    You need BEGIN ATOMIC, not BEGIN.

    Andy

  3. #3
    Join Date
    Jan 2010
    Posts
    2
    Thanks for the reply.

    I tried it before but when I put ATOMIC, I can not declare a cursor in the function definition. I might be confusing some basic things about DB2 functions but DB2 is not consistent itself. It works without ATOMIC in DB2 ExpressC.

    So my question is now:

    May I create a function with ATOMIC and declare a cursor in it?

    I have read some documentation but it did not help.

  4. #4
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    You cannot declare a cursor in a function. The syntax diagrams on the links I provided so that.

    Andy

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
  •