PM45829: NEW FUNCTION APAR FOR CONVERTING FROM INDEX BASED PARTITIONING TO TABLE BASED

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • New function apar for converting from index based partitioning
    to table based...
    IDAAV2R1/K
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: (1) Users of DB2 9 and 10 for z/OS using     *
    *                 index-controlled partitioned table space     *
    *                 (2) Users of DB2 9 for z/OS who set the      *
    *                 DSN6SPRM.INLIST_PTC parameter to YES         *
    ****************************************************************
    * PROBLEM DESCRIPTION: (1) Converting from index-controlled to *
    *                      table-controlled partitioning results   *
    *                      in all columns of the partitioning      *
    *                      index to be used for partitioning,      *
    *                      rather than using only column(s)        *
    *                      with a limit key value specified.       *
    *                      (2) DB2 V9 ignores INLIST_PTC=YES and   *
    *                      always behaves as if INLIST_PTC=NO.     *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    (1) DB2 automatic conversion of index-controlled partitioned
    table space to table-controlled partitioned table space
    uses all columns of the partitioning index for partitioning,
    instead of using only the columns which the limit key values
    were specified for.
    
    Consider the following scenario:
      CREATE DATABASE EMPDB;
    
      CREATE TABLESPACE EMPTS
      IN EMPDB
      NUMPARTS 4;
    
      CREATE TABLE EMPTAB
      (FIRST          CHAR(5) NOT NULL,
       MIDDLE         CHAR(5) NOT NULL,
       LAST           CHAR(5) NOT NULL,
       ID             CHAR(5) NOT NULL)
      IN EMPDB.EMPTS;
    
      CREATE INDEX EMPPARTIX ON EMPTAB (LAST,FIRST,MIDDLE)
      CLUSTER    (PARTITION 1 ENDING AT ('BAKER'),
                  PARTITION 2 ENDING AT ('DOUGL') ,
                  PARTITION 3 ENDING AT ('HUGHS'),
                  PARTITION 4 ENDING AT ('JAMES'));
    
      ALTER TABLE EMPTAB ADD PARTITION ENDING AT ('SMITH');
       When adding a new partition, the conversion to table-
       controlled partitioned table space results in an internal
       limit key in SYSTABLEPART.LIMITKEY_INTERNAL which includes
       all the columns in the partitioning index.
    
       'BAKER',x'FFFFFFFFFF',x'FFFFFFFFFF'
       'DOUGL',x'FFFFFFFFFF',x'FFFFFFFFFF'
       'HUGHS',x'FFFFFFFFFF',x'FFFFFFFFFF'
       'JAMES',x'FFFFFFFFFF',x'FFFFFFFFFF'
       'SMITH',x'FFFFFFFFFF',x'FFFFFFFFFF'
    
      CREATE INDEX EMPIX02 ON EMPTAB(LAST,ID) PARTITIONING;
       EMPIX02 WILL BE CONSIDERED A DATA PARTITIONING
       secondary index (DPSI) instead of a partitioning index since
       the limit key is now defined on LAST, FIRST, and MIDDLE.
    
    (2) DB2 V9 ignores use of DSN6SPRM.INLIST_PTC=YES and always
    behaves as if INLIST_PTC=NO.  See the DB2 APAR PM35321 for
    information about this parameter.
    

Problem conclusion

Temporary fix

Comments

  • (1) This APAR introduces an online-changeable subsystem meter
    parameter in DSN6SPRM called IX_TB_PART_CONV_EXCLUDE. Valid
    settings are YES and NO. The default is NO, meaning that all
    columns  are used. When set to YES, DB2 will use only nt
    significant columns of the partitioning index that is on an
    index-controlled partitioned table space to define the
    table-controlled partitioning key. DB2 will exclude all
    trailing columns with all 'FF'x in SYSINDEXPART.LIMITKEY.
    There will be no distinction between explicitly specified
    'FF'x, MAXVALUE, or MINVALUE versus an omitted limit key
    value.
    
    The following will be affected:
     o CREATE INDEX statement with the PARTITIONED clause to
       create a secondary partitioned index on an index-controlled
       partitioned table space.
     o ALTER INDEX statement with the NOT CLUSTER clause on a
       partitioning index that is on an index-controlled
       partitioned table space.
     o DROP INDEX statement to drop a partitioning index on an
       index-controlled partitioned table space.
     o ALTER TABLE statement to add a new partition, change a
       partition boundary, or rotate a partition to last on an
       index-controlled partitioned table space. Trailing
       'FF'x, MAXVALUE,or MINVALUE specified in the limit
       key value of the statement will always be significant.
     o ALTER TABLE statement to add a clone table.
    
    IX_TB_PART_CONV_EXCLUDE does not apply to a CREATE INDEX
    statement with a PART VALUES clause and without a CLUSTER
    clause to create a partitioning index even though this causes
    automatic conversion to table-controlled partitioning.
    
    (2) This APAR also corrects DB2 V9 to again recognize use of
    INLIST_PTC=YES.TER SQLDROP SQLCLONE IDAAV2R1/K
    
    ADDITIONAL KEYWORDS:
    SQLCREATE SQLALTER SQLDROP SQLCLONE INLIST_PTC
    
    ++HOLD COMMENT FOR RELEASE A10, TYPE DOC:
    PM45829 introduces an online-changeable subsystem parameter
    in DSN6SPRM called IX_TB_PART_CONV_EXCLUDE. Valid settings
    are YES and NO. The default is NO, meaning that all columns
    are used. When set to YES, DB2 will use only significant
    columns of the partitioning index that is on an
    index-controlled partitioned table space to define the
    table-controlled partitioning key. DB2 will exclude all
    trailing columns with all 'FF'x in SYSINDEXPART.LIMITKEY.
    There will be no distinction between explicitly specified
    'FF'x, MAXVALUE, or MINVALUE versus an omitted limit key
    value.
    
    The approximate text is to be added to the DB2 manuals. This
    text is subject to change.
    
    =============================
    Changes to DB2 Administration
    =============================
     Automatic conversion to table-controlled partitioning
     -----------------------------------------------------
      When subsystem parameter IX_TB_PART_CONV_EXCLUDE is set NO
      (default), all columns of the partitioning index that is on
      an index-controlled partitioned table space will be used to
      define the table-controlled partitioning key. When set to YES,
      DB2 will use only significant columns of the partitioning
      index. DB2 will exclude all trailing columns with all 'FF'x
      in SYSINDEXPART.LIMITKEY. There will be no distinction between
      explicitly specified 'FF'x, MAXVALUE, or MINVALUE versus an
      omitted limit key value.
    
      For ALTER TABLE statement to add a new partition, change a
      partition boundary, or rotate a partition to last on an
      index-controlled partitioned table space, the trailing
      'FF'x, MAXVALUE, or MINVALUE specified in the limit key value
      of the statement will always be significant.
    
      IX_TB_PART_CONV_EXCLUDE does not apply to a CREATE INDEX
      statement with a PART VALUES clause and without a CLUSTER
      clause to create a partitioning index.
    ===========================
    Changes to DB2 Installation
    ===========================
      PM45829 introduces an online-changeable subsystem parameter
      in DSN6SPRM called IX_TB_PART_CONV_EXCLUDE that can be used
      to indicate whether DB2 will use only significant columns of
      the index-controlled partitioning partitioning index to
      define the table-controlled partitioning key. Valid settings
      are YES and NO. The default is NO, meaning that all columns
      are used. IX_TB_PART_CONV_EXCLUDE is online changeable. In
      data sharing, it has member scope.
    
    ++HOLD COMMENT FOR RELEASE 910, TYPE DOC:
    PM45829 introduces an online-changeable subsystem parameter
    in DSN6SPRM called IX_TB_PART_CONV_EXCLUDE. Valid settings
    are YES and NO. The default is NO, meaning that all columns
    are used. When set to YES, DB2 will use only significant
    columns of the partitioning index that is on an
    index-controlled partitioned table space to define the
    table-controlled partitioning key. DB2 will exclude all
    trailing columns with all 'FF'x in SYSINDEXPART.LIMITKEY.
    There will be no distinction between explicitly specified
    'FF'x, MAXVALUE, or MINVALUE versus an omitted limit key
    value.
    The approximate text is to be added to the DB2 manuals. This
    text is subject to change.
    
    =============================
    Changes to DB2 Administration
    =============================
     Automatic conversion to table-controlled partitioning
     -----------------------------------------------------
      When subsystem parameter IX_TB_PART_CONV_EXCLUDE is set NO
      (default), all columns of the partitioning index that is on
      an index-controlled partitioned table space will be used to
      define the table-controlled partitioning key. When set to YES,
      DB2 will use only significant columns of the partitioning
      index. DB2 will exclude all trailing columns with all 'FF'x
      in SYSINDEXPART.LIMITKEY. There will be no distinction between
      explicitly specified 'FF'x, MAXVALUE, or MINVALUE versus an
      omitted limit key value.
    
      For ALTER TABLE statement to add a new partition, change a
      partition boundary, or rotate a partition to last on an
      index-controlled partitioned table space, the trailing
      'FF'x, MAXVALUE, or MINVALUE specified in the limit key value
      of the statement will always be significant.
    
      IX_TB_PART_CONV_EXCLUDE does not apply to a CREATE INDEX
      statement with a PART VALUES clause and without a CLUSTER
      clause to create a partitioning index.
    
    ===========================
    Changes to DB2 Installation
    ===========================
      PM45829 introduces an online-changeable subsystem parameter
      in DSN6SPRM called IX_TB_PART_CONV_EXCLUDE that can be used
      to indicate whether DB2 will use only significant columns of
      the index-controlled partitioning partitioning index to
      define the table-controlled partitioning key. Valid settings
      are YES and NO. The default is NO, meaning that all columns
      are used. IX_TB_PART_CONV_EXCLUDE is online changeable. In
      data sharing, it has member scope.
    
    ++HOLD COMMENT FOR RELEASE A10, TYPE ACT:
    PM45829 introduces an online-changeable subsystem parameter in
    DSN6SPRM called IX_TB_PART_CONV_EXCLUDE that can be used to
    indicate whether DB2 will use only significant columns of the
    index-controlled partitioning partitioning index to define the
    table-controlled partitioning key. Valid settings are YES and
    NO. The default is NO, meaning that all elements are used.
    
    If you have already installed or migrated to this version of DB2
    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
    
    Detailed guidance for these actions follows:
    
    ----------------------------------------------------------------
    (1) Update customized copies of DB2 installation CLIST members
    ----------------------------------------------------------------
    ==> This action is recommended 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 IX_TB_PART_CONV_EXCLUDE=<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
          IX_TB_PART_CONV_EXCLUDE 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 IX_TB_PART_CONV_EXCLUDE
         to the CLIST default input members 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 line:
    
    IX_TB_PART_CONV_EXCLUDE  CHAR  M  NO          YES           <x>
    
         Change <x> to the value you specified for
         IX_TB_PART_CONV_EXCLUDE in step (2), above.
    
         If you do not add IX_TB_PART_CONV_EXCLUDE to a DSNTIDxx
         member, the DB2 installation CLIST will assume
         IX_TB_PART_CONV_EXCLUDE=NO when run with that member as
         input.
    
    ++HOLD COMMENT FOR RELEASE 910, TYPE ACT:
    PM45829 introduces an online-changeable subsystem parameter in
    DSN6SPRM called IX_TB_PART_CONV_EXCLUDE that can be used to
    indicate whether DB2 will use only significant columns of the
    index-controlled partitioning partitioning index to define the
    table-controlled partitioning key. Valid settings are YES and
    NO. The default is NO, meaning that all elements are used.
    
    If you have already installed or migrated to this version of DB2
    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
    
    Detailed guidance for these actions follows:
    
    ----------------------------------------------------------------
    (1) Update customized copies of DB2 installation CLIST members
    ----------------------------------------------------------------
    ==> This action is recommended 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 IX_TB_PART_CONV_EXCLUDE=<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
          IX_TB_PART_CONV_EXCLUDE 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 IX_TB_PART_CONV_EXCLUDE
         to the CLIST default input members 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 line:
    
    IX_TB_PART_CONV_EXCLUDE  CHAR  M  NO          YES           <x>
    
         Change <x> to the value you specified for
         IX_TB_PART_CONV_EXCLUDE in step (2), above.
    
         If you do not add IX_TB_PART_CONV_EXCLUDE to a DSNTIDxx
         member, the DB2 installation CLIST will assume
         IX_TB_PART_CONV_EXCLUDE=NO when run with that member as
         input.
    

APAR Information

  • APAR number

    PM45829

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    YesHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2011-08-16

  • Closed date

    2011-10-24

  • Last modified date

    2012-08-13

  • APAR is sysrouted FROM one or more of the following:

  • APAR is sysrouted TO one or more of the following:

    UK73183 UK73184

Modules/Macros

  •    DSN@XAZP DSNDQWPZ DSNDSPRM DSNGDART DSNTIDXA
    DSNTIDXB DSNTIJUZ DSNTINST DSNTXAZP DSNWZIFA DSNWZIF9 DSNXIAB6
    DSNXIAB7 DSNXISB6 DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK73183

       UP11/11/09 P F111

  • R910 PSY UK73184

       UP11/11/09 P F111

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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM45829

Modified date:

2012-08-13

Translate my page

Machine Translation

Content navigation