Hi all,

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.

performance 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.