Today I am not going to discuss all the gory details about the internal workings of IDAA. But really just what us DB2 Geeks need to know and how we can see if our killer four page, 1 hour elapsed, 15 minute CPU time query would actually run on the external device. You see it is up to the DB2 Optimizer to decide if this query would work better off platform or keeping the query within DB2 on the mainframe. The DB2 Optimizer uses a set of rules to make this decision, the end user can change those rules but it is best to leave the default until you know a bit more. Information such as result set size, table size are all used to determine whether a statement is routed to the accelerator.
Before we get too far into the weeds there are some criteria the optimizer uses to determine if a query can be routed to IDAA. The current release of IBM IDAA is 3.1; some of these restrictions have changed in the past with PTFs and release upgrades.
· DB2 in V9 NFM, V10 CM9 or V10 NMF.
· The entire query can be accelerated, i.e. the unit of acceleration is a whole query
· All tables referenced to in the query must be enabled for acceleration
· The query is dynamic
· The query is defined as read-only
· The query is a SELECT statement
· The associated cursor is not defined as a scrollable or a rowset cursor (multi-row fetch)
· The DRDA protocol access is in effect for a remote workload (no private protocol)
· The query is from a package (not plan DBRM)
· Routing to IDAA is considered more efficient for performance by DB2 Optimizer than to execute the query in DB2 mainline
You notice the third item; the tables referenced in the query must be enabled for acceleration. Yes … that means when you actually implement IDAA, copies of your tables need to be put onto the IBM Netezza device. This is where planning and preparation are important. Why unload/load tables to a Netezza if no queries are going to run there, or if your query has a join you have to make sure all those tables are on the Netezza device. The creation of these tables, maintenance, unload/load, propagation is all controlled by DB2. DB2 Analytics Accelerator Studio is the interface used by the Database Administrator, which is hosted by IBM Data Studio.
Back to how we can see if our queries are IDAA worthy.
The DB2 Explain function has added information about accelerator usage or lack of usage. It will tell you whether the query qualifies for acceleration and if it does not it will give a reason code. The actual access path details if it is accelerated by IDAA are separate and will be shown with IDAA Data Studio.
For each query we pass to DB2 Explain a row is inserted into the following DB2 plan tables. If the query is accelerated the PLAN_TABLE ACCESSTYPE will be set to a value of ‘A’ and the DSN_QUERYINFO_TABLE will show the converted query text in the QI_DATA column. If the query is not qualified, the DSN_QUERYINFO_TABLE will be populated with a non-zero REASON_CODE and the QI_DATA column will tell the sorry story.
Here is the fun part, the explain tables can be populated even if there is no accelerator connected to DB2. The following are the steps that need to be followed:
· Set System Parameters (DSNZPARM) correctly, most important is ACCEL. Needs to be set to AUTO or COMMAND. DB2 V9 systems have an ACCEL_LEVEL parameter as well.
· Create accelerator tables SYSACCEL.SYSACCELERATORS and SYSACCEL.SYSACCELERATEDTABLES. The DDL can be found in member DSNTIJAS in the SDSNSAMP library.
· Have all your explain tables created, a good member if you are considering IDAA is DSNTIJSG in SDSNSAMP.
· Update the SYSSACCELERATORS table by inserting:
o INSERT INTO SYSACCEL.SYSACCELERATORS VALUES (‘SYSACCEL’, NULL);
· The tables you think would work best on the Netezza machine get them into the SYSACCELERATEDTABLES table.
· Start the pseudo accelerator with EXPLAINONLY parameter
o –START ACCEL(*) ACCESS(EXPLAINONLY)
· EXPLAIN your queries
o SET CURRENT QUERY ACCELERATION = ENABLE (that can be set via a ZPARM)
o Execute EXPLAIN statement
o Check PLAN_TABLE for ACCESSTYPE=A
o Check DSN_QUERYINFO_TABLE for those who didn’t make the cut
So why does this all matter? Understanding your environment and how IDAA can or can’t help. Depending on which side of the fence you live, IDAA can save dollars because you might not need as much Mainframe MIPS to process your data warehouse requests. If you are an end-user, with IDAA your queries will run that much fast and you can make business decisions in minutes vs. hours. Then there is that fuzzy area where IDAA can eliminate the need to do DB2 SQL tuning. I say, as long as there are DB2 queries out there DBAs, Application developers, and Performance experts will be needed to help optimize DB2 to make the right decision.