HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now

Surekha Parekh

HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now

HURRY IBM Db2 AI for z/OS Launch webcast starting 2nd Oct 11 AM EST! REGISTER NOW

The day has arrived for IBM Db2 AI for z/OS launch webcast, if you have not registered hurry you have a few hours left to register. Remember even if you can not attend today by registering you have access to the webcast replay and the live Q&A !

 

Register Now and secure your seat with 800+ interested attendees

 

http://ibm.biz/Db2ZAIWebcast


Surekha Parekh (LinkedIn)
DB2 Marketing Portfolio Director, Manager                                   IDUG Global Marketing Chair
MBA DipM MCIM Chartered Marketer               &amp

Michael Hannan

RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Surekha Parekh)

No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the  Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire.  LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full  result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just  identifying  high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1, and of course look forward to trying the AI data capture and Machine Learning. In the meantime have been using Predicate Selectivity Overrides, when we pretty much have determined the true predicate filtering, by data queries, and know the likely hostvar value patterns.

When we don't know the Hostvar values and distribution Frequency, AI should be able to do it for us.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Oct 04, 2018 - 05:17 AM (Europe/Berlin)

Walter Janißen

AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Michael Hannan)
Hi Michael

I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Michael Hannan <[login to unmask email]>
Gesendet: Donnerstag, 4. Oktober 2018 05:11
An: [login to unmask email]
Betreff: [DB2-L] - RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now


No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire. LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just identifying high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

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

  • image001.png (2.6k)

Roy Boxwell

HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Walter Janißen)
That’s my take as well... I also asked about just using the Db2 on z part of the feature – it could be cool to “harvest” all the info for human use without the problems of the x86 side... Sadly Terry said that would not work...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

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



From: Walter Janißen [mailto:[login to unmask email]
Sent: Thursday, October 4, 2018 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



Hi Michael



I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Gesendet: Donnerstag, 4. Oktober 2018 05:11
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire. LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just identifying high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



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

Attachments

  • smime.p7s (5.1k)

Joe Geller

RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Roy Boxwell)

A question for Terry:

The AI will track the pattern of which values are passed as host variables and use that to help pick an access path.  For static SQL there will still be just one access path picked (at the next rebind).  Will the optimizer base it on just the frequency of occurrence of the values or will it weight it by the estimated costs of the access paths?

For example, if there are only 2 values used - value A occurs 97% of the time and value B occurs 3% of the time.  Access path 1 is best for value A and access path 2 is best for value B. However, if access path 2 is used for value A it will only be slightly worse than access path 1, but if access path 1 is used for value B it will be much, much worse than access path 2.

Will the optimizer decide that access path 1 should be picked because it is better 97% of the time, or will it be conservative and pick access path 2 because it will not be terrible for either value and will be much better for value B?

Joe

In Reply to Roy Boxwell:

That’s my take as well... I also asked about just using the Db2 on z part of the feature – it could be cool to “harvest” all the info for human use without the problems of the x86 side... Sadly Terry said that would not work...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

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



From: Walter Janißen [mailto:[login to unmask email]
Sent: Thursday, October 4, 2018 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



Hi Michael



I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Gesendet: Donnerstag, 4. Oktober 2018 05:11
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire. LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just identifying high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



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

Terry Purcell

RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Roy Boxwell)

Roy - targeting your question here first. Simply externalizing information for human consumption doesn't help our goal of improving the inputs to the optimizer to make better access path decisions. Your ability to take such data and manually input to the optimizer is either cumbersome (with regard to overriding filtering) or not possible (such as OPTIMIZE clause - unless you change the SQL). There is more to it than that - but that is a 30,000ft level view.

With regard to the complexity of the infrastructure with regard to x86 - I understand - and we are about to release a zLinux install option as an alternative to x86. There are of course other infrastructure simplification options that are possible - but I cannot comment on any future plans that aren't already announced.

Regards

Terry Purcell

In Reply to Roy Boxwell:

That’s my take as well... I also asked about just using the Db2 on z part of the feature – it could be cool to “harvest” all the info for human use without the problems of the x86 side... Sadly Terry said that would not work...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

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



From: Walter Janißen [mailto:[login to unmask email]
Sent: Thursday, October 4, 2018 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



Hi Michael



I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Gesendet: Donnerstag, 4. Oktober 2018 05:11
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire. LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just identifying high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



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

Terry Purcell

RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Joe Geller)

Joe - hoping that we don't get overlapping threads due to my answering yourself and Roy separately.

To your question - it is a machine learning model that predicts what the next value is, and the confidence it has in that prediction. It is up to the optimizer to use that information to decide on the access path.

This area can be more difficult than you first think. We actually attempted to address this issue back in DB2 10 when we introduced an "uncertainty factor" to supplement the filter factor. Whereas in V10 we hit performance issues if we assumed you could potentially access the skewed value - we could pick a better "average" access path if you did, but would regress queries if you never searched for the skewed value(s). And I can tell you that we saw both......

"Learning" of course gives us the opportunity to "know" - which is your point. This is a challenge but also an opportunity for us to learn based upon the query execution history information - where we can correlate the actual performance with host variable values used.

I don't want to make commitments about the success of AI here, just as I cannot ever make commitments about the optimizer choosing the right access path. But I do know that learning is now possible where it wasn't achievable previously.

Regards

Terry Purcell

In Reply to Joe Geller:

A question for Terry:

The AI will track the pattern of which values are passed as host variables and use that to help pick an access path.  For static SQL there will still be just one access path picked (at the next rebind).  Will the optimizer base it on just the frequency of occurrence of the values or will it weight it by the estimated costs of the access paths?

For example, if there are only 2 values used - value A occurs 97% of the time and value B occurs 3% of the time.  Access path 1 is best for value A and access path 2 is best for value B. However, if access path 2 is used for value A it will only be slightly worse than access path 1, but if access path 1 is used for value B it will be much, much worse than access path 2.

Will the optimizer decide that access path 1 should be picked because it is better 97% of the time, or will it be conservative and pick access path 2 because it will not be terrible for either value and will be much better for value B?

Joe

In Reply to Roy Boxwell:

That’s my take as well... I also asked about just using the Db2 on z part of the feature – it could be cool to “harvest” all the info for human use without the problems of the x86 side... Sadly Terry said that would not work...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

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



From: Walter Janißen [mailto:[login to unmask email]
Sent: Thursday, October 4, 2018 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



Hi Michael



I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Gesendet: Donnerstag, 4. Oktober 2018 05:11
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire. LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just identifying high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



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

Joe Geller

RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Terry Purcell)

Terry,

I never assumed it was easy :).  Of course, learning and knowing the performance for each host variable value is not the same as knowing the best answer.  For some queries a 50 ms increase in response time for the majority may be better than a 10 second response for the outliers (in the opinion of the DBA/business).  But for other queries (especially very high volume) a 50 ms increase (especially if it is CPU time) may be unacceptable (too much load on the system) and the outliers may be rare enough and only for in-house users, not customers and can therefore afford 10 second response time.

Just like with people, learning all the facts and knowing everything does not mean you will make the right decision.

Joe

In Reply to Terry Purcell:

Joe - hoping that we don't get overlapping threads due to my answering yourself and Roy separately.

To your question - it is a machine learning model that predicts what the next value is, and the confidence it has in that prediction. It is up to the optimizer to use that information to decide on the access path.

This area can be more difficult than you first think. We actually attempted to address this issue back in DB2 10 when we introduced an "uncertainty factor" to supplement the filter factor. Whereas in V10 we hit performance issues if we assumed you could potentially access the skewed value - we could pick a better "average" access path if you did, but would regress queries if you never searched for the skewed value(s). And I can tell you that we saw both......

"Learning" of course gives us the opportunity to "know" - which is your point. This is a challenge but also an opportunity for us to learn based upon the query execution history information - where we can correlate the actual performance with host variable values used.

I don't want to make commitments about the success of AI here, just as I cannot ever make commitments about the optimizer choosing the right access path. But I do know that learning is now possible where it wasn't achievable previously.

Regards

Terry Purcell

In Reply to Joe Geller:

A question for Terry:

The AI will track the pattern of which values are passed as host variables and use that to help pick an access path.  For static SQL there will still be just one access path picked (at the next rebind).  Will the optimizer base it on just the frequency of occurrence of the values or will it weight it by the estimated costs of the access paths?

For example, if there are only 2 values used - value A occurs 97% of the time and value B occurs 3% of the time.  Access path 1 is best for value A and access path 2 is best for value B. However, if access path 2 is used for value A it will only be slightly worse than access path 1, but if access path 1 is used for value B it will be much, much worse than access path 2.

Will the optimizer decide that access path 1 should be picked because it is better 97% of the time, or will it be conservative and pick access path 2 because it will not be terrible for either value and will be much better for value B?

Joe

In Reply to Roy Boxwell:

That’s my take as well... I also asked about just using the Db2 on z part of the feature – it could be cool to “harvest” all the info for human use without the problems of the x86 side... Sadly Terry said that would not work...



Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: <mailto:[login to unmask email]> [login to unmask email]
http://www.seg.de http://www.seg.de

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



From: Walter Janißen [mailto:[login to unmask email]
Sent: Thursday, October 4, 2018 9:54 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



Hi Michael



I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
<mailto:[login to unmask email]> [login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Michael Hannan <[login to unmask email] <mailto:[login to unmask email]> >
Gesendet: Donnerstag, 4. Oktober 2018 05:11
An: [login to unmask email] <mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now



No "hurry" really, I had a look at it today.

Currently Filter Factors and costs estimates for host variable predicates are not great, due to skewed data and range predicates optimism.

I look forward to the Optimizer getting the Filter Factors right (as right as possible) with Db2zAI, a quantum leap, and to future enhancements. It hasn't done me out of a job yet, since is not yet Designing/Optimizing the indexes (merely gives a much greater chance the Optimizer will choose the optimal indexes when they are available) and not yet trying various access paths till see which one performs best. When that all arrives, and DB2 self tunes, I will have to retire. LOL It is possible to automate the human decisions used to design Indexes and tune. The difficulty is get a true understanding of complex SQLs.

Past FFs for Hostvars were too optimistic (mostly). I hope AI decides to make them just a bit on the pessimistic side, to make conservative access paths, so that access paths are performing well for worst case filtering scenarios, and even better for good filtering scenarios. Also be just a little pessimistic on how many rows might be fetched.

I always wanted a Pessimizer. LOL

I wonder, is the capture of Hostvars the critical thing or capture the number of qualifying rows as well, in case site does not have the right Stats. Perhaps that would have too much overhead, I don't know.

I had a recommendation to code OPTIMIZE FOR n ROWS on all online Cursor SQLs, however might not be needed with AI detection of actual rows fetched. My improved recommendation to code FETCH FIRST n ROWS ONLY for all online Cursors with possible sort, still stands. That means DB2 may be able to avoid materialising the full result set with any Sort. Enhancements of Sort possible in the future too, to maybe sort chunks of data in stages when index provides data partially ordered.

Automation of Tuning tasks is the future (and near future with AI), not just identifying high cost SQLs, done by monitors. I already start to think what extras are likely for AI Release +1.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd



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



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

Michael Hannan

RE: AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Walter Janißen)

In Reply to Walter Janißen:

I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

Politics! I hate political decisions getting in the way of technology. LOL However we have to live in the real world. I have no idea what it costs either.

I find many sites are thinking their access paths are pretty much O.K. even for complex SQLs, yet many access paths may not be quite optimal or not precise. Sure they have index match cols but maybe not the best possible index is present or has been chosen. Maybe the join sequence is not optimal. Can a site afford the cost of high usage SQLs not quite Optimal. By not taking DB2 zAI they are taking this risk that they run more expensive in the Peak hours than what they need to. They blissfully think everything is well tuned and Optimal. For single table SQLs and other simple stuff. they may be right. The very complex SQLs, and we do see some doosies, are too baffling to touch them. LOL Scary! Sometimes when we collect Hostvar values for query performance exceptions, we are quite surprised by the strange values of the hostvars. We had no idea. As example, what appeared at first glance to be a name and address search, had several equals predicates with hostvar values of blank, giving the impression that the table had been fudged with also holding data not for name and address search at all. The filtering was quite different to what we might have supposed for all the rows with blank name and addresses. Machine learning could know these things!

When sites start getting Db2z AI and seeing access path changes and significant savings, others might be forced to take note. 

Sites could use Predicate Selectivity Overrides if they are aware why their paths are not optimal, but with a lot more human knowledge and effort involved. I put that effort in already and got a lot of access path changes (some vastly better), and some quite different to what I had in mind (but maybe even better). I don't arrogantly think I always know the exact best Access Path for a medium to very complex SQL. There are so many alternatives to choose from, and subtle variations. A brute force OPTHINT or SQL Access Path Stabilisation may not always be optimal either. In old days, I used to recommend DB2 Stats and SQL fudges to disable or downgrade  Filter Factors, but now don't need those. This will be my fallback when not having Db2z AI.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Phil Grainger

AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Michael Hannan)
“Can a site afford the cost of high usage SQLs not quite Optimal” – What a GREAT question for a Friday

Politics, pressures of work, attitude to risk, outsourcing all play a part in considerations of SQL performamce

Bluntly, SQL seems to fall into one of two categories


1. The performance is so bad, people are complaining and work is at hand to make things better
2. Nobody is complaining, so performance must be OK

I’ve always been of the opinion that EVERY Db2 installation has SQL that is running suboptimally. Either because the access path/SQL statement is not the best it could be, or that the data storage/definitions are not ideal

But what to do about those? Is there even a willingness to hunt for them? Could sites really same money by addressing problems they didn’t know they had?

A tangential question, that I’ve been trying to get a sensible answer to is “What is the BUSINESS value of best-possible performing SQL?”. In other words, how does the BUSINESS value end-user performance? We all “know” that dissatisfied customers will move away, but following several banking meltdowns in the UK, it seems sometimes customers need to be really REALLY annoyed to actually make the move elsewhere. So in the grand scheme of things, perhaps end-user response time doesn’t matter as much as we think it does – which is kind of sad

The question here becomes one of “Who pays to improve performance?”. If it’s IT, they need to know the VALUE of that improvement. Or will the business be asked to fund an IT investment to make the applications better? Money is tight
________________________________

Phil Grainger

Principal Enablement Manager

[login to unmask email]

Direct



+44 (0)118 921 8000

Mobile



+44(0)7808 643 479


E2, Eskdale Road
Winnersh
Berkshire
RG41 5TS


[http://media.cms.bmc.com/images/corp_signature_bmclogo_2014.jpg] http://www.bmc.com

[https://acclaim-production-app.s3.amazonaws.com/images/2429c3cd-a1de-44fc-b4f3-bc762bb2f963/IBM%2BChampion%2B-%2BAnalytics%2B2018.png]





From: Michael Hannan [mailto:[login to unmask email]
Sent: 05 October 2018 05:29
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now


In Reply to Walter Janißen:
I think the worst thing here being is that Db2zAI is a separate feature you have to charge for. And most companies do their machine learning on other platforms, so will be reluctant to install machine learning on z.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

Politics! I hate political decisions getting in the way of technology. LOL However we have to live in the real world. I have no idea what it costs either.

I find many sites are thinking their access paths are pretty much O.K. even for complex SQLs, yet many access paths may not be quite optimal or not precise. Sure they have index match cols but maybe not the best possible index is present or has been chosen. Maybe the join sequence is not optimal. Can a site afford the cost of high usage SQLs not quite Optimal. By not taking DB2 zAI they are taking this risk that they run more expensive in the Peak hours than what they need to. They blissfully think everything is well tuned and Optimal. For single table SQLs and other simple stuff. they may be right. The very complex SQLs, and we do see some doosies, are too baffling to touch them. LOL Scary! Sometimes when we collect Hostvar values for query performance exceptions, we are quite surprised by the strange values of the hostvars. We had no idea. As example, what appeared at first glance to be a name and address search, had several equals predicates with hostvar values of blank, giving the impression that the table had been fudged with also holding data not for name and address search at all. The filtering was quite different to what we might have supposed for all the rows with blank name and addresses. Machine learning could know these things!

When sites start getting Db2z AI and seeing access path changes and significant savings, others might be forced to take note.

Sites could use Predicate Selectivity Overrides if they are aware why their paths are not optimal, but with a lot more human knowledge and effort involved. I put that effort in already and got a lot of access path changes (some vastly better), and some quite different to what I had in mind (but maybe even better). I don't arrogantly think I always know the exact best Access Path for a medium to very complex SQL. There are so many alternatives to choose from, and subtle variations. A brute force OPTHINT or SQL Access Path Stabilisation may not always be optimal either. In old days, I used to recommend DB2 Stats and SQL fudges to disable or downgrade Filter Factors, but now don't need those. This will be my fallback when not having Db2z AI.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
BMC Software Limited Registered Office: Building E2, Eskdale Road, Winnersh, Wokingham, Berkshire, United Kingdom, RG41 5TS Registered in England No. 1927903 The content of this email is confidential. If you are not the addressee, you may not distribute, copy or disclose any part of it. If you receive this message in error, please delete this from your system and notify the sender immediately.
Attachments

  • image001.jpg (8k)
  • image004.png (9.3k)

Peter Vanroose

Re: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Phil Grainger)

The economically "correct" answer is very simple:
when the ROI (return on investment), expressed in $$$, is larger than the investment (time etc. spent optimizing the SQL), it should be done. Otherwise, it shouldn't.

In Reply to Phil Grainger:

[...] “What is the BUSINESS value of best-possible performing SQL?” [...] “Who pays to improve performance?”

--      Peter Vanroose
        ABIS Training & Consulting,
        Leuven, Belgium.
        https://www.abis.be/

Michael Hannan

RE: AW: HURRY IBM Db2 AI for z/OS Launch webcast is TODAY! Register Now
(in response to Phil Grainger)

In Reply to Phil Grainger:

“Can a site afford the cost of high usage SQLs not quite Optimal” – What a GREAT question for a Friday

Politics, pressures of work, attitude to risk, outsourcing all play a part in considerations of SQL performamce

Bluntly, SQL seems to fall into one of two categories


1. The performance is so bad, people are complaining and work is at hand to make things better
2. Nobody is complaining, so performance must be OK

I’ve always been of the opinion that EVERY Db2 installation has SQL that is running suboptimally. Either because the access path/SQL statement is not the best it could be, or that the data storage/definitions are not ideal

But what to do about those? Is there even a willingness to hunt for them? Could sites really same money by addressing problems they didn’t know they had?

Re point 2, it is well known that when users are used to the bad performance, they don't keep complaining, only when something goes bad that was good before.

The charging is CPU based, and heavy CPU is not the user problem to complain about unless it causes very poor response time.

Smart shops will keep reducing CPU and costs, until they find that no more savings can be found or effort is too high, and I don't mean by the outsourcer company, LOL.

Computing is also full of solutions looking for a problem, i.e. we came up with the solution, but we are not sure what the problem was. i.e. we put in index compression because it was there, LOL, or we put in a scrollable cursor. LOL

Maybe many sites are not clear what the problem is or if they have one. It might be clear to some of us why Db2z AI is a potential large saver, because we have seen many access paths issues that need to be solved, or could be, and why thy are not Optimal already. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd