IBM announces IBM Db2 AI for z/OS - General Availability 21st September

Surekha Parekh

IBM announces IBM Db2 AI for z/OS - General Availability 21st September

I'm very excited to let you know that today IBM Announced Db2 AI for z/OS. IBM Db2 AI for z/OS empowers the Db2 for z/OS optimizer to select improved access paths for SQL and improves application performance using machine learning models. This new offering can help:

  • Improve Db2 SQL access path selection
  • Enhance Db2 application performance
  • Enable rapid model learning specific to the data and configuration per Db2 subsystem


Internal IBM benchmarks show up to 25% performance benefit in terms of reduction in CPU time when machine learning technology integrated with  Db2 AI for z/OS was employed for a set of qualifying Db2 queries.

Learn More : Join Db2 AI for z/OS Launch Webcast  

http://ibm.biz/Db2ZAIWebcast


Date: 2nd October 2018 

Time: 11:00 AM - 12:30PM  EST 4:00 PM - 5:30 PM BST

 
To learn more and register visit: 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: IBM announces IBM Db2 AI for z/OS - General Availability 21st September
(in response to Surekha Parekh)

This looks like a major breakthrough. I see two main points in the announcement:
1. Capture the number of rows Fetched for Cursors.
2. Capture Host Variable values

These are two techniques I am using, a bit more manually, to tune SQLs along with naturally wanting to know the CPU and try to have Getpages by object for real Prod workload. I want to know the true Filter Factors, especially in worst case scenarios.

If the typical number of rows Fetched is known, this is better than an inaccurate OPTIMIZE FOR n ROWS if one was coded, which was possibly the number of Fetches expected to be executed. Imagine if the typical rows Fetched is 0.2 per OPEN. This being known could help a lot.

If typical Host Variable values are known, a weighted average filter factor can be determined far better than the current overly Optimistic Filter Factors, for Static SQLs and maybe dynamic with parameter markers. This hopefully better automates what I try to do with Predicate Selectivity Overrides.

I just guess from the announcement, but looks like there will be access paths greatly improved, by better understanding of typical runtime behaviour.

The need for good Stats to support these things will increase, particularly Frequent Value Stats (not purely on first column of an index), I would guess, or will it capture the actual filtering at run time, when in data collection mode.

DB2 still can't choose the best access path if we don't have the right indexes, so need for tuning does not go away. 

I wonder if we will ever get to having two or more different concurrent access paths for a Static SQL, where the decision to use one or the other, is done at runtime, based on Hostvar values? V12 Adaptive Multi-index was a slight step in that direction.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Ray Lopez

RE: IBM announces IBM Db2 AI for z/OS - General Availability 21st September
(in response to Michael Hannan)

Michael, is IBM DB2 AI for zOS something that comes with DB2 12?  Or is it something we have to pay extra for? 

na

Terry Purcell

RE: IBM announces IBM Db2 AI for z/OS - General Availability 21st September
(in response to Ray Lopez)

IBM Db2 AI for z/OS (Db2ZAI) is an extra charge feature.

Good summary Michael - and to answer your specific questions and clarify some comments.

- The need for accurate statistics is still there in this iteration. And exploitation of frequencies is now possible even though the original query had host vars/parameter markers.

- Utilization of the number of rows fetched is only used if we detect that that the application does NOT fetch to end-of-file.

- You are correct that this doesn't negate the need or opportunity from performance tuning and especially the benefit that comes from appropriate indexing.

Regards

Terry Purcell

In Reply to Ray Lopez:

Michael, is IBM DB2 AI for zOS something that comes with DB2 12?  Or is it something we have to pay extra for? 

na