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.
