A fix is available
APAR status
Closed as new function.
Error description
New function for DB2 10 for z/OS (part 4)
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 for z/OS users who have or are * * interested in the IBM DB2 Analytics * * Accelerator and who use either of the * * following DB2 functions: * * - ROWSET queries and multi-row FETCH * * - static SQL queries * **************************************************************** * PROBLEM DESCRIPTION: DB2 10 for z/OS new function support * * for the following features for query * * acceleration on the IBM DB2 Analytics * * Accelerator Version 4 is now added to * * DB2 via APARs PM93786 PM93787 PM93788 * * and PM93789 : * * (1) limited DB2 ROWSET fetching * * (aka multiple-row fetching / MRF ) * * of local dynamic or static * * accelerated queries * * (2) DB2 acceleration of static SQL * * queries * * These new DB2 features also require * * the following IBM DB2 Analytics * * Accelerator Version 4 new function * * APARs: PM92398 PM92399 PM92400 PM92402 * * PM92403 PM92404 PM92405 * **************************************************************** * RECOMMENDATION: * **************************************************************** ---------------------------------------------------------------- // DB2 ROWSET or Multiple-Row Fetching of Accelerated Queries // . DB2 ROWSET or multiple-row fetching uses a cursor defined as WITH ROWSET POSITIONING and an SQL FETCH that specifies rowset- positioning. Prior to these APARs, DB2 only supported ROWSET fetching for non-accelerated queries. With these APARs, DB2 10 extends limited multiple-row fetching to accelerated local dynamic or static queries, in order to support high performance access to accelerated query data. . For this limited multiple-row fetch support for accelerated local queries, the following restrictions apply to the FETCH statement used for the accelerated ROWSET query: - All FETCH requests must be rowset fetches - All FETCH requests must specify a FOR N ROWS clause - All FETCH requests must specify the same rowset size - All FETCH requests must specify target host variables (that is, the FETCH cannot be targetless for positioning) . A remote ROWSET query (where DB2 for z/OS is the remote server receiving the query request from a client) does not qualify for query acceleration. Acceleration of ROWSET queries is only extended to local queries. ---------------------------------------------------------------- ---------------------------------------------------------------- // DB2 Acceleration of Static SQL Queries // . Previously, DB2 10 for z/OS only supported acceleration of dynamic SQL queries. With these new function APARs, DB2 10 now also supports acceleration of static SQL queries in DB2 applications by using the following new bind options and values for DB2 packages: QUERYACCELERATION (NONE | ENABLE | ENABLEWITHFAILBACK | ELIGIBLE | ALL) . GETACCELARCHIVE (NO | YES) . As with existing dynamic SQL query acceleration, these new acceleration bind options only apply to cursor queries and the SELECT portion of the SQL INSERT from SELECT statement. The static SQL SELECT INTO statement cannot be bound for query acceleration. . The new acceleration bind options are supported on the follow- ing DSN subcommands and bind operations: - BIND/BIND COPY/REBIND PACKAGE (local and remote binds) - BIND DEPLOY (for SQLPL procedure) - SQL ALTER/CREATE PROCEDURE for native SQLPL procedure and SQL ALTER/CREATE FUNCTION for SQLPL scalar function, using the following SQL syntax: QUERY ACCELERATION NONE | ENABLE | ENABLE WITH FAILBACK | ELIGIBLE | ALL . GET_ACCEL_ARCHIVE NO | YES . . The new bind options are not supported for either CREATE/ALTER TRIGGER or REBIND TRIGGER PACKAGE. . The functions and behavior of these acceleration bind options for static SQL queries are the same as the comparable existing DB2 special registers CURRENT QUERY ACCELERATION and CURRENT GET_ACCEL_ARCHIVE that are used for dynamic query acceleration, except the DB2 decision to accelerate a static query and bind it for acceleration occurs at BIND/REBIND PACKAGE time, not at query execution when the application is run. The description of the new bind options and their values is as follows: . QUERYACCELERATION . NONE - No static SQL query in the application is bound for acceleration or will be accelerated when the application is run. . ENABLE - A static SQL query is bound for acceleration if it satisfies the acceleration criteria, including the cost and heuristics criteria, and the query is routed to an accelerator when the application is run. Otherwise, if the static query does not satisfy the acceleration criteria, then the query is bound for execution in DB2. . If an error condition such as one of the following examples occurs while executing the accelerated static query when the application is run, then DB2 will fail the static query and return a negative SQL code to the application: - if a failure occurs while executing the static query on the accelerator - the accelerator returns an error for the query - the accelerator is not started and DB2 cannot route the static query to the accelerator for execution . ENABLE WITH FAILBACK - Same as ENABLE behavior, except if one of the error condition examples listed occurs on first OPEN of the accelerated static query when the application is run, then instead of failing the static query and returning a negative SQL code to the application, DB2 will 'failback' the static query to DB2 by performing a temporary 'statement-level' incremental bind of the query and running the query in DB2. The application does not see the acceleration failure. . No query 'failback' to DB2 is possible after the application does a successful OPEN for the query on the accelerator. . ELIGIBLE - A static SQL query is bound for acceleration if the query meets the basic acceleration criteria, regardless of cost or heuristics criteria, and the query will be routed to the accelerator when the application is run. . ALL - ALL static SQL queries in the application are to be bound for acceleration and routed to the accelerator when the application is run. If DB2 determines that a static query cannot be bound to run on the accelerator and the query references a user base table/view, DB2 fails the BIND/REBIND PACKAGE operation with an error message for that query. (A failure exception is made for Declared Global Temporary Tables (DGTTs) and Created Global Temp Tables (CGTTs) because these tables cannot be accelerated). . Except as noted for ENABLE WITH FAILBACK, if an error condition occurs while executing the accelerated static query when the application is run, DB2 will fail the static query and return a negative SQL code to the application. . . GETACCELARCHIVE . NO - No static SQL query is bound to retrieve archived data from the accelerator. If the static query is also not bound for acceleration, then the query will be bound to run in DB2. If the static query is bound for acceleration because bind option QUERYACCELERATION was specified, then when the application is run the query will be routed to the accelerator, but the query will only retrieve 'active' data on the accelerator and no archived data on the accelerator will be retrieved. . YES - If bind option QUERYACCELERATION is also specified and the static query references an accelerated table that has partitioned data archived on an accelerator, and the static query statisfies the acceleration criteria (as specified by QUERYACCELERATION), then the query is bound for acceleration and when the application is run the query retrieves the archived data on the accelerator. . If the static query cannot be bound for acceleration because it does not statisfy the criteria according to the QUERYACCELERATION specified, then DB2 will fail the BIND/REBIND PACKAGE operation with an error message for that query. . The default value for both new bind options is option not specified . The default for the bind options are NOT taken from the comparable DB2 installation system parameters (zparms) QUERY_ACCELERATION and GET_ACCEL_ARCHIVE. . For a successful bind using the new accelerated bind options, the bind option values are NOT stored in DB2 catalog table SYSIBM.SYSPACKAGE. Instead, the new bind option values are stored in new DB2 table SYSACCEL.SYSACCELERATEDPACKAGES that the user must create using DB2-supplied DDL shipped in these APARs. This table is required before using the acceleration bind options. More details are provided below under "Setup required for static SQL query acceleration". . The bind options can also be used for acceleration of dynamic queries. If the bind options are specified, when the application is run the bind options will also set the initial values of the comparable acceleration special registers CURRENT QUERY ACCELERATION and CURRENT GET_ACCEL_ARCHIVE if the special registers have not already been explicitly SET prior to that application being invoked. Those special register values will then be used for dynamic queries in that application so you do not need to change the application by adding an explicit SET statement for the acceleration special register(s). The special registers do not apply for static queries. Precedence order (low to high) for setting the initial value of the special registers is as follows: (1) DB2 Installation system parameters (QUERY_ACCELERATION and GET_ACCEL_ARCHIVE) (2) Bind option value if specified (QUERYACCELERATION and GETACCELARCHIVE) (3) explicit SET statement for the special register . ---------------------------------------------------------------- Important usage notes to remember for static SQL query acceleration: (1) Before doing the BIND/REBIND PACKAGE (or CREATE/ALTER PROCEDURE/FUNCTION) with the acceleration bind options to bind static SQL queries for acceleration, you must - first accelerate the referenced DB2 table(s) to an accelerator on IBM DB2 Analytics Accelerator, - then you BIND/REBIND the query's package specifying the desired QUERYACCELERATION behavior to bind that static query for acceleration. If you want a static query to use data that is archived to IBM DB2 Analytics Accelerator, you must - first archive at least one partition of the referenced DB2 table(s) (and this includes having previously accelerated the DB2 table to IBM DB2 Analytics Accelerator), - then you BIND/REBIND the query's package specifying GETACCELARCHIVE(YES) and the desired QUERYACCELERATION behavior to bind that query for acceleration and retrieve archived data. . These steps apply anytime you choose a new static query to accelerate or retrieve archived data from IBM DB2 Analytics Accelerator. You must repeat the BIND/REBIND PACKAGE (or ALTER PROCEDURE/FUNCTION) to accelerate the static query for any newly accelerated/archived table. . Note that the accelerator need not be 'started' before doing the bind. The validation that the target accelerator for the query's accelerated table is started/active is done when the static query is run, not at bind package time. . (2) For a static query that has been bound for acceleration, in the SYSIBM.SYSPACKSTMT entry for the package's query, the column STATUS will have the new value 'O' for the DECLARE CURSOR, as well as, the OPEN, FETCH, and CLOSE. This is one easy way to see static SQL queries that are accelerated in a package. . (3) If the existing bind option EXPLAIN(YES | ONLY) is specified along with the new acceleration bind options, the DB2 bind package process will insert a row for the static query in the tables DSN_QUERYINFO_TABLE and PLAN_TABLE . The DSN_QUERYINFO_TABLE row will have a special reason code in column REASON_CODE if the static query did not qualify for acceleration, just as is currently done during execution-time PREPARE for dynamic queries. . (4) Under VALIDATE(RUN) or SQLERROR(CONTINUE), static query that requires a 'statement-level' incremental bind when the query is executed also will not qualify for acceleration. . ---------------------------------------------------------------- Setup required for static SQL query acceleration after applying these APARs to the DB2 subsystem -- . (1) As with acceleration of dynamic queries and the usage of the acceleration special registers, usage of the new bind options requires that the DB2 zparm ACCEL be set to a value other than NONE to enable the DB2 subsystem for query acceleration to the IBM DB2 Analytics Accelerator. . (2) In addition, the creation of new user-created DB2 table SYSACCEL.SYSACCELERATEDPACKAGES and its two indexes, SYSACCEL.DSNACP01 and SYSACCEL.DSNACP02, are required before using the new bind options. DB2 supplies the DDL needed to create these new SYSACCEL objects in updated job DSNTIJAS of these APARs (also in the HOLD instructions). If you already accelerate dynamic SQL queries and have created the other SYSACCEL objects defined by the DDL in DSNTIJAS, you only need to run the DDL to create SYSACCEL.SYSACCELERATEDPACKAGES and the two indexes SYSACCEL.DSNACP01 and SYSACCEL.DSNACP02 . Please see the HOLD for further instructions for this DDL. . (3) It is recommended that these PTFs be applied to all DB2 members of a Data Sharing group before you begin using the new acceleration bind options, in case you attempt to use or run a package bound with an accelerated static query on a DB2 member that does not have these PTFs applied. If this scenario occurs, the following are examples of existing standard error messages you will receive because package is marked with higher functional release dependency mark 'P' (even though fallback from DB2 11 for z/OS did not occur): - For an attempt to run the package (on a DB2 subsystem that has the DB2 11 for z/OS fallback SPE applied), SQLCODE904 with reason code 00E7009E / RC00E7009E is issued by DB2: SQLCODE -904 SQLERRM 00E7009E.00000801.collid.pkgname.contoken SQLERRP DSNXEAAL SQLERRD -110 SQLSTATE57011 . - For an attempt to REBIND the package, message DSNT246I or DSNT236I is issued by DB2. - For an attempt to BIND COPY the package, message DSNT207I or DSNT208I is issued by DB2. - For an attempt to run the package on a DB2 subsystem that does not have the DB211 fallback SPE applied, the following intentional DB2 application abend will occur: ABND=04E-00E30068 C=101.SCC -PKGAUTOB ABEND04E / AB04E RC00E30068 / 00E30068 ---------------------------------------------------------------- Please see the HOLD DOC information in these APARs/PTFs for the following new messages and SQLCODEs related to this new function support: - SQLCODE -4746 - DSNT320I DSNT321I DSNT322I DSNT323I DSNT324I ----------------------------------------------------------------
Problem conclusion
Temporary fix
Comments
Additional search keywords: IDAAV4R1/K SQLMULTIROW SQLFETCH MULTI-ROW SQLPROCEDURE SQLCODE4746 MSGDSNT236I MSGDSNT246I MSGDSNT207I MSGDSNT208I MSGDSNT320I MSGDSNT321I MSGDSNT322I MSGDSNT323I MSGDSNT324I
APAR Information
APAR number
PM93789
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt
Submitted date
2013-07-25
Closed date
2013-11-18
Last modified date
2013-12-02
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Modules/Macros
DSNXEAAL DSNXEBPG DSNXECL DSNXECLF DSNXECW DSNXECWA DSNXECWU DSNXEDP DSNXEEZT DSNXEMG1 DSNXEPM DSNXEPP DSNXERB DSNXERBP DSNXERT DSNXERT2 DSNXESX4 DSNXGRTM DSNXIEPL DSNXOBFC DSNXODML DSNXOD5 DSNXOEX1 DSNXOFF DSNXOIN DSNXOLTD DSNXOMD DSNXONZA DSNXONZB DSNXONZC DSNXONZH DSNXONZO DSNXOOP DSNXOOS1 DSNXOQ2 DSNXOTF DSNXOW1 DSNXOYPL DSNXRACC DSNXRFF DSNXRFN DSNXROHR HDBAA10J
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK98716
UP13/11/28 P F311
Fix is available
Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.
[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}},{"Business Unit":{"code":"BU054","label":"Systems w\/TPS"},"Product":{"code":"SG19M","label":"APARs - z\/OS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"10.1","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 December 2013