A fix is available
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
| GC18984612 | GC19297403 |
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
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:
01 September 2011