RUNSTATS and Dynamic SQL - DB2 z/OS

Mike Martin

RUNSTATS and Dynamic SQL - DB2 z/OS
Hi all,

I know that RUNSTATS can help static SQL after rebinding package(s). Can RUNSTATS help dynamic SQL coming from a remote server over DDF?

Mike Martin
This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

Raymond Bell

RUNSTATS and Dynamic SQL - DB2 z/OS
(in response to Mike Martin)
Mike,

Runstats can help static SQL before rebinding packages. And yes, it can help with dynamic SQL, regardless of who's running it and where it's come from, for the same reason.

Remember, Runstats is (among other things) about giving the Optimizer (sp) some info on the lie of the land in preparation (pun intended...) for choosing an access path. So given access paths have to be chosen regardless of whether the SQL is dynamic or static, Runstats will help in this endeavour.

Perhaps 'influence' might be a better word than 'help', but I'm not going there.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: MARTIN, MIKE [mailto:[login to unmask email]
Sent: 20 April 2017 13:51
To: [login to unmask email]
Subject: [DB2-L] - RUNSTATS and Dynamic SQL - DB2 z/OS


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

Hi all,

I know that RUNSTATS can help static SQL after rebinding package(s). Can RUNSTATS help dynamic SQL coming from a remote server over DDF?

Mike Martin

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

-----End Original Message-----
The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. The Royal Bank of Scotland plc may monitor e-mails for business and operational purposes. By replying to this message you give your consent to our monitoring of your email communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

Philip Sevetson

RUNSTATS and Dynamic SQL - DB2 z/OS
(in response to Raymond Bell)
**please note my email address change**
Mike and Raymond,

I saw a presentation early this month at TRIDEX (NYC DB2 UG) about this - it's also possible to affect the access path for dynamic SQL more directly in V12. There's a capture mechanism for the Dynamic Statement Cache, and path information is store-able for proactively tuning predictable/repeat queries.

Of course, due to my advancing age, I can't remember what the presenter said exactly or even who he was, other than that it was one of IBM's DB2 heavyweights - one of the four or five best known DB2 project people.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Thursday, April 20, 2017 9:01 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS

Mike,

Runstats can help static SQL before rebinding packages. And yes, it can help with dynamic SQL, regardless of who's running it and where it's come from, for the same reason.

Remember, Runstats is (among other things) about giving the Optimizer (sp) some info on the lie of the land in preparation (pun intended...) for choosing an access path. So given access paths have to be chosen regardless of whether the SQL is dynamic or static, Runstats will help in this endeavour.

Perhaps 'influence' might be a better word than 'help', but I'm not going there.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: MARTIN, MIKE [mailto:[login to unmask email]
Sent: 20 April 2017 13:51
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RUNSTATS and Dynamic SQL - DB2 z/OS


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
Hi all,

I know that RUNSTATS can help static SQL after rebinding package(s). Can RUNSTATS help dynamic SQL coming from a remote server over DDF?

Mike Martin

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

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

The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. The Royal Bank of Scotland plc may monitor e-mails for business and operational purposes. By replying to this message you give your consent to our monitoring of your email communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

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

  • image001.png (3.3k)

Philip Sevetson

RUNSTATS and Dynamic SQL - DB2 z/OS
(in response to Philip Sevetson)
**please note my email address change**
The aging brain cells have cleared a bit. Speaker was Robert Catterall.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, April 20, 2017 9:45 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS

**please note my email address change**
Mike and Raymond,

I saw a presentation early this month at TRIDEX (NYC DB2 UG) about this - it's also possible to affect the access path for dynamic SQL more directly in V12. There's a capture mechanism for the Dynamic Statement Cache, and path information is store-able for proactively tuning predictable/repeat queries.

Of course, due to my advancing age, I can't remember what the presenter said exactly or even who he was, other than that it was one of IBM's DB2 heavyweights - one of the four or five best known DB2 project people.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Thursday, April 20, 2017 9:01 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS

Mike,

Runstats can help static SQL before rebinding packages. And yes, it can help with dynamic SQL, regardless of who's running it and where it's come from, for the same reason.

Remember, Runstats is (among other things) about giving the Optimizer (sp) some info on the lie of the land in preparation (pun intended...) for choosing an access path. So given access paths have to be chosen regardless of whether the SQL is dynamic or static, Runstats will help in this endeavour.

Perhaps 'influence' might be a better word than 'help', but I'm not going there.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: MARTIN, MIKE [mailto:[login to unmask email]
Sent: 20 April 2017 13:51
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RUNSTATS and Dynamic SQL - DB2 z/OS


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
Hi all,

I know that RUNSTATS can help static SQL after rebinding package(s). Can RUNSTATS help dynamic SQL coming from a remote server over DDF?

Mike Martin

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

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

The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. The Royal Bank of Scotland plc may monitor e-mails for business and operational purposes. By replying to this message you give your consent to our monitoring of your email communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

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

  • image001.png (3.3k)

Raymond Bell

RUNSTATS and Dynamic SQL - DB2 z/OS
(in response to Philip Sevetson)
Hey Phil,

I remember having a play with access path 'hints' for dynamic SQL a year or so ago. Fiddly as fook, but worked. Can't remember the details but there were 3 ways of setting up the hints. I think Bind Query featured in there somewhere. Not looked a V12 yet but if there's something in there to clean that up, so much the better.

My favourite Aussie Optimiser presented on something similar at a UK GSE meeting a year or two ago, as did my erstwhile colleague Mr. Thomas. I don't think I heckled too much, did I, Steve?

V12... hmmm, might be a while yet before I get my hands on that. V11 will do me just fine for now. :o)

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: 20 April 2017 14:45
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************

**please note my email address change**
Mike and Raymond,

I saw a presentation early this month at TRIDEX (NYC DB2 UG) about this - it's also possible to affect the access path for dynamic SQL more directly in V12. There's a capture mechanism for the Dynamic Statement Cache, and path information is store-able for proactively tuning predictable/repeat queries.

Of course, due to my advancing age, I can't remember what the presenter said exactly or even who he was, other than that it was one of IBM's DB2 heavyweights - one of the four or five best known DB2 project people.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Thursday, April 20, 2017 9:01 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS

Mike,

Runstats can help static SQL before rebinding packages. And yes, it can help with dynamic SQL, regardless of who's running it and where it's come from, for the same reason.

Remember, Runstats is (among other things) about giving the Optimizer (sp) some info on the lie of the land in preparation (pun intended...) for choosing an access path. So given access paths have to be chosen regardless of whether the SQL is dynamic or static, Runstats will help in this endeavour.

Perhaps 'influence' might be a better word than 'help', but I'm not going there.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: MARTIN, MIKE [mailto:[login to unmask email]
Sent: 20 April 2017 13:51
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RUNSTATS and Dynamic SQL - DB2 z/OS


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
Hi all,

I know that RUNSTATS can help static SQL after rebinding package(s). Can RUNSTATS help dynamic SQL coming from a remote server over DDF?

Mike Martin

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

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

The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. The Royal Bank of Scotland plc may monitor e-mails for business and operational purposes. By replying to this message you give your consent to our monitoring of your email communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
-----End Original Message-----
The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. The Royal Bank of Scotland plc may monitor e-mails for business and operational purposes. By replying to this message you give your consent to our monitoring of your email communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.
Attachments

  • image001.png (3.3k)

Joe Geller

RE: RUNSTATS and Dynamic SQL - DB2 z/OS
(in response to Philip Sevetson)

Phil,

What you are referring to is called Dynamic Plan Stability (new in V12).  At the upcoming IDUG NA Conference in Anaheim, Pat Bossman (who is the designer of this feature) will be doing a presentation on it.  For those of you attending, it is session F07 on Tues May 2 at 10:30.

The IDUG V12 White Paper also has a section describing dynamic plan stability and my testing of it - DB2 12 for z/OS - an IDUG Perspective http://www.idug.org/p/bl/ar/blogaid=527

Joe

**please note my email address change**
The aging brain cells have cleared a bit. Speaker was Robert Catterall.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Sevetson, Phil [mailto:[login to unmask email]
Sent: Thursday, April 20, 2017 9:45 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS

**please note my email address change**
Mike and Raymond,

I saw a presentation early this month at TRIDEX (NYC DB2 UG) about this - it's also possible to affect the access path for dynamic SQL more directly in V12. There's a capture mechanism for the Dynamic Statement Cache, and path information is store-able for proactively tuning predictable/repeat queries.

Of course, due to my advancing age, I can't remember what the presenter said exactly or even who he was, other than that it was one of IBM's DB2 heavyweights - one of the four or five best known DB2 project people.

Philip Sevetson
Computer Systems Manager
5 Manhattan West (33rd St at 10th Ave)
New York, NY 10001
212-857-1688 w
917-991-7052 c
212-857-1659 f
[cid:[login to unmask email]

From: Bell, Raymond (IT Operations, Technology) [mailto:[login to unmask email]
Sent: Thursday, April 20, 2017 9:01 AM
To: '[login to unmask email]'
Subject: [DB2-L] - RE: RUNSTATS and Dynamic SQL - DB2 z/OS

Mike,

Runstats can help static SQL before rebinding packages. And yes, it can help with dynamic SQL, regardless of who's running it and where it's come from, for the same reason.

Remember, Runstats is (among other things) about giving the Optimizer (sp) some info on the lie of the land in preparation (pun intended...) for choosing an access path. So given access paths have to be chosen regardless of whether the SQL is dynamic or static, Runstats will help in this endeavour.

Perhaps 'influence' might be a better word than 'help', but I'm not going there.

Cheers,


Raymond

Raymond Bell
DB2 Database Administrator | IT Operations | Technology | RBS

From: MARTIN, MIKE [mailto:[login to unmask email]
Sent: 20 April 2017 13:51
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - RUNSTATS and Dynamic SQL - DB2 z/OS


*********************************************
" This message originates from outside our organisation. Consider carefully whether you should click on any links, open any attachments or reply. If in doubt, forward to ~ Phishing"
*********************************************
Hi all,

I know that RUNSTATS can help static SQL after rebinding package(s). Can RUNSTATS help dynamic SQL coming from a remote server over DDF?

Mike Martin

This email may contain confidential and privileged material for the sole use of the intended recipient. If you are not the intended recipient, please contact the sender and delete all copies. Any review or distribution by others is strictly prohibited. Personal emails are restricted by policy of the State Employees' Credit Union (SECU). Therefore SECU specifically disclaims any responsibility or liability for any personal information or opinions of the author expressed in this email.

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

The Royal Bank of Scotland plc, Registered in Scotland No. 90312. Registered Office: 36 St Andrew Square, Edinburgh EH2 2YB

Authorised by the Prudential Regulation Authority and regulated by the Financial Conduct Authority and the Prudential Regulation Authority.

This e-mail message is confidential and for use by the addressee only. If the message is received by anyone other than the addressee, please return the message to the sender by replying to it and then delete the message from your computer. Internet e-mails are not necessarily secure. The Royal Bank of Scotland plc does not accept responsibility for changes made to this message after it was sent. The Royal Bank of Scotland plc may monitor e-mails for business and operational purposes. By replying to this message you give your consent to our monitoring of your email communications with us.

Whilst all reasonable care has been taken to avoid the transmission of viruses, it is the responsibility of the recipient to ensure that the onward transmission, opening or use of this message and any attachments will not adversely affect its systems or data. No responsibility is accepted by The Royal Bank of Scotland plc in this regard and the recipient should carry out such virus and other checks as it considers appropriate.

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

Patrick Bossman

RE: RUNSTATS and Dynamic SQL - DB2 z/OS
(in response to Joe Geller)

Thank you for the plug Joe!  I remember a joke somewhere, "I am familiar with all internet traditions".  (It's a joke because that is impossible)

... however, I am familiar with all of the ways to give optimizer hints if anyone wanted to talk about that.