PI76584: BUFFER POOL SPECIFIED IN TBSBPOOL ZPARM IS NOT USED FOR A TABLE SPACE CREATED IMPLICITLY IN AN EXPLICITLY CREATED DATABASE

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• buffer pool to be inherited from the database first if it can
  fit the record size, and if not, then we go to the ZPARM fields.
  

Local fix

• ALTER TABLESPACE BUFFERPOOL, or for new tables, on the CREATE
  TABLE statement it is possible to explicitly specify BUFFERPOOL
  

Problem summary

• ****************************************************************
  * USERS AFFECTED: All DB2 11 for z/OS users of CREATE TABLE    *
  *                 with IN DATABASE clause.                     *
  ****************************************************************
  * PROBLEM DESCRIPTION: A CREATE TABLE statement with the IN    *
  *                      DATABASE clause, but without a          *
  *                      BUFFERPOOL specification, incorrectly   *
  *                      defaults to using the subsystem         *
  *                      parameter buffer pool names instead of  *
  *                      inheriting the buffer pool from the     *
  *                      specified database.                     *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  A CREATE TABLE statement with the IN DATABASE clause, but
  without a BUFFERPOOL specification, incorrectly defaults to
  using the subsystem parameter buffer pool names (TBSBPOOL,
  TBSBP8K, TBSBP16K, TBSBP32K) for the implicitly created table
  space (SYSTABLESPACE BPOOL) instead of inheriting the buffer
  pool from the specified database when the table's record size
  can fit in the database's buffer pool.
  
  For example, consider the following scenario:
  
  1. Subsystem parameter TBSBPOOL is BP0
  2. Database DB1 is created using BUFFERPOOL BP23
     CREATE DATABASE DB1
     BUFFERPOOL BP23
  3. Table TB1 is created in database DB1
     CREATE TABLE TB1 ( ... )
     IN DATABASE DB1
  4. The implicitly created table space incorrectly uses buffer
     pool BP0 instead of BP23 (SYSTABLESPACE.BPOOL = 'BP0')
  

Problem conclusion

• DB2 has been modified such that CREATE TABLE with IN DATABASE
  clause but without BUFFERPOOL specification will use the
  database's buffer pool for the implicitly created table space if
  the database buffer pool's page size is appropriate for the
  table.  If the page size of the database's buffer pool is not
  appropriate for the table (i.e.  table record size does not fit,
  or table uses hash organization and page size is not optimal),
  then DB2 will use an appropriate buffer pool from the subsystem
  parameters TBSBPOOL, TBSBP8K, TBSBP16K, or TBSBP32K.
  
  In addition, documentation in the following DB2 Publications
  sections will be modified to clarify the expected behavior.
  Note that the exact text in the Information Center is subject
  to change without notice.
  
  1. Implicitly defined table spaces topic
     The topic for implicitly defined table spaces will be
     modified to as follows:
  
     DB2  implicitly creates a partition-by-growth or range
     partitioned universal table space when you issue a CREATE
     TABLE statement without specifying an existing table space
     name.
  
     When DB2 defines a table space implicitly, it completes the
     following actions:
     * Generates a table space for you.
     ...
     * Uses default values for space allocation.
  |  * Uses the buffer pool for the specified database. However,
  |    DB2 chooses a suitable buffer pool for the table space
  |    from the subsystem parameter values TBSBPOOL, TBSBP8K,
  |    TBSBP16K, and TBSBP32K if any of the following conditions
  |    apply:
  |    * The IN DATABASE clause is not specified.
  |    * The IN DATABASE clause is specified, and the table
  |      record length does not fit in the database buffer pool
  |      page size.
  |    * The IN DATABASE clause is specified, and the table space
  |      uses hash organization and has a calculated optimal page
  |      size that is not the same as the database buffer pool
  |      page size.
  
  2. CREATE DATABASE ... BUFFERPOOL description
     Part of the description will be modified as follows:
  |  If you omit the BUFFERPOOL clause, the buffer pool for
  |  the TBSBPOOL subsystem parameter value is used. If the
  |  table space is implicitly defined, DB2 selects the buffer
  |  pool as described in Implicitly defined table spaces.
                          _______________________________
  
  3. ALTER DATABASE ... BUFFERPOOL description
     A new sentence will be added to the description as follows:
  |  If the table space is implicitly defined, DB2 selects the
  |  buffer pool as described in Implicitly defined table spaces.
                                 _______________________________
  
  4. TBSBPOOL, TBSBP8K, TBSBP16K, TBSBP32K subsystem parameters:
     The descriptions will be modified to state that for CREATE
     TABLE, if you do not specify a table space name, DB2
     implicitly creates a table space and selects the buffer pool
     as described in Implicitly defined table spaces.
                     _______________________________
  
  5. CREATE TABLE ... BUFFERPOOL description
     The description will be modified to state that for implicitly
     created table spaces, DB2 selects the buffer pool as
     described in Implicitly defined table spaces.
                  _______________________________
  
  
  The corresponding APAR on DB2 12 is PI80406.
  
  Additional Keywords:
  SQLCREATE
  
  HOLD Text for PI76584
  ---------------------
  APAR PI76584 fixes DB2 behavior for a CREATE TABLE statement
  with IN DATABASE clause but without BUFFERPOOL specification.
  Prior to PI76584, for the implicitly created table space's
  buffer pool, DB2 chose a buffer pool from one of the subsystem
  parameters (TBSBPOOL, TBSBP8K, TBSBP16K, TBSBP32K) based on the
  calculated optimal page size for the table instead of
  inheriting the buffer pool from the table space's database.
  
  DB2 has been modified such that CREATE TABLE with IN DATABASE
  clause but without BUFFERPOOL specification will use the
  database's buffer pool for the implicitly created table space if
  the database buffer pool's page size is appropriate for the
  table.  If the page size of the database's buffer pool is not
  appropriate for the table (i.e.  table record size does not fit,
  or table uses hash organization and page size is not optimal),
  then DB2 will default to using an appropriate buffer pool.
  
  The APAR only affects tables created after the APAR is applied
  and does not affect existing tables.  To get a sense of which
  explicitly created databases have implicitly created table
  spaces with different buffer pools, the following query can be
  used.  Please note that this is a sample query provided for
  reference only and may not be comprehensive of all types of
  customer environments.
  
  This query attempts to identify *possible* implicitly created
  table spaces in an explicitly created database where the table
  space page size <= database buffer pool page size, and the
  table space buffer pool is not the same as the database buffer
  pool. For hash tables, it's where the table space page size is
  not the same as the database buffer pool page size.  This query
  over-identifies because we are not able to filter out the cases
  where user has:
  1. Executed CREATE TABLE w/explicit BUFFERPOOL specification
  2. Executed ALTER TABLESPACE BUFFERPOOL to change buffer pool
  
  For databases identified by the following query where it's not
  desired that implicitly created table spaces use the database's
  buffer pool, one of the following can be performed:
  1. On the CREATE TABLE statement, use the BUFFERPOOL clause to
     specify the desired buffer pool.
  2. Use ALTER DATABASE BUFFERPOOL to change the database's
     buffer pool to the desired buffer pool.
  
  SELECT     TAB.CREATOR          AS TBCREATOR,
             TAB.NAME             AS TBNAME,
             TAB.TYPE             AS TBTYPE,
             TAB.HASHKEYCOLUMNS,
             TAB.RECLENGTH,
             DBA.NAME             AS DBNAME,
             DBA.IMPLICIT         AS DBIMPLICIT,
             DBA.BPOOL            AS DBBPOOL,
             DBA.PGSIZE           AS DBPGSIZE,
             TSP.NAME             AS TSNAME,
             TSP.TYPE             AS TSTYPE,
             TSP.IMPLICIT         AS TSIMPLICIT,
             TSP.PGSIZE           AS TSPGSIZE,
             TSP.BPOOL            AS TSBPOOL,
             TSP.CREATEDTS        AS TSCREATEDTS,
             TSP.ALTEREDTS        AS TSALTEREDTS
  FROM       SYSIBM.SYSTABLES     AS TAB,
             SYSIBM.SYSTABLESPACE AS TSP,
             ( SELECT   DBA_TEMP.* ,
                        CASE
                          WHEN BPOOL =    ' '      THEN  0
                          WHEN BPOOL LIKE 'BP32K%' THEN 32
                          WHEN BPOOL LIKE 'BP16K%' THEN 16
                          WHEN BPOOL LIKE 'BP8K%'  THEN  8
                          ELSE                           4
                        END AS PGSIZE
               FROM     SYSIBM.SYSDATABASE AS DBA_TEMP
             ) DBA
  WHERE      TAB.DBNAME     = TSP.DBNAME
  AND        TAB.TSNAME     = TSP.NAME
  AND        DBA.NAME       = TSP.DBNAME
  AND        TSP.TYPE  NOT IN ( 'O','P')   -- Exclude LOB, XML TS
  AND        TSP.IMPLICIT   = 'Y'          -- Implicit TS
  AND        DBA.IMPLICIT   = 'N'          -- Explicit DB
  AND        DBA.BPOOL     <> ' '          -- Exclude DB w/no BP
  AND        (   TSP.BPOOL <> DBA.BPOOL
             AND (  (   TAB.HASHKEYCOLUMNS = 0     -- TB w/o hash
                    AND TSP.PGSIZE        <= DBA.PGSIZE )
                 OR (   TAB.HASHKEYCOLUMNS <> 0    -- TB w/hash
                    AND TSP.PGSIZE         = DBA.PGSIZE )
                 )
             )
  ORDER BY  TAB.CREATOR, TAB.NAME
  ;
  

Temporary fix

Comments

APAR Information

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

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

  PI80406 UI49145

Modules/Macros

• DSNXICTB
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI49145

     UP17/08/24 P F708

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.