DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?

John SooHoo

DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?

Dear Esteemed Listers,

 

Has anyone ever found a way to develop the SQL equivalent of a syspackdep for dynamic SQL (a sort of sysSQLdep)?  I'm thinking specifically of trying to analyze all the SQL (which is mostly dynamic) for a particular application's service ID, what the SQL is and what indexes they use.

If there is a way to do that, I would appreciate if someone could illuminate the steps taken to get there.

Thanks in advance for any help in this matter.

 

John Soohoo

Roy Boxwell

DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to John SooHoo)
Write a little routine that snaps the DSC and EXPLAINS all the statements within. Then you have the data you need. Start with EXPLAIN STMTCACHE ALL and then loop through the output doing EXPLAIN STMTCACHE STMTID xxxxx read from the first EXPLAIN. If this looks too difficult, scary, not all the data can be processed quickly enough, it can’t be done [delete what is not applicable] then contact a few vendors as they all can do this as well!


Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: John SooHoo [mailto:[login to unmask email]
Sent: Friday, July 6, 2018 12:41 AM
To: [login to unmask email]
Subject: [DB2-L] - DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?


Dear Esteemed Listers,



Has anyone ever found a way to develop the SQL equivalent of a syspackdep for dynamic SQL (a sort of sysSQLdep)? I'm thinking specifically of trying to analyze all the SQL (which is mostly dynamic) for a particular application's service ID, what the SQL is and what indexes they use.

If there is a way to do that, I would appreciate if someone could illuminate the steps taken to get there.

Thanks in advance for any help in this matter.



John Soohoo

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

Michael Hannan

RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to John SooHoo)



In Reply to John SooHoo:

Has anyone ever found a way to develop the SQL equivalent of a syspackdep for dynamic SQL (a sort of sysSQLdep)?  I'm thinking specifically of trying to analyze all the SQL (which is mostly dynamic) for a particular application's service ID, what the SQL is and what indexes they use.

I don't want to advertise any specific product (and I am not affiliated)  but there are various tools out there that capture Dynamic SQLs as they run, and an Object Analysis function that will show which objects were referenced, and better still, give some info on Getpages, Sync I/Os, Prefetch I/Os etc. for each Object. It maybe that Object Analysis can be switched on for specific workloads.

I am aware of one site that then captures some of this info into DB2 Tables. Really great info for tuning since it can show which objects have worst performance in SQLs that are not so simple.

Dynamic Explain of captured SQLs (as Roy mentioned) can show what access path would be used now with current Catalog Stats etc., for the SQLs. Note that some Dynamic SQLs are not explainable without amendment due to DB2 not being aware of parameter marker data type. Can be explained with modification.

Building your own tool to do it or part of it, might be quite a bit of work. There might be performance traces in DB2 that can do a lot of it, but with high overhead. 

I am aware of one site explaining the full Dynamic Statement Cache. Not sure what benefit they get from it, as dynamic workload is not the dominant thing. It may depend on what is in the cache at the time they sample it. It could be very expensive to sample the Dynamic Stmt Cache very frequently and then try to Explain the whole lot, unless can recognise which SQLs were new, otherwise a very major CPU burner. The danger with DSC is that not all SQLs necessarily appear there. SQL could be thrown out before Sampling it.

Object Analysis is also handy for tuning Static SQLs, especially complex ones.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

John SooHoo

RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to Roy Boxwell)

Thank you, Roy.

We had looked briefly at the Explain Dynamic Statement Cache feature when it was first made available.  As I recall, we had to turn on some additional traces for the period of time that we did the explain.  Because of that, I was not sure that was something that we should do all the time.  At the time it seemed like something for a one-of troubleshooting rather than regular monitoring.  I'll take a second look.

Thanks again.

John

John SooHoo

RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to Michael Hannan)

Thank you Michael, for your thoughts.

We have something that captures dynamic SQL but there is no information on indexes used, which was what I was looking to get.  To convert that format to something that could be used in an explain would take a little doing.

Thank you for the caveat on resource usage of traces and explain.  We will have to keep that in mind.

Regarding, "not all SQLs necessarily appear there," are you referring to a statement being flushed out of the cache because of non-use or need for space for new statements (perhaps because the cache is undersized)?

John

Michael Hannan

RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to John SooHoo)



In Reply to John SooHoo:

We have something that captures dynamic SQL but there is no information on indexes used, which was what I was looking to get.  To convert that format to something that could be used in an explain would take a little doing.

Thank you for the caveat on resource usage of traces and explain.  We will have to keep that in mind.

Regarding, "not all SQLs necessarily appear there," are you referring to a statement being flushed out of the cache because of non-use or need for space for new statements (perhaps because the cache is undersized)?

John

I have written my own stuff to explain captured SQLs, so can be done.

Yes I mean that if you sample the Dyn Stmt Cache DSC once per day, then some SQLs that ran would be flushed out due to limited space there. I have even seen a long running expensive SQL flushed out before it completed, so its performance  numbers could not be attached to it. I don't know if IBM corrected that weakness from long ago.

If you sample the DSC very regularly, then you will collect a lot of duplicate SQLs, so need to eliminate dups before Explaining. Also may want to pattern match SQLs, rather than looking for exact literal match. Some Dynamic SQLs will fail Explain unless transformed. All this can be costly to do.  So I pointed out a tool that collects Object Analysis might be much easier.

You can find out if something is using your indexes easily, Last Used, but which SQL? Also DB2 12 will be collecting Getpages on Objects in real time Stats (also not on an SQL basis). I hope they populate it reliably. I have been annoyed by columns in there with Null "Value Not Known". Even an estimate would do.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

Chris Hoelscher

DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to Michael Hannan)
We use CA-Detector to capture dynamic sql – and it does indeed show the tables AND indexes used in the SQL

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

From: Michael Hannan <[login to unmask email]>
Sent: Wednesday, July 11, 2018 9:13 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?



In Reply to John SooHoo:

We have something that captures dynamic SQL but there is no information on indexes used, which was what I was looking to get. To convert that format to something that could be used in an explain would take a little doing.

Thank you for the caveat on resource usage of traces and explain. We will have to keep that in mind.

Regarding, "not all SQLs necessarily appear there," are you referring to a statement being flushed out of the cache because of non-use or need for space for new statements (perhaps because the cache is undersized)?

John

I have written my own stuff to explain captured SQLs, so can be done.

Yes I mean that if you sample the Dyn Stmt Cache DSC once per day, then some SQLs that ran would be flushed out due to limited space there. I have even seen a long running expensive SQL flushed out before it completed, so its performance numbers could not be attached to it. I don't know if IBM corrected that weakness from long ago.

If you sample the DSC very regularly, then you will collect a lot of duplicate SQLs, so need to eliminate dups before Explaining. Also may want to pattern match SQLs, rather than looking for exact literal match. Some Dynamic SQLs will fail Explain unless transformed. All this can be costly to do. So I pointed out a tool that collects Object Analysis might be much easier.

You can find out if something is using your indexes easily, Last Used, but which SQL? Also DB2 12 will be collecting Getpages on Objects in real time Stats (also not on an SQL basis). I hope they populate it reliably. I have been annoyed by columns in there with Null "Value Not Known". Even an estimate would do.

Michael Hannan,

DB2 Application Performance Specialist
CPT Global Ltd

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

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Roy Boxwell

DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to John SooHoo)
Those extra traces are basically free and are really really really worth their weight in gold!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert, Ulf Heinrich

From: John SooHoo [mailto:[login to unmask email]
Sent: Wednesday, July 11, 2018 4:34 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?


Thank you, Roy.

We had looked briefly at the Explain Dynamic Statement Cache feature when it was first made available. As I recall, we had to turn on some additional traces for the period of time that we did the explain. Because of that, I was not sure that was something that we should do all the time. At the time it seemed like something for a one-of troubleshooting rather than regular monitoring. I'll take a second look.

Thanks again.

John

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

Michael Hannan

RE: DB2 z/OS - Any way to create a sysSQLdep for Dynamic SQL?
(in response to Chris Hoelscher)

In Reply to Chris Hoelscher:

We use CA-Detector to capture dynamic sql – and it does indeed show the tables AND indexes used in the SQL

A question, if I may. I have used Detector a lot in the past for Static SQL tuning, but in recent years mainly another product for dynamic SQL tuning. 

These days, dynamic SQLs coming in can get hashed twice for the dynamic Stmt Cache, once with literals, and again with literals replaced by a marker (Concentrate SQLs With Literals function), to allow SQLs with same pattern to be matched. So is very helpful if the SQL Performance monitor product also has ability to pattern match the collected dynamic SQLs rather than report huge number of repeats of basically the same SQL with slightly different literal values, separately rather than summarised (I have written post processors to pattern match with some products in the past, e.g. APA). Does Detector have this capability to pattern match Dynamic SQLs? Such an ability in a product has allowed me to tune Dynamic SQLs easily without using the DSC snapshot sampling, although usually the products have a DSC sampling feature as well, for showing recent Dynamic.

Usually I want the long term history of a dynamic SQL pattern, so that I know it is big continually (every day), and worth tuning effort.

One more question occurs to me (on a diversion). Last time I opened Detector I was shown 80 char width screens and scroll right and left was needed. Yet I was using PCOM 62x160 screen size. Can Detector work with wider than 80 screen so I don't need to scroll left and right so much? Should it not be automatic?

I have some old manuals to check (and did) but they don't seem to answer questions that an experienced user might know. the answer too, easily. I don't have really recent manuals so they may have more info these days. To be fair I don't see the manuals from any of the competing products as being very comprehensive to explain functionality of the screens. Generally we learn these products by trial and error and maybe try a help screen sometimes hoping for a bit more detail than is typically provided.
 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jul 14, 2018 - 05:20 AM (Europe/Berlin)