I would like some opinions on my proposed solution to solve the
problem our production main job is having with lock suspends/
deadlocks.
Machine is a z/OS 01.06
DB2 is V7.1
Three threads are launched at the same time, and they do all their
processing based on 3 files populated with a list of entities to
process. These entities will be the main key of 60% of our tables.
At the moment these three files are populated as follows.
file 1 gets the following
entity 1
entity 4
entity 7
file 2 gets the following
entity 2
entity 5
entity 8
file 3 gets the following
entity 4
entity 6
entity 9
e.g. entities to process are distributed alternally into the 3 files,
and are ordered ascending.
On the particular run i give the stats below the distribution was as
follows.
file 1 - 15732 records
file 1 - 15732 records
file 1 - 15731 records
the threads read the corresponding input file, and for each record
they do some processing, and within the same record at least one
commit/rollback is issued.
>From my initial analysis of the problem, my solution to thisperformance issue is to
1- implement row locking on some (around 5) of the tables, mainly
tables that are used to get sequence numbers to populate other tables.
2- Change the way the input files are populated.
Some of the lockings are happening on tables/indexes based on the key
mentioned above, even though the threads will never need to update the
same key.
If the records are distributed differently on the input files as
follows
file 1 - entity 1 to 15732
file 2 - entity 2 to 15732
file 3 - entity 2 to 15731
this would automatically prevent locking on these tables without the
need to change them to row level lock.
Locking will still occur on other tables that have indexes based on
information common to all entities, although some, if not all would be
covered by the changes mentioned on point 1
The change on point 2 may or not have adverse effect on the I/O, and
the row locks may also have some effect on CPU usage, although I do
not forsee it to be a problem on this case, as CPU usage is not even
next to the limits so basically I am asking for opinions from other
DBA' more used to DB2 on a mainframe environment on what would be
possible issues with my proposed solution, and whether I should look
for other things.
Thread 1
Date 01/03/07 Started 19:38:55 RecType ThdType
ALLIED-N
Term Cd NORMAL Ended 23:14:40 Commits 15424 Aborts 0K Excptns
0
-------------------------------------------------------------------------------
Times in
HH:MM:SS.T
Elapsed Time App 03:35:45.2 Max Pg Locks 92 Select 7504K
Getpage 38246K
Elapsed Time DB2 03:18:55.4 Lock Suspnds 6K Fetch 8362K Read I/
O 527501
CPU Time DB2 38:54.2 Deadlocks 102 I/U/D 1007K Read
Eff 72.5
Wait All DB2 I/O 01:04:52.9 Timeouts 0 Dynamic 0 Pref
Reqs 1319350
Wt All Lock/Ltch 01:07:41.3 Escalations 0 DDL/DCL 0 Buf
Updts 7253578
Wait Log 0.0 L Prf No Stg 0 Calls 0 BP
Warn 0
DB2 Services 10:20.1 Parallel Err 0 CallFail 0 Avg I/
O 0.0139
Wt Data Shr Msgs 0.0 Para. Tasks
0
Wt Stor Proc TCB 0.0 Log
Write 2048645
Routine Elapsed 0.0 WLM
Name ........
Lock events 18120
Suspensions - Lock 5973
Suspensions - Latch 682
Thread 2
Date 01/03/07 Started 19:38:55 RecType ThdType
ALLIED-N
Term Cd NORMAL Ended 23:16:06 Commits 15445 Aborts 0K Excptns
0
-------------------------------------------------------------------------------
Times in
HH:MM:SS.T
Elapsed Time App 03:37:11.5 Max Pg Locks 82 Select 7417K
Getpage 37963K
Elapsed Time DB2 03:19:44.0 Lock Suspnds 7K Fetch 8733K Read I/
O 463479
CPU Time DB2 38:56.5 Deadlocks 81 I/U/D 997K Read
Eff 81.9
Wait All DB2 I/O 54:37.0 Timeouts 0 Dynamic 0 Pref
Reqs 1318976
Wt All Lock/Ltch 01:18:39.3 Escalations 0 DDL/DCL 0 Buf
Updts 7214718
Wait Log 0.0 L Prf No Stg 0 Calls 0 BP
Warn 0
DB2 Services 10:20.7 Parallel Err 0 CallFail 0 Avg I/
O 0.0132
Wt Data Shr Msgs 0.0 Para. Tasks
0
Wt Stor Proc TCB 0.0 Log
Write 2010172
Routine Elapsed 0.0 WLM
Name ........
lock events 19537
Suspensions - Lock 7070
Suspensions - Latch 649
Thread 3
Date 01/03/07 Started 19:38:55 RecType ThdType
ALLIED-N
Term Cd NORMAL Ended 23:16:11 Commits 15446 Aborts 0K Excptns
0
-------------------------------------------------------------------------------
Times in
HH:MM:SS.T
Elapsed Time App 03:37:15.7 Max Pg Locks 77 Select 7484K
Getpage 38358K
Elapsed Time DB2 03:20:27.4 Lock Suspnds 8K Fetch 8343K Read I/
O 399328
CPU Time DB2 38:46.8 Deadlocks 104 I/U/D 1006K Read
Eff 96.1
Wait All DB2 I/O 50:01.1 Timeouts 2 Dynamic 0 Pref
Reqs 1325269
Wt All Lock/Ltch 01:24:14.7 Escalations 0 DDL/DCL 0 Buf
Updts 7259658
Wait Log 0.0 L Prf No Stg 0 Calls 0 BP
Warn 0
DB2 Services 10:19.7 Parallel Err 0 CallFail 0 Avg I/
O 0.0140
Wt Data Shr Msgs 0.0 Para. Tasks
0
Wt Stor Proc TCB 0.0 Log
Write 2018155
Routine Elapsed 0.0 WLM
Name ........
Lock events 19631
Suspensions - Lock 7660
Suspensions - Latch 686
All indexes are type 2, and most of the ones that are updated have 3
or more lvls.
One of the biggest ones with contention is 5 levels.
Tables are all 4k page size.
stats and reorgs are run every week.
The base design of the tables mean that there are 4 main types of
primary indexes (unique)
1-60% of tables
2-30% of tables
3-4% of tables
4-4% of tables
and the remaining
All packages (COBOL with SQL) are using CS, and are bound at compile
time.