Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2011
    Posts
    28

    Unanswered: Update in DB2 Procedure

    Hi All,

    I have created an Update Procedure which needs to update a column called indicator to "E" from "I". There are aroung many records in my table.

    I am passing year, number and indicator "I" as input to the procedure and telling to update the indicator column to "I" whereever year and number is matching.

    My procedure is :

    CREATE PROCEDURE UPDATE_TABLE
    (IN MY_YEAR CHAR (2), IN MY_NUMBER CHAR (5), IN MY_INDICATOR CHAR (1))
    LANGUAGE SQL
    BEGIN
    UPDATE MY_TABLE SET INDICATOR = MY_INDICATOR WHERE YEAR = MY_YEAR AND NUMBER = MY_NUMBER;
    END@


    This got executed correctly but when I call it from my program, it took alot of time to complete the task and even it has not updated the table.

    Kindly provide me suggestion if I am missing any points or doing something wrong.

    I will be very thankful.

  2. #2
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    What are the data types of YEAR and NUMBER in MY_TABLE table?

    Are you shure that matching rows(YEAR = MY_YEAR AND NUMBER = MY_NUMBER) exists?
    How did you confirmed existence of those row?

  3. #3
    Join Date
    Apr 2011
    Posts
    28
    Hi,

    Data-types are correct. It is defined as character only for year and number as well.

    The things I am seeing is when from my java program I am calling a simple update query it is working fine and updating the indicator correcly. But, when I am calling procedure, it is not doing the operation correctly and it is taking a lot of time to send back response back to my program.

    What I feel is something wrong is going with the procedure only, as my same program call the Insert Procedure correctly , so no problem with the program.

    I am sending the values of MY_YEAR, MY_NUMBER and MY_INDICATOR from the program.
    I am sure that YEAR = MY_YEAR AND NUMBER = MY_NUMBER is matching and I tried it with query also.

    I want to do this using procedure because I think it will be faster than query.

    Kindly suggest.
    Last edited by ashu000; 04-20-11 at 05:55.

  4. #4
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    How did you called the procedure?

    From what program?

    Show us the concrete code which you used.

  5. #5
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Did you actually COMMIT your transaction in which the stored procedure was called?
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  6. #6
    Join Date
    Apr 2011
    Posts
    28
    Quote Originally Posted by tonkuma View Post
    How did you called the procedure?

    From what program?

    Show us the concrete code which you used.
    From Java Program I am calling the procedure.
    I am making connection to DB and then calling the procedure.

    The same program works fine for Select and Insert procedure.

    I mention the Procedure to be called as :

    String pro = "CALL USP_UPDATE_TABLE (:firstParam1,:secondParam2)";

    Then, setting values of firstParam1 and secondParam2 in a Map, say myMap.
    Then,
    getSimpleJdbcTemplate().update(proc, myMap);

    This will do the required operation and doing also successfully except for update scenario.

    Last edited by ashu000; 04-21-11 at 03:20.

  7. #7
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    Sorry, I can't help you.


    One reason is...
    I felt that main cause of error might not be a DB2 issue.
    However, I don't know much about Java.


    Another reason is...
    Your descriptions are still not concrete and including inconsistencies(perhaps careless mistake).

    For example
    Procedure name:
    CREATE PROCEDURE UPDATE_TABLE
    or
    CALL USP_UPDATE_TABLE
    ?

    Number of parameters:
    three
    (IN MY_YEAR CHAR (2), IN MY_NUMBER CHAR (5), IN MY_INDICATOR CHAR (1))
    or
    two
    (:firstParam1,:secondParam2)
    ?

    field name:
    String pro = "CALL ... ";
    or
    getSimpleJdbcTemplate().update(proc, myMap)
    ?

  8. #8
    Join Date
    Apr 2011
    Posts
    28
    Hi,
    Thanks for your reply.
    Actually I was just putting example and done careless mistakes. In real, I am doing it in a fine way, otherwise program throws errors.

    I will try some other options and if I will get some solution, I will post here with my mistakes as well

    Thanks for all of replies.

  9. #9
    Join Date
    Apr 2011
    Posts
    28
    Hi All,

    Finally I got the solution and it is not a mistake either in Java Program or Update Procedure.

    While providing IN parameters in procedure whatever name I was providing like, year, i was using same name(column name of table) in SET command -

    Set year = year and so on.

    I took IN parameter as IN_year and used set command as

    Set year = IN_year and it worked succesfully.

    I don't know why DB2 is behaving this way, but it worked finally.

    Cheers!
    Praphulla (ashu000).

  10. #10
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    While providing IN parameters in procedure whatever name I was providing like, year, i was using same name(column name of table) in SET command -

    Set year = year and so on.
    You didn't show that essential information in the description of the issue, like
    UPDATE MY_TABLE SET INDICATOR = MY_INDICATOR WHERE YEAR = MY_YEAR AND NUMBER = MY_NUMBER;
    So, it is apparent that no one could help you!
    (Wrong description of an issue wouldn't guide to a solution of the issue.)

    Note:
    1) No update, if you specified...
    SET INDICATOR = INDICATOR
    (You can write an expression including column names in the right side of equal sign. So, right side INDICATOR must be interpreted as the column name.)

    2) Take long time to complete,
    because all rows match WHERE condition(except rows including null values in some columns in the condition), if you specified...
    WHERE YEAR = YEAR AND NUMBER = NUMBER
    (Both of YEAR and both of NUMBER must be interpreted as column names.)

  11. #11
    Join Date
    Sep 2004
    Location
    Belgium
    Posts
    1,126
    If you use DB2 on z/OS: try creating the procedure as follows:
    Code:
    CREATE PROCEDURE UPDATE_TABLE
    (IN MY_YEAR CHAR(2), IN MY_NUMBER CHAR(5), IN MY_INDICATOR CHAR(1))
    LANGUAGE SQL WITH EXPLAIN
    UPDATE MY_TABLE SET INDICATOR = MY_INDICATOR
     WHERE YEAR = MY_YEAR AND NUMBER = MY_NUMBER
    @
    and then inspect your PLAN_TABLE to find out what is the access path. If you e.g. see ACCESS_TYPE = 'R' (table scan), that could explain the long runtime (and possibly timeout and rollback).
    If it looks OK, it could indeed be a missing "commit"; add "COMMIT ON RETURN YES" to the CREATE statement, and try again, see if that solves the problem.
    --_Peter Vanroose,
    __IBM Certified Database Administrator, DB2 9 for z/OS
    __IBM Certified Application Developer
    __ABIS Training and Consulting
    __http://www.abis.be/

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
  •