DB2 SQL Procedure Multi User Env.

[bjethwan]

Hi,

I have a small doubt in SQL Procedures in DB2.

It is the first time that I am using DB2 UDB, so please bear me.

Following, is a very small example to put across my doubt.

In following procedure we have a select query query which returns only one row, and after some processing I update the same row.

My question is if two different clients runs the same procedure with the same input parameter, Is it every possible that procedure may return the same serial number.

How actually procedures are handled in multi user environment?

Do I need to write sql with explicit locking? (eg WITH CS, RR or RS)

How actually the "FOR UPDATE" is implemented in DB2 ? (It seems to be different than "FOR UPDATE clause in Oracle)

CREATE PROCEDURE updateAndReturn ( IN input_param CHARACTER(2), OUT return_value VARCHAR(7) )

P1: BEGIN

-- select a value
SELECT bColumn INTO temp_number
FROM aTable
WHERE aColumn = input_param;

-update the value
return_value = update(temp_number)

--update the corresponding record in database.
UPDATE aTable
SET bColumn = return_value
WHERE aColumn = input_param;

END P1



Client1 CALL updateAndReturn('A', ?);
Client2 CALL updateAndReturn('A', ?);

Is there a remote possibility that both client 1 & 2 might select the same row for update?
Wherein update from some of the users might be lost.

Thanks & Regards
Bipin

[momisabag]

Hi,

I have a small doubt in SQL Procedures in DB2.

It is the first time that I am using DB2 UDB, so please bear me.

Following, is a very small example to put across my doubt.

In following procedure we have a select query query which returns only one row, and after some processing I update the same row.

My question is if two different clients runs the same procedure with the same input parameter, Is it every possible that procedure may return the same serial number.

How actually procedures are handled in multi user environment?

Do I need to write sql with explicit locking? (eg WITH CS, RR or RS)

How actually the "FOR UPDATE" is implemented in DB2 ? (It seems to be different than "FOR UPDATE clause in Oracle)

CREATE PROCEDURE updateAndReturn ( IN input_param CHARACTER(2), OUT return_value VARCHAR(7) )

P1: BEGIN

-- select a value
SELECT bColumn INTO temp_number
FROM aTable
WHERE aColumn = input_param;

-update the value
return_value = update(temp_number)

--update the corresponding record in database.
UPDATE aTable
SET bColumn = return_value
WHERE aColumn = input_param;

END P1



Client1 CALL updateAndReturn('A', ?);
Client2 CALL updateAndReturn('A', ?);

Is there a remote possibility that both client 1 & 2 might select the same row for update?
Wherein update from some of the users might be lost.

Thanks & Regards
Bipin

Hi
like any other program that access data in db2, your sql procedure has a package which it runs with.
that package has the isolation level parameter that defines the way the package behave with other applications (or other invokation of the same package),
or in other word,
your sql procedure is no different in that manner than any other applicaiton