Summarizing use of SQL statements in a program

Tim Hare

Summarizing use of SQL statements in a program

I've tried searching for  this with little success, because I'm not sure exactly what to put as a search term.  What I'm looking for is a way to summarize the execution of a program's SQL statements and I'm wondering if some monitor already does this.  Seems to me that the calls to DSNxLI, at least in what I see for a COBOL listing, contain the program name (SQL-PROG-NAME in UTF-8) and the statement number (SQL-STMT-NUM) for each SQL statement after it is pre-processed, in the SQL-PLISTx parameter list.   Having a count by SQL-STMT-NUMBER would give us a good 'profile' of where the program is spending its DB2 time.  

Do any of the monitoring tools do this? 

Philip Sevetson

Summarizing use of SQL statements in a program
(in response to Tim Hare)
Tim,

I’m aware of Compuware STROBE for DB2 doing this. Not sure who else does. I’ll let the Compuware folks give you details about it.

Also, Compuware folks? If you have this discussion on-thread, please include [AD] in the title to discuss product features.

Thanks, all.

--Phil

From: Tim Hare [mailto:[login to unmask email]
Sent: Thursday, February 15, 2018 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] - Summarizing use of SQL statements in a program


I've tried searching for this with little success, because I'm not sure exactly what to put as a search term. What I'm looking for is a way to summarize the execution of a program's SQL statements and I'm wondering if some monitor already does this. Seems to me that the calls to DSNxLI, at least in what I see for a COBOL listing, contain the program name (SQL-PROG-NAME in UTF-8) and the statement number (SQL-STMT-NUM) for each SQL statement after it is pre-processed, in the SQL-PLISTx parameter list. Having a count by SQL-STMT-NUMBER would give us a good 'profile' of where the program is spending its DB2 time.

Do any of the monitoring tools do this?

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

Bill Gallagher

Summarizing use of SQL statements in a program
(in response to Tim Hare)
CA-Insight can do this. I used it at my previous job. You can create a report and let a filter narrow in specifically on the DB2 package(s) you want to monitor. It will give you not only the number of times a particular SQL statement in a package executed during the period of time in which you had the report active and collecting data, but it would also show you the total elapsed time and total DB2 CPU time spent processing each statement. That, too me, was more meaningful than just a count of the number of times a statement ran.

Bill Gallagher
DB2 Database Administrator
State of Connecticut
Department of Children and Families
Office: 860-263-1389
[login to unmask email]<mailto:[login to unmask email]>


From: Tim Hare [mailto:[login to unmask email]
Sent: Thursday, February 15, 2018 12:11 PM
To: [login to unmask email]
Subject: [DB2-L] - Summarizing use of SQL statements in a program


I've tried searching for this with little success, because I'm not sure exactly what to put as a search term. What I'm looking for is a way to summarize the execution of a program's SQL statements and I'm wondering if some monitor already does this. Seems to me that the calls to DSNxLI, at least in what I see for a COBOL listing, contain the program name (SQL-PROG-NAME in UTF-8) and the statement number (SQL-STMT-NUM) for each SQL statement after it is pre-processed, in the SQL-PLISTx parameter list. Having a count by SQL-STMT-NUMBER would give us a good 'profile' of where the program is spending its DB2 time.

Do any of the monitoring tools do this?

-----End Original Message-----

Tim Hare

RE: Summarizing use of SQL statements in a program
(in response to Bill Gallagher)

Is CA-Insight different from the tools they bought from Platinum years ago? We still have those tools (up-to-date versions I mean)

Edited By:
Tim Hare[Organization Members] @ Feb 15, 2018 - 12:44 PM (America/Eastern)

Bill Gallagher

Summarizing use of SQL statements in a program
(in response to Tim Hare)
It’s been so long since CA acquired Platinum that I honestly don’t remember if Insight was a Platinum product that came along with the purchase, or if it was already a CA product at the time of acquisition.

Bill Gallagher
DB2 Database Administrator
State of Connecticut


From: Tim Hare [mailto:[login to unmask email]
Sent: Thursday, February 15, 2018 12:44 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Summarizing use of SQL statements in a program


Is CA-Insight different from the tools they bought from Platinum years ago? We still have those tools.

-----End Original Message-----

Carol Anne Sutfin

Summarizing use of SQL statements in a program
(in response to Bill Gallagher)
No, Insight did not come from Platinum. I believe it was developed by CA staff.
Carol Sutfin



Sent from my Samsung Galaxy , an AT&T LTE smartphone
-------- Original message --------From: "GALLAGHER, WILLIAM" <[login to unmask email]> Date: 2/15/18 11:50 AM (GMT-06:00) To: "'[login to unmask email]'" <[login to unmask email]> Subject: [DB2-L] - RE: Summarizing use of SQL statements in a program


It’s been so long since CA acquired Platinum that I honestly don’t remember if Insight was a Platinum product that came along with the purchase, or if it was
already a CA product at the time of acquisition.
 
Bill Gallagher
DB2 Database Administrator
State of Connecticut
 
 
From: Tim Hare [mailto:[login to unmask email]


Sent: Thursday, February 15, 2018 12:44 PM

To: [login to unmask email]

Subject: [DB2-L] - RE: Summarizing use of SQL statements in a program
 
Is CA-Insight different from the tools they bought from Platinum years ago? We still have those tools.
 

-----End Original Message-----



Site Links:
View post online  
View mailing list online  
Start new thread via email  
Unsubscribe from this mailing list  
Manage your subscription  


This email has been sent to: [login to unmask email]

** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **

---> Chennai, India, March 29, 2018 <---

http://ibm.biz/IDUGChennai2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

J&#248;rn Thyssen

[AD] RE: Summarizing use of SQL statements in a program
(in response to Tim Hare)

Hi,

Which Db2 monitoring vendor products do you have?

IBMs SQL level monitor is Db2 Query Monitor. 

There are also other options if you have IBM Omegamon Db2.

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

Views are personal. 

Tim Hare

RE: [AD] RE: Summarizing use of SQL statements in a program
(in response to Jørn Thyssen)

We have the DB2 Tools from CA that used to be Platinum's,  which one of the DBAs (I am a systems programmer / consultant) showed me does have counts by statement number (whether it keeps a record for historical purposes we are still researching) and Omegamon/DB2 .   Ideally we need "historical" records because you don't always know that something used a lot of resources until the next morning...

Michael Hannan

RE: Summarizing use of SQL statements in a program
(in response to Tim Hare)

Tim,

It is not enough to have a count of the SQL calls, to be a good indicator of performance. Most advanced tools are capable of capturing other information to indicate performance, the most critical is of course CPU time consumed, both general processor and zIIP. The next most important one is usually Getpages followed by Elapesed time, Waits for I/O and Lock etc.

There are quite a number of tools that monitor SQLs. APA and Strobe are examples of tool that sample a Job or Region based on a request. They have high overhead options to collect additional DB2 Getpages and I/O information, beyond the normal CPU time and SQL call count estimates.

The best tools for tuning SQL sample more on an ongoing basis so the next day you look back at what happened yesterday, e.g. IBM's Query Monitor, BMC Apptune, CA Detector. I have used all 3 to assist tuning. 

IBM SMF Accounting Data at Package level collects data for Package but not at individual SQL Statement level. Even more handy though if your Package happens to have only one SQL section.

Package level data collection Class 7 becomes more useful (beyond just CPU and total SQL count) if you turn on Class 10 collection which includes Getpages, and other useful fields at Package level such as SQLs call counts by Type. Otherwise they are collected by Class 2 at Thread level. It is possible to tune with just this information and extended Explain access path details, if you can detect which SQL statements are the expensive ones. Packages with a lot of sections are more difficult.

Web searches ought to be able to give more details of all the tools out there in the market place. Some of them can be quite expensive. You need to compare the price compared to the functionality and consider the tool overheads added to the cost of your application Packages. They all come with an overhead when turned on. Turning the tools off, or using reduced monitoring,  in your monthly peak workload time, is a good option.

I judge a tool by how much it can increase my productivity in tracking down and solving problem performance SQLs. The tool does not have to have perfection in how it measures the costs. SMF data is for accurate  measurement.


 In Reply to Tim Hare:

I've tried searching for  this with little success, because I'm not sure exactly what to put as a search term.  What I'm looking for is a way to summarize the execution of a program's SQL statements and I'm wondering if some monitor already does this.  Seems to me that the calls to DSNxLI, at least in what I see for a COBOL listing, contain the program name (SQL-PROG-NAME in UTF-8) and the statement number (SQL-STMT-NUM) for each SQL statement after it is pre-processed, in the SQL-PLISTx parameter list.   Having a count by SQL-STMT-NUMBER would give us a good 'profile' of where the program is spending its DB2 time.  

Do any of the monitoring tools do this? 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Feb 16, 2018 - 11:41 AM (Europe/Berlin)

Troy Coleman

RE: Summarizing use of SQL statements in a program
(in response to Carol Anne Sutfin)

CA-Insight renamed as CA SYSVIEW Performance Management Option for Db2 for z/OS was acquired from Platinum Technology which was acquired by several more companies.  I worked with Insight back in the late 80's when I  believe Goal Systems which I believe was then acquired by Legent and then Platinum then CA.

Joel Goldstein

Summarizing use of SQL statements in a program
(in response to Troy Coleman)
Insight was the first DB2 monitor I used in the mid 80’s.

I think it was before DB2PM was available.



I think Goal was bought by Legent, then Legent bought by CA in ‘95



So many software companies have disappeared since the boom days of DB2 software products…



Remember the Platinum and BMC party face-offs at IDUG -- the two story BMC booth…

When Platinum had The Dallas Cowboy Cheerleaders at their party…

When there used to be (probably) more than 30+ vendors at a conference.



About a yr ago, I started making a list of all the DB2 vendors that don’t exist anymore… I was over 30…

Wonder where I saved that list…



Joel Goldstein
Responsive Systems
Buffer Pool Tool(R) for DB2, the worldwide industry standard

Predicts the IO rate/Sec for tuning changes
Performance software that works......
Predicts Group Buffer Pool performance too!
http://www.responsivesystems.com www.responsivesystems.com
tel. (732) 972-1261
fax.(732) 972-9416

[login to unmask email]



From: Troy Coleman [mailto:[login to unmask email]
Sent: Friday, February 16, 2018 10:36 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Summarizing use of SQL statements in a program



CA-Insight renamed as CA SYSVIEW Performance Management Option for Db2 for z/OS was acquired from Platinum Technology which was acquired by several more companies. I worked with Insight back in the late 80's when I believe Goal Systems which I believe was then acquired by Legent and then Platinum then CA.



-----End Original Message-----

Craig Mullins

Summarizing use of SQL statements in a program
(in response to Joel Goldstein)
Platinum never owned Insight for DB2. Pretty sure CA bought it from Legent (who bought it from Goal Systems, who I think bought it from the original owner whose name I cannot recall). But I know we never had it at Platinum.

Cheers,
Craig S. Mullins
Mullins Consulting, Inc.
www.MullinsConsulting.com

Sent from my iPhone

> On Feb 17, 2018, at 12:27 AM, Joel Goldstein - Responsive Systems <[login to unmask email]> wrote:
>
> Insight was the first DB2 monitor I used in the mid 80’s.
> I think it was before DB2PM was available.
>
> I think Goal was bought by Legent, then Legent bought by CA in ‘95
>
> So many software companies have disappeared since the boom days of DB2 software products…
>
> Remember the Platinum and BMC party face-offs at IDUG -- the two story BMC booth…
> When Platinum had The Dallas Cowboy Cheerleaders at their party…
> When there used to be (probably) more than 30+ vendors at a conference.
>
> About a yr ago, I started making a list of all the DB2 vendors that don’t exist anymore… I was over 30…
> Wonder where I saved that list…
>
> Joel Goldstein
> Responsive Systems
> Buffer Pool Tool(R) for DB2, the worldwide industry standard
> Predicts the IO rate/Sec for tuning changes
> Performance software that works......
> Predicts Group Buffer Pool performance too!
> www.responsivesystems.com
> tel. (732) 972-1261
> fax.(732) 972-9416
> [login to unmask email]
>
> From: Troy Coleman [mailto:[login to unmask email]
> Sent: Friday, February 16, 2018 10:36 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: Summarizing use of SQL statements in a program
>
> CA-Insight renamed as CA SYSVIEW Performance Management Option for Db2 for z/OS was acquired from Platinum Technology which was acquired by several more companies. I worked with Insight back in the late 80's when I believe Goal Systems which I believe was then acquired by Legent and then Platinum then CA.
>
>
> -----End Original Message-----
>
> Site Links: View post online View mailing list online Start new thread via email Unsubscribe from this mailing list Manage your subscription
>
> This email has been sent to: [login to unmask email]
> ** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
> ---> Bengaluru, India, March 27, 2018 <---
> http://ibm.biz/IDUGBengaluru2018
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

James Campbell

[AD] Summarizing use of SQL statements in a program
(in response to Tim Hare)
I assume you are referring to Detector and/or Subsystem Analyzer. When you create a
datastore you specify how many intervals you want to keep in it. When you start
detecting/analyzing you specify how long the intervals will be. (At least that's what my
memory is telling me.)

And, of course, the datastore has to be created big enough for all that data.

James Campbell

On 15 Feb 2018 at 12:56, Tim Hare wrote:

>
> We have the DB2 Tools from CA that used to be Platinum's,  which one of the DBAs (I am a
> systems programmer / consultant) showed me does have counts by statement number (whether it
> keeps a record for historical purposes we are still researching) and Omegamon/DB2 .   Ideally we
> need "historical" records because you don't always know that something used a lot of resources
> until the next morning...
>
>

Chris Hoelscher

[AD] Summarizing use of SQL statements in a program
(in response to James Campbell)
In case anyone cares - we set up detector at 1 hour intervals, 768 intervals (gives us 30+ days), and we roll over live datastores to archive datastores every month

Its lots of DASD but our performance folks (and developers) justify it - we also capture distributed SQL text to help us track DB2-mandated SQL changes due to char/varchar changes

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

-----Original Message-----
From: James Campbell [mailto:[login to unmask email]
Sent: Sunday, February 18, 2018 1:43 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [AD] Summarizing use of SQL statements in a program

I assume you are referring to Detector and/or Subsystem Analyzer. When you create a datastore you specify how many intervals you want to keep in it. When you start detecting/analyzing you specify how long the intervals will be. (At least that's what my memory is telling me.)

And, of course, the datastore has to be created big enough for all that data.

James Campbell

On 15 Feb 2018 at 12:56, Tim Hare wrote:

>
> We have the DB2 Tools from CA that used to be Platinum's,  which one
> of the DBAs (I am a systems programmer / consultant) showed me does
> have counts by statement number (whether it keeps a record for
> historical purposes we are still researching) and Omegamon/DB2 .  
> Ideally we need "historical" records because you don't always know that something used a lot of resources until the next morning...
>
>

-----End Original Message-----

Jeff Gross

RE: [AD] Summarizing use of SQL statements in a program
(in response to Chris Hoelscher)

All,

Thanks for the shout out on the product CA-Detector for Db2 for z/OS.  

 

Going back to Tim's initial query;

"What I'm looking for is a way to summarize the execution of a program's SQL statements and I'm wondering if some monitor already does this.   Do any of the monitoring tools do this?  "    Yes, this is the exact reason the product was written.  

 

The product is still actively developed and we validate new functionality using a customer validation process.  Sign up at Validate.ca.com and look for the "CA DB2 Tools 20.0" entry.  

 

Tim, feel free to reach out to me for some product assistance or contact our support team at support.ca.com

Thanks,

Jeff Gross

Product Owner for CA Detector for Db2 for z/OS

Tim Hare

RE: Summarizing use of SQL statements in a program
(in response to Michael Hannan)

I know there are many facets of performance tuning.  My question was more about using the a count _by_statement_number_  to determine where the application code was spending time (since we don't have Strobe and we don't have output from the LIST option of the COBOL compiler,  we realized the SQL statement numbers were also an indicator of location in the program)

Philip Sevetson

Summarizing use of SQL statements in a program
(in response to Tim Hare)
Tim,

This is a first cut at how to look up this information.


1. I believe there’s an IFCID which reports the instance of an execution of a SQL statement.

2. IFCID definitions are stored in the IBM-supplied file, provided by IBM as DSNA10.SDSNIVPD(DSNWMSGS)

3. DSNWMSGS shows the PERFORMANCE class 3 as containing SQL-related events:

3 SQL-RELATED EVENTS 173,22,53,55,

58,59,60,61,

62,63,64,65,

66,92,95-96,

97,106,112,

177,233,237,

272,311,324,

343,360

4. Examination of DSNWMSGS indicates that IFCID 53 is probably written for each executed SQL statement.

5. You’d want to
-START TRACE(PERFM) DEST(SMF) CLASS(3) PKGCOL(Collection) -
PKGPROG(program name) IFCID(53) COMMENT('SQL COUNTS')
for a particular plan and package. I believe a TRACE ID or TRACE NUMBER is returned when you start the trace.

6. When you’re done with the SQL Count, run
-STOP TRACE
for the created trace. Your SMF data should appear in the next generation of SMF datasets which are cut by the system.

7. Then, you’d need a SMF reader product or homegrown interpreter to tell you which statements were executing (and a copy of the Precompile, or a catalog read of SYSIBM.SYSPACKSTMT)

8. Troy Coleman wrote a post describing, for V9, how to create tables which contain SMF data. It might be useful in reading the data:
http://ibmsystemsmag.com/blogs/db2utor/archive/where-to-find-d/

Hope this helps. Let us know how things go, whether you use this or not, please?

--Phil Sevetson


From: Tim Hare [mailto:[login to unmask email]
Sent: Friday, February 23, 2018 11:35 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: Summarizing use of SQL statements in a program


I know there are many facets of performance tuning. My question was more about using the a count _by_statement_number_ to determine where the application code was spending time (since we don't have Strobe and we don't have output from the LIST option of the COBOL compiler, we realized the SQL statement numbers were also an indicator of location in the program)

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

J&#248;rn Thyssen

RE: Summarizing use of SQL statements in a program
(in response to Tim Hare)

Hi Tim,

I believe you mentioned you had access to Omegamon Db2? 

 

When you follow Phil's instructions in the previous post you can use Omegamon's Batch CRD collector to write the SMF records in step 5 and 6 directly to a dataset instead of your system SMF destination, and you can use Omegamon's SMF formatter for creating reports in Step 7. 

In this post https://www.idug.org/p/fo/st/topic=19&post=184704#p184704 I attached some examples on Batch CRD and SMF formatting (albeit for a different IFCID).

Best regards,

Jørn Thyssen

Rocket Software
77 Fourth Avenue • Waltham, MA • 02451 • USA
E: [login to unmask email] • W: www.rocketsoftware.com 

2018 IBM Champion.

Views are personal.