IBM Support

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

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

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":"BU054","label":"Systems w\/TPS"},"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":"BU050","label":"BU NOT IDENTIFIED"},"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