PM17336: MODIFY DSNWTFG TO ALSO GENERATE WORK FILE TABLE SPACES WITH SECQ TY OF 0

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • Modify DSNWTFG to also generate work file table spaces with
    SECQTY of 0
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All users of the DB2 Version 9.1 for z/OS    *
    *                 and DB2 10 for z/OS installation and         *
    *                 migration jobs and CLIST are affected by     *
    *                 this change.                                 *
    ****************************************************************
    * PROBLEM DESCRIPTION: This APAR enhances the process for      *
    *                      creating workfile table spaces during   *
    *                      installation and migration of DB2 for   *
    *                      z/OS.                                   *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    Beginning in DB2 Version 9.1 for z/OS, the WORKFILE database
    was extended to support declared global temporary tables (DGTT)
    in addition to its traditional support for sort work files,
    created global temporary tables (CGTT), trigger transition
    tables, etc.  DGTTs previously required a separate database
    called the TEMP database for temporary (DGTT) tables.
    
    The DB2 installation process provides a DB2 REXX exec called
    DSNTWFG that can be used to create specified numbers of 4-KB
    type and 32-KB type table spaces in the WORKFILE database, each
    having a specified primary space quantity, segment size, and DB2
    storage group. DSNTWFG is executed by installation jobs DSNTIJTM
    and (for data sharing only) DSNTIJFT, and the DSNTWFG calls in
    these jobs are customized according to WORKFILE preferences
    entered on installation panel DSNTIP9.
    
    Since general availability of DB2 V9.1 for z/OS, the DB2 work
    file implementation has evolved to provide for better management
    of system resource:
    * PK70060 alleviates the problem of DGTTs running out of space
      due to competition for space with SORT work files in the work
      file database, by causing:
      - Sort work processing to avoid table spaces that grow into
        secondary extents
      - Temp work (DGTT) processing to prefer table spaces that can
        grow  into secondary extents since DGTTs cannot span table
        spaces
    * PM02528 subsequently added the DSN6SPRM.WFDBSEP parameter for
      enforcing separation of work file table space types:
      - WFDBSEP=NO : Uses prefer/avoid behavior described above
      - WFDBSEP=YES: Strict table space type requirement according
        to use (else SQLCODE -904)
        * Temp work (DGTT) processing requires work file table
          spaces with a non-0 SECQTY on DB2-managed storage
        * Sort work processing requires work file table spaces on
          DB2-managed storage with SECQTY 0 or on user-managed
          storage.
    DSNTWFG has not kept pace with these changes.  Currently, it
    creates only table spaces that have a non-0 SECQTY and it
    creates them on DB2-managed storage.  Such work file table
    spaces are appropriate for temp work (DGTT) processing but not
    preferred for sort work processing (when WFDBSEP=NO) or
    allowed for sort work processing (when WFDBSEP=YES).
    

Problem conclusion

Temporary fix

Comments

  • This APAR enhances DB2 installation and migration support for
    creating table spaces in the work file database:
    (1) The DSNTWFG exec can now create sort work file table spaces
        as well as temporary (DGTT) work file table spaces.  Prior
        to this APAR, it could create only the latter.
    (2) DSNTWFG now accepts its input either from keyword parameters
        or from positional parameters.  Prior to this APAR, it
        accepted input only from positional parameters.  Use of
        keyword parameters is now preferred and is required in order
        to use DSNTWFG to create sort work file table spaces.
    (3) Installation jobs DSNTIJFT and DSNTIJTM are modified to
        use the keyword parameter format of DSNTWFG.
    (4) On installation panel DSNTIP9, new fields are provided for
        gathering preferences for sort work file table spaces to
        be created.  Prior to this APAR, DSNTIP9 gathered only
        preferences for creating temporary (DGTT) work file table
        spaces.
    ================================================================
    (1) DSNTWFG is enhanced to create sort work file table spaces
    ================================================================
    Prior to this APAR, all work file table spaces created by
    DSNTWFG have a non-0 secondary space quantity, meaning that
    they grow via secondary extents. Such table spaces are suitable
    for temporary work (DGTT) processing but not for sort work
    processing.  This APAR adds the capability for DSNTWFG to also
    create work file table spaces that have a zero secondary space
    quantity and are thus suitable for sort work processing. Details
    on how to use DSNTWFG to create sort work table spaces are given
    in the next section.  In DB2 V10, DSNTWFG can create temp (DGTT)
    work file table spaces as either segmented or partitioned by
    growth (PBG).  However, it creates sort work file table spaces
    only as segmented because sort work file table spaces are not
    intended to grow.
    ================================================================
    (2) DSNTWFG is enhanced to accept input via keyword parameters
    ================================================================
    Prior to this APAR, all input was passed to DSNTWFG by means of
    positional parameters.  The DSNTWFG positional parameters are
    unchanged and continue to work as previously (see the DSNTWFG
    prolog for a description of its positional parameters).  This
    APAR introduces keyword parameters for DSNTWFG.  Keyword
    parameters, unlike positional parameters, indicate their purpose
    and can be specified to DSNTWFG in any order.  Like positional
    parameters, all keyword parameters (except as noted below) must
    be specified when calling DSNTWFG.  DSNTWFG does not permit
    mixing of positional and keyword parameters.  Temp (DGTT) work
    file table spaces can be created using either keyword or
    positional parameters.  However, sort work table spaces can be
    created only by using keyword parameters.  The DSNTWFG keyword
    parameters are as follows:
      --------------------------------------------------------------
      * General parameters                                         *
      --------------------------------------------------------------
    - DB2SSN: Local DB2 SSID.
        Example: DB2SSN(DSN)
    - AUTHID: Authorization ID to perform the work.
        Example: AUTHID(ME)
    - WFDBNAME: Name of the DB2 work file database
        Example: AUTHID(DSNDB07)
      --------------------------------------------------------------
      * Parameters for 4-KB type temp (DGTT) work table spaces     *
      --------------------------------------------------------------
    - TEMP4K_QTY: The number of 4-KB type table spaces to be
      created for temporary (DGTT) work
        Example: TEMP4K_QTY(10)
    - TEMP4K_MAXPARTS (V10 only): The MAXPARTITIONS of each 4-KB
      type temp work file table space to be created.
        Example: TEMP4K_MAXPARTS(10), TEMP4K_MAXPARTS()
      When TEMP4K_MAXPARTS is specified and set to a non-null, non-0
      value, the TEMP4K_DSSIZE parameter must also be specified with
      a non-null, non-0 value; and the TEMP4K_PRIQTY parameter
      either must not be specified or must be set to null or 0.  If
      these conditions are met then DSNTWFG will create the
      specified number of 4-KB type temp work file table spaces as
      partition by growth (PBG).
    - TEMP4K_DSSIZE (V10 only): The maximum GB per partition
      (DSSIZE) of each 4-KB type temp work file table space to be
      created.
        Example: TEMP4K_DSSIZE(4), TEMP4K_DSSIZE(0), TEMP4K_DSSIZE()
      When TEMP4K_DSSIZE is specified and set to a non-null, non-0
      value, the TEMP4K_MAXPARTS parameter must also be specified
      with a non-null, non-0 value; and the TEMP4K_PRIQTY parameter
      either must not be specified or must be set to null or 0.  If
      these conditions are met then DSNTWFG will create the
      specified number of 4-KB type temp work file table spaces as
      partition by growth (PBG).
    - TEMP4K_PRIQTY: The primary space in MB for each 4-KB type
      temp work file table space.
        Example: TEMP4K_PRIQTY(20)
      ==> The following considerations apply only in DB2 V10:
        When TEMP4K_PRIQTY is specified and set to a non-null,
        non-0 value, the TEMP4K_MAXPARTS and TEMP4K_DSSIZE
        parameters must either not be specified or must be set to
        null or 0.  If these conditions are met then DSNTWFG will
        create the specified number of 4-KB type temp work file
        table spaces as segmented.
    - TEMP4K_SEGSIZE: The segment type for each 4-KB type temp
      work  table space to be created.
        Example: TEMP4K_SEGSIZE(16)
    - TEMP4K_BPOOL: The name of the DB2 buffer pool for each 4-KB
      type temp work table spaces to be created
        Example: TEMP4K_BPOOL(BP0)
    - TEMP4K_STOGROUP: The DB2 STOGROUP for 4-KB type temp work
      table spaces
        Example: TEMP4K_STOGROUP(SYSDEFLT)
      --------------------------------------------------------------
      * Parameters for 32-KB type temp (DGTT) work table spaces    *
      --------------------------------------------------------------
    - TEMP32K_QTY: The number of 32-KB type table spaces to be
      created for temporary (DGTT) work
        Example: TEMP32K_QTY(10)
    - TEMP32K_MAXPARTS (V10 only): The MAXPARTITIONS of each 32-KB
      type temp work file table space to be created.
        Examples: TEMP32K_MAXPARTS(0), TEMP32K_MAXPARTS()
      When TEMP32K_MAXPARTS is specified and set to a non-null,
      non-0 value, the TEMP32K_DSSIZE parameter must also be
      specified with a non-null, non-0 value; and the TEMP32K_PRIQTY
      parameter either must not be specified or must be set to null
      or 0.  If these conditions are met then DSNTWFG will create
      the specified number of 32-KB type temp work file table spaces
      as partition by growth (PBG).
    - TEMP32K_DSSIZE (V10 only): The maximum GB per partition
      (DSSIZE) of each 32-KB type temp work file table space to be
       created.
        Examples: TEMP32K_DSSIZE(0), TEMP32K_DSSIZE()
      When TEMP32K_DSSIZE is specified and set to a non-null, non-0
      value, the TEMP32K_MAXPARTS parameter must also be specified
      with a non-null, non-0 value; and the TEMP32K_PRIQTY parameter
      either must not be specified or must be set to null or 0.  If
      these conditions are met then DSNTWFG will create the
      specified number of 32-KB type temp work file table spaces as
      partition by growth (PBG).
    - TEMP32K_PRIQTY: The primary space in MB for each 32-KB type
      temp work file table space.
        Example: TEMP32K_PRIQTY(20)
      ==> The following considerations apply only in DB2 V10:
        When TEMP32K_PRIQTY is specified and set to a non-null,
        non-0 value, the TEMP32K_MAXPARTS and TEMP32K_DSSIZE
        parameters must either not be specified or must be set to
        null or 0.  If these conditions are met then DSNTWFG will
        create the specified number of 32-KB type temp work file
        table spaces as segmented.
    - TEMP32K_SEGSIZE: The segment type for each 32-KB type temp
      work  table space to be created.
        Example: TEMP32K_SEGSIZE(16)
    - TEMP32K_BPOOL: The name of the DB2 buffer pool for each 32-KB
      type temp work table spaces to be created
        Example: TEMP32K_BPOOL(BP0)
    - TEMP32K_STOGROUP: The DB2 STOGROUP for 32-KB type temp work
      table spaces
        Example: TEMP32K_STOGROUP(SYSDEFLT)
      --------------------------------------------------------------
      * Parameters for 4-KB type sort work table spaces            *
      --------------------------------------------------------------
    - SORT4K_QTY: The number of 4-KB type table spaces to be
      created for sort work
        Example: SORT4K_QTY(10)
    - SORT4K_PRIQTY: The primary space in MB for each 4-KB type
      sort work file table space.
        Examples: SORT4K_PRIQTY(20)
       Note: DSNTWFG support creating sort work table spaces only
             as segmented table spaces, thus there are no
             parameters for specifying the MAXPARTITIONS and
             DSSIZE settings for sort work table spaces.
    - SORT4K_SEGSIZE: The segment type for each 4-KB type sort
      work  table space to be created.
        Example: SORT4K_SEGSIZE(16)
    - SORT4K_BPOOL: The name of the DB2 buffer pool for each 4-KB
      type sort work table spaces to be created
        Example: SORT4K_BPOOL(BP0)
    - SORT4K_STOGROUP: The DB2 STOGROUP for 4-KB type sort work
      table spaces
        Example: SORT4K_STOGROUP(SYSDEFLT)
      --------------------------------------------------------------
      * Parameters for 32-KB type sort work table spaces           *
      --------------------------------------------------------------
    - SORT32K_QTY: The number of 32-KB type table spaces to be
      created for sort work
        Example: SORT32K_QTY(10)
    - SORT32K_PRIQTY: The primary space in MB for each 32-KB type
      sort work file table space.
        Examples: SORT32K_PRIQTY(20)
      Note: DSNTWFG support creating sort work table spaces only
            as segmented table spaces, thus there are no
            parameters for specifying the MAXPARTITIONS and
            DSSIZE settings for sort work table spaces.
    - SORT32K_SEGSIZE: The segment type for each 32-KB type sort
      work  table space to be created.
        Example: SORT32K_SEGSIZE(16)
    - SORT32K_BPOOL: The name of the DB2 buffer pool for each 32-KB
      type sort work table spaces to be created
        Example: SORT32K_BPOOL(BP0)
    - SORT32K_STOGROUP: The DB2 STOGROUP for 32-KB type sort work
      table spaces
        Example: SORT32K_STOGROUP(SYSDEFLT)
    ================================================================
    (3) Installation jobs DSNTIJFT and DSNTIJTM are modified
    ================================================================
    Prior to this APAR, DSNTIJFT and DSNTIJTM executed DSNTWFG
    passing input by means of positional parameters.  This APAR
    modifies these jobs to use the keyword parameters discussed
    above.  The keyword parameter format is required in order to
    use DSNTWFG to create sort work file table spaces.  Example:
      DSNTWFG DB2SSN(DSN) AUTHID(SYSADM) WFDBNAME(DSNDB07) +
           SORT4K_QTY(1)      SORT4K_SEGSIZE(16) +
           SORT4K_PRIQTY(20)  +
           SORT4K_BPOOL(BP0)  SORT4K_STOGROUP(SYSDEFLT) +
           SORT32K_QTY(1)     SORT32K_SEGSIZE(16) +
           SORT32K_PRIQTY(20) +
           SORT32K_BPOOL(BP0) SORT32K_STOGROUP(SYSDEFLT) +
           TEMP4K_QTY(1)      TEMP4K_SEGSIZE(16) +
           TEMP4K_PRIQTY(20)  TEMP4K_MAXPARTS()  TEMP4K_DSSIZE() +
           TEMP4K_BPOOL(BP0)  TEMP4K_STOGROUP(SYSDEFLT) +
           TEMP32K_QTY(1)     TEMP32K_SEGSIZE(16) +
           TEMP32K_PRIQTY(20) TEMP32K_MAXPARTS() TEMP32K_DSSIZE() +
           TEMP32K_BPOOL(BP0) TEMP32K_STOGROUP(SYSDEFLT)
    ================================================================
    (4) Installation panel DSNTIP9 is modified
    ================================================================
    Prior to this APAR, DSNTIP9 gathered only preferences for
    creating temporary (DGTT) work file table spaces. This APAR adds
    six fields for gathering sort work file criteria:
    ----------------------------------------------------------------
    4K SORT PRIMARY SPACE: The value of the 4K SORT PRIMARY SPACE
    field specifies the total amount of space for all 4 KB sort work
    table spaces in the work file database.  This value is specified
    in MB but you can enter the value in GB by specifying a suffix
    of G, for example 1024G.  Sort work table spaces are always
    segmented table spaces and, unlike temporary (DGTT) work table
    spaces, can not increase in size.
    - Range: 1 - 32,702,464 (for installation) or 0 - 32,702,464
      (for migration)
    - Default: 20 (for installation) or 0 (for migration)
    The value of 4K SORT PRIMARY SPACE is divided by the value of
    the 4K SORT WORK TS'S field to obtain the primary space quantity
    (PRIQTY) for creating each 4 KB type sort work file table space.
    ----------------------------------------------------------------
    4K SORT WORK TS'S: The value of the 4K SORT WORK TS'S field
    specifies the number of 4 KB sort work file table spaces that
    are to be created.
    - Range: 1 to 500 (for installation) or 0 to 500 (for migration)
    - Default: 1 (for installation) or 0 (for migration)
    ----------------------------------------------------------------
    4K SORT WORK SEG SIZE: The value of the 4K SORT WORK SEG SIZE
    field specifies the segment size that is to be used for 4 KB
    sort work file table spaces. The value must be a multiple of 4,
    from 4 to 64.
      Range: 4, 8, 12, ..., 60, 64 (multiples of 4)
      Default: 16
    ----------------------------------------------------------------
    32K SORT PRIMARY SPACE: The value of the 32K SORT PRIMARY SPACE
    field specifies the total amount of space for all 32 KB sort
    work table spaces in the work file database.  This value is
    specified in MB but you can enter the value in GB by specifying
    a suffix of G, for example 1024G.  Sort work table spaces are
    always segmented table spaces and, unlike temporary (DGTT) work
    table spaces, can not increase in size.
    - Range: 1 - 32,702,464 (for installation) or 0 - 32,702,464
      (for migration)
    - Default: 20 (for installation) or 0 (for migration)
    The value of 32K SORT PRIMARY SPACE is divided by the value of
    the 32K SORT WORK TS'S field to obtain the primary space
    quantity (PRIQTY) for creating each 32 KB type sort work file.
    ----------------------------------------------------------------
    32K SORT WORK TS'S: The value of the 32K SORT WORK TS'S field
    specifies the number of 32 KB sort work file table spaces that
    are to be created.
    - Range: 1 to 500 (for installation) or 0 to 500 (for migration)
    - Default: 1 (for installation) or 0 (for migration)
    ----------------------------------------------------------------
    32K SORT WORK SEG SIZE: The value of the 32K SORT WORK SEG SIZE
    field specifies the segment size that is to be used for 32 KB
    sort work file table spaces. The value must be a multiple of 4,
    from 4 to 64.
    - Range: 4, 8, 12, ..., 60, 64 (multiples of 4)
    - Default: 16
    ----------------------------------------------------------------
    In addition, these existing DSNTIP9 are renamed as follows:
      TEMP 4K SPACE        is renamed   4K TEMP PRIMARY SPACE
      TEMP 4K TBL SPACES   is renamed   4K TEMP WORK TS'S
      TEMP 4K SEG SIZE     is renamed   4K TEMP WORK SEG SIZE
      TEMP 32K SPACE       is renamed   32K TEMP PRIMARY SPACE
      TEMP 32K TBL SPACES  is renamed   32K TEMP WORK TS'S
      TEMP 32K SEG SIZE    is renamed   32K TEMP WORK SEG SIZE
    

APAR Information

  • APAR number

    PM17336

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    910

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-06-25

  • Closed date

    2011-07-08

  • Last modified date

    2011-09-01

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

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

    UK69607 UK69608

Modules/Macros

  •    DSNTCALC DSNTIDXA DSNTIDXB DSNTIJFT DSNTIJTM
    DSNTINST DSNTINS1 DSNTINS2 DSNTINS3 DSNTIP9  DSNTWFG  DSNT55
    

Publications Referenced
GC18984612 GC19297403      

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK69607

       UP11/08/03 P F108

  • R910 PSY UK69608

       UP11/08/03 P F108

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.



Rate this page:

(0 users)Average rating

Add comments

Document information


More support for:

DB2 for z/OS

Software version:

910

Reference #:

PM17336

Modified date:

2011-09-01

Translate my page

Machine Translation

Content navigation