II14587: DB2 V9 , V10 & V11 WORKFILE RECOMMENDATIONS ( 5740XYR00 R910 / RA10 / RB10 )

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 is sysrouted FROM one or more of the following:

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

Fix information

Applicable component levels