IBM Support

PM26475: PRODUCTION MODELLING FUNCTION

A fix is available

Subscribe

You can track all active APARs for this component.

 

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