What happens if most users can work with a snapshot, but some users definitely need to query original data since important updates must be honored? If a defined mart hasn’t been refreshed to contain the most recent data, queries can be routed to DB2 for z/OS traditional data access methods by setting CURRENT REFRESH AGE special register to 0. On the other hand, a value of ANY allows for IBM Smart Analytics Optimizer processing, giving you maximum flexibility.
A query can consist of one or more query blocks. Typical queries eligible for IBM Smart Analytics Optimizer processing access data in a large (fact) table, including inner joins or left outer joins with smaller (dimension) tables. If only one or more query blocks of a given query use the structure previously described, queries can also partially benefit from the IBM Smart Analytics Optimizer since DB2 for z/OS routes one query block at a time to the IBM Smart Analytics Optimizer for execution. To predict the possible amount of queries that can be routed to the IBM Smart Analytics Optimizer for execution in a DB2 for z/OS environment using dynamic SQL, IBM offers an assessment of DB2 for z/OS OLAP workloads (see Figure 3).
Offloading Queries to the IBM Smart Analytics Optimizer
DBAs can identify which query blocks will be routed to the IBM Smart Analytics Optimizer before execution, so they don’t need to apply any of the aforementioned performance practices for those types of query blocks. Additionally, it’s easy to understand if a query will be executed using a mart stored inside the IBM Smart Analytics Optimizer by obtaining the new information that will be available in a new explain table named DSN_QUERYINFO_TABLE. The explain function is enhanced to populate this table. If column TYPE = ‘A’ and the REASON_CODE = 0 after executing explain for a given query, a query block successfully qualifies for offloading with the Accelerated Query Table (AQT) specified in QINAME1 and QINAME2.
To fully protect System z and DB2 for z/OS SLAs, the IBM Smart Analytics Optimizer has its own built-in failover mechanisms. DB2 for z/OS is fully fenced against possible accelerator failures. The main memory of the worker nodes contains data of one or more marts. If a blade fails, a coordinator node takes the content of the failing blade’s memory from the storage where it has also been stored during the offload process and continues processing. After the failing node has been brought up again, it’s now referred to as a coordinator node.
If, for some reason, the entire IBM Smart Analytics Optimizer has been disabled and DB2 for z/OS realizes that the IBM Smart Analytics Optimizer is no longer available for query processing, it simply uses a fallback access path and accesses the data.
System Setup and Availability
The IBM Smart Analytics Optimizer is a combined solution of both new hardware and software. It runs on the new IBM zEnterprise BladeCenter Extension (zBX). The IBM Smart Analytics Optimizer software is shipped as SMP/E installable for the DB2-related stored procedures, and the acceleration software is installed on the zBX via the System z Service Element using a product DVD. Once installed, updates to the software are implemented as PTFs, which are then propagated to the blades by calling a stored procedure. The GUI to identify any marts needs to be installed on a client machine. The software prerequisite for the IBM Smart Analytics Optimizer is DB2 9 for z/OS. IBM Smart Analytics Optimizer will be GA on Nov. 19, 2010.