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_num ber)
--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
1 2564
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_num ber)
--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
Sign in to post your reply or Sign up for a free account.
Similar topics |
by: Berend |
last post by:
I am trying to pass multi values into a where clause with an in clause
in a store procedure to use in a Crystal report. This can change
depending on the user. Maybe there is another way to pass multi
values.
CREATE OR REPLACE PROCEDURE eva_sp_wrk014_spec_test (
p_eva_product_header_ids IN VARCHAR2,
cur_spec_cd IN OUT
sysadm.eva_pkg_wrk014_spec_test.ref_spec_spec_cd
|
by: marc |
last post by:
I've been developing a stored procedure that uses a user defined
function in the query portion of the procedure. However, since the end
product needs to allow for dynamic table names, the UDF will not work.
I've been trying to get this to work with converting the UDF to a
procedure, but I'm having no luck.
Here is the background on what I'm trying to accomplish. I need to
perform a sub-identity on a table, I have the normal identity set,...
|
by: SHPsalm139 |
last post by:
If I'm in, say, a 3rd level sub and want to exit, not only the sub but
the entire procedure, without a GoTo, can this be done without using
switches?
|
by: bobwansink |
last post by:
Hi, I'm relatively new to programming and I would like to create a C++
multi user program. It's for a project for school. This means I will
have to write a paper about the theory too. Does anyone know a good
place to start looking for some theory on the subject of multi user
applications?
I know only bits and pieces, like about transactions, but a compendium
of possible approches to multi user programming would be very
appreciated!
|
by: news.sbcglobal.net |
last post by:
Here's my problem:
I'm developing an ASP.NET 2.0 application that has a user select one or more
auto manufacturers from a listbox ("lstMakes"). Once they do this, another
listbox ("lstModels") should be filled with all matching models made by the
selected manufacturers. If lstMakes was not multi-select, I'd have no
problem. But in this case it has to be multi-select. The database is SQL
Server 2005 which does not accept arrays as...
| |
by: mirandacascade |
last post by:
Apologies in advance for what I'm guessing may be a trivial question.
Assume the following:
1) multi-user environment
2) standard (i.e. not a class module) module with:
Public strSample as String
3) first user user opens app at 9:00 and runs code that results in
setting the variable strSample to "abc"
|
by: SOI_0152 |
last post by:
Hi all!
Happy New Year 2008. Il hope it will bring you love and happyness
I'm new on this forum.
I wrote a stored procedure on mainframe using DB2 7.1.1 and IBM language c.
Everything works fine.
Now we decided to move from mainframe IMS-DB2 to Windows 2003 server-DB2 UDB
for LUW 9.5.
|
by: Mark C. Stock |
last post by:
"Mark C. Stock" <mcstockX@Xenquery .comwrote in message news:...
|
| "Berend" <Berend.Brinkhuis@evatone.comwrote in message
| news:bdd9ac20.0401271301.22cdb65e@posting.google.com...
| | I am trying to pass multi values into a where clause with an in clause
| | in a store procedure to use in a Crystal report. This can change
| | depending on the user. Maybe there is another way to pass multi
| | values.
| |
| |
|
by: woodey2002 |
last post by:
This problem is driving me crazy.
Hello there,
i am trying to create a search form for records in my access database. The search form will contain text boxes and a multi select list box. The user can enter their search criteria eg. surname, reg num, etc. in the text boxes. The multi select list box allows the user to select multiple counties which they have the option of including in the search. The user should be able to select or omit the...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it.
First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed.
This is as boiled down as I can make it.
Here is my compilation command:
g++-12 -std=c++20 -Wnarrowing bit_field.cpp
Here is the code in...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: conductexam |
last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one.
At the time of converting from word file to html my equations which are in the word document file was convert into image.
Globals.ThisAddIn.Application.ActiveDocument.Select();...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |