473,324 Members | 2,196 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

DB2 performance on a V7.1 on Z/OS - long post

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.
>From my initial analysis of the problem, my solution to this
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.

Mar 2 '07 #1
1 2200
Just to add another detail.

ALL tables are on their own table space.

After changing some of the tables to row lock on a DEV environment,
the max lock went up to 800, while suspention whent down to around 5%
of the original run on the same amount of data. And this was without
changing all my intended tables.

Mar 2 '07 #2

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

6
by: Andreas Lauffer | last post by:
I changed from Access97 to AccessXP and I have immense performance problems. Details: - Access XP MDB with Jet 4.0 ( no ADP-Project ) - Linked Tables to SQL-Server 2000 over ODBC I used...
5
by: MGB | last post by:
I have a strange performance question hopefully someone can clarify for me. I take a production database and make a copy of it, called test, on the same instance on the same server both running at...
5
by: Steve_CA | last post by:
Hello all, I've been recruited to assist in diagnosing and fixing a performance problem on an application we have running on SQL Server 7. The application itself is third party software, so we...
5
by: Scott | last post by:
I have a customer that had developed an Access97 application to track their business information. The application grew significantly and they used the Upsizing Wizard to move the tables to SQL...
7
by: arndt.blumenthal | last post by:
Hi, i implement a ASP.NET-Application for a customer. Every day the customer has the problem, that the first access to the application takes very long. What could be the reason? Thanks for...
20
by: Development - multi.art.studio | last post by:
Hello everyone, i just upgraded my old postgres-database from version 7.1 to 7.4.2. i dumped out my 7.1 database (with pg_dump from 7.1) as an sql-file with copy-commands and to one file using...
46
by: dunleav1 | last post by:
I have a process that does inserts that runs 50% slower that Oracle and Mssql. Queries normally take 50% slower than normal. DB2, Oracle, Mssql all are configured on same os, same disk array...
7
by: David Veeneman | last post by:
This is a very simple question, but for moe reason I can't find an answer. Do I take a performance hit if a foreach statement has to evaluate an expression? For example, consider the following:...
8
by: NAdir | last post by:
Hi, thank you for your help. My VB.Net application contains a document that the user can refresh at any time. The refresh works fine and needs to loop through few datatables (hundreds of rows)....
8
by: mveygman | last post by:
Hi, I am writing code that is using std::map and having a bit of an issue with its performance. It appears that the std::map is significantly slower searching for an element then a sequential...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.