A fix is available
APAR status
Closed as new function.
Error description
DB2 11 for z/OS new function.
Local fix
n/a
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 11 for z/OS users of query * * acceleration * **************************************************************** * PROBLEM DESCRIPTION: Currently, a user cannot specify a * * target accelerator for an * * accelerated query to be executed. If a * * user has multiple accelerators where * * the same set of DB2 tables are * * accelerated, the accelerator workload * * balance algorithm will distribute the * * queries depending on queue length on * * each accelerator. * * * * This APAR addresses that requirement * * by providing the new function support * * that allows a user to specify a target * * accelerator for accelerated queries. * * This APAR requires APAR PI73563 to be * * applied together with this APAR. * **************************************************************** * RECOMMENDATION: * **************************************************************** IBM DB2 Analytics Accelerator enables high performance execution for complex analytics statements. Currently, a user cannot specify a target accelerator for an accelerated query to be executed. If a user has multiple accelerators where the same set of DB2 tables are accelerated, the accelerator workload balance algorithm will distribute the queries depending on queue length on each accelerator. . This APAR helps in the following use cases: 1. User wants to direct their queries to different accelerators depending on the priorities of the workload, with high priority queries directed to the fastest, highest capacity accelerator and low priority queries directed to a slower accelerator. DB2's current acceleration support does not allow this level of 'directed' control. 2. User has an additional accelerator on a remote location (Disaster Recovery location) in addition to locally deployed accelerators. Current algorithm does not take into account latency caused by the long network distance and some high priority queries are sent to the remote accelerators and incur increased elapsed time. . A new special register and Bind option will allow users to direct accelerated queries to a specific accelerator(s). . (1) CURRENT ACCELERATOR (special register) The CURRENT ACCELERATOR special register specifies the name of preferred accelerator(s) to which should DB2 send accelerated dynamic queries. The special register does not apply to static queries. When the CURRENT ACCELERATOR special register names eligible accelerator servers, DB2 will consider sending eligible accelerated queries to one of those servers before other accelerator servers. If none of the accelerator servers named by CURRENT ACCELERATOR are not available or eligible, DB2 will consider other available accelerator servers. . The data type is VARCHAR(8). . The initial value of CURRENT ACCELERATOR is determined by one of the following settings: - If the ACCELERATOR bind option is specified for the bind of a package, the default for CURRENT ACCELERATOR is the ACCELERATOR bind option value. This bind option does not have a default value. - Otherwise, the default setting of CURRENT ACCELERATOR is blank (no preferred accelerator). . Example: The following statement sets the CURRENT ACCELERATOR special register so that ACCEL1 is the preferred accelerator. SET CURRENT ACCELERATOR = ACCEL1; . Syntax . >>-SET CURRENT ACCELERATOR--=--+-accelerator-name-+---------->< '--host-variable---' . 1. Nearly all DB2 for z/OS special registers are described as being able to be set from a "host-variable". In practice, this really means a "host-variable" or an SQL variable but not a global variable. . Description When the CURRENT ACCELERATOR special register names eligible accelerator servers, DB2 will consider sending eligible accelerated queries to one of those preferred accelerator servers before other accelerator servers. If none of the accelerator servers named by the CURRENT ACCELERATOR are available or eligible, DB2 will consider other available accelerator servers. . The value specified by accelerator-name or host-variable is a single accelerator name or an accelerator logical name as recorded in SYSIBM.LOCATIONS (the logical name represents one or more accelerator server names). . The following restrictions are in place for the accelerator name: - It must not consist of more than eight characters. - Allowed characters are A-Z, 0-9. . Defining an accelerator alias enables you to map a logical name (alias) to one or more physical accelerator server names that are used for a specific DB2 subsystem. A DB2 application can then use that alias, which points to accelerator server(s) for that DB2 subsystem. You can also set up multiple aliases to point to the same accelerator(s), if needed. . To create an alias for an accelerator: Create special rows in the SYSIBM.LOCATIONS communications database (CDB) table of one DB2 subsystem. For example: INSERT INTO SYSIBM.LOCATIONS (LOCATION, LINKNAME, DBALIAS) VALUES ('logical_system_accel_name', 'DSNACCELERATORALIAS', 'physical_system_accel_name') To specify more than one physical accelerator to be represented by an alias, concatenate the accelerator names separated by whitespace as a string to be inserted as the value for DBALIAS. A maximum of 14 accelerator names with a maximum of 8 characters for each name can be inserted. The logical name itself should be a maximum of 8 characters long, to be compatible with the ACCELERATOR special register and Bind option. . Example 1: The following statement sets the CURRENT ACCELERATOR special register so that ACCEL1 is the preferred accelerator. SET CURRENT ACCELERATOR = ACCEL1; . Example 2: The following statements set the CURRENT ACCELERATOR special register to the logical name/alias IDAATEST that represents 2 accelerators, ACCLPRO1 and ACCLPRO2 : INSERT INTO SYSIBM.LOCATIONS (LOCATION, LINKNAME, DBALIAS) VALUES (IDAATEST, 'DSNACCELERATORALIAS', 'ACCLPRO1 ACCLPRO2'); . SET CURRENT ACCELERATOR = IDAATEST; . (2)NEW SQL WARNING SQLSTATE 0169D THE ACCELERATOR DOES NOT EXIST. SQLCODE +4754 THE ACCELERATOR <accelerator> DOES NOT EXIST. Explanation A statement referenced an accelerator server that does not exist or has not been started yet. The specified accelerator name may not have been defined to DB2 yet or has not been started since DB2 was last started. . <accelerator> The name of the accelerator server that does not exist or has not been started yet. . System Action Processing continues normally. . Programmer Response No change is required. The specified accelerator server may be defined to DB2 before it is used during the execution of accelerated queries that require it. However, if the specified accelerator server does not exist or is not started when an accelerated query is executed, then DB2 ignores the ACCELERATOR special register or bind option at that time, including when a logical name or alias is used to represent 2 or more accelerator servers and none of the accelerator servers are defined or started. . (3) NEW Reason Code for SQL ERROR -4742 26: The CREATE TABLE statement clause IN ACCELERATOR specifies an accelerator alias that resolves to more than one accelerator. Programmer response Use the reason code to determine the cause of the failure, and take the appropriate action: 26: Ensure that IN ACCELERATOR clause in the CREATE TABLE statement specifies an accelerator alias that resolves to only one accelerator. . (4) ACCELERATOR BIND OPTION A new Bind option ACCELERATOR is introduced with this PTF. When bind option ACCELERATOR is specified, the bind option will: - set the target accelerator(s) to be used for static statements queries - be the default value for the special register if there is no SET Statement issued. The bind option value, like the special register only affects runtime behavior of an accelerated query. If one of the specified accelerator(s) does not exist during Bind time, then DB2 will issue a warning message and will not fail the Bind. . (5) NEW WARNING FOR BIND COMMAND DSNT326I csect-name bind-type WARNING FOR PACKAGE = package-name. The ACCELERATOR <accelerator> DOES NOT EXIST or has not been started yet. . Explanation The BIND or REBIND subcommand referenced an accelerator server that does not exist or has not been started yet. The specified accelerator may not have been defined to DB2 yet or has not been started since DB2 started. . csect-name The name of the control section that issued the message. . bind-type The type of bind subcommand: BIND or REBIND. . package-name The fully qualified name of the package in the following format: 'location.collection.package.(version)'. . <accelerator> The name of the accelerator server that does not exist or has not been started yet. . System Action Processing continues. . Programmer Response No change is required. The specified accelerator server may be defined to DB2 before it is used during the execution of the package. . (6) New Clause for CREATE PROCEDURE (SQL - native), ALTER PROCEDURE (SQL - native), CREATE FUNCTION (compiled SQL scalar), ALTER FUNCTION (compiled SQL scalar) >>- ACCELERATOR--=--+-accelerator-name-+--------------->< . specifies an accelerator that when eligible, DB2 would consider sending eligible SQL to that server before other accelerator servers. If the accelerator server named by ACCELERATOR is not available, DB2 would consider other available accelerator servers. . RESTRICTIONS 1. This PTF is only supported on DB2 11 NFM 2. This PTF is only supported on IDAAV5 and up 3. DB2 will block flowing the special register value to the accelerator in the case where the special register is referenced in the query 4. The following restrictions are in place for the name of the accelerator, otherwise SQLCODE -713 will be issued on the set statement or an error will be returned on the BIND command. a. It must be unique within the DB2 subsystem or data sharing group. b. It must not consist of more than eight characters. c. Allowed characters are A-Z, 0-9. This APAR requires PI73563 to be applied together. Additional Keywords: IDAAV5R1/K IDAAV6R1/K SQLCODE4754 SQLCODE4742
Problem conclusion
Temporary fix
Comments
DB2 11 FOR Z/OS NEW FUNCTION ×**** PE17/08/10 FIX IN ERROR. SEE APAR PI85765 FOR DESCRIPTION
APAR Information
APAR number
PI72150
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
YesSpecatt / New Function / Xsystem
Submitted date
2016-11-10
Closed date
2017-02-10
Last modified date
2017-09-18
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI44585
Modules/Macros
DSNHAPLY DSNHPTAB DSNHSMP2 DSNHSM2V DSNHSM7 DSNHSYMH DSNHTBLS DSNLXENV DSNLXRCM DSNLXRCS DSNLXRSS DSNLXRWA DSNLXSR DSNLZOSR DSNLZSMT DSNTIJAS DSNXISB6 DSNX8API DSNX8CTG DSNX8WAC
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RB10 PSY UI44585
UP17/02/28 P F702
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":"11.0","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":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
18 September 2017