Accelerating Data Transformation with IBM DB2 Analytics Accelerator for z/OS Understanding and Using Accelerator-only Tables

Accelerating Data Transformation with IBM DB2 Analytics Accelerator for z/OS Understanding and Using Accelerator-only Tables

by Ute Baumbach, Patric Becker, Uwe Denneler, Eberhard Hechler, Wolfgang Hengstler, Steffen Knoll, Frank Neumann, Guenter Georg Schoellmann, Khadija Souissi, Timm Zimmermann
Released December 2015
Publisher(s): IBM Redbooks
ISBN: 9780738441191

Read it now on the O’Reilly learning platform with a 10-day free trial.

O’Reilly members get unlimited access to books, live events, courses curated by job role, and more from O’Reilly and nearly 200 top publishers.

Start your free trial

Book description

Transforming data from operational data models to purpose-oriented data structures has been commonplace for the last decades. Data transformations are heavily used in all types of industries to provide information to various users at different levels. Depending on individual needs, the transformed data is stored in various different systems.

Sending operational data to other systems for further processing is then required, and introduces much complexity to an existing information technology (IT) infrastructure. Although maintenance of additional hardware and software is one component, potential inconsistencies and individually managed refresh cycles are others.

For decades, there was no simple and efficient way to perform data transformations on the source system of operational data. With IBM® DB2® Analytics Accelerator, DB2 for z/OS is now in a unique position to complete these transformations in an efficient and well-performing way. DB2 for z/OS completes these while connecting to the same platform as for operational transactions, helping you to minimize your efforts to manage existing IT infrastructure.

Real-time analytics on incoming operational transactions is another demand. Creating a comprehensive scoring model to detect specific patterns inside your data can easily require multiple iterations and multiple hours to complete. By enabling a first set of analytical functionality in DB2 Analytics Accelerator, those dedicated mining algorithms can now be run on an accelerator to efficiently perform these modeling tasks.

Given the speed of query processing on an accelerator, these modeling tasks can now be performed much quicker compared to traditional relational database management systems. This speed enables you to keep your scoring algorithms more up-to-date, and ultimately adapt more quickly to constantly changing customer behaviors.

This IBM Redbooks® publication describes the new table type that is introduced with DB2 Analytics Accelerator V4.1 PTF5 that enables more efficient data transformations. These tables are called accelerator-only tables, and can exist on an accelerator only.

The tables benefit from the accelerator performance characteristics, while maintaining access through existing DB2 for z/OS application programming interfaces (APIs). Additionally, we describe the newly introduced analytical capabilities with DB2 Analytics Accelerator V5.1, putting you in the position to efficiently perform data modeling for online analytical requirements in your DB2 for z/OS environment.

This book is intended for technical decision-makers who want to get a broad understanding about the analytical capabilities and accelerator-only tables of DB2 Analytics Accelerator. In addition, you learn about how these capabilities can be used to accelerate in-database transformations and in-database analytics in various environments and scenarios, including the following scenarios:

  • Multi-step processing and reporting in IBM DB2 Query Management Facility™, IBM Campaign, or Microstrategy environments

  • In-database transformations using IBM InfoSphere® DataStage®

  • Ad hoc data analysis for data scientists

  • In-database analytics using IBM SPSS® Modeler

    •

    Table of contents

    1. Front cover
    2. Notices
      1. Trademarks
    3. IBM Redbooks promotions
    4. Preface
      1. Authors
      2. Now you can become a published author, too
      3. Comments welcome
      4. Stay connected to IBM Redbooks
    5. Chapter 1. Analytics on an IBM z Systems environment
      1. 1.1 Traditional data warehouse and analytics concepts
        1. 1.1.1 Persistency layers
        2. 1.1.2 Information supply chain
        3. 1.1.3 Analytics on z Systems aspects
      2. 1.2 IBM Transaction and Analytics Processing
        1. 1.2.1 Logical data warehouse
        2. 1.2.2 Technical approaches
        3. 1.2.3 Real-time analytics on z Systems
        4. 1.2.4 Analytics on operational data
      3. 1.3 Transformation patterns
        1. 1.3.1 Traditional ETL
        2. 1.3.2 Accelerating data transformation
        3. 1.3.3 New transformation patterns
      4. 1.4 New use cases
        1. 1.4.1 Use case description
      5. 1.5 Strategic outlook
    6. Chapter 2. Accelerator-only tables
      1. 2.1 Concepts and architecture
        1. 2.1.1 Non-accelerator DB2 table
        2. 2.1.2 Accelerator-shadow table
        3. 2.1.3 Accelerator-archived table and partition
        4. 2.1.4 Accelerator-only table (AOT)
      2. 2.2 Software level prerequisites
      3. 2.3 Syntax and capabilities
        1. 2.3.1 Creating accelerator-only tables
        2. 2.3.2 Inserting data into accelerator-only tables
        3. 2.3.3 Removing accelerator-only tables
        4. 2.3.4 Using accelerator-only tables in queries or DML statements
      4. 2.4 Transactional considerations
      5. 2.5 Lifecycle management
      6. 2.6 Limitations and restrictions
      7. 2.7 Performance considerations
        1. 2.7.1 INSERT SELECT performance considerations
    7. Chapter 3. Use cases that are enabled by accelerator-only tables and in-database analytics
      1. 3.1 The four use cases of the DB2 Analytics Accelerator
      2. 3.2 How accelerator-only tables and in-database analytics extend use cases
      3. 3.3 Acceleration of existing business critical queries
      4. 3.4 Derive business insight from z/OS transaction systems
      5. 3.5 Reduce IT sprawl for analytics initiatives
      6. 3.6 Improve access to historical data and lower storage costs
      7. 3.7 Summary
    8. Chapter 4. Multistep reporting
      1. 4.1 Concepts of multistep reporting
    9. Chapter 5. Using IBM DB2 QMF to store query results and import tables
      1. 5.1 QMF and IBM z Systems
      2. 5.2 QMF for z/OS and DB2 Analytics Accelerator
        1. 5.2.1 Accelerator-only table support in QMF for z/OS V11.2
      3. 5.3 Running queries and saving results using a QMF procedure
        1. 5.3.1 Queries used in the sample scenario
        2. 5.3.2 Running the procedure in DB2 for z/OS
        3. 5.3.3 Running the procedure in DB2 for z/OS and DB2 Analytics Accelerator
        4. 5.3.4 Running the procedure in DB2 Analytics Accelerator using accelerator-only tables created by the SAVE DATA AS command
        5. 5.3.5 Running the procedure in DB2 Analytics Accelerator using accelerator-only tables created by the RUN QUERY command
      4. 5.4 Importing tables as accelerator-only tables
      5. 5.5 Preferred practices
    10. Chapter 6. Accelerating IBM Campaign processing
      1. 6.1 What is IBM Campaign
      2. 6.2 Components and architecture
        1. 6.2.1 IBM Campaign and DB2 for z/OS
        2. 6.2.2 IBM Campaign performance considerations and usage of temp tables
        3. 6.2.3 Defining temporary tables
      3. 6.3 Our IBM Campaign environment
      4. 6.4 Campaign example scenario used in this chapter
        1. 6.4.1 Flowchart for car insurance campaign
        2. 6.4.2 Using temp tables for car insurance campaign
        3. 6.4.3 Using and enabling accelerator-only tables for car insurance campaign
    11. Chapter 7. In-database transformations
      1. 7.1 In-database transformations
      2. 7.2 Custom transformation and extract, transform, and load processes
        1. 7.2.1 Consolidation and optimization
        2. 7.2.2 Performance
      3. 7.3 Accelerator and accelerator-only table usage in IBM InfoSphere DataStage
        1. 7.3.1 IBM InfoSphere Information Server
        2. 7.3.2 Things to consider for configuration
        3. 7.3.3 Basic accelerator usage within Data Stage
        4. 7.3.4 Accelerator maintenance through DataStage
        5. 7.3.5 Optimizing existing DataStage jobs
        6. 7.3.6 Pitfalls
        7. 7.3.7 Loading large amounts of data through DataStage to the Accelerator
    12. Chapter 8. Accelerator-only tables supporting data scientists’ ad hoc analysis
      1. 8.1 Data science and ad hoc analysis
      2. 8.2 Interactive analysis with notebooks: Python and Jupyter
      3. 8.3 Example with insurance claim data in DB2 for z/OS
        1. 8.3.1 Sample data layout in DB2 for z/OS
        2. 8.3.2 Accessing DB2 for z/OS data in Python
        3. 8.3.3 Data analysis examples with accelerator-only tables
      4. 8.4 More data scientist aspects
    13. Chapter 9. Integrating more data sources and archiving for analytics
      1. 9.1 DB2 Analytics Accelerator Loader for z/OS
      2. 9.2 General function overview
        1. 9.2.1 Group Consistent Load
        2. 9.2.2 Dual load
        3. 9.2.3 Image Copy load
      3. 9.3 Support for accelerator-only tables
        1. 9.3.1 Loading accelerator-only tables using Accelerator Loader
      4. 9.4 Integrating other DBMS data with DB2 for z/OS data
      5. 9.5 Architecture pattern: Incremental archiving with accelerator-only tables
    14. Chapter 10. In-database analytics
      1. 10.1 Reasons to use in-database analytics with IBM DB2 Analytics Accelerator
      2. 10.2 Executing analytical functions using an Accelerator
      3. 10.3 Enable IBM SPSS modeling for acceleration
      4. 10.4 Technical implementation
    15. Appendix A. Description of IBM z Systems environment used for this publication
      1. A.1 IBM DB2 Analytics Accelerator
      2. A.2 IBM DB2 for z/OS
      3. A.3 IBM System z system environment
      4. A.4 IBM InfoSphere Information Server DataStage
    16. Related publications
      1. IBM Redbooks
      2. Other publications
      3. Online resources
      4. Help from IBM
    17. Back cover

    Product information

    • Title: Accelerating Data Transformation with IBM DB2 Analytics Accelerator for z/OS Understanding and Using Accelerator-only Tables
    • Author(s): Ute Baumbach, Patric Becker, Uwe Denneler, Eberhard Hechler, Wolfgang Hengstler, Steffen Knoll, Frank Neumann, Guenter Georg Schoellmann, Khadija Souissi, Timm Zimmermann
    • Release date: December 2015
    • Publisher(s): IBM Redbooks
    • ISBN: 9780738441191