for those of you working on the ifcid 366/376 detected required modifications

Chris Hoelscher

for those of you working on the ifcid 366/376 detected required modifications

For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the "hit"? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives .....


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]: humana.com
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 714-8615, (502) 476-2538


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.
Attachments

  • image001.jpg (2.9k)
  • image004.jpg (<1k)

Muthuraj Kumaresan

for those of you working on the ifcid 366/376 detected required modifications
(in response to Chris Hoelscher)

I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

> On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email]> wrote:
>
> <image001.jpg>
> For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?
>
> If the former, how are you associating the sql text with the instance of the detected sql?
>
> We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..
>
>
> thanks
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> <image004.jpg>: humana.com
> 123 East Main Street
> Louisville, KY 40202
> Humana.com
> (502) 714-8615, (502) 476-2538
>
>
> 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.
>
> Attachment Links: image001.jpg (3 k) image004.jpg (1 k)
> 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]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Chris Hoelscher

for those of you working on the ifcid 366/376 detected required modifications
(in response to Muthuraj Kumaresan)
I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]: humana.com
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 714-8615, (502) 476-2538

From: mzkumaresan [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 8:07 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:
<image001.jpg>
For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
<image004.jpg>: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538


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.
-----End Original Message-----

-----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.
Attachments

  • image002.jpg (<1k)

Venkat Srinivasan

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Chris Hoelscher)

Chris,

Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then. 

On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.

Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.

The assumption here is that 366 is cut reasonably soon so you can query sql cache.

Perhaps you can talk to Roy hopefully he does it for you in his tool. 
Venkat


In Reply to Chris Hoelscher:

I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]: humana.com
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 714-8615, (502) 476-2538

From: mzkumaresan [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 8:07 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:

For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538


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.
-----End Original Message-----

-----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.

Venkat Srinivasan

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Venkat Srinivasan)

Have you considered tracing 350 which has a stmt id that you could match? If volume is a concern (it ought to be rightfully), you could limit 350 to a set of applications, and do this whole process piece by piece.

Venkat 

In Reply to Venkat Srinivasan:

Chris,

Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then. 

On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.

Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.

The assumption here is that 366 is cut reasonably soon so you can query sql cache.

Perhaps you can talk to Roy hopefully he does it for you in his tool. 
Venkat


In Reply to Chris Hoelscher:

I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]: humana.com
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 714-8615, (502) 476-2538

From: mzkumaresan [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 8:07 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:

For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538


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.
-----End Original Message-----

-----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.

Muthuraj Kumaresan

for those of you working on the ifcid 366/376 detected required modifications
(in response to Venkat Srinivasan)

I am not sure how big your environment is.. you can cut dynamic statement cache dump for every 15 or 30
Mins using explain cache all statement.. ( I have seen CPU spike when explain statement cache dump runs)..then you can match dynamic cache dump table with 376 data.. but as Srinivas said it won't be easy.. because
1.statement ID is different b/w DB2s
2.statement will be evicted based on LRU algorithm
3. Statement ID will be reused after db2 recycle

So you will need to match the statement ID with cache table asap..

Or else, you can write demon which can monitor 376 in real time.. as soon as it sees a record , demon can cut 316/317/318 record that belong to the statementID.. or you can run explain statement cache stmtid for the statement id found on 376..

Muthu
Sent from my iPhone

> On 11 Mar 2017, at 11:54 AM, Venkat Srinivasan <[login to unmask email]> wrote:
>
> Have you considered tracing 350 which has a stmt id that you could match? If volume is a concern (it ought to be rightfully), you could limit 350 to a set of applications, and do this whole process piece by piece.
>
> Venkat
>
> In Reply to Venkat Srinivasan:
>
> Chris,
>
> Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then.
>
> On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.
>
> Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.
>
> The assumption here is that 366 is cut reasonably soon so you can query sql cache.
>
> Perhaps you can talk to Roy hopefully he does it for you in his tool.
> Venkat
>
>
> In Reply to Chris Hoelscher:
>
> I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)
>
> thanks
>
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> [Description: Description: cid:[login to unmask email]: humana.com
> 123 East Main Street
> Louisville, KY 40202
> Humana.com
> (502) 714-8615, (502) 476-2538
>
> From: mzkumaresan [mailto:[login to unmask email]
> Sent: Friday, March 10, 2017 8:07 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications
>
>
> I think your question is more related to dynamic SQL rather than distributed threads..
>
> Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..
>
> for static SQL, it should be easy as you can get it from syspackstmt..
>
>
>
> Muthu
> Sent from my iPhone
>
> On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email]>> wrote:
>
> For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?
>
> If the former, how are you associating the sql text with the instance of the detected sql?
>
> We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..
>
>
> thanks
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> : humana.com<http://humana.com>
> 123 East Main Street
> Louisville, KY 40202
> Humana.com<http://Humana.com>
> (502) 714-8615, (502) 476-2538
>
>
> 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.
> -----End Original Message-----
>
> -----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.
>
> 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]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Daniel Luksetich

for those of you working on the ifcid 366/376 detected required modifications
(in response to Chris Hoelscher)
I have to admit I’ve only been scanning this thread quickly so toss this if it isn’t helpful, but follow this link.



<http://www.enterprisesystemsmedia.com/it-management/snapping-the-db2-for-z-os-dynamic-statement-cache> http://www.enterprisesystemsmedia.com/it-management/snapping-the-db2-for-z-os-dynamic-statement-cache



maybe it can help.



Dan



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Chris Hoelscher [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 7:15 PM
To: '[login to unmask email]' <[login to unmask email]>
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications



I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)



thanks



Chris Hoelscher

Technology Architect, Database Infrastructure Services

Technology Solution Services

: humana.com

123 East Main Street

Louisville, KY 40202

Humana.com

(502) 714-8615, (502) 476-2538



From: mzkumaresan [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 8:07 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications





I think your question is more related to dynamic SQL rather than distributed threads..



Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..



for static SQL, it should be easy as you can get it from syspackstmt..







Muthu
Sent from my iPhone


On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email] <mailto:[login to unmask email]> > wrote:

<image001.jpg>

For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?



If the former, how are you associating the sql text with the instance of the detected sql?



We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..





thanks

Chris Hoelscher

Technology Architect, Database Infrastructure Services

Technology Solution Services

<image004.jpg>: humana.com <http://humana.com>

123 East Main Street

Louisville, KY 40202

Humana.com <http://Humana.com>

(502) 714-8615, (502) 476-2538




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.

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



-----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.

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

Venkat Srinivasan

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Muthuraj Kumaresan)

Explain stmtcache stmtid is to get the cached access path into plan table and to my knowledge it doesnt externalize the sql. 316 and 318 (only enables the stmt level performance counters on 316) aren't needed. 376 is a rollup entry of 366 and it may be too late when it is written. 376 / 350 limited by specific application authid is an alternative which can be dealt by reading from smf destination. 350 can generate a lot of data. 

explain stmt cache all and populate a scratch pad statement table

merge the scratch pad into a permanent statement table tracker on its unique key

delay 5 mins or whatever

go back to explain stmt cache all into scratch pad

if you enable 318 you now have a poor man's sql level performance monitor. It will be intrusive. potential stmt id reuse / restart of db2 will be an issue to the context. In datasharing the intrusion will be on all members.   

Venkat 

In Reply to Muthuraj Kumaresan:


I am not sure how big your environment is.. you can cut dynamic statement cache dump for every 15 or 30
Mins using explain cache all statement.. ( I have seen CPU spike when explain statement cache dump runs)..then you can match dynamic cache dump table with 376 data.. but as Srinivas said it won't be easy.. because
1.statement ID is different b/w DB2s
2.statement will be evicted based on LRU algorithm
3. Statement ID will be reused after db2 recycle

So you will need to match the statement ID with cache table asap..

Or else, you can write demon which can monitor 376 in real time.. as soon as it sees a record , demon can cut 316/317/318 record that belong to the statementID.. or you can run explain statement cache stmtid for the statement id found on 376..

Muthu
Sent from my iPhone

> On 11 Mar 2017, at 11:54 AM, Venkat Srinivasan <[login to unmask email]> wrote:
>
> Have you considered tracing 350 which has a stmt id that you could match? If volume is a concern (it ought to be rightfully), you could limit 350 to a set of applications, and do this whole process piece by piece.
>
> Venkat
>
> In Reply to Venkat Srinivasan:
>
> Chris,
>
> Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then.
>
> On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.
>
> Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.
>
> The assumption here is that 366 is cut reasonably soon so you can query sql cache.
>
> Perhaps you can talk to Roy hopefully he does it for you in his tool.
> Venkat
>
>
> In Reply to Chris Hoelscher:
>
> I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)
>
> thanks
>
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> [Description: Description: cid:[login to unmask email]: humana.com
> 123 East Main Street
> Louisville, KY 40202
> Humana.com
> (502) 714-8615, (502) 476-2538
>
> From: mzkumaresan [mailto:[login to unmask email]
> Sent: Friday, March 10, 2017 8:07 PM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications
>
>
> I think your question is more related to dynamic SQL rather than distributed threads..
>
> Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..
>
> for static SQL, it should be easy as you can get it from syspackstmt..
>
>
>
> Muthu
> Sent from my iPhone
>
> On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email]>> wrote:
>
> For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?
>
> If the former, how are you associating the sql text with the instance of the detected sql?
>
> We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..
>
>
> thanks
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> : humana.com<http://humana.com>
> 123 East Main Street
> Louisville, KY 40202
> Humana.com<http://Humana.com>
> (502) 714-8615, (502) 476-2538
>
>
> 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.
> -----End Original Message-----
>
> -----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.
>
> 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]
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>

Daniel Luksetich

for those of you working on the ifcid 366/376 detected required modifications
(in response to Venkat Srinivasan)
What do you mean by “intrusion”



Daniel L Luksetich

DanL Database Consulting



IBM GOLD Consultant

IBM Champion for Analytics

IDUG Content Committee Chairman

IBM Certified Database Adminstrator – DB2 11 DBA for z/OS

IBM Certified System Administrator – DB2 11 for z/OS

IBM Certified Application Developer – DB2 11 for z/OS

IBM Certified Advanced Database Administrator – DB2 10.1 for Linux UNIX and Windows



From: Venkat Srinivasan [mailto:[login to unmask email]
Sent: Saturday, March 11, 2017 7:06 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications



Explain stmtcache stmtid is to get the cached access path into plan table and to my knowledge it doesnt externalize the sql. 316 and 318 (only enables the stmt level performance counters on 316) aren't needed. 376 is a rollup entry of 366 and it may be too late when it is written. 376 / 350 limited by specific application authid is an alternative which can be dealt by reading from smf destination. 350 can generate a lot of data.

explain stmt cache all and populate a scratch pad statement table

merge the scratch pad into a permanent statement table tracker on its unique key

delay 5 mins or whatever

go back to explain stmt cache all into scratch pad

if you enable 318 you now have a poor man's sql level performance monitor. It will be intrusive. potential stmt id reuse / restart of db2 will be an issue to the context. In datasharing the intrusion will be on all members.

Venkat

In Reply to Muthuraj Kumaresan:


I am not sure how big your environment is.. you can cut dynamic statement cache dump for every 15 or 30
Mins using explain cache all statement.. ( I have seen CPU spike when explain statement cache dump runs)..then you can match dynamic cache dump table with 376 data.. but as Srinivas said it won't be easy.. because
1.statement ID is different b/w DB2s
2.statement will be evicted based on LRU algorithm
3. Statement ID will be reused after db2 recycle

So you will need to match the statement ID with cache table asap..

Or else, you can write demon which can monitor 376 in real time.. as soon as it sees a record , demon can cut 316/317/318 record that belong to the statementID.. or you can run explain statement cache stmtid for the statement id found on 376..

Muthu
Sent from my iPhone

> On 11 Mar 2017, at 11:54 AM, Venkat Srinivasan wrote:
>
> Have you considered tracing 350 which has a stmt id that you could match? If volume is a concern (it ought to be rightfully), you could limit 350 to a set of applications, and do this whole process piece by piece.
>
> Venkat
>
> In Reply to Venkat Srinivasan:
>
> Chris,
>
> Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then.
>
> On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.
>
> Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.
>
> The assumption here is that 366 is cut reasonably soon so you can query sql cache.
>
> Perhaps you can talk to Roy hopefully he does it for you in his tool.
> Venkat
>
>
> In Reply to Chris Hoelscher:
>
> I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)
>
> thanks
>
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> [Description: Description: cid:[login to unmask email]: humana.com
> 123 East Main Street
> Louisville, KY 40202
> Humana.com
> (502) 714-8615, (502) 476-2538
>
> From: mzkumaresan [mailto:[login to unmask email]
> Sent: Friday, March 10, 2017 8:07 PM
> To: [login to unmask email] <mailto:[login to unmask email]>
> Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications
>
>
> I think your question is more related to dynamic SQL rather than distributed threads..
>
> Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..
>
> for static SQL, it should be easy as you can get it from syspackstmt..
>
>
>
> Muthu
> Sent from my iPhone
>
> On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email] <mailto:[login to unmask email]%3e%3e> >> wrote:
>
> For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?
>
> If the former, how are you associating the sql text with the instance of the detected sql?
>
> We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..
>
>
> thanks
> Chris Hoelscher
> Technology Architect, Database Infrastructure Services
> Technology Solution Services
> : humana.com<http://humana.com <http://humana.com%3e> >
> 123 East Main Street
> Louisville, KY 40202
> Humana.com<http://Humana.com <http://Humana.com%3e> >
> (502) 714-8615, (502) 476-2538
>
>
> 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.
> -----End Original Message-----
>
> -----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.
>
> 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] <mailto:[login to unmask email]>
> ** ** ** Attend the 2017 IDUG Tech Conference North America ** ** **
> ---> Anaheim, California, April 30 - May 04, 2017 <---
> http://www.idug.org/na
>
> Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2
>



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

Roy Boxwell

for those of you working on the ifcid 366/376 detected required modifications
(in response to Muthuraj Kumaresan)
You have to be soooooooooooo lucky to find that statementid in your dump! Sometimes the lifespan of an SQL in the dsc is seconds so I think it highly unlikely that you will find more than 1 or 2 in the DSC - especially if looking hours or days later!
/AD
My firm sells WorkLoadExpert that also enables you to tie a 366/376 To a dynamic SQL text - very handy indeed for all these BIF/ICI problems
/AD
I also see that the first V12 ICI is out there now - 1201 For POWER function...

Ho hum! Never a dull day in DB2 land!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 11 Mar 2017, at 02:07, mzkumaresan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:

<image001.jpg>
For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
<image004.jpg>: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538


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.

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

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

Roy Boxwell

for those of you working on the ifcid 366/376 detected required modifications
(in response to Venkat Srinivasan)
Indeed we do!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 11 Mar 2017, at 04:42, Venkat Srinivasan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Chris,

Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then.

On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.

Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.

The assumption here is that 366 is cut reasonably soon so you can query sql cache.

Perhaps you can talk to Roy hopefully he does it for you in his tool.
Venkat

In Reply to Chris Hoelscher:

I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538

From: mzkumaresan [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 8:07 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email]>> wrote:

For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com><http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com><http://Humana.com>
(502) 714-8615, (502) 476-2538


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.
-----End Original Message-----

-----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.

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

Roy Boxwell

for those of you working on the ifcid 366/376 detected required modifications
(in response to Muthuraj Kumaresan)
That is crazy overhead levels! Imagine having 389,000 statements in the DSC like a customer of mine... just try EXPLAINing that in less than 30 minutes!!! No chance...
/AD
WLX does this with style... contact for details!
/AD

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 11 Mar 2017, at 05:52, mzkumaresan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


I am not sure how big your environment is.. you can cut dynamic statement cache dump for every 15 or 30
Mins using explain cache all statement.. ( I have seen CPU spike when explain statement cache dump runs)..then you can match dynamic cache dump table with 376 data.. but as Srinivas said it won't be easy.. because
1.statement ID is different b/w DB2s
2.statement will be evicted based on LRU algorithm
3. Statement ID will be reused after db2 recycle

So you will need to match the statement ID with cache table asap..

Or else, you can write demon which can monitor 376 in real time.. as soon as it sees a record , demon can cut 316/317/318 record that belong to the statementID.. or you can run explain statement cache stmtid for the statement id found on 376..

Muthu
Sent from my iPhone

On 11 Mar 2017, at 11:54 AM, Venkat Srinivasan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Have you considered tracing 350 which has a stmt id that you could match? If volume is a concern (it ought to be rightfully), you could limit 350 to a set of applications, and do this whole process piece by piece.

Venkat

In Reply to Venkat Srinivasan:

Chris,

Easy as in easier said. The doc reference is in managing performance IFI READS function. If you are relying on SMF as the destination it is not possible to interrogate the cache since it is fairly too late by then.

On the other hand, if you capture 366 with a IFI READA, then for every dyn SQL from 366 you could attempt a IFI READS for 317 matching WQALSTID and a WQALFLTR x'04' for unique statement id. I have no need so I haven't done this. I can send you a sample for READA / READS (general purpose trace code)and you can attempt to modify to suit your needs.

Looking at the field, QW0366SI, says it is eight bytes. WQAL takes only 4 bytes. You would need to confirm what you see in 366SI and how it feeds into WQAL filter which is a 4 byte field. There will be clearly too much heavy lifting to do this as the program should be able to handle periods where the 366 volume is high.

The assumption here is that 366 is cut reasonably soon so you can query sql cache.

Perhaps you can talk to Roy hopefully he does it for you in his tool.
Venkat

In Reply to Chris Hoelscher:

I will [plead my ignorance – how does one interrogate the dynamic SQL cache dump?? (pointing me to the appropriate manual would be great!!)

thanks

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
[Description: Description: cid:[login to unmask email]: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538

From: mzkumaresan [mailto:[login to unmask email]
Sent: Friday, March 10, 2017 8:07 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RE: for those of you working on the ifcid 366/376 detected required modifications


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email]>> wrote:

For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com><http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com><http://Humana.com>
(502) 714-8615, (502) 476-2538


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.
-----End Original Message-----

-----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.

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

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

J&#248;rn Thyssen

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Venkat Srinivasan)

Hi Venkat,

To my knowledge the overhead of 316/318 is effectively zero (below measurement uncertainty).

The same should apply for 400/401 ("static statement cache")

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal. 

J&#248;rn Thyssen

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Roy Boxwell)

Hi Roy,

In my opinion if a statement only stays seconds in the DSC then it is seriously under dimensioned...


In Reply to Roy Boxwell:

You have to be soooooooooooo lucky to find that statementid in your dump! Sometimes the lifespan of an SQL in the dsc is seconds so I think it highly unlikely that you will find more than 1 or 2 in the DSC - especially if looking hours or days later!
/AD
My firm sells WorkLoadExpert that also enables you to tie a 366/376 To a dynamic SQL text - very handy indeed for all these BIF/ICI problems
/AD
I also see that the first V12 ICI is out there now - 1201 For POWER function...

Ho hum! Never a dull day in DB2 land!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 11 Mar 2017, at 02:07, mzkumaresan <[login to unmask email]<mailto:[login to unmask email]>> wrote:


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:


For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com>
(502) 714-8615, (502) 476-2538


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.

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

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



 

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal. 

Venkat Srinivasan

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Jørn Thyssen)

agreed. to the context, 316 318 is not needed (the discussion being centered around accessing sqlcache, given a stmtid). you can still access 317 with READS. Overhead part is between using explain stmtcache all versus accessing 317 given a statement id or other filter that is permissible for READS on 317. READS is far more elegant compared to sql explain stmtcache all.

Venkat 

In Reply to Jørn Thyssen:

Hi Venkat,

To my knowledge the overhead of 316/318 is effectively zero (below measurement uncertainty).

The same should apply for 400/401 ("static statement cache")

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal. 

Roy Boxwell

for those of you working on the ifcid 366/376 detected required modifications
(in response to Jørn Thyssen)
at one if my customers - they have dsc sized at 4,000,000 kb - near its physical max on a sixteen way machine ( not all members at that size!) they have tons of SQL that stays obediently in the cache and also loads that just disappear... no Alter, runstats, etc. All very strange!
A few ibmers have commented that the dsc is over sized...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 12 Mar 2017, at 13:54, Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Hi Roy,

In my opinion if a statement only stays seconds in the DSC then it is seriously under dimensioned...

In Reply to Roy Boxwell:

You have to be soooooooooooo lucky to find that statementid in your dump! Sometimes the lifespan of an SQL in the dsc is seconds so I think it highly unlikely that you will find more than 1 or 2 in the DSC - especially if looking hours or days later!
/AD
My firm sells WorkLoadExpert that also enables you to tie a 366/376 To a dynamic SQL text - very handy indeed for all these BIF/ICI problems
/AD
I also see that the first V12 ICI is out there now - 1201 For POWER function...

Ho hum! Never a dull day in DB2 land!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]>
http://www.seg.de

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

On 11 Mar 2017, at 02:07, mzkumaresan mailto:[login to unmask email]>> wrote:


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email]>> wrote:


For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com><http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com><http://Humana.com>
(502) 714-8615, (502) 476-2538


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.

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

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





Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal.

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

J&#248;rn Thyssen

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Venkat Srinivasan)

Hi Venkat,

Yes, READS is more elegant, but requires some non-trivial coding. I have one customer who implemented READS and I have several customers that does regular scheduled dumps of the dynamic statement cache, albeit to collect performance data so slightly different context.

In Reply to Venkat Srinivasan:

agreed. to the context, 316 318 is not needed (the discussion being centered around accessing sqlcache, given a stmtid). you can still access 317 with READS. Overhead part is between using explain stmtcache all versus accessing 317 given a statement id or other filter that is permissible for READS on 317. READS is far more elegant compared to sql explain stmtcache all.

Venkat 

In Reply to Jørn Thyssen:

Hi Venkat,

To my knowledge the overhead of 316/318 is effectively zero (below measurement uncertainty).

The same should apply for 400/401 ("static statement cache")

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal. 



 

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal. 

J&#248;rn Thyssen

RE: for those of you working on the ifcid 366/376 detected required modifications
(in response to Roy Boxwell)

LOL, my customers are somewhat smaller, so I have never come across that problem. Sounds like a bug to me...

In Reply to Roy Boxwell:

at one if my customers - they have dsc sized at 4,000,000 kb - near its physical max on a sixteen way machine ( not all members at that size!) they have tons of SQL that stays obediently in the cache and also loads that just disappear... no Alter, runstats, etc. All very strange!
A few ibmers have commented that the dsc is over sized...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/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

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

On 12 Mar 2017, at 13:54, Jørn Thyssen <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Hi Roy,

In my opinion if a statement only stays seconds in the DSC then it is seriously under dimensioned...

In Reply to Roy Boxwell:

You have to be soooooooooooo lucky to find that statementid in your dump! Sometimes the lifespan of an SQL in the dsc is seconds so I think it highly unlikely that you will find more than 1 or 2 in the DSC - especially if looking hours or days later!
/AD
My firm sells WorkLoadExpert that also enables you to tie a 366/376 To a dynamic SQL text - very handy indeed for all these BIF/ICI problems
/AD
I also see that the first V12 ICI is out there now - 1201 For POWER function...

Ho hum! Never a dull day in DB2 land!

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]><mailto:[login to unmask email]>
http://www.seg.de

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

On 11 Mar 2017, at 02:07, mzkumaresan mailto:[login to unmask email]>> wrote:


I think your question is more related to dynamic SQL rather than distributed threads..

Since it provides the statement ID, the best thing to use is dynamic SQL cache dump.. it would be easy to match the statement ID to find the statement which has the incompatibilities..

for static SQL, it should be easy as you can get it from syspackstmt..



Muthu
Sent from my iPhone

On 11 Mar 2017, at 5:29 AM, Chris Hoelscher mailto:[login to unmask email]>> wrote:


For distributed threads, are y9ou attempting to show the actual SQL TEXT associated with the “hit”? or just the ssid / package name / user id / date-time / server / ip-address and letting the apps folks figure out the rest?

If the former, how are you associating the sql text with the instance of the detected sql?

We are looking at detector offloads or possibly ez/db2, but I am looking for alternatives …..


thanks
Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
: humana.com<http://humana.com><http://humana.com>
123 East Main Street
Louisville, KY 40202
Humana.com<http://Humana.com><http://Humana.com>
(502) 714-8615, (502) 476-2538


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.

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

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





Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal.

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



 

Best regards,

Jørn Thyssen

Works for IBM Denmark. Views are personal.