A fix is available
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
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