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.

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?

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.

How did you called the procedure?

From what program?

Show us the concrete code which you used.

Did you actually COMMIT your transaction in which the stored procedure was called?

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.

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)
?

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.

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).

You didn't show that essential information in the description of the issue, like
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.)

If you use DB2 on z/OS: try creating the procedure as follows: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.