A fix is available
APAR status
Closed as new function.
Error description
New function on DB2 10 for z/OS (part 2)
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: All users of DB2 10 for z/OS who use query * * acceleration * **************************************************************** * PROBLEM DESCRIPTION: 1.This APAR adds a new feature: * * IDT (In-Database Transformation) * * to help users take full * * advantage of the fast * * speed of an accelerator for * * operations which only occur on * * an accelerator. * * To achieve this, new table type * * AOT (Accelerator-Only Table) is * * added to DB2 and the accelerator. * * The characteristic of this new * * table type is that when CREATE * * TABLE is issued, the table will be * * created on both DB2 and the * * accelerator. However, data only * * exists on the accelerator; i.e., * * DML could only be executed on * * the accelerator. * * * * 2.Enable offloading of a Temporal * * query to an accelerator. * * Currently only Business Time * * is supported. * * 3.Block offloading when the * * length of nchar/nvarchar is > * * 16000 in the converted query. * * SQLCODE -4742 with reason code * * 11 will be issued. * * 4.Fix SQLCODE -904 "Invalid SQL * * Statement" for an offloaded * * query which includes a minus * * literal string. * * 5.Fix 04E-00E70005 DSNXOMSF:P170. * * After the fix, SQLCODE -4742 * * with reason code 11 is issued * * for an unsupported statement * * that contains MEDIAN. * * * **************************************************************** * RECOMMENDATION: * **************************************************************** An accelerator improves query performance dramatically. However, some operations involving huge data movement could not take full advantage of the fast speed of an accelerator. For example, some user applications save the intermediate result set to a table. This table is populated by INSERT with SELECT and the table will be used for the next step e.g.,data transformation. There is no need to backup this table as the data can always be recreated from the insert statement. With the current DB2 accelerator support, an application needs to explicitly invoke a stored procedure to create a table in the accelerator, which does not work for some tool-generated statements. In addition, only the SELECT part of the INSERT statement can be accelerated, which leads to high CPU overhead if the SELECT results in a large result set. What's more, INSERT occurs on DB2 only, so the subsequent DML cannot run in the accelerator because the accelerator does not have the data that is newly inserted, or it still needs to explicitly invoke the stored procedure to load the data. This new function APAR now resolves the above scenario with the introduction of a new table type: AOT (Accelerator Only Table). With the Accelerator Only Table, the whole process above is automated and enabled on the accelerator: The table is automatically created in the accelerator when the CREATE is issued on DB2. Data is automatically inserted in the accelerator when the INSERT is issued on DB2 and the data only exists in the accelerator, not on DB2. The query that references the AOT will be accelerated (If not, DB2 DB2 will issue SQLCODE -4742). Summary of Syntax/Semantics change: 1. CREATE TABLE ...... IN ACCELERATOR <accelerator-name> (a). New clause IN ACCELERATOR <accelerator-name> Specifies that the table is an Accelerator-Only Table. "accelerator-name" specifies the accelerator that the table will be defined in. (b). The DB2 Catalog contains the table and column definitions. TYPE is 'D' (definition only) for an AOT. The data of the table exists only in the accelerator, not in DB2. (c). When IN ACCELERATOR option is specified, CREATE table will also implicitly create the corresponding table in the accelerator and populate the DB2 SYSACCEL.SYSACCELERATEDTABLES pseudo catalog table. (d). CURRENT QUERY ACCELERATION special register does not need to be set for the successful creation of an AOT. (e). When IN ACCELERATOR clause is specified, some options of the CREATE TABLE are not allowed. Refer to the corresponding documentation for details. 2. DROP TABLE If the table is defined as Accelerator-Only table, DROP TABLE will also implicitly (a) Drop the table from the accelerator. (b) Delete the row from SYSACCEL.SYSACCELERATEDTABLES pseudo catalog table (c) CURRENT QUERY ACCELERATION special register does not need to be set for the successful drop of the table. The accelerator does not necessarily need to be active at the time of DROP. (d) DROP table will issue a new warning message +4748 with a specific reason code under the following situations: Reason 1: A row corresponding to the table does not exist in the SYSACCELERATEDTABLES catalog table Reason 2: The accelerator server is not available for one of the following reasons: The accelerator server has not been started. The accelerator server was started in EXPLAIN ONLY mode The accelerator server was started in maintenance mode The accelerator server accelerator does not exist Reason 3: A DRDA error was encountered 3. DML DML that references this table, for example, INSERT/UPDATE/DELETE/SELECT must be executed in the accelerator, not in DB2. If the statement type is not supported by the accelerator or if the statement contains any expression that is not supported by the accelerator, DB2 will return SQLCODE -4742. CURRENT QUERY ACCELERATION cannot be NONE if INSERT/UPDATE/DELETE/SELECT references an accelerator-only table. Otherwise, -4742 will be issued. 4. Utilities: Except for RUNSTATS and REPAIR, the rest of the utility statements--for example, LOAD, REORG, UNLOAD, etc.-- are not allowed on this table. RUNSTATS for Accelerator-Only tables will leave all statistics as -1. 5. Accelerator-Only Table is supported in a static application. 6. All the existing authorization rules apply and are enforced by DB2. 7. Accelerator Alias. An Alias could be created for a specific accelerator name. 8. Explain New reason code 22 New values(22) for DSN_QUERYINFO_TABLE columns REASON_CODE and QI_DATA are added: 22 The statement references an accelerator-only table, but the statement is not offloadable. For example: MERGE statement references an Accelerator-Only table. UPDATE or DELETE statement references an Accelerator-Only table, but the target table of the UPDATE or DELETE is a regular DB2 table: UPDATE DB2_TABLE SET ..(SELECT .. FROM ACCEL_ONLY_TABLE) INSERT, UPDATE or DELETE of a row expression with subselect. UPDATE ACCEL_ONLY_TABLE SET (C1, C2) = (SELECT C3, C4 FROM TABLE2); When the target table of INSERT/UPDATE/DELETE is an Accelerator-Only Table, the plan table contains one row with ACCESSTYPE as 'A'. For EXPLAIN of a statement that references an Accelerator-Only Table and is not eligible for query acceleration, PLAN_TABLE output will be similar to the above examples, except ACCESSTYPE will have a value of blank, and for a SELECT statement, QBLOCK_TYPE='PRUNED'. 9. Catalog statistics RUNSTATS for an Accelerator-Only Table will leave all statistics as -1. The catalog statistics fields will need to be modified to reflect this. The following is an example. NPAGES: Total number of pages that include rows of the table. The value is -1 if statistics have not been gathered, or the row describes a view, an alias, a created temporary table, an Accelerator-Only table or an auxiliary table. This column can be updated. PCTPAGES: ..... assigned to the table. The value is -1 if statistics have not been gathered, or the row describes a view, alias, created temporary table, an Acceleraor-Only table or auxiliary table. This column can be updated. Additional Keywords: IDAAV4R1/K SQLCODE904 ABEND04E RC00E70005 SQLCODE4742 SQLMEDIAN
Problem conclusion
Temporary fix
Comments
APAR Information
APAR number
PI30376
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
A10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2014-11-25
Closed date
2015-03-31
Last modified date
2015-05-04
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI26404
Modules/Macros
DSNGDBRT DSNHAPLY DSNHERRM DSNHPTAB DSNHSM5 DSNHSM6A DSNHSYMH DSNTABA DSNTADL DSNTAEA DSNTBBP2 DSNTBRB2 DSNTIAM DSNTIA1 DSNTLRAC DSNUEXDB DSNXEAAL DSNXEBPG DSNXECL DSNXECLF DSNXECLN DSNXECW DSNXECWA DSNXECWU DSNXEDP DSNXEDSC DSNXEDS1 DSNXEPM DSNXEPP DSNXERBP DSNXERT DSNXERT2 DSNXESQL DSNXEUF0 DSNXIAB9 DSNXIAPR DSNXIATB DSNXIATS DSNXICB3 DSNXICIX DSNXICPR DSNXICSY DSNXICTB DSNXICTR DSNXIDCL DSNXIDPR DSNXIDTB DSNXIRTB DSNXISB1 DSNXISB6 DSNXISB8 DSNXISUB DSNXI14 DSNXI15 DSNXMIT2 DSNXODML DSNXOD1 DSNXOD3 DSNXOD5 DSNXOEX1 DSNXOGP DSNXOIN DSNXOIWS DSNXOLTD DSNXOMQT DSNXONZA DSNXONZB DSNXONZC DSNXONZH DSNXONZO DSNXONZQ DSNXONZS DSNXOOP DSNXOOS1 DSNXOP0 DSNXORFN DSNXOTG DSNXOTL DSNXOVD DSNXOW1 DSNXTAD
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UI26404
UP15/04/16 P F504
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"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
06 May 2020