By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
454,236 Members | 1,423 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 454,236 IT Pros & Developers. It's quick & easy.

DB2 SQL Procedure Multi User Env.

P: 1
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
Sep 10 '06 #1
Share this Question
Share on Google+
1 Reply


P: 2
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
Sep 14 '06 #2

Post your reply

Sign in to post your reply or Sign up for a free account.