Putting Accelerator-Only Tables to Use
In the first part of this two-part article accelerator-only tables were introduced. Part two discusses how accelerator-only tables can be put to use in IBM DB2 Analytics Accelerator. The discussion will cover loading accelerator-only tables with example of both what works and what cannot be used.
Using Accelerator-Only Tables
I would like to make sure you are aware of a couple of new terms for the different types of Analytics Accelerator data (see Table 1). Hopefully, we can all use these accelerator table descriptive names to minimize confusion about to what type of table we are referring.
Now that accelerator-only tables have been enabled for use and created in an Analytics Accelerator, they need data loaded into them. To do that today, start with accelerator-shadow DB2 tables that have already been loaded.
An existing table from a DB2 subsystem can be added to an Analytics Accelerator using the supplied stored procedure SYSPROC.ACCEL_ADD_TABLES. (Note: This stored procedure cannot be used to load an accelerator-only table.) Once the table has been added to the accelerator, the accelerator-shadow DB2 tables can then be loaded with data (rows) using one of two methods. The first is the stored procedure SYSPROC.ACCEL_LOAD_TABLES that will copy data from a non-accelerated DB2 table to an accelerator-shadow DB2 table. Once the accelerator-shadow DB2 table on the Analytics Accelerator has been enabled (SYSPROC.ACCEL_SET_TABLES_ACCELERATION), it’s available for query processing. The three previously mentioned stored procedures are delivered as part of the Analytics Accelerator.
The second accelerator load method available is the IBM DB2 Analytics Accelerator Loader for z/OS, a product offering available in most countries for order through ShopzSeries. Loader can load data directly into an accelerator-shadow DB2 table or into both a non-accelerated DB2 table and an accelerator-shadow DB2 table at the same time.
Keeping in mind that accelerator-only tables can only be loaded today from data that has already been copied to the Analytics Accelerator, it’s time to take a look at how that is done. You can use an INSERT… WITH SELECT statement to load rows into an accelerator-only table.
It is of note that as mentioned previously, if you are using the INSERT… WITH SELECT to load an accelerator-only table, and since both our source and target of the INSERT and the SELECT have to be on the accelerator, they are accelerated; both the SELECT and INSERT portions. Keep in mind the rows being read from the accelerator-shadow DB2 table on the accelerator are more than likely not going to be the most current data. You also won’t be able to backup the accelerator-only table.
What is very cool about using the SQL INSERT… WITH SELECT statement is that the SELECT portion can be any kind of SELECT statement that is eligible to run on an accelerator. That means you can combine multiple accelerator-shadow DB2 table using inner and outer joins, combining columns or performing calculations using multiple columns to form new values for new columns in the accelerator-only table, use only specific columns to form shorter rows, performing data manipulation like aggregation and summarization, cleansing data, and normalization or denormalization of the data. All of these functions are being completed on the Analytics Accelerator, taking advantage of the Analytics Accelerator’s performance. These are the type of actions that one might perform today when moving data for extract and transformation (the “E” and “T” in ETL) or in the creation of data marts.
Multistep processes— processes that require the creation of temporary intermediate tables—are another use of accelerator-only table. Numerous products will be able to make use of this feature. The one that I think of first is Query Management Facility and its SAVE DATA function (a future planned enhancement). There’s also a product that determines the difference between two columns by doing two SELECTs that generate two temporary tables.
The ability to manipulate table data into the equivalent of a temporary table in the accelerator could be a tremendous asset to the folk that have to develop deeper insights in a corporation’s customers or markets. Accelerator-only tables then become the work areas for data scientists.
Before closing out this portion of the discussion, there are a couple of restrictions associated with accelerated-only tables that have yet to be mentioned, including that:
- There’s no high availability or workload balancing support (accelerator-only table is created in only one Analytics Accelerator)
- The source table in the SELECT must be an accelerator-shadow DB2 table or another accelerator-only tables
- It’s not possible to update values in distribution key columns
- No backup/recovery mechanisms
- MIXED CHAR or UNICODE CHAR data types are padded with blanks in DB2, but not on the Analytics Accelerator. Therefore query results might be different on accelerator-only tables compared to non-accelerated DB2 tables.
- Two SQL sessions cannot UPDATE/DELETE the same row in an accelerated-only table concurrently
On the Horizon
There are great things planned for the use of accelerator-only tables including IBM DB2 Analytics Accelerator LOADER and IBM DB2 Query Management Facility for z/OS 11 (QMF for z/OS).
I used the DB2 Accelerator LOADER in one of the above examples. I think it only prudent to mention that IBM DB2 Analytics Accelerator Loader for z/OS, V1.1 (5639-OLA) support for direct loads into an accelerator-only table is not currently available. However, the future direction is to support this feature. Read more about it in the original announcement materials.
As for QMF, QMF 11 for z/OS has been around for about eight months. It’s a great tool and very popular. However, since the introduction of the Analytics Accelerator, it has had one failing: SAVE DATA. Due to the way SAVE DATA works, queries that use that QMF feature are not eligible to run on the Analytics Accelerator, but it’s in the process of being remedied. It’s also the future direction of QMF is to take advantage of accelerator-only tables for the SAVE DATA function, making queries that need this capability accelerator eligible in the future.
More to Learn
Accelerator-only tables are very new and this article has only scratched the surface of what they might be used for in the future. If you have an IBM DB2 Analytics Accelerator, you really need to check them out. I think you’ll like what you see.
Like what you just read? To receive technical tips and articles directly in your inbox twice per month, sign up for the EXTRA e-newsletter here.
comments powered by