InSight DB2 Deadlocks and Timeouts

[Anon Anon]

I thought this was CA InSight board instead of CA Business Service Insight board.

Anyway, is there a way to capture DB2 deadlocks and timeouts and save the data for a week? Would like to see blocker and victim detailed information including SQL.

We had this feature in Mainview, should have a corollary in InSight.

Thanks,
Dana Rothrock

[Keith Sessions]

Hi There,

   Yes you can go into

CA INSIGHT "System History"   

 

 

Then select the

"Additional Systems Statistics Displays"

option

 

 

Then Report option

 

 

"Timeouts and Deadlocks History"

 

 

that should provide what you are looking for.

 

 

Thanks keith

[Anon Anon]

Hi, Keith,

 

I can't find the "Additional Systems Statistics Displays" option in InSight.

 

I went to "System History", entered a date/time range and hit PF5 List.  The "Additional" option did not appear on either screen.

I wonder if it is installed right.

 

Thanks for responding.  This is a new tool for us.

 

Dana

[Keith Sessions]

Hi Dana,

  I do not think there is any install problem, but there are some specific parms that need to be set to get this report working.

 

Have you got a telephone number i can call you on, it's probably best we go over the options together?

Also where are you based, so i can get my time zones correct for calling you?

 

      Thanks keith   

[Anon Anon]

Wait a minute.  I found it.  My screen action may be different than yours.

 

I selected a time period in System History List which displayed System Overview History, then option 7 for more...  This displays "Additional Systems Statistics Displays" with option 21 "Deadlock and Timeout List".

The problem with this is that you must know the hour in which the deadlocks occured.  Is there a way to see all deadlocks for a week's time?

 

Thanks,

Dana

[Anon Anon]

I found the answer.  After entering the time period on "System History Selection" screen, then hit PF6 Summary, then 6 More...., then option 20 - Deadlock and Timeout List.

 

Oh, my gosh, we have work to do.

 

Thanks, Keith, for the pointers.  This product needs a road map.

 

Dana

[Keith Sessions]

Hi Dana,

  Great to hear you got there in the end.  INSIGHT is certainly a great product with so much function and great features.

 

Chat to your CA services rep about maybe getting someone on site to give some training, it would be worth it.

 

      Keith 

[Anon Anon]

Hi, Keith,

 

We recently decommissioned BMC AppTune, so we will now  be relying more on InSight.  I don’t use it that much, but we have issues now and then to ponder.

 

One thing I noticed today, the Timeouts and Deadlocks History appears to retain only the last 24 hours or so in test DB2, even though System History is kept longer.  Our production Timeouts and Deadlocks History appears to be kept for 4 days, but SQL details are not kept beyond one day.

 

We need to keep at least a week of Deadlocks and Timeouts History.

 

Can these parameters be adjusted?  Our InSight is hosted, so we have to submit service request to reconfigure.

 

Thank you,

Dana Rothrock

[e-mail]

Blue Cross and Blue Shield Association, Inc.

Project Technical Architecture / Enterprise IT

z/OS DB2 IMS CICS MQ

Software Architect

[phone]

Cell: [phone]

[Anon Anon]

Hello Dana,

 

Thank you for taking the time to use our CA Communities facility to post questions!

I noted that in your latest question you mentioned that you have decommissioned BMC AppTune.

 

A CA DB2 tools product that more closely matches AppTune would be our CA Detector product.

Both products are meant to be SQL Analysis tools, while Insight is an overall DB2 monitor, in the same vein as BMC Mainview.

So there will be some differences in functionality, mainly because Detector relies on reading control block structures to gather SQL statistics, and details like SQL text and host variables,

Insight, like most DB2 monitors, collects it's data using DB2's IFI facility. It starts traces and then reads the Ifcid records that are generated.

 

My apologies if you already know this, just wanted to ensure we are on the same page with regards to Insight's capabilities versus a product like AppTune.

 

But to answer your question regarding Insight's Timeout and Deadlock History, I'm assuming you are referring to the option available on the Active Threads display, option 4, the LOCKCONT panel.

As opposed to the panels available from System History, which you were discussing with Keith previously.

Please let me know if that isn't the case.

 

This panel, even though it has "History" in it's title, is not dispalying history data read from a history datastore, which is what System History is doing.

Everything in Insight depends on IQL, Insight Query Language, and in this case, the LOCKCONT IQL request.  You can find it in the CDBATREQ library.

If you browse this member, you will see this line near the end:

NEVENTS=120.

 

This causes Insight to keep 120 occurrences of the Ifcif records used by this IQL request in active memory. It's an internal table.

And when it gets to the 121st, then the oldest entry drops off the internal table.

 

So how far back in time you can go for this panel is based on recent activity.

However, since this is IQL, you are free to modify it if you wish. We have clients that do this on a regular basis, even write custom IQL to suit their requirements.

So you could increase the value used in the NEVENTS statement to get more data stored internally.

Of course, more data means more storage used.  And there is no ready method to determine what value will get you a full weeks worth of data.

 

Thankfully there is no need, because the system history displays accomplish the same thing, only the data persists longer because it is using an actual history datastore.

The Ifcids involved get written to our System Hisotry datasets because the Ifcid 172 and 196 records are listed in the  HIST-STATS-RECS sysparm.

 

And these Ifcids get written to System History based on the HISTORY-STATS-TIME parm, which defaults to 30 minutes.

However, you could change this to a smaller interval if you wish. We have clients that have it set to 1 minute. for example.

And given the nature of the System History datastore, it is very easy to have data that goes back several weeks, if not months.

 

But the point is that what you see on the Timeout and Deadlock History panel will very closely resemble the display on the System History Deadlock and Timeout List panel.

At least after 30 minutes have past from your current viewing of the Active Threads panel!

 

The other thing I wanted to point out is the display of SQL text.

Both Detector and AppTune are designed to gather this information in a very effecient manner.

Insight does not do this, because the cost using traditional Ifcid traces is too high.

 

So when viewing the data on the Timeout and Deadlock panels, either from Active Threads or System History, the technique Insight uses to dispaly SQL text is very basic.

If the SQL is static, then it fetches the SQL text from the catalog.

But if the SQL is dynamic, Insight gets the SQL text from the Dynamic Statement cache. Or at least Insight's snapshot of it.

 

It uses the DYNSQLTX panel to display the information.

And the quantity of dynamic statement cache SQL text stored by Insight is based in the DYNSQLST IQL request, which has a NEVENTS value of 10000.

Quite sizeable, but not sure how far back that would be in a very active system.

 

Again, you could play with the value, but then you have to consider storage utilization in the Insight DC address space.

 

I suspect the internal table for Insight's DYNSQLST IQL request is not likley to have the sql text for a timeout or deadlock thread which occurred several days in the past.

So there is a limitation with Insight's capabilities in this area.

 

Hope the above information proves helpful.

 

Regards

Michael Murray

[Anon Anon]

Hi, Michael,

 

This information is very enlightening.  We do have the entire CA suite of Detector, Subsystem Analyzer, RC Query, etc.

 

But, it sounds like we may be out of luck pulling a week’s history of deadlocks.  Nearly all of these are dynamic SQL from web services.  Our users, and our applications, may not be adept at handling timeouts and deadlocks and we need some form of surveillance.

 

Mainview/DB2 had this capability of saving deadlock and timeout history from weekly IPL to IPL.  We are looking for a similar capability, or change our methods to daily manual monitoring.

 

Are Alerts possible in timeouts and deadlocks?

 

Thank you,

Dana Rothrock

[e-mail]

Blue Cross and Blue Shield Association, Inc.

Project Technical Architecture / Enterprise IT

z/OS DB2 IMS CICS MQ

Software Architect

[phone]

Cell: [phone]

[Anon Anon]

Dana,

 

Apologies for not getting back to you sooner on your question.

Yes, you can use the Insight Exception Monitor to activate an exception to check for deadlocks encountered by an application.

There should be a standard one defined as part of the install.

The message looks like this:

"Deadlock encountered for PLAN=&PLANNAM,       

AUTH=&AUTHIDX, CONN=&CONNNAM, CORR=&CORRELATION"

A similar exception exists for Timeouts as well.

You can set an action to notify a DBA, or issue an WTO message that can be used by an automation tool like our OPS/MVS to send an email.

Hope the above information is helpful.

MIke

[Anon Anon]

Hi, Mike,

 

Thanks for the function descriptions.  I think I have authorities issues that I need to hash out with the host vendor.  The email notifications would be a much better option than saving trace data for weeks; and, of course, timely.  I don’t think we have any auto-operator software, though.  We have the full CA DB2 Tools package (RC Query, Detector, Plan Analyzer, etc.).  Do any of these products send emails?

 

Thanks,

Dana

[Anon Anon]

Hi Dana,

 

To answer your question about our products sending emails, that is not a function that has been incorporated into the CA DB2 products.

Most mainframe products with the traditional 3270 interface do not incorporate it.

However, most mainframe shops have some form of automation tool, for that reason.

They use it to automate shutdown and start, for instance.

If you don't have OPS/MVS, or CA Solve, perhaps you have a competitor's product, from BMC or IBM?

Basically a product that can read the console log and respond to the messages that appear there.

There is one possibility with Insight, but it involves integrating with a CA Common Services component called GSS.

It provides a Rexx interface, so you could code a REXX exec that could be dispatched by Insight, and the Rexx could interact with whatever software you are using to send emails from the mainframe.

But it can be quite involved.

It would be simpler if you had an automation product to capture the message from the Insight exception and send an email.

 

Mike