IBM Support

PM26973: 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 environment 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
    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 zparms are DSN6SPRM.SIMULATED_CPU_SPEED and
    DSN6SPRM.SIMULATED_CPU_COUNT:
    * SIMULATED_CPU_COUNT
      This parameter is for DB2 optimization modelling only.  It
      specifies the number the of local CPUs being simulated. This
      parameter is honored only when SET CURRENT DEGREE='ANY' or
      BIND/REBIND option DEGREE has a value of ANY.
      Acceptable values are OFF or an integer from 1 - 127.  The
      default setting of OFF means that DB2 optimizes for the
      actual number of local CPUs on which it is running.  OFF is
      recommended for DB2 production environments.
    
    * SIMULATED_CPU_SPEED
      This parameter is for DB2 optimization modelling only.  It
      specifies  the microseconds of task or service request block
      (SRB) execution time per service unit for the CPU being
      simulated.  Acceptable values are OFF or an integer from 1 -
      2147483647.  The default setting of OFF means that DB2
      optimizes for the actual CPU on which it is running.  OFF is
      recommended for DB2 production environments.
    
    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);
    
    INSERT INTO SYSIBM.DSN_PROFILE_ATTRIBUTES
     (PROFILEID,KEYWORDS,ATTRIBUTE1,ATTRIBUTE2)
     VALUES
     (4713, 'MAX_RIDBLOCKS',NULL, 300);
    
    (7) Using the CPU_SPEED and CPU_COUNT values from step 1, set
        parameters DSN6SPRM.SIMULATED_CPU_SPEED and
        DSN6SPRM.SIMULATED_CPU_COUNT to match the production
        system.
    
    (8) Issue -START PROFILE so that the rows inserted in step 4
        are applied.
    
    (9) Execute EXPLAIN. In DSN_STATEMNT_TABLE the column REASON
        will contain the value 'PROFILEID 4713' appended to
        the existing REASON value for that statement.  The column
        REASON will have this value when a subsystem parameter
        exists in SYSIBM.DSN_PROFILE_ATTRIBUTES and is for an
        active global profile in SYSIBM.DSN_PROFILE_TABLE
        where PROFILE_ENABLED = 'Y'.
    
    Keywords:
    SQLEXPLAIN
    DSN_PROFILE_TABLE DSN_PROFILE_ATTRIBUTES DSN_STATEMNT_TABLE
    

Problem conclusion

Temporary fix

Comments

  • Why does this APAR contain changes for V8 and V9?
    --------------------------------------------------
    Please note that although this is a V10 APAR, it does include
    a small change in V8 and V9. 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 V10 data sharing coexistence modes,
    the V8 and V9 SYSPROC.ADMIN_INFO_SYSPARM must recognize the
    new subsystem parameters.
    
    This APAR provides production modelling support for DB2 V10.
    Please refer to APAR PM26475 for DB2 V9.
    

APAR Information

  • APAR number

    PM26973

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    A10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function

  • Submitted date

    2010-11-17

  • Closed date

    2011-04-29

  • 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:

    UK67292 UK67293 UK67294

Modules/Macros

  • DSN@XAZP DSNDPLN  DSNDQWPZ DSNDSPRF DSNDSPRM
    DSNTIDXA DSNTIDXB DSNTIJUZ DSNTINST DSNTXAZH DSNTXAZP DSNT1RSP
    DSNWZIFA DSNXECSS DSNXEDP  DSNXEXP  DSNXMOPC DSNXOAF  DSNXOD3
    DSNXOD4  DSNXOMPS DSNXOPRS DSNXOTL  DSNZOVTB DSN6SPRC DSN6SPRM
    

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RA10 PSY UK67292

       UP11/05/19 P F105

  • R810 PSY UK67293

       UP11/05/19 P F105

  • R910 PSY UK67294

       UP11/05/19 P F105

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.



Document information

More support for: DB2 for z/OS

Software version: 10.1

Reference #: PM26973

Modified date: 17 October 2011


Translate this page: