Process to Justify an IBM DB2 Analytics Accelerator (IDAA) Part 3

Every mainframe DB2 shop should investigate reducing their costs by offloading workload to an IDAA appliance.  By investing in an IDAA appliance and sending your SQL workloads to it, you can speed up your processing without making any application changes. After investigating my daily SQL workload, I discovered millions of mainframe CPU MIPS that could be saved by using an IDAA appliance, so begin your IDAA justification at no cost today.

Hopefully everyone had a chance to read the first two parts of this series.  In the first part I talked about the setup steps for getting an IDAA Virtual Server configured and deployed within your environment. In the second part I walked through the steps to quickly and easily define an IDAA Virtual Server and a “production-like” environment for your investigation and justification activities.

In part 3 blog of “Justifying your IBM DB2 Analytics Accelerator IDAA,” I explain how to capture and examine your different SQL workloads for the right SQL to use to justify an IDAA environment. Based on a company’s business processing needs, every company’s workload is different. Some will show huge CPU savings and elapsed time savings by utilizing an IDAA appliance, but not all.

In the last blog, I asked you to read the blog describing how to capture SQL through the snapshot of the dynamic statement cache. I hope that the blog helped you practice snapping the dynamic cache to quickly grab real SQL for testing in your IDAA Virtual Server environment.

Justifying IDAA with Your Real SQL

1. The first thing to do is gather the right SQL for the first IDAA Virtual Server analysis. By snapping the Dynamic Statement Cache, you will be able to quickly gather a lot of SQL to examine. If you grab SQL during the middle of the day, you’ll probably get your online transactions and update/insert workloads. Unfortunately, IDAA does not process any SQL UPDATE, INSERT, or DELETE SQL so you can filter those statements from your analysis immediately. Next, examine the SQL that runs in sub-seconds; these highly tuned SQL statements are not going to be a big savings within your IDAA analysis either.
   
   Start looking at any SQL processing within your environment that runs in minutes or more. Examine all of your batch DB2 reporting and analytics that go against any of your larger tables. In addition, look at the new projects that are being scheduled or developed. New projects are good sources to use to easily justify an IDAA appliance because they can be done simply with the power of SQL. Justification is easy because the IDAA environment will dramatically cut development time, costs, and number of people required compared to the cost of traditional Java application developers. Designing a reporting system to provide new analytics analysis through Business Objects or your favorite GUI SQL tool can be done quite quickly. Having a process to go against Big Data or Data Lake environments can be done through SQL running against the IDAA appliance instead of using multiple steps designed by Java developers creating web interface processing modules and parsers to access Big Data information.
   
   
2. After looking at all your SQL performance improvement opportunities, gather the SQL into a text file for analysis. I recommend taking about 100 SQL statements per text file for analysis. Try to tie and group the 100 SQL statements to a particular creator, database, group of tables or application situation.
   
   Once you have all the SQL, examine the statements for special characters, a lack of database table creator names, and SQL text that goes beyond column 72. Since the Data Studio and IDAA Virtual Server tooling analysis gets confused when any SQL statement goes past column 72, it is good to look for the situation and fix it early. Next, add “WITH UR” and a semi-colon “;” delimiter to the end of every statement. This helps the Data Studio and IDAA Virtual Server tooling understand the SQL statements are read activities and be able to discern the end of each SQL statement.
   
   
3. Once you have reviewed and fixed the SQL accordingly, go into the Data Studio “Tune Queries” activity and create a new Query Tuning Project. After looking at all your SQL performance improvement opportunities, gather the SQL that has the most promising performance improvement opportunities and designate that SQL text file for analysis. Remember to keep the number of SQL statements small, and I again recommend taking about 100 SQL statements per text file for analysis. It is best to try to tie and group the 100 SQL statements to a particular creator, database, group of tables, or application situation because your testing also focuses the CPU improvements to a business group or area that can then sponsor an IDAA proof of concept project.
   
   
   Then go into your new Query Tuning Project and import your file of SQL into Data Studio for analysis. Expand the first Query Group and import the first file of SQL statements. You will also need to set the Creator Id to be used for the various table references  Data Studio will validate the SQL statements and access to your EXPLAIN tables.
   
   
4. Next. make sure that your IDAA Virtual Server definition is completed and that the IDAA Virtual Server is started. This can be done through the simple DB2 Command “-DIS ACCEL(*)” which will hopefully bring back a display of the “VIRTIDAA” IDAA Virtual Server that was defined previously when you read the first blog. If your display doesn’t show your IDAA Virtual Server active, verify that it is defined and can be started. You can start it by issuing the DB2 Command “-START ACCEL(*)ACCESS(EXPLAINONLY)”. If you need to define your IDAA Virtual Server, review the first blog process to Justify an IBM DB2 Analytics Accelerator (IDAA) Part 1.
   
   
5. Next, go back into Data Studio. The first time through the Data Studio SQL analysis against the IDAA Virtual Server, select all the analysis activities that are available against your file of SQL statements. By choosing all of the analysis activities, you can validate all the settings and EXPLAIN tables, and ensure that the IDAA Virtual Server is ready.
   
   
6. Clicking “OK” sets the Data Studio selected activities in motion. Data Studio will invoke all the requested activities against your file of first group of SQL statements. Based on the activities selected, SQL complexity, and the number of tables involved, the analysis processing can take some time. Look at the bottom right hand corner of Data Studio, as it displays the progress or status of the activities. It takes a while to do all the Data Studio selected activities, so be patient. For my environment, it took about 12 minutes to evaluate 100 statements that went against only 13 tables. Depending on the number of SQL statements and the number of tables involved within your SQL statement group, it may take longer.
   
   
7. When the Workload Analytics Acceleration Advisor IDAA activity begins during the selected Data Studio activities, a combination box is displayed. This combo box lets you choose the type of IDAA Analytics Acceleration desired. If your IDAA Virtual Server definition efforts didn’t work out, you can choose accelerator modeling. Once you’ve chosen your desired Workload Analytics Acceleration Advisor option, the Data Studio processing continues.
   
   
8. Once all the Data Studio selected activities are complete, the full reports of the SQL statements evaluation are displayed. The report contains sections for RUNSTATs recommendations, index recommendations, query advisor, access path advisor, and Analytics Accelerator Advisor information. All of the Advisors’ information about the workload provides a wealth of data about the SQL statements. The reports have suggestions on how to improve the various SQL statements’ runtime, access paths, and database with better statistics and more indexes. The report also details which SQL statements are eligible and ineligible for IDAA along with how much CPU savings potential is available. These reports can be quite extensive and need to be thoroughly reviewed for their merits.

Next time we will go through the full Analytics Accelerator Advisor information and its suggestions. These reports can be very interesting, so it is vital to understand all the details.

Are you planning on going to the World of Watson conference in Las Vegas at the end of October?  Come to my presentation “Justified Big Data Performance: Transform your business with IBM Analytics Accelerator – IDAA,” Tuesday 25-Oct, 3:00 PM-3:45 PM in the Mandalay Bay – Jasmine B Session DMT-2041. #ibmwow

If you are not going to the IBM World of Watson conference you can hear my educational session “Justified Big Data Performance Transforms Your Business with IBM Analytics Accelerator – IDAA” through the DB2NightShow on October 14th at 11:00 EST. Go to the DB2 Night Show website or click on this link.

Links to other parts:

Process to Justify an IBM DB2 Analytics Accelerator (IDAA) Part 1

Process to Justify an IBM DB2 Analytics Accelerator (IDAA) Part 2

Process to Justify an IBM DB2 Analytics Accelerator (IDAA) Part 4

Dave Beulke is a system strategist, application architect, and performance expert specializing in Big Data, data warehouses, and high performance internet business solutions. He is an IBM Gold Consultant, Information Champion, President of DAMA-NCR, former President of International DB2 User Group, and frequent speaker at national and international conferences. His architectures, designs, and performance tuning techniques help organization better leverage their information assets, saving millions in processing costs.