db2 transaction , lock

[Guest_imported]

In the project we are using jsp/servlets. We are having
lot of transactions used for different purposes . Different
transactions use some of common tables and update , select from those tables.
please let me know some links , tips to optimize the transactions , locks and get better performance
when i run stress test tool i get few transactions fail message. May be the lock , deadlock is causing problem
please advice

 

[Hattusas]

Have you ever got that message
"The current transaction has been rolled back because of a deadlock or timeout reason code:68?" Salih Sipahi
Software Engineer.
City of Istanbul Turkey
openyourmind77@yahoo.com

 

[matchum]

Why not exploit the wonderful db2batch facility. Begin with PERF_DETAIL of 5. You can place all sorts of commands in it for "benchmarking" but it will also yield the source of avoidable overhead.

Using the results generated from db2batch, you can then choose the optimum tuning approach. e.g. Modification of the physical design, especially indices. In my case, I have found Bufferpool tuning and the DB cfg settings allowed throughput to speed-up dramatically (refer to DBHEAP and its subordinates).

The admin guide give a smart example of db2batch.

I use the monitor switches to do a full snapshot of a KNOWN (performance) problem.

The "benchmark" approach is good for measuring the incidence of any changes you make to the physical design or CFG settings. Always keep the empirical data of course.

Here is an example I used recently:

-----------------------------------------------------------
--Invoke this file with the command: db2batch -d misuk06 -f batch1.sql -r batch1.res
--Note: -d is the dbname
-- -f is THIS FILE name
-- -r is where the output is sent


-- batch1.sql
-- ------------
--#SET PERF_DETAIL 3 ROWS_OUT 50
--suppress next 5
-- SET ROWS_FETCH 100
-- SET DELIMITER ;
-- SET PAUSE
-- SET TIMESTAMP
-- SET SLEEP 12


-- This SQL was sensed from a SNAPSHOT showing locks with
-- multiple iterations of this INSERT statement.
--
--#COMMENT Insert number 1
insert into misuk06_t_slots_evt (server_hndl, date_reception, event_hndl,
slot_name, short_slot_value)
values (?, ?, ?, ?, ?);


--#SET PERF_DETAIL 5 ROWS_OUT 1
--#COMMENT Update number 1
update misuk06_t_evt_rep set sub_source = ?,
status = ?, administrator = ?, credibility = ?,
duration = ?, last_modified_time = ?
where server_hndl = 1 and event_hndl = 198
and date_reception = 1017143012;