APAR status
INTRAN
Error description
In DB2 V9 the Declare Global Temporary Tables (DGTT) were moved from their own database to the workfile database. This allowed the DGTT's to share the same table spaces as used in Sort. DB2 V9 will favor using 32K workfile table space as opposed to using 4K table spaces in DB2 V8. For more on "How sort work files are allocated" check DB2 for z/OS Managing Performance & the below links : DB2 V10 - http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2 /topic/com.ibm.db2z10.doc.perf/src/tpc/db2z_sortfilesallocated.h tm DB2 V9 - http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2 /topic/com.ibm.db2z9.doc.perf/src/tpc/db2z_sortfilesallocated.ht m So it is suggested that the customer increase the number of 32k workfile table spaces for DB2 V9 an also increase the size of the 32K bufferpool. The reason DB2 V9 favors 32K workfile table spaces is that such preference results in better performance, improved prefetch, and fewer get-pages. A rule of thumb is that 80% of workfile table spaces be defined as 32K. For more on "Creating additional work file table spaces to reduce contention" check DB2 for z/OS Managing Performance & the below links : DB2 V10 - http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2 /topic/com.ibm.db2z10.doc.perf/src/tpc/db2z_createadditionalwork filets.htm DB2 V9 - http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2 /topic/com.ibm.db2z9.doc.perf/src/tpc/db2z_createadditionalworkf ilets.htm IFCID 002 fields QISTWFP1/QISTWFP2 can be used to monitor whether 32K/4K table spaces were available to DB2 every time it deemed them best fit. Non-zero values indicate a need to further adjust the mix of 32K/4K table spaces. The following APARS should be applied for V9 workfile processing To correct problems with the MAXTEMPS option: PK73474/UK42450 PK84127/UK48884 PM05871/UK56135 Online ZPARM MAXTEMPS controls how much space in workfile database an agent can use (or a family of parallel tasks in case of query CP parallelism). The usage can be for sort workfiles, created global temporary tables (CGTT), declared global temporary tables (DGTT), scrollable cursors result tables, trigger transition tables, etc. The zparm is not granulated to any one type of these uses, instead it tracks the overall use by the agent. APARS that workfile table space usage is effected by: PK70347/UK39199 PK92163/UK50740 PK70060/UK46839 To target a workfile table space for utilization by non-DGTT (sort workfiles, created global temporary tables, trigger transition tables etc) or use by DGTT (external DGTTs or tables built internally by DB2 for static scrollable cursors), APAR PK70060/UK46839 can be used. With the APAR, DB2 will favor DB2-managed (STOGROUP) workfile table spaces with SECQTY 0 or user-managed table spaces (regardless of their secondary allocation) for non-DGTT work and DB2-managed table spaces with SECQTY > 0 or -1 (or omitted) for DGTT work. Therefore, user-managed table spaces are treated the same as DB2-managed table spaces with SECQTY 0. Please note that if a favored table space does not have room or is unavailable for any reason, DB2 will still try to use a non-favored table space, which could lead to sharing of space between non-DGTT and DGTT applications. See APAR closing text for details. PM02528/UK56046 To go even further than the soft separation introduced in PK70060/UK46839 and cause workfile table spaces to be used solely for non-DGTT and solely for DGTT work, APAR PM02528/UK56046 can be applied and new zparm WFDBSEP used (default NO). See APAR closing text for details. For more on ZPARM WFDBSEP see the below publications & links : V10 DB2 for z/OS Installation and Migration Guide "SEPARATE WORK FILES field (WFDBSEP subsystem parameter)" http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com .ibm.db2z10.doc.inst/src/tpc/db2z_ipf_wfdbsep.htm V9 DB2 for z/OS Installation Guide "Subsystem parameters that are not on installation panels" http://publib.boulder.ibm.com/infocenter/dzichelp/v2r2/topic/com .ibm.db2z9.doc.inst/src/tpc/db2z_zparmnotonpanels.htm The suggested Primary and Secondary space allocation for workfile table spaces used by workfiles: 4K pagesize - PRIQTY 2096640 (KB) SECQTY 0 32K pagesize - PRIQTY 2096512 (KB) SECQTY 0 The suggested Primary and Secondary space allocation for workfile table spaces used by DGTT: 4K pagesize - any PRIQTY > 0 or -1 and any SECQTY > 0 or -1 (or omitted) 32K pagesize - any PRIQTY > 0 or -1 and any SECQTY > 0 or -1 (or omitted) To allow more concurrent users of a workfile table spaces and better management of the storage, if the number of rows in the logical workfile tables and DGTTs is typically small, it is recommended that in New Function Mode (NFM) a SEGSIZE value smaller than default 16 be set. In V10, workfile database operates with a 12767 limit of table object identifiers (OBIDs) per thread (or a family of parallel tasks in case of query CP parallelism). If this limit is exceeded and DB2 is not able to process the SQL in a different fashion, SQLCODE -497 / SQLCODE -904 will be issued. In most cases, timely COMMITs will help keep the OBID limit from being exceeded. Should there be unexpected space behavior in workfile database, collecting IFCID 002, IFCID 342, IFCID 343 is typically advised. IFCID 002, which tends to be left on in a customer environment, monitors usage of workfile database space at subsystem level. IFCID 342 and IFCID 343 track usage at thread level (or a family of parallel tasks in case of query CP parallelism). Please note that some performance impact may be observed while running IFCID 342, since it is written each time a segment of pages is allocated or deallocated." This apar also applies to Skip Level Migration Customers, migrating directly from V8 straight to V10. New in V10, UTS PBG are available for DGTT use only with ZPARM WFDBSEP = YES and so classic segmented workfile tablespaces also require to be defined for SORT workfile clients, else this type of application workload will receive resource unavailable conditions if only UTS PBG workfile tablespaces have been created. This is discussed in apars PM65767 & PM17336 which adds UTS PBG changes to DSNTWFG - MAXPARTS > 1 for DGTT use. New in V11, LARGE RBA/LRSN was added and so data pages + space map pages for the work file database will use the 10-byte format as soon as they are first accessed in Version 11, regardless of whether the subsystem is migrated from Version 10 or is a new installation. However, for migrated subsystems, the DB2 Catalog is not updated to reflect the format of the work files, so RBA_FORMAT will be 'blank'. . In V10 and V11 for Workfile DB2-managed classic segmented table spaces only, DB2 was not honouring SECQTY = 0, in that a table space could grow into multiple pieces ( data sets). Apar PI29949 has corrected this and with this applied, the workfile table space will not grow into multiple pieces.
Local fix
Problem summary
Problem conclusion
Temporary fix
Comments
APAR Information
APAR number
II14587
Reported component name
PB LIB INFO ITE
Reported component ID
INFOPBLIB
Reported release
001
Status
INTRAN
PE
NoPE
HIPER
NoHIPER
Special Attention
NoSpecatt / Xsystem
Submitted date
2010-07-22
Closed date
Last modified date
2019-09-18
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
Fix information
Applicable component levels
[{"Business Unit":{"code":null,"label":null},"Product":{"code":"SG19O","label":"APARs - MVS environment"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"001","Edition":"","Line of Business":{"code":"","label":""}},{"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":"001","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
18 September 2019