Results 1 to 14 of 14
  1. #1
    Join Date
    Sep 2011
    Posts
    85

    Unanswered: DB2 Function Return

    Hi all

    I have a little problem..

    Im creating a function to take a Integer parameter and Update a table according to it..

    What i want is return 1 if update successful and return 0 if not

    This is what i got so far..

    I know return is worng..

    plz help..

    CREATE OR REPLACE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH" ( "PERIOD" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    INHERIT SPECIAL REGISTERS

    BEGIN ATOMIC

    IF (PERIOD = (SELECT TO_CHAR (SCHEDULE_DATE, 'YYYYMM')FROM FROM DB2ADMIN.PROGRAMME_SCHEDULE_HDR WHERE PERIOD = (SELECT TO_CHAR (SCHEDULE_DATE, 'YYYYMM') FROM DB2ADMIN.PROGRAMME_SCHEDULE_HDR)))THEN
    UPDATE DB2ADMIN.PROGRAMME_SCHEDULE_HDR
    SET PUBLISHED_STATUS = 1 , INIT_PUBLISHED_DATE = CURRENT_DATE LAST_UPDATE_DATE = CURRENT_DATE
    WHERE PERIOD = (SELECT TO_CHAR (SCHEDULE_DATE, 'YYYYMM') FROM DB2ADMIN.PROGRAMME_SCHEDULE_HDR);
    RETURN 1;
    ELSE
    RETURN 0;
    END IF;
    END;

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    What do you mean by "if update successful " ? elaborate.

    "I know return is worng.."
    Why worng?

    Do you get an error when creating the function or is it just not giving the result you are after ?
    If it is former, what error ?
    If latter, what is the result you get and what is the result you expect ? examples ?

    And do not forget to provide your DB2 Version (including fix level) and platform



    ---
    Last edited by sathyaram_s; 09-20-11 at 11:31.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Sep 2011
    Posts
    85
    Ok im using 9.7 Exp C in windows..

    I want to return 1 if the update done..

    That mean it should pass the IF condition and do the update and return 1

    if not it should go to ELSE part and return 0

    I have posted my code in 1st post

    When i execute the function it return 4 errors..

    1. ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "." was found following "')FROM FROM DB2ADMIN". Expected tokens may include: ",". LINE NUMBER=1. SQLSTATE=42601

    2. ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "RETURN" was found following "BEGIN-OF-STATEMENT". Expected tokens may include: "<values>". SQLSTATE=42601

    3. ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "RETURN" was found following "ELSE ". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

    4. ERROR [42601] [IBM][DB2/NT] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "END IF". Expected tokens may include: "JOIN <joined_table>". SQLSTATE=42601

  4. #4
    Join Date
    Sep 2011
    Posts
    85
    This is what i got after correct some spellings mistakes

    CREATE OR REPLACE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH" ( "PERIOD" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    MODIFIES SQL DATA
    CALLED ON NULL INPUT
    INHERIT SPECIAL REGISTERS

    BEGIN ATOMIC

    IF (PERIOD = (SELECT DISTINCT TO_CHAR (SCHEDULE_DATE, 'YYYYMM')FROM DB2ADMIN.PROGRAMME_SCHEDULE_HDR WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM')))THEN
    UPDATE DB2ADMIN.PROGRAMME_SCHEDULE_HDR
    SET PUBLISHED_STATUS = 1 , INIT_PUBLISHED_DATE = CURRENT_DATE ,LAST_UPDATE_DATE = CURRENT_DATE
    WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

    RETURN 1;

    ELSE

    RETURN 0;

    END IF;


    END;

    Error is - DB2 Database Error: ERROR [42613] [IBM][DB2/NT] SQL0628N Multiple or conflicting keywords involving the "MODIFIES SQL DATA" clause are present. LINE NUMBER=26. SQLSTATE=42613

  5. #5
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    You can see Note 3. for MODIFIES SQL DATA in CREATE FUNCTION (SQL scalar, table, or row)
    in "DB2 9.7 for Linux, UNIX, and Windows SQL Reference, Volume 2"
    3 Valid if RETURNS specifies a table (that is, TABLE column-list). Also valid if
    RETURNS specifies a scalar result and the SQL-function-body is a compound
    SQL (compiled) statement. In this case, ...
    You are not creating table function.
    So, the function-body must be a compound SQL (compiled) statement.


    This was in Description of Compound SQL (compiled)
    If the ATOMIC keyword is specified in a dynamically prepared compound
    statement or an SQL function that is not within a module, the compound
    statement is processed as a compound SQL (inlined) statement.
    As a result, please try BEGIN NOT ATOMIC.

  6. #6
    Join Date
    Sep 2011
    Posts
    85
    Thanks for the help..

    It worked..

    But when i executing the function it gives a error..

    ERROR [51034] [IBM][DB2/NT] SQL0740N Routine "DB2ADMIN.DTV_SCHEDULE_PUBLISH1" (specific name "SQL110921122336600") is defined with the MODIFIES SQL DATA option, which is not valid in the context where the routine is invoked. SQLSTATE=51034

    This is the final function

    CREATE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH1" ( "PERIOD" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    MODIFIES SQL DATA
    STATIC DISPATCH
    CALLED ON NULL INPUT
    INHERIT SPECIAL REGISTERS


    BEGIN NOT ATOMIC

    IF (PERIOD = (SELECT DISTINCT TO_CHAR (SCHEDULE_DATE, 'YYYYMM')FROM DB2ADMIN.PROGRAMME_SCHEDULE_HDR WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM')))THEN
    UPDATE DB2ADMIN.PROGRAMME_SCHEDULE_HDR_TEST
    SET PUBLISHED_STATUS = 1 , INIT_PUBLISHED_DATE = CURRENT_DATE ,LAST_UPDATE_DATE = CURRENT_DATE
    WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

    RETURN 1;

    ELSE

    RETURN 0;

    END IF;


    END;

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    ... which is not valid in the context where the routine is invoked.
    In what context did you used the function?

  8. #8
    Join Date
    Sep 2011
    Posts
    85
    Im not that much experienced in DB2 and
    I don't know what are you asking..

    This function will Return 1 if update done
    Else return 0

    But i'll explain what i have done..

    I changed the function as you said to NOT ATOMIC

    Than i execute it..

    Im using Toad for DB2 freeware for my DB2 work..

    than i go back to Functions Tab in Toad and right click on the Function and select Execute Function Option
    Then i enter the parameter and i got the above mention error..

    Plz guide me what i have to do..
    Last edited by ai_zaviour; 09-21-11 at 04:00.

  9. #9
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  10. #10
    Join Date
    Sep 2011
    Posts
    85
    WoW..
    This is weird...

    When i compiled the Below function like this...

    CREATE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH1" ( "PERIOD" INTEGER )
    RETURNS INTEGER
    LANGUAGE SQL
    DETERMINISTIC
    NO EXTERNAL ACTION
    MODIFIES SQL DATA
    STATIC DISPATCH
    CALLED ON NULL INPUT
    INHERIT SPECIAL REGISTERS


    BEGIN NOT ATOMIC

    IF (PERIOD = (SELECT DISTINCT TO_CHAR (SCHEDULE_DATE, 'YYYYMM')FROM DB2ADMIN.PROGRAMME_SCHEDULE_HDR WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM')))THEN
    UPDATE DB2ADMIN.PROGRAMME_SCHEDULE_HDR_TEST
    SET PUBLISHED_STATUS = 1 , INIT_PUBLISHED_DATE = CURRENT_DATE ,LAST_UPDATE_DATE = CURRENT_DATE
    WHERE PERIOD = TO_CHAR (SCHEDULE_DATE, 'YYYYMM');

    RETURN 1;

    ELSE

    RETURN 0;

    END IF;


    END;


    But in the database it save like this... [Because of the NOT ATOMIC]

    CREATE FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH1" ( "PERIOD" INTEGER )
    RETURNS INTEGER
    SPECIFIC "SQL110921134325500"
    LANGUAGE SQL
    NOT DETERMINISTIC
    NO EXTERNAL ACTION
    CONTAINS SQL
    RETURNS NULL ON NULL INPUTGRANT EXECUTE ON FUNCTION "DB2ADMIN"."DTV_SCHEDULE_PUBLISH1"( INTEGER ) TO USER "DB2ADMIN" WITH GRANT OPTION;

    Thats why when i execute the function it gives a error which i mention above...

    What is this...??

  11. #11
    Join Date
    Sep 2011
    Posts
    85
    Thanx for the help sathyaram_s..

    So what is your suggestions for above function...??

  12. #12
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    "Im not that much experienced in DB2 and
    I don't know what are you asking.."
    ........
    so I don't understand why you have to do this job or all that is requested..
    Hire somebody that knows DB2 and he will know what he is doing..
    or assign somebody that is willing to learn DB2..
    if resources needed, always available, just let us know..
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  13. #13
    Join Date
    Sep 2011
    Posts
    85
    Ok..

    Thats not what i was expected....

    According to sathyaram_s post..

    A where clause not valid in MODIFIES SQL DATA option..

    But i need to use that to take the values which matches according to the parameter..

    So any expert suggestions will appreciated..

  14. #14
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650
    Quote Originally Posted by ai_zaviour View Post
    Ok..

    Thats not what i was expected....
    What were you not expected to do?????

    According to sathyaram_s post..

    A where clause not valid in MODIFIES SQL DATA option..

    But i need to use that to take the values which matches according to the parameter..
    You are testing it on your Quest TOAD, which is not what your real application will do. See what your application has to do and if it does not work, think of an alternative.



    BTW, basic question. Why are you doing it this way using function instead of say, stored procedure? What is the technical reason ?
    Visit the new-look IDUG Website , register to gain access to the excellent content.

Posting Permissions

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