A fix is available
APAR status
Closed as new function.
Error description
Restrictions for index-controlled partitioned table spaces . KEYWORD: DB2MIGV11/K .
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: All DB2 10 and DB2 11 for z/OS users that * * use index-controlled partitioned table * * spaces. * **************************************************************** * PROBLEM DESCRIPTION: Users need an ability to prevent alter * * of the limit key for index-controlled * * partitioned table spaces. * * Users need an ability to prevent * * creating index-controlled * * partitioned table spaces. * **************************************************************** * RECOMMENDATION: * **************************************************************** In DB2 11 NFM, altering limit key values via an ALTER TABLE statement sets the status to reorg pending advisory (AREO*) for table-controlled partitioning, but sets the status (as in DB2 10) to reorg pending (REORP) for index-controlled partitioning. Users need an ability to prevent alter of the limit key for index-controlled partitioned table spaces, to avoid inadvertent outages. In DB2 10 and DB2 11, it is possible to create new tables using index-controlled partitioning. As it is recommended to use table-controlled partitioning, users need an ability to prevent creating index-controlled partitioned table spaces. A system parameter is added (to DB2 11) that will fail alter of the limit key value (using ALTER TABLE statement) if the table is using index-controlled partitioning. A second system parameter is added (to DB2 10 and DB2 11) to prevent the creation of new tables using index-controlled partitioning to position for removal of support for tables using index-controlled partitioning in the future.
Problem conclusion
Temporary fix
Comments
Two system parameters are added: The PREVENT_ALTERTB_LIMITKEY system parameter is used to disable altering of limit key values via an ALTER TABLE statement for index-controlled partitioned table spaces. The default value is NO, meaning that the default is the existing behavior. This system parameter takes effect in DB2 11 for z/OS NFM (and is ignored in DB2 11 for z/OS CM). The PREVENT_NEW_IXCTRL_PART system parameter is used to prevent creation of new index-controlled partitioned tables. The default value is NO, meaning that the default is the existing behavior. This system parameter takes effect in DB2 10 for z/OS NFM (and is ignored in DB2 10 for z/OS CM). The following actions are associated with this APAR: ***Action for PM89655 (DB2 10) This PTF adds a DB2 parameter, PREVENT_NEW_IXCTRL_PART, that can be used to specify whether DB2 will disallow creation of new index-controlled partitioned tables. Use of table-controlled partitioning is recommended. Valid settings are: * NO : Means that new index-controlled partitioned tables can be created. ==> This is the default setting. * YES: Means that new partitioned table spaces must use table-controlled partitioning. Attempts to create index-controlled partitioned tables will fail with SQLCODE -876 with reason '6'. If you have already installed or migrated to DB2 10, you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Update your customized copy of job DSNTIJUZ (3) Update private copies of the DSNTIDxx CLIST input member (4) Update the package for DSNTXAZP Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ==> This action is required for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Update your customized copy of job DSNTIJUZ ==> This action is required for all customers This PTF modifies DB2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: * Add the keyword parameter PREVENT_NEW_IXCTRL_PART=<x>, where <x> is NO or YES, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding PREVENT_NEW_IXCTRL_PART here, the value will be set to the default of NO when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (3) Update private copies of the DSNTIDxx CLIST input member ==> This action is required for all customers This PTF adds an entry for PREVENT_NEW_IXCTRL_PART to the CLIST default input member, DSNTIDXA, in the SDSNSAMP target library. You need to add this entry to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following line: PREVENT_NEW_IXCTRL_PART CHAR G NO YES <x> Change <x> to the value you specified for PREVENT_NEW_IXCTRL_PART in step (2), above. ---------------------------------------------------------------- (4) Update the package for DSNTXAZP ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members: Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where <prefix> is the high-level qualifier for the DB2 subsystem target libraries. ---------------------------------------------------------------- ***Action for PM89655 (DB2 11) This PTF adds two DB2 parameters, PREVENT_ALTERTB_LIMITKEY and PREVENT_NEW_IXCTRL_PART: - PREVENT_ALTERTB_LIMITKEY specifies whether DB2 will disallow altering of the limit key via an ALTER TABLE statement for index-controlled partitioned table spaces. Such altering causes the table space to be placed in reorganization pending (REORP) status. Valid settings are: * NO : Means that altering of the limit key via an ALTER TABLE statement for index-controlled partitioned table spaces is permitted. ==> This is the default setting. * YES: Means that altering of the limit key via an ALTER TABLE statement for index-controlled partitioned table spaces is not permitted. Attempts to do so will fail with SQLCODE -876 with reason '6'. - PREVENT_NEW_IXCTRL_PART specifies whether DB2 will disallow creation of new index-controlled partitioned tables. Use of table-controlled partitioning is recommended. Valid settings are: * NO : Means that new index-controlled partitioned tables can be created. ==> This is the default setting. * YES: Means that new partitioned table spaces must use table-controlled partitioning. Attempts to create index-controlled partitioned tables will fail with SQLCODE -876 with reason '6'. If you have already installed or migrated to DB2 11, you need to take the following actions after applying this PTF: (1) Update customized copies of DB2 installation CLIST members (2) Copy updated DB2 installation panels to alternate libraries (3) Update your customized copy of job DSNTIJUZ (4) Update private copies of the DSNTIDxx CLIST input member (5) Update the package for DSNTXAZP Detailed guidance for these actions follows: ---------------------------------------------------------------- (1) Update customized copies of DB2 installation CLIST members ==> This action is required for all customers This PTF modifies CLIST member DSNTINST in the SDSNCLST target library only. You need to redo any record format changes and reapply any tailoring you have done to your copies of this CLIST. You may also want to move it to the prefix.NEW.SDSNCLST data set, where the CLISTs processed by job DSNTIJVC reside. ---------------------------------------------------------------- (2) Copy updated DB2 installation panels to alternate libraries * This action is required for all DB2 11 customers who * maintain copies of the DB2 installation panels outside * of SMP/E This PTF modifies the DB2 installation panels DSNTIP71 in the prefix.SDSNSPFP target library. If you keep the DB2 installation panels in a different library then after applying this PTF, you need to copy the updated panel to that library. ---------------------------------------------------------------- (3) Update your customized copy of job DSNTIJUZ ==> This action is required for all customers This PTF modifies DB2 installation job DSNTIJUZ in the SDSNSAMP target library. After applying this PTF, you need to update your customized copy of this job as follows: * Add the keyword parameter PREVENT_ALTERTB_LIMITKEY=<x>, where <x> is NO or YES, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding PREVENT_ALTERTB_LIMITKEY here, the value will be set to the default of NO when you assemble the DSNZPxxx module. * Add the keyword parameter PREVENT_NEW_IXCTRL_PART=<y>, where <y> is NO or YES, to the invocation of the DSN6SPRM macro. Make sure to add a continuation character in column 72 if needed. If you omit adding PREVENT_NEW_IXCTRL_PART here, the value will be set to the default of NO when you assemble the DSNZPxxx module. * Run the first two steps of the DSNTIJUZ job you modified. * After the job completes, you must either use the -SET SYSPARM command or stop and restart DB2 for the change to take effect. ---------------------------------------------------------------- (4) Update private copies of the DSNTIDxx CLIST input member ==> This action is required for all customers This PTF adds entries for PREVENT_ALTERTB_LIMITKEY and PREVENT_NEW_IXCTRL_PART to the CLIST default input member, DSNTIDXA, in the SDSNSAMP target library. You need to add these entries to all private copies of your CLIST output DSNTIDxx member. In each such copy, add the following lines: PREVENT_ALTERTB_LIMITKEY CHAR G NO YES <x> PREVENT_NEW_IXCTRL_PART CHAR G NO YES <y> Change <x> to the value you specified for PREVENT_ALTERTB_LIMITKEY in step (3), above. Change <y> to the value you specified for PREVENT_NEW_IXCTRL_PART in step (3), above. ---------------------------------------------------------------- (5) Update the package for DSNTXAZP ==> This action is required for all customers who use DSNTXAZP to update DB2 CLIST input (DSNTIDXX) members: Submit a job that contains the following BIND statement: BIND PACKAGE(DSNTXAZP) MEMBER(DSNTXAZP) - ACTION(ADD) ENCODING(EBCDIC) - LIBRARY('prefix.SDSNDBRM') where <prefix> is the high-level qualifier for the DB2 subsystem target libraries. ---------------------------------------------------------------- ADDITIONAL KEYWORDS: SQLCODE876
APAR Information
APAR number
PM89655
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-05-23
Closed date
2013-10-03
Last modified date
2013-11-04
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK98188 UK98189
Modules/Macros
DSN@XAZP DSNDQWPZ DSNDSPRM DSNTIDXA DSNTIDXB DSNTIJUZ DSNTINST DSNTIP71 DSNTXAZP DSNWZIFA DSNWZIFB DSNXIATB DSNXICIX DSN6SPRM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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:
04 November 2013