A fix is available
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 number
PI76584
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
B10
Status
CLOSED PER
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2017-02-15
Closed date
2017-07-25
Last modified date
2017-09-02
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.
[{"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":"11.0","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":"11.0","Edition":"","Line of Business":{"code":"","label":""}}]
Document Information
Modified date:
02 September 2017