IBM Support

PM89655: RESTRICTIONS FOR INDEX-CONTROLLED PARTITIONED TABLE SPACES

A fix is available

Subscribe

You can track all active APARs for this component.

 

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

  • RA10 PSY UK98188

       UP13/10/18 P F310

  • RB10 PSY UK98189

       UP13/10/18 P F310

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