A fix is available
APAR status
Closed as new function.
Error description
New Function
Local fix
N/A
Problem summary
**************************************************************** * USERS AFFECTED: * * All Db2 12 for z/OS users. * * This includes all Db2 12 users of Db2 QUERY * * ACCELERATION and the IBM Db2 Analytics * * Accelerator. * **************************************************************** * PROBLEM DESCRIPTION: * * Introducing Db2 12 function level 509. * **************************************************************** * RECOMMENDATION: * **************************************************************** Enables activation of Db2 12 for z/OS function level 509. For detailed descriptions of the new Db2 12 capabilities in function level 509, and specific activation details, see "Function level 509" in IBM Knowledge Center: http://ibm.biz/db2z12functionlevels For an introduction to Db2 12 continuous delivery concepts and instructions for adopting new capabilities in Db2 12, see: http://ibm.biz/db2zadoptnewfunction . . =============================================================== *************************************************************** =============================================================== (1) If you have not run DSNTIJRT since applying previous APAR/PTF PH30574/UI73236, then do so now according to the instructions in this APAR PH33015 PTF's ACTION ++HOLD. DSNTIJRT is a Db2 job that installs and configures Db2-supplied routines. (2) If you do not use Db2 QUERY ACCELERATION and the IBM Db2 Analytics Accelerator then the remainder of this APAR text is not applicable for your Db2 12 for z/OS subsystem. . . Included in Db2 12 function level 509 is Db2 Installation support for Db2 QUERY ACCELERATION new function, high availability accelerator-only tables (HA AOTs) for the IBM Db2 Analytics Accelerator V7 -- an enhancement to the existing Db2 accelerator-only tables ( AOTs ) feature. . This APAR adds a new column, FEATURE, to existing Db2 table SYSACCEL.SYSACCELERATEDTABLES, a table in the Db2 Accelerator database DSNACCEL. Database DSNACCEL and the objects in that database are used by Db2 and the IBM Db2 Analytics Accelerator (of either type) when accelerating queries to an accelerator. The row for a user table is created in table SYSACCEL.SYSACCELERATEDTABLES when either a Db2 user table is accelerated to or an accelerator-only table ( AOT ) is created in the IBM Db2 Analytics Accelerator. . This Db2 QUERY ACCELERATION enhancement provided in this APAR and in previous APAR PH30574 PTF UI73236 using this new column FEATURE, is only for IBM Db2 Analytics Accelerator V7. . Db2 table SYSACCEL.SYSACCELERATEDTABLES new column FEATURE is an INTEGER column containing a numeric value that indicates additional features of the table in an accelerator: 0 = The table does not have any of the additional features that are indicated by the other values. 0 is the default value. 1 = The table is a high availability accelerator-only table. . The default value for new column FEATURE is 0. If a high availability accelerator-only table is defined, the value is 1. . The FEATURE column must be defined in SYSACCELERATEDTABLES in order to create high availability accelerator-only tables (HA AOTs) for Db2 and IBM Db2 Analytics Accelerator V7. However, *not* adding this new column does not cause a problem with Db2 QUERY ACCELERATION or creating regular AOTs -- you just will not be able to create high availability AOTs. . This APAR/PTF modifies the existing CREATE TABLE statement for Db2 table "SYSACCEL"."SYSACCELERATERATEDTABLES" in the Db2-supplied sample job DSNTIJAS, by adding a line for column FEATURE after the existing line for column REMOTELOCATION : , "FEATURE" INTEGER NOT NULL WITH DEFAULT . FEATURE is now the fourteenth and new last column of table SYSACCEL.SYSACCELERATEDTABLES. . As confirmation, the following should be the new CREATE TABLE SYSACCEL.SYSACCELERATEDTABLES statement after following the directions above: CREATE TABLE "SYSACCEL"."SYSACCELERATEDTABLES" ( "NAME" VARCHAR(128) NOT NULL , "CREATOR" VARCHAR(128) NOT NULL , "ACCELERATORNAME" VARCHAR(128) NOT NULL , "REMOTENAME" VARCHAR(128) NOT NULL , "REMOTECREATOR" VARCHAR(128) NOT NULL , "ENABLE" CHAR(1) NOT NULL , "CREATEDBY" VARCHAR(128) NOT NULL , "CREATEDTS" TIMESTAMP NOT NULL WITH DEFAULT , "ALTEREDTS" TIMESTAMP NOT NULL WITH DEFAULT , "REFRESH_TIME" TIMESTAMP NOT NULL WITH DEFAULT , "SUPPORTLEVEL" SMALLINT NOT NULL WITH DEFAULT , "ARCHIVE" CHAR(1) NOT NULL WITH DEFAULT , "REMOTELOCATION" VARCHAR(128) NOT NULL WITH DEFAULT , "FEATURE" INTEGER NOT NULL WITH DEFAULT ) IN DSNACCEL.SYSTSACT CCSID UNICODE; . *************************************************************** Failure to add them in the correct order as described above will result in the SYSACCELERATEDTABLES table becoming unusable by Db2, and Db2 will issue an error message that the table is not defined correctly. *************************************************************** . If you maintain a customized job DSNTIJAS, please see ++HOLD of this APAR PH33015 PTF for important information about modifying your customized copy of job DSNTIJAS. . . If you are already using QUERY ACCELERATION and have previously created the Db2 table SYSACCEL.SYSACCELERATEDTABLES, after applying this APAR/PTF, process the following ALTER SQL statement using SPUFI or similar tool to update Db2 table SYSACCEL.SYSACCELERATEDTABLES with the new column FEATURE : ALTER TABLE "SYSACCEL"."SYSACCELERATEDTABLES" ADD COLUMN FEATURE INT NOT NULL WITH DEFAULT; . *************************************************************** ** However, BEFORE issuing the above ALTER TABLE ADD COLUMN, ** ** you *must* review this APAR PH33015 ++HOLD information and** ** instructions to confirm that your Db2 DSNACCEL table ** ** spaces have been migrated to UTS and that Db2 table ** ** SYSACCEL.SYSACCELERATEDTABLES has the thirteenth and ** ** previous last column REMOTELOCATION . ** *************************************************************** . After running the above ALTER TABLE ADD COLUMN, FEATURE should now be the fourteenth and new last column of table SYSACCEL.SYSACCELERATEDTABLES, and this table should have the following columns in the order shown here: "NAME" VARCHAR(128) NOT NULL "CREATOR" VARCHAR(128) NOT NULL "ACCELERATORNAME" VARCHAR(128) NOT NULL "REMOTENAME" VARCHAR(128) NOT NULL "REMOTECREATOR" VARCHAR(128) NOT NULL "ENABLE" CHAR(1) NOT NULL "CREATEDBY" VARCHAR(128) NOT NULL "CREATEDTS" TIMESTAMP NOT NULL WITH DEFAULT "ALTEREDTS" TIMESTAMP NOT NULL WITH DEFAULT "REFRESH_TIME" TIMESTAMP NOT NULL WITH DEFAULT "SUPPORTLEVEL" SMALLINT NOT NULL WITH DEFAULT "ARCHIVE" CHAR(1) NOT NULL WITH DEFAULT "REMOTELOCATION" VARCHAR(128) NOT NULL WITH DEFAULT "FEATURE" INTEGER NOT NULL WITH DEFAULT . *************************************************************** Again, the proper order of these columns is VERY important. Failure to add them in the correct order as shown will result in the SYSACCELERATEDTABLES table becoming unusable by Db2. *************************************************************** . This ALTER operation may place the DSNACCEL.SYSTSACT table space, where the SYSACCEL.SYSACCELERATEDTABLES table resides, in AREO* state (i.e., 'Advisory' REORG-pending state). Perform REORG utility on the DSNACCEL.SYSTSACT table space to remove it from REORG-pending state for optimal performance. . (If ALTER TABLE ADD COLUMN for SYSACCELERATEDTABLES fails with SQLCODE -904 RC00C900E3 for table space DSNDB06.SYSALTER, then you must perform REORG utility on the DSNDB06.SYSALTER table space and afterwards retry the ALTER TABLE ADD COLUMN for SYSACCELERATEDTABLES. This SQLCODE904 RC00C900E3 for tablespace SYSALTER is not due to any code in this APAR/PTF, but can occur on Db2 V12 for an ALTER TABLE of a 'user-created' table if the table space SYSALTER was created or cloned as a result of a DSN1COPY performed at some previous point in time from a pre-V11 Db2 subsystem. Please see APAR PI55114 for additional information about this specific SQLCODE904.) . *************************************************************** After applying this PTF and issuing the above ALTER ADD COLUMN FEATURE, STOP (if applicable) then re-START or START at least one accelerator in order for Db2 to retrieve the information about the newly added column FEATURE and validate the new column. Failure to do so after the ALTER ADD COLUMN is run successfully will prevent you from creating a high availability AOT (HA AOT), resulting in SQLCODE -4742 with reason 26 on the CREATE TABLE. *************************************************************** . Note that if you have applications that SELECT from the Db2 table SYSACCEL.SYSACCELERATEDTABLES, after adding new column FEATURE such applications may be affected. Please examine those applications to determine if they are affected by the addition of the new column. =============================================================== .
Problem conclusion
Temporary fix
Comments
The code in this APAR allows activation and use of Db2 12 function level 509. . For users of Db2 QUERY ACCELERATION and the IBM Db2 Analytics Accelerator, please refer to this APAR PH33015 PTF's ++HOLD for additional information about and before adding new column FEATURE in Db2 table SYSACCEL.SYSACCELERATEDTABLES used for IBM Db2 Analytics Accelerator V7. . If you have not run DSNTIJRT since applying previous APAR/PTF PH30574/UI73236, then do so now according to the instructions in this APAR PH33015 PTF's ACTION ++HOLD. . Additional keywords: DB2MIGV12/K SQLQUERY SQLCODE4742 RSN26 IDAAV7R1/K IDAAV7R5/K
APAR Information
APAR number
PH33015
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
C10
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2020-12-30
Closed date
2021-02-05
Last modified date
2021-03-05
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UI73836
Modules/Macros
DSNXFNLV DSNUECM2 DSNGEDLC DSNX9RIB DSNUECM0 DSNUECMI DSNTIJAS DSNUEXUP DSNXLDBD DSNARIB DSNRNTFY DSNRRPRC DSN7GCMD
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RC10 PSY UI73836
UP21/02/19 P F102
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.
[{"Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSEPEK","label":"Db2 for z\/OS"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"12.0"}]
Document Information
Modified date:
06 March 2021