A fix is available
APAR status
Closed as new function.
Error description
Production Modelling Function (DK1485)
Local fix
Problem summary
**************************************************************** * USERS AFFECTED: DB2 users that need to model CPU speed, * * number of processors, RID pool, sort pool, * * and bufferpool settings on a test system to * * match a production system. * **************************************************************** * PROBLEM DESCRIPTION: This APAR allows a test system to * * better recreate environment settings * * on a test system compared to a * * production system. * **************************************************************** * RECOMMENDATION: * **************************************************************** When modelling a production system on a test system, it may not be possible to set certain enviroment settings to be the same. The CPU speed, number of processors, sort pool, RID pool, and bufferpools settings on the test system may have fewer fewer resources and cannot have the same settings as the production system. This can cause a difference in access paths between the test and production system even though all other environment settings are the same. New zparms and profile monitoring keywords values have been added to allow modelling of CPU speed, number of processors, sort pool, RID pool, and bufferpool settings. These new attributes are only used when determining an access path and are not used elsewhere. The actual values for the modelled settings remain unchanged. The new KEYWORDS values in SYSIBM.DSN_PROFILE_ATTRIBUTES are the following: SORT_POOL_SIZE: The sort pool size. MAX_RIDBLOCKS: The RID pool size. For bufferpools: The KEYWORDS value is the same as the bufferpool names listed in the DSNTIP1 panel. For example a KEYWORDS value of 'BP8K0' corresponds to bufferpool BP8K0. For example: The following step is executed on the production system. (1) Capture the CPU speed, number of processors, sort pool, RID pool settings on the production system by executing the following statements using a unique QUERYNO value and converting the hex value to integer. SET CURRENT DEGREE='ANY'; EXPLAIN ALL SET QUERYNO=6475 FOR SELECT * FROM SYSIBM.SYSDUMMY1; SELECT HEX(SUBSTR(IBM_SERVICE_DATA,25,2)) AS CPU_COUNT, HEX(SUBSTR(IBM_SERVICE_DATA,69,4)) AS CPU_SPEED, HEX(SUBSTR(IBM_SERVICE_DATA,13,4)) AS RIDPOOL, HEX(SUBSTR(IBM_SERVICE_DATA,9,4)) AS SORT_POOL_SIZE FROM PLAN_TABLE WHERE QUERYNO=6475; The following steps are executed on the test system: (2) Explain tables PLAN_TABLE and DSN_STATEMNT_TABLE exist or are created. (3) Profile monitoring is enabled by creating the necessary SYSIBM profile tables in sample job DSNTIJOS for steps DSNTPRO, DSNTRUN, and DSNTEXP. (4) Execute the following INSERT statement. Any unique PROFILEID value can be used. The INSERT statement creates a global profile for a single DB2 subsystem that is active when profile monitoring is on. INSERT INTO SYSIBM.DSN_PROFILE_TABLE (PROFILEID) VALUES (4713); (5) Insert bufferpool values to model into DSN_PROFILE_ATTRIBUTES for the global parameter profile that was just defined in step 3. For example, BP0 will use a value of 25000 and BP8K0 will use a value of 2500 and override the actual bufferpool sizes of 20000 and 2000 when determining the access path. The actual bufferpool sizes are not changed. The bufferpool assignments for tables in your test system need to be consistent with bufferpool assignments in production. It is not important that the exact bufferpool used on the test system is the same as the production system. However, if a table is assigned to a bufferpool with VPSIZE 10000 in production, make sure it is also assigned to a bufferpool modeled at size 10000 in the modeled environment. This is also true for indexes. (6) Using the RIDPOOL and SORT_POOL_SIZE values from step 1, insert RID pool and sort pool values to model into DSN_PROFILE_ATTRIBUTES for the global parameter profile that was defined in step 3. INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'BP0',NULL, 25000); INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'BP8K0',NULL, 2500); INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2) VALUES (4713, 'SORT_POOL_SIZE',NULL, 307200);
Problem conclusion
Temporary fix
Comments
Why does this APAR contain changes for V8 and V10? -------------------------------------------------- Please note that although this is a V9 APAR, it does include a small change in V8 and V10. The APAR updates DSNADMIZ, the external module for the SYSPROC.ADMIN_INFO_SYSPARM stored procedure in DB2 V8, V9, and V10 so that it can report the setting for the new subsystem parameters added by this APAR. 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 modes, the V8 and V10 SYSPROC.ADMIN_INFO_SYSPARM must recognize the new subsystem parameters. This APAR provides production modelling support for DB2 V9. Please refer to APAR PM26973 for DB2 V10.
APAR Information
APAR number
PM26475
Reported component name
DB2 OS/390 & Z/
Reported component ID
5740XYR00
Reported release
910
Status
CLOSED UR1
PE
NoPE
HIPER
NoHIPER
Special Attention
YesSpecatt / New Function
Submitted date
2010-11-10
Closed date
2011-03-01
Last modified date
2011-10-17
APAR is sysrouted FROM one or more of the following:
APAR is sysrouted TO one or more of the following:
UK65332 UK65333 UK65334
Modules/Macros
DSN@XAZP DSNDPLN DSNDQWPZ DSNDSPRF DSNDSPRM DSNTIDXA DSNTIJUZ DSNTINST DSNTXAZH DSNTXAZP DSNT1RSP DSNWZIF9 DSNXECSS DSNXEDP DSNXEXP DSNXMOPC DSNXOAF DSNXOD3 DSNXOD4 DSNXOMPS DSNXOTL DSNZOVTB DSN6SPRC DSN6SPRM
Fix information
Fixed component name
DB2 OS/390 & Z/
Fixed component ID
5740XYR00
Applicable component levels
RA10 PSY UK65332
UP11/03/17 P F103
R810 PSY UK65333
UP11/03/18 P F103
R910 PSY UK65334
UP11/03/17 P F103
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:
17 October 2011