Lock issues in db2

[senthiltkp]

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