DELETE query in Multi Threads

[ismail69]

I want to delete bunch of records from a table like,

delete from Table1 where col1 in (select col1 from Table2);

now col1 from Table2 will be some where b/w 200-500.
But Table1 will be having 3000-5000 records for a single entry from Table2.

Hence instead of deleting at a stretch, I want to get the col1 from Table2
in some Java datastructures. ..

And I want to delete the records using Threads.. like 5 threads at a time deleting records from Table1 for 5 entries from Table2.

I dont know whether it will work. I'm not aware of the Lock during delete operation. Please tell me whether it will work.. Or please suggest some solution..

[roshancaptain1]

ya..sure it is posible to delete the values from the table...but instead of using java threads it would be better if you use triggers in database(sql)

[ismail69]

I'm not sure.. whether DELETE statement will lock the table or not?
why because,
how it will know the diff between -
delete from Table1;

delete from Table1 where empName = "xxxx";

Here if the above statements are executed simultaneously, will it not result in error? I'm not sure abt locking mechanism.. can u please clarify?

[r035198x]

I want to delete bunch of records from a table like,

delete from Table1 where col1 in (select col1 from Table2);

now col1 from Table2 will be some where b/w 200-500.
But Table1 will be having 3000-5000 records for a single entry from Table2.

Hence instead of deleting at a stretch, I want to get the col1 from Table2
in some Java datastructures. ..

And I want to delete the records using Threads.. like 5 threads at a time deleting records from Table1 for 5 entries from Table2.

I dont know whether it will work. I'm not aware of the Lock during delete operation. Please tell me whether it will work.. Or please suggest some solution..

I am not getting the problem here. Why are you not doing:

select col1 from Table2 and store the values in an array/arraylist

Then do
for all in array
delete from Table1 where col1 = array[i];

[ismail69]

That can be done. But that will be a serial operation.
atleast 1000 delete statements will be executed.
Each delete statements will delete atleast 5000 records.

But 5000 records deleted by one delete query is independant of all other delete statements.
Hence What I need is, If the DB2 is capable of processing concurrent delete statements, then I can issue more than one deletes at a time (using a multi thread pgm in Java). Which will improve performance I hope.

But I'm not sure whether I can do it? is it possible to execute concurrent delete statements (which will delete independant set of records)?

[r035198x]

That can be done. But that will be a serial operation.
atleast 1000 delete statements will be executed.
Each delete statements will delete atleast 5000 records.

But 5000 records deleted by one delete query is independant of all other delete statements.
Hence What I need is, If the DB2 is capable of processing concurrent delete statements, then I can issue more than one deletes at a time (using a multi thread pgm in Java). Which will improve performance I hope.

But I'm not sure whether I can do it? is it possible to execute concurrent delete statements (which will delete independant set of records)?

Depends on the implementation and the connection pooling then.

[maverick19]

i think you should use stored procedures for this case
this would be far more efficient than using threads and making unnecessary objects on the heap for processing . You also dont have to worry about locking