You didn't tell us the nature of the
"transactions" whose counts are high. Let's
assume they are INSERTS and you have a locking
problem causing the timeouts. [For DELETEs the following also
I recall a scenario from when I was teaching
IBM's DB2 z/OS Application Performance and Tuning
class back around version 8 or 9. For INSERTs or
DELETEs which may have multiple indexes [say
power utility ACCOUNT with a PAYMENTS table
having 4 indexes leading on TRANS#, ACCOUNT,
DATE, and PAYMENT$ respectively--I'll call them T, A, D and P].
If you do an INSERT into PAYMENTS, using
spinning discs you might expect worst case 5
synchronous reads of say 10-millisec each, one to
the table and one to each index, T, A, D and
P. [Note if RI tests are needed, add 1 SR per test.]
While the X-lock is only on the table, the
duration of the x-lock include the 5 SRs or up to 50-ms or 1/20th
of a second.
A clever IBM Swiss systems engineer in the
1990's came up with the "Swiss solution" - first
do four SELECTs with no locks [UR] so that 4 SR's
are done before the INSERT bringing the 4 index
pages into their respective buffer pools before
the INSERT. Each would be INDEX ONLY in the order
of the index key, [for example the P index might
be (Payment$, Trans#, Account, Date)] such as
SELECT Account, Date from PAYMENTS WHERE
Payment$ = ? and Trans# = ? WITH UR]; similarly
for every other index [and RI check].
When the INSERT is done, the table read gets
the 1 SR and all other index inserts and RI
checks are in the buffer pool, shortening the X-lock to just
If there is a one-to-many INSERT [like ORDERS,
ORDER-LINES] this pre-reading of all of the
ORDERS and ORDER-LINES indexes using the Swiss
solution may reduce x-lock durations by 1/50th or more.
I understand this requires an application
change and all that that implies. But if your
company has mission critical processes that need
to be improved this is one technique.
Hope this is helpful. Contact me offline to continue this.
Larry Kintisch Pres, ABLE Information Services
[login to unmask email]
At 08:18 AM 9/15/2017, you wrote:
>Recently we have run in to situations where our
>ssidMSTR starts consuming high amounts of cpu
>when transaction counts are high. We get
>rollbacks due to timeouts which take long
>amounts of elapsed time to rollback. Meanwhile
>transactions – CICS and DDF continue to queue up.
>Has anyone else wandered in to this situation? We are v11.
>We have been unable to get a dump (mgt doesn’t
>want to take the time). Our dumps are taking 10 seconds or more
>What would take rollbacks so long to
>complete. Lots of logs to read through?
>IBM isn’t much help without a dump to analyze.
>Thanks in advance.
>online <mailto:[login to unmask email]>Start
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing
>subscription This email has been sent to: [login to unmask email]
>Setup a data refresh task in less time than it
>takes to make a cup of coffee + save up to 90%
>in CPU ESAi's BCV5 & XDM fast data refresh &
>Test Data Mgmt products will make you a hero to
>users. See http://www.ESAIGroup.com/idug
>Use of this email content is governed by the terms of service