472,988 Members | 2,296 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 472,988 software developers and data experts.

Lock issues in db2

Hi,

I'm new to DB2 and have little knowledge on database.

i'm facing a Spin lock issue in db2..

Thread here am talking is Java thread..(Sepearte process)

The spin Lock is acquired to ensure Same thread Canít be stated again.
In our Application each thread is mapped to row .So lock is acquired on a row.

The issues in Db2.

1.We can acquire a Row Level Lock using for update. But it will release the lock when we use commit or rollback, we canít use this because in our application we are doing multiple commits on that after acquiring a lock,,

2.There is another methodology available to achieve this is Cursor with Hold, if we use cursor with hold then it will maintain the lock even we used commit or rollback until we close the cursor.
So i try to implement this

We have multiple threads each thread have lock, So we need to Multiple cursor .Each cursor will hold a lock on each thread,..

The problem here is we donít know how many number of threads required its dynamic so number of Cursorís needed also Dynamic. (Each thread is to mapped to a cursor)
So we have to create a cursor Name Dynamically and close the same..
How to achieve this
1.Create 2 procedure one for acquire a lock (Create a cursor) and another to release the lock (Close the cursor)
2.Change the name of the cursor dynamically in the proc .


The Cursor Name creation dynamically using Java its failed Because Declare statement of Cursor can only be embedded in an application program. It is not an executable statement.
It must not be specified in Java. So i'm not proceeding to execute from java.

The option we have is to create a cursor name dynamically in a proc for acquiring and releasing the locks.

Sample Cursor :


CREATE PROCEDURE FCZ221k1.ACQURING_LOCK1 (IN RUNDATE CHAR , IN STREAMNO NUMERIC(10))
BEGIN
DECLARE C1_streamno CURSOR WITH HOLD FOR SELECT * FROM FCZ221K1.BatchProcessResult where rundate=RUNDATE FOR UPDATE;
Open C1_ streamno;
END;

Converted to Dynamic cursor name as per the example i found in net.

CREATE PROCEDURE FCZ221k1.ACQURING_LOCK1 (IN streamno VARCHAR(120) ,IN processdate VARCHAR(36) )
LANGUAGE SQL
BEGIN
DECLARE EX_IMMD_VAR2 VARCHAR(4000) ;
DECLARE EX_IMMD_STMT2 STATEMENT ;
BEGIN

SET EX_IMMD_VAR2 = 'CREATE CURSOR ' || cur_streamno || 'CURSOR WITH HOLD FOR SELECT * FROM FCZ221K1.BatchProcessResult where rundate=RUNDATE FOR UPDATE' ;
PREPARE EX_IMMD_STMT2 FROM EX_IMMD_VAR2 ;
EXECUTE EX_IMMD_STMT2;
END ;
End;

Itís also giving this error

SQL0312N The host variable "<host-name>" is used in a dynamic
SQL statement, a view definition, or a trigger
definition.

Explanation:

The host variable "<host-name>" appears in the SQL statement, but
host variables are not allowed in dynamic SQL statements, in the
SELECT statement of a view definition, or in the triggered action
of a trigger definition.

The statement cannot be processed.

User Response:

Use parameter markers (?) instead of host variables for dynamic
SQL statements. Do not use host variables and parameter markers
in view or trigger definitions.

sqlcode : -312

sqlstate : 42618

Iím not sure whether we can create a cursor name dynamically or notÖ

Please guide me on thisÖ

Regards
Senthil
Jul 26 '08 #1
0 2754

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

Similar topics

6
by: n_o_s_p_a__m | last post by:
I have seen some debate but am not clear on what the problematic implications of the expression lock(this) are. Microsoft seems to advocate it, but others seem to be against it. Pros and cons? ...
0
by: Andrew Dowding | last post by:
Hi Everybody, I have been looking at problems with my Windows Forms C# application and it's little Jet 4 (Access) database for the last few days. The Windows Forms app implements a facade and...
2
by: Fernando RodrŪguez | last post by:
Hi, I've been reading the multithreading tutorial at http://www.yoda.arachsys.com/csharp/threads/locking.shtml (thanks Jon :-) and there's one thing I don't understand. Why do have to pass a...
2
by: Kevin Frey | last post by:
Hello, I've been reading that ASP.NET serialises (ie. processes one at a time) HTTP requests if two simultaneous requests need to access the same session state. It also makes note that ASP.NET...
5
by: Bob Bins | last post by:
Is there a way to create a shared lock using the Monitor class or any other synchronization class in .NET? So I have one resource that may have multiple threads using it at once but a second...
4
by: misterutterbag | last post by:
Hi. Given the following: ---------------- String lockSubscriptionQuery = "SELECT subscriptionId FROM subscription WHERE subscriptionId = ? FOR UPDATE"; pStmt =...
7
by: Troels Arvin | last post by:
Hello, Some users are frustrated by DB2 locking their database operations (most recently: a table drop took forever, probably because of a lock). The natural and sane question I'm being asked:...
2
by: WingSiu | last post by:
I am writing a Logging util for my ASP.NET application. I am facing mulit process problem. I developed a class LogFactory, and have a method called Get_Logger to create a FileLogger, which will...
11
by: fritzcwdev | last post by:
I have a class as follows: public class OperationFeedback { DateTime _startTime; public DateTime StartTime { get {
0
by: lllomh | last post by:
Define the method first this.state = { buttonBackgroundColor: 'green', isBlinking: false, // A new status is added to identify whether the button is blinking or not } autoStart=()=>{
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 4 Oct 2023 starting at 18:00 UK time (6PM UTC+1) and finishing at about 19:15 (7.15PM) The start time is equivalent to 19:00 (7PM) in Central...
0
tracyyun
by: tracyyun | last post by:
Hello everyone, I have a question and would like some advice on network connectivity. I have one computer connected to my router via WiFi, but I have two other computers that I want to be able to...
2
by: giovanniandrean | last post by:
The energy model is structured as follows and uses excel sheets to give input data: 1-Utility.py contains all the functions needed to calculate the variables and other minor things (mentions...
1
by: Teri B | last post by:
Hi, I have created a sub-form Roles. In my course form the user selects the roles assigned to the course. 0ne-to-many. One course many roles. Then I created a report based on the Course form and...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 1 Nov 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM) Please note that the UK and Europe revert to winter time on...
3
by: nia12 | last post by:
Hi there, I am very new to Access so apologies if any of this is obvious/not clear. I am creating a data collection tool for health care employees to complete. It consists of a number of...
0
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
3
SueHopson
by: SueHopson | last post by:
Hi All, I'm trying to create a single code (run off a button that calls the Private Sub) for our parts list report that will allow the user to filter by either/both PartVendor and PartType. On...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.