Skip to Content
Information
Limited support BMC provides limited support for this version of the product. As a result, BMC no longer accepts comments in this space. If you encounter problems with the product version or the space, contact BMC Support.BMC recommends upgrading to the latest version of the product. To see documentation for that version, see BMC AMI Ops Monitor for Db2 13.1 image-2024-5-19_8-5-1.png .

Preparing tables for Performance Reporter

Use one of the following procedures to prepare the tables that Performance Reporter will use:

Warning

Note

You cannot change Performance Reporter version 9 and earlier tables into version 12 tables. You must create new tables.

To create new Performance Reporter tables

  1. (optional) Using the DZPRUCNV utility with the DZPRUPRM and DZPRUJCL samples, you can change the default names to conform to your site's naming conventions. See the samples in the following table.For more information, see Using-product-libraries.

    Warning

    Note

    If your current Performance Reporter database contains a single segmented table space containing all of the Performance Reporter product tables, BMC recommends that you take the following steps:

    • Create a new Performance Reporter database by using the HLQ.BBSAMP(DPJCREAT) member.
    • Place each table in a separate universal table space (UTS).
    • (if required) Use the LOAD and UNLOAD commands to move existing table data to the new database.

    Default table name and description

    Default table space name

    Create table space/table member (UBBSAMP)

    Report members (BBPARM)

    DMRPR.DMRACDTL

    (accounting table—detail)

    DMRPRTAD

    DPCSACDT

    DPCTACDT

    ACxxxxxx

    DMRPR.DMRACSUM

    (accounting table—summary)

    DMRPRTAS

    DPCSACSM

    DPCTACSM

    SAxxxxxx

    DMRPR.DMRACSM2

    (accounting table—summary–2)

    DMRPRTA2

    DPCSACS2

    DPCTACS2

    SAxxxxxx

    DMRPR.DMRAXDTL

    (Accounting Accelerator table—detail)

    DMRPRXAD

    DPCTAXDT

    Not applicable

    DMRPR.DMRAXSUM

    (Accounting Accelerator table—summary)

    DMRPRXAS

    DPCTAXSM

    Not applicable

    DMRPR.DMRAXSM2

    (Accounting Accelerator table—summary)–2

    DMRPRXA2

    DPCTAXS2

    Not applicable

    DMRPR.DMRAUSUM

    (audit summary table)

    DMRPRAUS

    DPCSAUSM

    DPCTAUSM

    AUSUM

    AUDTL

    DMRPR.DMRAUGRV

    (authorization control—GRANTs/REVOKEs table)

    DMRPRAUG

    DPCSAUGR

    DPCTAUGR

    AUDGRV

    DMRPR.DMRAUFAL

    (authorization failures table)

    DMRPRAUF

    DPCSAUFL

    DPCTAUFL

    AUFAIL

    DMRPR.DMRAUCHG

    (authorization ID change table)

    DMRPRAUC

    DPCSAUCH

    DPCTAUCH

    AUCHNG

    DMRPR.DMRABDTL

    (buffer accounting table—detail)

    DMRPRTID

    DPCSABDT

    DPCTABDT

    ACxxxxxx

    DMRPR.DMRABSUM

    (buffer accounting table—summary)

    DMRPRTIS

    DPCSABSM

    DPCTABSM

    SAxxxxxx

    DMRPR.DMRABSM2

    (buffer accounting table—summary–2)

    DMRPRTI2

    DPCSABS2

    DPCTABS2

    SAxxxxxx

    DMRPR.DMRSBFDT

    (buffer statistics table—detail)

    DMRPRTBD

    DPCSSBDT

    DPCTSBDT

    STxxxxx

    DMRPR.DMRSBSUM

    (buffer statistics table—summary)

    DMRPRTBS

    DPCSSBSM

    DPCTSBSM

    SSxxxxx

    DMRPR.DMRSBSM2

    (buffer statistics table—summary–2)

    DMRPRTB2

    DPCSSBS2

    DPCTSBS2

    SSOVDFT

    SSOVRxx

    DMRPR.DMRADDTL

    (DDF accounting table—detail)

    DMRPRTDD

    DPCSADDT

    DPCTADDT

    ACxxxxx

    DMRPR.DMRADSUM

    (DDF accounting table—summary)

    DMRPRTDS

    DPCSADSM

    DPCTADSM

    SAxxxxxx

    DMRPR.DMRADSM2

    (DDF accounting table—summary–2)

    DMRPRTD2

    DPCSADS2

    DPCTADS2

    SAxxxxxx

    DMRPR.DMRSTDF

    (DDF statistics table—detail)

    DMRPRTSF

    DPCSSTSD

    DPCTSTSD

    STOVDFT

    DMRPR.DMRSDSUM

    (DDF statistics table—summary)

    DMRPRTFS

    DPCSSFSM

    DPCTSFSM

    STOVDFT

    DMRPR.DMRSDSM2

    (DDF statistics table—summary–2)

    DMRPRTF2

    DPCSSFS2

    DPCTSFS2

    SSxxxxx

    DMRPR.DMRAUDDL

    (DDL access table)

    DMRPRAUD

    DPCSAUDL

    DPCTAUDL

    AUDDL

    DMRPR.DMRAUDML

    (DML access table)

    DMRPRAUM

    DPCSAUDM

    DPCTAUDM

    AUDML

    DMRPR.DMRAUDMB

    (DML at BIND table)

    DMRPRAUB

    DPCSAUDB

    DPCTAUDB

    AUDMLB

    DMRPR.DMRAPDTL

    (package accounting table—detail)

    DMRPRTPD

    DPCSAPDT

    DPCTAPDT

    ACxxxx

    DMRPR.DMRAPSUM

    (package accounting table—summary)

    DMRPRTPS

    DPCSAPSM

    DPCTAPSM

    SAxxxx

    DMRPR.DMRAPSM2

    (package accounting table—summary–2)

    DMRPRTP2

    DPCSAPS2

    DPCTAPS2

    SAxxxx

    DMRPR.DMRSXDTL

    (Statistics Accelerator table—detail)

    DMRPRXSD

    DPCTSXDT

    Not applicable

    DMRPR.DMRSXSUM

    (Statistics Accelerator table—summary)

    DMRPRXSM

    DPCTSXSM

    Not applicable

    DMRPR.DMRSXSM2

    (Statistics Accelerator table—summary–2)

    DMRPRXS2

    DPCTSXS2

    Not applicable

    DMRPR.DMRSTAT

    (statistics table—detail)

    DMRPRTSS

    DPCSSTDT

    DPCTSTDT

    STxxxxxx

    DMRPR.DMRSTSUM

    (statistics table—summary)

    DMRPRTTS

    DPCSSTSM

    DPCTSTSM

    SSxxxxxx

    DMRPR.DMRSTSM2

    (statistics table—summary–2)

    DMRPRTT2

    DPCSSTS2

    DPCTSTS2

    SSxxxxx

    DMRPR.DMRSTADT

    (storage address space table)

    DMRSTADT

    DPCTSADT

    Not applicable

    DMRPR.DMRSTSDT

    (storage system table)

    DMRSTSDT

    DPCTSSDT

    Not applicable

    DMRPR.DMRAUUTL

    (utility access table)

    DMRPRAUU

    DPCSAUUT

    DPCTAUUT

    AUUTIL

  2. If you changed the STOGROUP name (when setting up Performance Reporter defaults) also change the STOGROUP name in the DOCSxxxx sample members to the same name.
  3. Customize the table definitions.The following table customization applies to all tables, both detail and summary.
    1. Determine the data that you do not want stored, such as:

      • Entire tables not used at your site

        For example, the detail accounting table does not need to be created if you summarize accounting data and only load summary data.

      • DDF data

        If your site does not use DDF or DRDA, you do not need to create any DDF tables or reports.

      • Package accounting data

        If your site does not run with accounting trace class 7/8 active, you do not need to create any package accounting tables.

      • Data collected by Performance Reporter that is not used in any Performance Reporter reports and that is not needed for special reports at your site

        For more information about the data that is used in the Performance Reporter reports, see the performance data tables section of the Working-with-reports.

      • Columns in Performance Reporter tables that are meaningful only if data sharing is used at your site
      • Columns in Performance Reporter tables not used for reporting at your site
      • COMMENT ON statements for Performance Reporter columns
      • CREATE INDEX columns that are not used in any sample reports, but do not delete the CREATE INDEX for the index ending in RIDX that uses the ROWID column
    2. Using the samples that start with a DPCT prefix (TABLE CREATE members), remove the following items:

      • Unwanted tables

        Remove the utility statements that create the table, table space, and table index, and grant SELECT ACCESS to that table to PUBLIC.

        Warning

        Note

        The member names must also be removed in the DPJCREAT or DPJCRELP sample JCL (see Step 4).

        Later in this procedure, you will also remove the member names in the DPJCREAT or DPJCRELP sample JCL.

      • Columns not used in any distributed report

        Each member contains a column name, DMRAUTOCUST, that marks the beginning of optional columns for that table. BMC AMI Ops configuration uses this column to delete the remaining column definitions if you request only the columns used in the distributed reports.

      • Data sharing and global locking columns

        Each member contains a column name, DMRACSHARE, that marks the beginning of data sharing and global locking columns for that table. BMC AMI Ops configuration uses this column to delete the columns up to the DMRAUTOCUST column definition if you request to exclude data sharing columns.

      • Long name columns

        Each member contains a column name, DMRAUTOLONG, that marks the beginning of long name columns for those columns that also have short name equivalents in that table. If you choose to exclude long name columns, BMC AMI Ops configuration deletes the columns following the DMRAUTOLONG column.

      • Unwanted columns

        Remove the control statement that creates the column name. Do not delete the ROWID columns.

        Warning

        Note

        If you remove a column name, you must also remove the corresponding COMMENT ON statement.

        This table customization applies to all tables, both detail and summary.

        Warning

        Note

        New column names are not supported. While it is possible to remove existing columns, it is not possible to add new ones.

      • COMMENT ON statements

        Remove any or all COMMENT ON statements. BMC AMI Ops configuration can optionally remove all COMMENT ON statements for you.

      • Unused CREATE INDEX columns

        Each member has a CREATE INDEX statement with index column names. Index column names after DATETIME are not used in any sample Performance Reporter reports and may be removed if not used by any special reports at your site. Any index column names preceding and including DATETIME must be kept to avoid processing of duplicate keys in Performance Reporter. BMC AMI Ops configuration can optionally remove unused CREATE INDEX statement column names for you.

        Warning

        Note

        If you want to create only the summary statistics tables that are new for the latest version of BMC AMI Ops Monitor for Db2, edit the DPJCREAT or DPJCRELP job (see Step 4) and remove references to all of the other tables. You must then run the DPJALTER job to update the other tables.

    3. Examine the space allocations performed by each DPCSxxxx sample for compatibility with the volumes you plan to use.
    4. COMPRESS YES is specified. Review for applicability in your environment.
    5. To create segmented table spaces instead of simple table spaces, uncomment the line that contains the SEGSIZE clause and replace the 0 with a valid segment size value.

  4.  Execute the CREATE statements that you generated in Step 3.Edit member DPJCREAT (IBM LOAD or BMC AMI Load) and run it on the same Db2 system where the objects are to be created:

    DPJCREAT

    a two-step allocation job

    The first step allocates a spin file that is required by the DPRSMF job, which extracts Db2 SMF records and loads the data into the Performance Reporter tables. If you do not use BMC AMI Ops configuration to install BMC AMI OpsM for Db2, you will need to create the spin file by using the JCL in the BBSAMP member DPRSPIN.

    The second step defines the storage group, database, table spaces, tables, and indexes that are used to store the performance data. DPJCREAT also grants SELECT authority to PUBLIC.

    1. Add your job statement.
    2. Change the HIDP parameter to the high-level qualifier of your BMC Software product libraries.
    3. Verify that the HIDB2 parameter is the correct prefix for your Db2 library names.
    4. Specify the UNIT and VOL parameters. These parameters are used to allocate a small permanent data set used by the SMF extractor.
    5. Submit the job.
    Warning

    Note

    You cannot migrate Performance Reporter version 11 data into newly created version 12 tables. If you want to use existing Performance Reporter version 11 tables, see To update existing Performance Reporter tables to a newer version

To update existing Performance Reporter tables to a newer version

  1. Verify that the maintenance for the previous version of Performance Reporter is up-to-date.That is, if you want to update version 11 tables to version 12 tables, ensure that the maintenance for Performance Reporter version 11 is up-to-date.
  2. Run the DPJALTER job to change the TABLEVERSION, and any other structure changes that might have been updated due to maintenance.You are provided with a populated set of Performance Reporter tables that you can maintain with version 12 of DPRDSMF.

Related topic

Setting-up-performance-batch-reports

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*

BMC AMI Ops Monitor for Db2 12.2