Db2 for LUW v11.1 statement cache

Daniel Luksetich

Db2 for LUW v11.1 statement cache
Hello,

I am looking at statements in the statement cache for a very low volume Db2
for LUW database. The interesting thing is that the statements only remain
in the cache for a few minutes. This is an extremely low volume database,
and I would think that the statements would remain in the cache for months,
and yet they do not. Unless, of course, I am not monitoring correctly. I am
using the MON_GET_PKG_CACHE_STMT table function. Any thoughts?

Thanks,

Dan

Bill Gallagher

[External] Db2 for LUW v11.1 statement cache
(in response to Daniel Luksetich)
Dan,

It's been a couple of years since I've been hands-on with LUW, but I used to do something similar via the SYSIBMADM.SNAPDYN_SQL view.

Does querying the dynamic statement cache via that view experience the same behavior as the MON_GET_PKG_CACHE_STMT table function?

Bill Gallagher | Senior Systems Engineer, DBA | Data Administration

From: Daniel L Luksetich <[login to unmask email]>
Sent: Friday, October 4, 2019 10:36 AM
To: [login to unmask email]
Subject: [External] [DB2-L] - Db2 for LUW v11.1 statement cache

Hello,
I am looking at statements in the statement cache for a very low volume Db2 for LUW database. The interesting thing is that the statements only remain in the cache for a few minutes. This is an extremely low volume database, and I would think that the statements would remain in the cache for months, and yet they do not. Unless, of course, I am not monitoring correctly. I am using the MON_GET_PKG_CACHE_STMT table function. Any thoughts?
Thanks,
Dan

-----End Original Message-----
________________________________
This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

Philip Nelson

[External] Db2 for LUW v11.1 statement cache
(in response to Bill Gallagher)
Is your database activated?

The cache only lasts as long as the database is active. If you haven't
activated it then it will activate / deactivate on first / last connection.

There is an event monitor which you can use to capture information being
flushed from the cache, which you might find useful.

Phil

On Fri, 4 Oct 2019 at 16:04, Gallagher,Bill R <[login to unmask email]> wrote:

> Dan,
>
>
>
> It’s been a couple of years since I’ve been hands-on with LUW, but I used
> to do something similar via the SYSIBMADM.SNAPDYN_SQL view.
>
>
>
> Does querying the dynamic statement cache via that view experience the
> same behavior as the MON_GET_PKG_CACHE_STMT table function?
>
>
>
> *Bill Gallagher *|* Senior Systems Engineer, DBA *|* Data Administration *
>
>
>
> *From:* Daniel L Luksetich <[login to unmask email]>
> *Sent:* Friday, October 4, 2019 10:36 AM
> *To:* [login to unmask email]
> *Subject:* [External] [DB2-L] - Db2 for LUW v11.1 statement cache
>
>
>
> Hello,
>
> I am looking at statements in the statement cache for a very low volume
> Db2 for LUW database. The interesting thing is that the statements only
> remain in the cache for a few minutes. This is an extremely low volume
> database, and I would think that the statements would remain in the cache
> for months, and yet they do not. Unless, of course, I am not monitoring
> correctly. I am using the MON_GET_PKG_CACHE_STMT table function. Any
> thoughts?
>
> Thanks,
>
> Dan
>
>
> -----End Original Message-----
> ------------------------------
> This message (including any attachments) may contain confidential,
> proprietary, privileged and/or private information. The information is
> intended to be for the use of the individual or entity designated above. If
> you are not the intended recipient of this message, please notify the
> sender immediately, and delete the message and any attachments. Any
> disclosure, reproduction, distribution or other use of this message or any
> attachments by an individual or entity other than the intended recipient is
> prohibited.
>
> TRVDiscDefault::1201
> -----End Original Message-----
>

Daniel Luksetich

[External] Db2 for LUW v11.1 statement cache
(in response to Philip Nelson)
Bill,

I have queried the following:

SELECT *

FROM TABLE(MON_GET_PKG_CACHE_STMT ( NULL, NULL, NULL, -2)) as T;



SELECT *

FROM SYSIBMADM.MON_PKG_CACHE_SUMMARY



select * from SYSIBMADM.SNAPDYN_SQL



I have also tried get snapshot for dynamic sql



They all return different results, but none have the statements I am looking for.



Phil,

That database is active. I have connections open where I am running dynamic statements. They remain in the cache less than 10 minutes. I am not closing the connections. I have looking into the event monitor, and have not tried it yet, but does it tell me why the statement is flushed?



Automatic RUNSTATS is ON. Could that be an impact? Statement cache is on AUTO. I could try to fix it and see if that makes a difference.



Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Philip Nelson <[login to unmask email]>
Sent: Friday, October 4, 2019 10:07 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] Db2 for LUW v11.1 statement cache



Is your database activated?



The cache only lasts as long as the database is active. If you haven't activated it then it will activate / deactivate on first / last connection.



There is an event monitor which you can use to capture information being flushed from the cache, which you might find useful.



Phil



On Fri, 4 Oct 2019 at 16:04, Gallagher,Bill R <[login to unmask email] <mailto:[login to unmask email]> > wrote:

Dan,



It’s been a couple of years since I’ve been hands-on with LUW, but I used to do something similar via the SYSIBMADM.SNAPDYN_SQL view.



Does querying the dynamic statement cache via that view experience the same behavior as the MON_GET_PKG_CACHE_STMT table function?



Bill Gallagher | Senior Systems Engineer, DBA | Data Administration



From: Daniel L Luksetich <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Friday, October 4, 2019 10:36 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [External] [DB2-L] - Db2 for LUW v11.1 statement cache



Hello,

I am looking at statements in the statement cache for a very low volume Db2 for LUW database. The interesting thing is that the statements only remain in the cache for a few minutes. This is an extremely low volume database, and I would think that the statements would remain in the cache for months, and yet they do not. Unless, of course, I am not monitoring correctly. I am using the MON_GET_PKG_CACHE_STMT table function. Any thoughts?

Thanks,

Dan



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

_____

This message (including any attachments) may contain confidential, proprietary, privileged and/or private information. The information is intended to be for the use of the individual or entity designated above. If you are not the intended recipient of this message, please notify the sender immediately, and delete the message and any attachments. Any disclosure, reproduction, distribution or other use of this message or any attachments by an individual or entity other than the intended recipient is prohibited.

TRVDiscDefault::1201

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



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)

Joe Geller

RE: [External] Db2 for LUW v11.1 statement cache
(in response to Philip Nelson)

Dan,

What is the size of your package cache (it is a db cfg parameter)?  If it is very very small then it might not have room for even a small variety of statements.  Also, it the SQL is dynamic and uses parameter markers for ID (or other high cardinality columns) then each execution will be a unique statement in the cache.  Of course if there is no room for a new statement, older ones will be flushed.

The other reason for statements being flushed is if they are not re-usable (whether dynamic or static). For example, if they have a global temp table, if Runstats or reorgs are done on any table in the statement, etc. 

Joe

In Reply to Philip Nelson:

Is your database activated?

The cache only lasts as long as the database is active. If you haven't
activated it then it will activate / deactivate on first / last connection.

There is an event monitor which you can use to capture information being
flushed from the cache, which you might find useful.

Phil

On Fri, 4 Oct 2019 at 16:04, Gallagher,Bill R <[login to unmask email]> wrote:

> Dan,
>
>
>
> It’s been a couple of years since I’ve been hands-on with LUW, but I used
> to do something similar via the SYSIBMADM.SNAPDYN_SQL view.
>
>
>
> Does querying the dynamic statement cache via that view experience the
> same behavior as the MON_GET_PKG_CACHE_STMT table function?
>
>
>
> *Bill Gallagher *|* Senior Systems Engineer, DBA *|* Data Administration *
>
>
>
> *From:* Daniel L Luksetich <[login to unmask email]>
> *Sent:* Friday, October 4, 2019 10:36 AM
> *To:* [login to unmask email]
> *Subject:* [External] [DB2-L] - Db2 for LUW v11.1 statement cache
>
>
>
> Hello,
>
> I am looking at statements in the statement cache for a very low volume
> Db2 for LUW database. The interesting thing is that the statements only
> remain in the cache for a few minutes. This is an extremely low volume
> database, and I would think that the statements would remain in the cache
> for months, and yet they do not. Unless, of course, I am not monitoring
> correctly. I am using the MON_GET_PKG_CACHE_STMT table function. Any
> thoughts?
>
> Thanks,
>
> Dan
>
>
> -----End Original Message-----
> ------------------------------
> This message (including any attachments) may contain confidential,
> proprietary, privileged and/or private information. The information is
> intended to be for the use of the individual or entity designated above. If
> you are not the intended recipient of this message, please notify the
> sender immediately, and delete the message and any attachments. Any
> disclosure, reproduction, distribution or other use of this message or any
> attachments by an individual or entity other than the intended recipient is
> prohibited.
>
> TRVDiscDefault::1201
> -----End Original Message-----
>

Daniel Luksetich

[External] Db2 for LUW v11.1 statement cache
(in response to Joe Geller)
DOH! That was it Joe. It was set to AUTO, but with a very low value. Once I increased it significantly things changed dramatically!

Thanks,

Dan



+--------------------------------------+-----------------------------------------------------------+

| Daniel L Luksetich | IBM Certified Advanced Database Administrator – |

| IBM GOLD Consultant | Db2 10.1 for Linux UNIX and Windows |

| IDUG Content Committee Past-Chairman | IBM Certified Database Adminstrator – Db2 11 DBA for z/OS |

| IDUG DB2-L Administrator | IBM Certified System Administrator – Db2 11 for z/OS |

| URL: https://db2expert.com https://db2expert.com | IBM Certified Application Developer – Db2 11 for z/OS |

+--------------------------------------+-----------------------------------------------------------+





From: Joe Geller <[login to unmask email]>
Sent: Friday, October 4, 2019 10:44 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: [External] Db2 for LUW v11.1 statement cache



Dan,

What is the size of your package cache (it is a db cfg parameter)? If it is very very small then it might not have room for even a small variety of statements. Also, it the SQL is dynamic and uses parameter markers for ID (or other high cardinality columns) then each execution will be a unique statement in the cache. Of course if there is no room for a new statement, older ones will be flushed.

The other reason for statements being flushed is if they are not re-usable (whether dynamic or static). For example, if they have a global temp table, if Runstats or reorgs are done on any table in the statement, etc.

Joe

In Reply to Philip Nelson:

Is your database activated?

The cache only lasts as long as the database is active. If you haven't
activated it then it will activate / deactivate on first / last connection.

There is an event monitor which you can use to capture information being
flushed from the cache, which you might find useful.

Phil

On Fri, 4 Oct 2019 at 16:04, Gallagher,Bill R wrote:

> Dan,
>
>
>
> It’s been a couple of years since I’ve been hands-on with LUW, but I used
> to do something similar via the SYSIBMADM.SNAPDYN_SQL view.
>
>
>
> Does querying the dynamic statement cache via that view experience the
> same behavior as the MON_GET_PKG_CACHE_STMT table function?
>
>
>
> *Bill Gallagher *|* Senior Systems Engineer, DBA *|* Data Administration *
>
>
>
> *From:* Daniel L Luksetich
> *Sent:* Friday, October 4, 2019 10:36 AM
> *To:* [login to unmask email] <mailto:[login to unmask email]>
> *Subject:* [External] [DB2-L] - Db2 for LUW v11.1 statement cache
>
>
>
> Hello,
>
> I am looking at statements in the statement cache for a very low volume
> Db2 for LUW database. The interesting thing is that the statements only
> remain in the cache for a few minutes. This is an extremely low volume
> database, and I would think that the statements would remain in the cache
> for months, and yet they do not. Unless, of course, I am not monitoring
> correctly. I am using the MON_GET_PKG_CACHE_STMT table function. Any
> thoughts?
>
> Thanks,
>
> Dan
>
>
> -----End Original Message-----
> ------------------------------
> This message (including any attachments) may contain confidential,
> proprietary, privileged and/or private information. The information is
> intended to be for the use of the individual or entity designated above. If
> you are not the intended recipient of this message, please notify the
> sender immediately, and delete the message and any attachments. Any
> disclosure, reproduction, distribution or other use of this message or any
> attachments by an individual or entity other than the intended recipient is
> prohibited.
>
> TRVDiscDefault::1201
> -----End Original Message-----
>



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

Attachments

  • image001.png (9.9k)
  • image002.png (14k)
  • image003.png (13.1k)
  • image004.png (14.5k)
  • image005.png (15.3k)
  • image006.png (14.2k)
  • image007.png (7.5k)