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

The benefits of having an IDAA appliance is something that every mainframe DB2 shop should investigate.  Investigating and justifying an IDAA appliance can be done at no cost.  In the previous blog, I talked about the set up steps for getting an IDAA Virtual Server configured and deployed within your environment.

In this blog, I will go through more steps to quickly and easily define a “production-like” environment for your IDAA investigation and justification activities.  Below are next steps to create the production environment and gather statistics so you can test your SQL against the IDAA Virtual Server and understanding huge potential CPU and elapsed time savings.  Once you see the IDAA CPU savings and estimate the potential SQL runtime improvements, you can start to have serious conversations and management justification discussions to improve your shop’s workload performance while saving CPU against your mainframe licensing.

Justifying IDAA through CPU Savings

When we left off last time, the definition of the IDAA Virtual Server was complete along with the installation of Data Studio on your workstation with connectivity to your test DB2 environment.  Completing those items is required to move forward, so finish those efforts as soon as possible if you have not already.

Analysis for the benefits of an IDAA appliance to assist and monitor the growing processing activities is done easily and at no additional cost to the company through use of an IDAA Virtual Server environment.  The next steps allow you to create a production-like environment within your test DB2 system that can then use the IDAA Virtual Server environment.

1. Avoid any production issues and never prototype in your production environment.  The following steps show you how to create a “production-like” environment within your test system.  Since no one has extra storage, only create definitions for database, tablespaces, tables, and indexes for our prototype IDAA Virtual Server production-like environment.
   
   Go to your production system, extract the current table, and index definitions (DDL) of the database that you think IDAA could provide the best performance benefit.  The DB2 database environment should be the one that has large tables, tablespace, or index scans or where the longest running processes execute.  Using any of the IBM, BMCc or CA DB2 administration products to extract the DDL easily or you can acquire the production DDL through your change control repository of production database table definitions.  If your DDL is not available, it can also be captured in through the next step.
   
   
2. Next, go back to your favorite DB2 Administration tool and gather all the productions table and index statistics currently used by the dynamic and static workload processes in production.  By capturing the current production environment statistics your IDAA Virtual Server prototype environment results will be directly compared to the production CPU workloads.
   
   To gather all the DB2 production DDL and statistics through the IBM DB2 Administration tool you need to migrate the production database, tables, index definitions, and statistics.  Instead of using the DDL command with the IBM DB2 Administration product, use the MIG command against the database tablespaces, and then ADD more tablespaces to your migration object list.  Within the IBM DB2 Administration tool, it can be a bit confusing, so run through the process with a single tablespace first to get the hang of it.
   
   Once all the database tablespace objects are within your migration list, the IBM DB2 Administration tool will take you to a panel listing all the migration options for the source and target migration parameters.  Be careful to choose the options for batch JCL execution, so you can review the processes before the migration is executed.  I use many of the defaults, the test DB2 system, and my new IDAA test environment Creator Id for the target database environment.  Once you complete this panel, the IBM DB2 Administration tool generates a job that creates three other jobs to extract the source production objects and recreate them in your target environment with your new Creator Id.  These three generated jobs also extract all the production tablespace objects DDL and DML to update the DB2 Catalog statistics within the various worklist data sets that it creates in the migration extract processing.
   
   If you are experienced with the IBM DB2 Administration tool, this migration process is common but if you are not, get assistance from someone who is experienced, because the migration process can be confusing for a beginner.  In addition, double check that your jobs and their WORKLIST datasets are large enough, since a partitioned environment’s DDL and Statistics DML can be a significant number of lines.  When migrating my environment, each of the DDL and DML files were over 325,000+ lines because of the large number of partitioned objects.
   
   
3. Next, run the first of the previously generated three DB2 Administration tool migration jobs from Step 2 to extract the production environment DDL and DML.  This job’s output should produce the DDL and DML Data sets to setup your IDAA Virtual Server environment.  If you have gotten the production DDL from your change repository, examine the DDL and manually do a Change All of the CREATOR ids to your unique Creator Id for all of the objects for your IDAA testing.  Since the DDL and DML files are so big, File-Aid or File Manager editing is usually required for this large production DDL and DML file modification.  In addition, Change All the object DB2 DDL object definitions to be DEFINE NO.  By defining your IDAA test environment with all the objects as DEFINE NO, the objects will be put into the DB2 Catalog only and no storage will be required for your IDAA justification prototype.
   
   
4. Once all the object’s Creator Ids and the DEFINE NO modifications to the DDL is complete, run the DDL to create your test “production like” IDAA prototype environment.  Once the database objects are created, the next step is to run the DML to update all the statistics for your new environment.  Make sure to get DB2 Catalog update privileges, since some shops have that capability RACF or DB2 restricted.  Verify the Creator Ids and run the DML DB2 Catalog statistic updates to complete your prototype “production like” IDAA environment.
   
   Run your standard DB2 database structure reports and verify your IDAA tablespace and index objects and statistics are all complete.  Also, verify all the statistics are production sized, realistic, and in good DB2 performance condition.  During my prototype IDAA definition and verification process, I uncovered some bad statistics and reorg conditions existed within the production environment.  Quickly, appropriate production maintenance was done and I had to re-extract the DB2 Catalog statistics from production and rerun them for the prototype IDAA environment.  Trust and verify your prototype before starting any SQL evaluation against your IDAA production like environment.
   
   
5. Now that your test “production like” environment is defined to the test DB2 subsystem, go into Data Studio and connect to it.  Verify that the all the tables and index definitions and their related statistics exist and are correct.  The IDAA Virtual Server testing will go against this “production like” environment. Since it has no data, it is critical that its statistics and structure be exactly the same as production.  The IDAA Virtual Server will capture and use the statistics from this environment to makes its access path decisions and CPU savings estimates.  For further verification find and move a simple production dynamic SQL statement to this new test environment, execute a DB2 EXPLAIN against the new environment, and compare its access path to the production access path.  The production and test environments should come back with the same EXPLAIN access path.
   
   
6. The next step is the whole process of picking the appropriate workload for testing against IDAA Virtual Server environment.  Analyze your workloads and any reporting processes for processing candidates to test against IDAA.  Review my old blog from 2010 on how to quickly and easily capture SQL through the snapshot of the dynamic cache.  Go through this blog and practice snapping the dynamic cache to quickly grab real SQL for testing in your IDAA Virtual Server environment.

Unbelievably, the IBM DB2 IDAA has been available for many years, helping all types of customers improve overall processing, especially their analytical processing.  The next blog will go through the process of examining the workload against the production like IDAA Virtual Server environment.  This is the step where the payoff comes through, and you discover the potential to save millions of CPU seconds through using an IDAA environment.

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

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 3

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.