MVSFORUMS.com

basky_g · Beginner Joined: 04 Jul 2004 Posts: 5 Topics: 2

Hi,
My application needs to update / insert thousands of rows into table 'A' with forceful commit at the end.

My question is whether the performance of the application will increase by taking an exclusive lock on the table 'A' before starting insertion or it doesn't have any bearing on performance.

kolusu · Posted: Wed Mar 15, 2006 9:20 am Post subject:

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Hi Basky,
Attaching an brief text from Sander's - EXAM700 Study guide.

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Hi Basky,
My answer might seem as if it is contradicting to Kollusu's answer.
But kollusu is also right but from other concurrent programs perspective.
So the answer for your question is
" It will improve the performance of "insert" program but will decrease the performance of other programs which are even just to read from this table "

When there are no programs to fetch data from the table when this "INSERT" program is run, then you can go for this explicit lock mode.

Cheers

Manas Biswal · Posted: Wed Mar 15, 2006 9:34 am Post subject:

basky_g,

It is very difficult to say what exactly to do without having more information on the table, what type of applications access it, whats the frequency and load of various kinds of transactions on it..etc.
Taking an exclusive lock somtimes makes sense if thats the only maintenance application accessing it at that time and all the other transactions are read-only or you have got a maintenance batch window.
The best way to find out is to run it both ways on your test subsystem and see for yourself.

Regards,
Manas
_________________
There is no path to peace. Peace is the path.
- Mahatma Gandhi (1869-1948)

basky_g · Beginner Joined: 04 Jul 2004 Posts: 5 Topics: 2

Hi Thanks for your reply.
Kolusu I can't use load utility as some manipulation need to be done on the data.

Also table 'A' will be accessed only by the application in question. No other application is expected to access this data and that is the main reason i was thinking of taking an exclusive lock.

Sander's - EXAM700 Study guide qoute solves my purpose i guess. And astro thanks for your disclaimer Laughing

and hope i have answered.

astro · Beginner Joined: 05 Oct 2005 Posts: 31 Topics: 7

Dear Basky,
My reply is not a disclaimer but just an extended & conclusive answer for your unclear question.

basky_g · Beginner Joined: 04 Jul 2004 Posts: 5 Topics: 2

Agreed Astro, That indeed made things clear.
Thanks all for responding.

chandrankk · Beginner Joined: 06 Dec 2005 Posts: 8 Topics: 0

inserting/updating a huge a number of records has another drawback. When the logs become full DB2 may stop and this will cause a major problem. You can't control the log when you do an insert/update. Update will create 2 entries in the log and insert will create 1 and if there are millions of records being updated or inserted, log becomes full (We have 8 log files in our shop and when all the 8 files became full the first one still didn't finish dumping to tape and hence couldn't cycle through)

Even if you have some manipulation to be done, you can do it via a program and write into a file and then use it for loading the table with the Load utility. We have recently had issues while inserting records and the DBA suggested to to a load with LOG NO option.