IBM Support

PM02528: V9 WORKFILE DATABASE USABILITY ENHANCEMENT - NEW ZPARM WFDBSEP FOR HARD BARRIER BETWEEN DGTT AND NON-DGTT SPACE USE

A fix is available

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as new function.

Error description

  • V9 Workfile database usability enhancement - new zparm WFDBSEP
    for hard barrier between DGTT and non-DGTT space use in Workfile
    database.
    
    This APAR evolves from the soft barrier introduced in 2009's
    APAR PK70060 (PTF UK46839), by providing the choice to make
    it a hard barrier.
    
    How to modify DSNTIJUZ and the DSNTIDxx member:
    
    If you have already installed or migrated to DB2, you need
    to take the following actions after applying this PTF:
    (1) Update your DB2 subsystem parameter (DSNZPxxx) modules:
        -> This action is optional if you use the default setting
           of WFDBSEP=NO
        * Add the keyword parameter
            WFDBSEP=<n>,
          where <n> is either NO or YES to the
          invocation of the DSN6SPRM macro in your customized
          version of the installation job DSNTIJUZ.  Make sure to
          add a continuation character in column 72 if needed.
          If you omit adding WFDBSEP here, the value will be
          set to the default of NO when you assemble the DSNZPxxx
        * Run the first two steps of the DSNTIJUZ job you modified,
          to assemble and link the load module.
        * After the job completes, you must stop and start DB2 for
          the change to take effect because WFDBSEP is not online
          changeable.
    (2) This PTF adds an entry for WFDBSEP=NO to the CLIST
        default input member, DSNTIDXA, in the SDSNSAMP target
        library.  You need to add this entry to all private copies
        of your CLIST output (DSNTIDxx) member.  In each such copy,
        add the following line:
    
    WFDBSEP CHAR  M  NO          YES           NO
    
        Change the second occurrence of NO to YES if you specified
        WFDBSEP=YES in step (1), above.
    
    (3) Update customized copies of DB2 installation CLIST member
        DSNTINST:
        This PTF modifies CLIST member DSNTINST in the SDSNCLST
        target library only.  You need to redo any record format
        changes and reapply any tailoring you have done to your
        copies of this CLIST.  You may also want to move it to the
        prefix.NEW.SDSNCLST data set, where the CLISTs processed by
        job DSNTIJVC reside.
    
    
                                                 (DK1323)
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED: All DB2 9 users of Workfile database         *
    *                 (conversion mode (CM) and higher)            *
    ****************************************************************
    * PROBLEM DESCRIPTION: Workfile database usability             *
    *                      enhancement: New offline zparm WFDBSEP  *
    *                      for a hard barrier between DGTT and     *
    *                      non-DGTT (a.k.a. workfile) space use in *
    *                      Workfile database                       *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    In V9, users may not be able to limit space usage effectively
    between declared global temporary tables (DGTT) and workfiles
    (sort workfiles, created global temporary tables (CGTT), trigger
    transition tables, etc).
    
    Prior to V9, a natural separation existed between these two
    groups of functions because DGTTs were contained in the TEMP
    database (defunct in V9) and workfiles were contained in the
    WORKFILE database.
    

Problem conclusion

Temporary fix

Comments

  • This APAR introduces a zparm in DSN6SPRM called WFDBSEP which
    specifies whether DB2 should provide an unconditional separation
    of table spaces in Workfile database based on the table spaces'
    allocation attributes.
    
    
    Which users might benefit from the new zparm WFDBSEP?
    -----------------------------------------------------
    The zparm may be suitable for users of Workfile database who
    prefer to set aside a fixed amount of space for declared
    global temporary table (DGTT) work vs. workfile work, and do not
    wish it to be exceeded, even at the cost of potentially failing
    workloads (e.g. DSNT501I / SQLCODE -904 due to lack of space)
    until more space is manually added. In essence, WFDBSEP=YES
    separates DGTTs and workfiles similarly to V8, where they were
    serviced in two separate databases (TEMP and WORKFILE).
    
    
    How does the new zparm WFDBSEP work?
    ------------------------------------
    If the value is YES, DB2 always directs declared global
    temporary table (DGTT) work only to DB2-managed (STOGROUP)
    workfile table spaces defined with a non-zero SECQTY and
    workfile work only to other workfile table spaces (DB2-managed
    table spaces defined with a zero SECQTY or user-managed table
    spaces). If no table space with the preferred allocation type is
    available, DB2 issues an error (e.g. message DSNT501I and/or
    SQLCODE -904).
    
    If the value is NO, DB2 attempts to direct declared global
    temporary table (DGTT) work to DB2-managed (STOGROUP) workfile
    table spaces defined with a non-zero SECQTY and workfile work to
    any other workfile table space (DB2-managed table spaces
    defined with a zero SECQTY or user-managed table spaces). If no
    table space with the preferred allocation type is available, DB2
    selects a table space with a non-preferred allocation type. In
    other words, the existing Workfile database behavior (ever since
    PK70060 (PTF UK46839)) remains.
    
    The default value is NO, which means that DB2 will not provide
    an unconditional separation of table spaces in Workfile
    database based on their allocation attributes.
    
    WFDBSEP is opaque in V9 (documented but not panel-supported)
    and its value can be changed starting with V9 CM.
    
    
    Sample messages and codes when the new zparm WFDBSEP is YES:
    -------------------------------------------------------------
    When WFDBSEP is YES, the resource name field in the Workfile
    database-related resource-unavailable messages or codes will
    tell which particular table space allocation type is missing.
    Below is a sample of what message DSNT501I might look like on
    a sort workfile allocation attempt when WFDBSEP is YES (NAME
    contains the new specification):
    
    DSNT501I  - DSNIWKFL RESOURCE UNAVAILABLE 257
               CORRELATION-ID=WFDBJOB
               CONNECTION-ID=BATCH
               LUW-ID=*
               REASON 00C90084
               TYPE 00000100
               NAME DB2-MANAGED SPACE WITHOUT SECONDARY ALLOCATION
                    OR USER-MANAGED SPACE IN DSNDB07
    
    Below is a sample of what SQLCODE -904 might look like on a
    sort workfile allocation attempt when WFDBSEP is YES -
    equivalent to the console message above (NAME contains the new
    specification):
    
    SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN
    UNAVAILABLE RESOURCE. REASON 00C90084, TYPE OF RESOURCE
    00000100, AND RESOURCE NAME DB2-MANAGED SPACE WITHOUT SECONDARY
    ALLOCATION OR USER-MANAGED SPACE IN DSNDB07
    
    Please note that due to the SQLCA limitation of 70 bytes for
    message tokens and delimiters, the long resource name in the
    SQLCODE -904 above will be truncated, but if the GET DIAGNOSTICS
    statement is used (e.g. with MESSAGE_TEXT), the full message
    text with no truncation will be retrieved.
    
    Below is a sample of what SQLCODE -904 might look like on a
    DGTT allocation attempt when WFDBSEP is YES (NAME contains the
    new specification):
    
    SQLCODE = -904, ERROR:  UNSUCCESSFUL EXECUTION CAUSED BY AN
    UNAVAILABLE RESOURCE. REASON 00E7009A, TYPE OF RESOURCE 200,
    AND RESOURCE NAME DB2-MANAGED SPACE WITH SECONDARY ALLOCATION
    IN DSNDB07
    
    
    Background on the new zparm WFDBSEP:
    ------------------------------------
    PM02528 evolves from the soft barrier introduced in 2009 by
    APAR PK70060 (PTF UK46839), by providing a choice to make
    it a hard barrier. Given the unconditional allocation preference
    introduced by PM02528 when WFDBSEP is YES, it is strongly
    advised that sufficient space with appropriate allocation be
    defined in Workfile database (DB2 STOGROUP-managed with
    SECQTY > 0 for DGTT work and DB2 STOGROUP-managed with SECQTY 0
    or user-managed table spaces for non-DGTT work).
    
    
    Why does this APAR contain a V8 change?
    ---------------------------------------
    Please note that although this is a V9 APAR, it does include a
    small V8 change. The APAR updates DSNADMIZ, the external module
    for the SYSPROC.ADMIN_INFO_SYSPARM stored procedure in DB2 V8
    and V9, so that it can report the setting for the new WFDBSEP
    subsystem parameter. In DB2 data sharing, a
    SYSPROC.ADMIN_INFO_SYSPARM connection on any member of the group
    can query the subsystem, DECP and certain IRLM parameter
    settings of any other member of that group. In order to support
    V9 data sharing coexistence mode, the V8
    SYSPROC.ADMIN_INFO_SYSPARM must recognize the new WFDBSEP
    subsystem parameter.
    

APAR Information

  • APAR number

    PM02528

  • 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

    2009-12-01

  • Closed date

    2010-04-13

  • Last modified date

    2011-02-22

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

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

    UK56045 UK56046

Modules/Macros

  • DSNDQWPZ DSNDSPRM DSNIGWAC DSNTIDXA DSNTIJUZ
    DSNTINST DSNTSRU  DSNWZIF9 DSNXIDCL DSNXISCR DSNZOVTB DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • R810 PSY UK56045

       UP10/05/01 P F004

  • R910 PSY UK56046

       UP10/05/01 P F004

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":"9.1","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":"9.1","Edition":"","Line of Business":{"code":"","label":""}}]

Document Information

Modified date:
22 February 2011