Distributed threads and KEEPDYNAMIC

David Joyce

Distributed threads and KEEPDYNAMIC

All,

  We have a substantial amount of distributed access using package SYSSH100 to our z/OS DB2 11.  Should this package be bound with KEEPDYNAMIC(YES) or (NO)?  We currently have it bound with (NO) but isn't that preventing statement reuse from the dynamic statement cache?  Thanks.

Michael Hannan

RE: Distributed threads and KEEPDYNAMIC
(in response to David Joyce)

In Reply to David Joyce:

All,

  We have a substantial amount of distributed access using package SYSSH100 to our z/OS DB2 11.  Should this package be bound with KEEPDYNAMIC(YES) or (NO)?  We currently have it bound with (NO) but isn't that preventing statement reuse from the dynamic statement cache?  Thanks.

David,

KEEPDYNAMIC is a complex topic, that most DB2ers don't really understand, even some of the experts. There are Red Books that explain it, although not easy to grasp.

Dynamic Cache reuse is available for Dynamic SQLs, regardless of the use of KEEPDYNAMIC (lets abbreviate it to Keepd). Keepd is actually an additional tuning technique, over and above Dynam Cache reuse. The reason is, when the cache is checked, the SQL statement must be hashed in order to find a matching statement in the cache. This is a lot cheaper than access path determination, but still a significant cost of very high reuse SQLs.

KeepD allows an application program to maintain its own local set of Prepared SQLs for fast access, behind the scenes, to potentially then skip the Dynam Stmt cache lookup. 

However, when an application issues a Prepare statement for Dynamic, it does not look in the local KeepD area. There is something about the workings architecture, that requires the application Program/Thread  to know that the same Cursor is to be opened again, but in order to avoid the Dynamic Cache overheads and use the KeepD information, the program must omit execution of the Prepare Statement.

Only by omitting the Prepare for a subsequent Open of the same Cursor, can DB2 know that it is the same SQL without incurring the CPU overheads of the dynamic SQL matching process. A Prepare could easily be a different SQL at run time to previous execution of the Prepare, so the SQL match process would be needed, and that is the expense KeepD is trying to avoid.

An application program needs to be written in such a way as to know when to skip execution of a repeated Dynamic Prepare, in order to benefit from KeepD.

Standard Packages like SYSSH100, etc. are not written to  keep track of which repeated Cursors that were already prepared and to skip subsequent re Prepares of the same SQL.

So KEEPDYNAMIC is a program design measure, not a tuning measure that can be applied later. Not everyone realized that KEEPDYNAMIC would have no benefit. Seen it recommended as a tuning measure plenty of times in error.  

"Concentrate SQLs with literals" is a possible tuning measure for simple SQLs with literals (literals varying by each execution) when the SQLs could use the same access path but previously were generally not matched in the Dynam Cache. There are DB2 Accounting metrics showing how many prepares got saved. Sometimes Prepare costs are dominating the total CPU used, and can be reduced. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Jan 23, 2020 - 12:53 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 23, 2020 - 01:27 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 23, 2020 - 01:32 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Jan 23, 2020 - 01:34 AM (Europe/Berlin)

David Joyce

RE: Distributed threads and KEEPDYNAMIC
(in response to Michael Hannan)

Michael,

   Thanks for the valuable information.  To summarize, dynamic SQL coming in from distributed applications using the NULLID.SYS* packages, regardless of the KeepD setting, will check the Dynamic Cache to see if there is an exact match and thus avoid the Prepare.  

Would it matter if instead we bound all the NULLID.SYS* packages with KeepD(YES) as opposed to their current KeepD(NO)?  Is there any benefit to this?  I assume any process executing dynamic SQL performing a COMMIT within the UOW could benefit from KeepD(YES).

Suresh Sane

Distributed threads and KEEPDYNAMIC
(in response to David Joyce)

David - see chapter 8 of the Redbook below (book is somewhat dated , but the material is still 100% relevant for this discussion).

http://www.redbooks.ibm.com/redbooks/pdfs/sg246418.pdf

Thx
Suresh
________________________________
From: David Joyce <[login to unmask email]>
Sent: Thursday, January 23, 2020 2:23 PM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - RE: Distributed threads and KEEPDYNAMIC


Michael,

Thanks for the valuable information. To summarize, dynamic SQL coming in from distributed applications using the NULLID.SYS* packages, regardless of the KeepD setting, will check the Dynamic Cache to see if there is an exact match and thus avoid the Prepare.

Would it matter if instead we bound all the NULLID.SYS* packages with KeepD(YES) as opposed to their current KeepD(NO)? Is there any benefit to this? I assume any process executing dynamic SQL performing a COMMIT within the UOW could benefit from KeepD(YES).

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

Neil Price

Distributed threads and KEEPDYNAMIC
(in response to Suresh Sane)
David,

IBM states that KEEPDYNAMIC(YES) results in improved performance if your
DRDA client application uses a cursor defined WITH HOLD. The 'H' in
SYSSH100 indicates that this particular package is used specifically for
cursors using WITH HOLD (see
https://www.ibm.com/support/pages/db2-v111-bind-file-and-package-name-list)
in which case your applications may benefit from KEEPDYNAMIC(YES) for this
package.

However rebinding NULLID packages in general with this option is not to be
recommended.

Incidentally I have encountered documents which erroneously state or imply
that KEEPDYNAMIC determines whether the "global" EDM Dynamic Statement
Cache is used - as the Redbook identified by Suresh explains, it's actually
controlled by the subsystem parameter CACHEDYN - so I agree with Michael
that there's a lot of misunderstanding in this area.

HTH

Neil Price
Db2 for z/OS trainer

On Thu, 23 Jan 2020 at 16:37, suresh sane <[login to unmask email]> wrote:

>
> David - see chapter 8 of the Redbook below (book is somewhat dated , but
> the material is still 100% relevant for this discussion).
>
> http://www.redbooks.ibm.com/redbooks/pdfs/sg246418.pdf
>
> Thx
> Suresh
> ------------------------------
> *From:* David Joyce <[login to unmask email]>
> *Sent:* Thursday, January 23, 2020 2:23 PM
> *To:* [login to unmask email] <[login to unmask email]>
> *Subject:* [DB2-L] - RE: Distributed threads and KEEPDYNAMIC
>
>
> Michael,
>
> Thanks for the valuable information. To summarize, dynamic SQL coming
> in from distributed applications using the NULLID.SYS* packages, regardless
> of the KeepD setting, will check the Dynamic Cache to see if there is an
> exact match and thus avoid the Prepare.
>
> Would it matter if instead we bound all the NULLID.SYS* packages with
> KeepD(YES) as opposed to their current KeepD(NO)? Is there any benefit to
> this? I assume any process executing dynamic SQL performing a COMMIT
> within the UOW could benefit from KeepD(YES).
>
> -----End Original Message-----
>

Michael Hannan

RE: Distributed threads and KEEPDYNAMIC
(in response to Neil Price)

Thanks Neil,

I looked it up with this link:

https://www.ibm.com/support/knowledgecenter/SSEPEK_10.0.0/comref/src/tpc/db2z_bindoptkeepdynamic.html

"Performance hint: KEEPDYNAMIC(YES) results in improved performance if your DRDA client application uses a cursor defined WITH HOLD. DB2 automatically closes a held cursor when there are no more rows to retrieve, which eliminates an extra network message"

It seems that after a Commit, a held cursor has to be re-prepared, unless KEEPDYNAMIC is used, so there is a saving, for held Cursors after commit. I must have read that before in Suresh's Red Book and forgot it.

It is not clear to me if the 2nd quoted sentence above is connected or relevant to KEEPDYNAMIC, or just stating something else.

So maybe it is worth trying out KEEPDYNAMIC for the Hold Cursor Packages, just to see if there is any saving at all. I would not expect too much. Many applications persons code a Cursor with HOLD, even when in fact they will never be using the Hold over  a commit point, just because they don't know any different.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Roy Boxwell

Distributed threads and KEEPDYNAMIC
(in response to Neil Price)
My favourite page of DSC knowledge – cobbled together from various sources:



Dynamic Statement Caching Summary



CACHEDYN NO YES



K NO No skeletons cached in EDMP Skeletons cached in EDMP

E Only full prepares 1st prepare full; others short (note 2)

E No prepared statements kept No prepared statements kept

P across commits (note 1) across commits (note 1)

D No statement strings kept No statement strings kept

Y across commits across commits

N NONE GLOBAL

A

M YES No skeletons cached in EDMP Skeletons cached in EDMP

I Only full prepares 1st prepare full; others short (note 2)

C No prepared statements kept Prepared stmts across

across commits (note 1) commits – avoided prepares (note 3)

Stmt strings kept across Stmt strings kept across

commits – implicit prepares commits – implicit prepares

LOCAL FULL





Note 1: unless a cursor WITH HOLD is open

Note 2: unless invalidated or flushed out due to LRU

Note 3: assuming MAXKEEPD > 0





Kudos to Suresh Sane for the first version of this that I saw way back in 2005...



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
Web http://www.seg.de http://www.seg.de

https://www.seg.de/corporate/rechtliche-hinweise/datenschutz Link zur Datenschutzerklärung


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



From: Neil Price [mailto:[login to unmask email]
Sent: Thursday, January 23, 2020 7:10 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Distributed threads and KEEPDYNAMIC



David,



IBM states that KEEPDYNAMIC(YES) results in improved performance if your DRDA client application uses a cursor defined WITH HOLD. The 'H' in SYSSH100 indicates that this particular package is used specifically for cursors using WITH HOLD (see https://www.ibm.com/support/pages/db2-v111-bind-file-and-package-name-list) in which case your applications may benefit from KEEPDYNAMIC(YES) for this package.



However rebinding NULLID packages in general with this option is not to be recommended.



Incidentally I have encountered documents which erroneously state or imply that KEEPDYNAMIC determines whether the "global" EDM Dynamic Statement Cache is used - as the Redbook identified by Suresh explains, it's actually controlled by the subsystem parameter CACHEDYN - so I agree with Michael that there's a lot of misunderstanding in this area.



HTH



Neil Price

Db2 for z/OS trainer



On Thu, 23 Jan 2020 at 16:37, suresh sane <[login to unmask email] <mailto:[login to unmask email]> > wrote:


David - see chapter 8 of the Redbook below (book is somewhat dated , but the material is still 100% relevant for this discussion).



http://www.redbooks.ibm.com/redbooks/pdfs/sg246418.pdf



Thx

Suresh

_____

From: David Joyce <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Thursday, January 23, 2020 2:23 PM
To: [login to unmask email] <mailto:[login to unmask email]> <[login to unmask email] <mailto:[login to unmask email]> >
Subject: [DB2-L] - RE: Distributed threads and KEEPDYNAMIC



Michael,

Thanks for the valuable information. To summarize, dynamic SQL coming in from distributed applications using the NULLID.SYS* packages, regardless of the KeepD setting, will check the Dynamic Cache to see if there is an exact match and thus avoid the Prepare.

Would it matter if instead we bound all the NULLID.SYS* packages with KeepD(YES) as opposed to their current KeepD(NO)? Is there any benefit to this? I assume any process executing dynamic SQL performing a COMMIT within the UOW could benefit from KeepD(YES).



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



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



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

Attachments

  • smime.p7s (5.1k)

David Joyce

RE: Distributed threads and KEEPDYNAMIC
(in response to Roy Boxwell)

Thanks everyone for the great information!  This forum is such a valuable tool to the less experienced DB2ers.

Suresh Sane

Distributed threads and KEEPDYNAMIC
(in response to Roy Boxwell)
Thanks Roy. Since the formatting seemed messed up, wanted to re-post the correct picture as shown below:

[cid:db9719ac-f6c5-427e-9111-6d420f51cfe5]

________________________________
From: Boxwell, Roy <[login to unmask email]>
Sent: Friday, January 24, 2020 7:10 AM
To: [login to unmask email] <[login to unmask email]>
Subject: [DB2-L] - RE: Distributed threads and KEEPDYNAMIC


My favourite page of DSC knowledge – cobbled together from various sources:



Dynamic Statement Caching Summary



CACHEDYN NO YES



K NO No skeletons cached in EDMP Skeletons cached in EDMP

E Only full prepares 1st prepare full; others short (note 2)

E No prepared statements kept No prepared statements kept

P across commits (note 1) across commits (note 1)

D No statement strings kept No statement strings kept

Y across commits across commits

N NONE GLOBAL

A

M YES No skeletons cached in EDMP Skeletons cached in EDMP

I Only full prepares 1st prepare full; others short (note 2)

C No prepared statements kept Prepared stmts across

across commits (note 1) commits – avoided prepares (note 3)

Stmt strings kept across Stmt strings kept across

commits – implicit prepares commits – implicit prepares

LOCAL FULL





Note 1: unless a cursor WITH HOLD is open

Note 2: unless invalidated or flushed out due to LRU

Note 3: assuming MAXKEEPD > 0





Kudos to Suresh Sane for the first version of this that I saw way back in 2005...



Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-

Vagedesstrasse 19
40479 Dusseldorf/Germany
Tel. +49 (0)211 96149-675[cid:e2c0e86a-c775-406c-92fc-252cf7fad3ac]
Fax +49 (0)211 96149-32[cid:67dabe95-123d-4304-b044-e1ca98f4974f]
Email: [login to unmask email]<mailto:[login to unmask email]>
Web http://www.seg.de http://www.seg.de

Link zur Datenschutzerklärung https://www.seg.de/corporate/rechtliche-hinweise/datenschutz

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



From: Neil Price [mailto:[login to unmask email]
Sent: Thursday, January 23, 2020 7:10 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: Distributed threads and KEEPDYNAMIC



David,



IBM states that KEEPDYNAMIC(YES) results in improved performance if your DRDA client application uses a cursor defined WITH HOLD. The 'H' in SYSSH100 indicates that this particular package is used specifically for cursors using WITH HOLD (see https://www.ibm.com/support/pages/db2-v111-bind-file-and-package-name-list) in which case your applications may benefit from KEEPDYNAMIC(YES) for this package.



However rebinding NULLID packages in general with this option is not to be recommended.



Incidentally I have encountered documents which erroneously state or imply that KEEPDYNAMIC determines whether the "global" EDM Dynamic Statement Cache is used - as the Redbook identified by Suresh explains, it's actually controlled by the subsystem parameter CACHEDYN - so I agree with Michael that there's a lot of misunderstanding in this area.



HTH



Neil Price

Db2 for z/OS trainer



On Thu, 23 Jan 2020 at 16:37, suresh sane <[login to unmask email]<mailto:[login to unmask email]>> wrote:

David - see chapter 8 of the Redbook below (book is somewhat dated , but the material is still 100% relevant for this discussion).



http://www.redbooks.ibm.com/redbooks/pdfs/sg246418.pdf



Thx

Suresh

________________________________

From: David Joyce <[login to unmask email]<mailto:[login to unmask email]>>
Sent: Thursday, January 23, 2020 2:23 PM
To: [login to unmask email]<mailto:[login to unmask email]> <[login to unmask email]<mailto:[login to unmask email]>>
Subject: [DB2-L] - RE: Distributed threads and KEEPDYNAMIC



Michael,

Thanks for the valuable information. To summarize, dynamic SQL coming in from distributed applications using the NULLID.SYS* packages, regardless of the KeepD setting, will check the Dynamic Cache to see if there is an exact match and thus avoid the Prepare.

Would it matter if instead we bound all the NULLID.SYS* packages with KeepD(YES) as opposed to their current KeepD(NO)? Is there any benefit to this? I assume any process executing dynamic SQL performing a COMMIT within the UOW could benefit from KeepD(YES).



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

  • pastedImagefile.png (76.5k)
  • Outlook-fd4jkbno.png (<1k)
  • Outlook-32fd0ln0.png (<1k)