# Compaq ACMS for OpenVMS
Managing Applications

Previous | Contents | Index

## 15.5.4 Creating a Customized Report

You can create customized reports that provide information on specific events and items. The /OPTIONS qualifier to the REPORT command allows you to specify characteristics for individual events and items. Example 15-3 shows the command to generate a report which uses a different report format for each event.

Example 15-3 Using Reporting Options to Generate a Customized Report

    $ COLLECT REPORT MY_DATABASE -
            /SINCE = "01-JAN-1991" -
            /WIDTH = 80 -
            /TYPE = SUMMARY -
            /LENGTH = 66 -
            /OUTPUT = ACMS.REPORT -
            /STATISTICS = ALL -
            /TITLE = "ACMS Reports" -
            /OPTIONS

    EVENT EXCHANGE_STEP -
            /FACILITY = ACMS -
            /TYPE = FREQUENCY -
            /INTERVAL = SECONDS -
            /SUBTITLE = "ACMS Exchange Step Frequency Report"

    EVENT PROCEDURE_CALL -
            /FACILITY = ACMS -
            /GROUP_BY = (PROCEDURE_INDEX) -
            /STATISTICS = (MINIMUM, MAXIMUM, MEAN, STANDARD_DEVIATION) -
            /SUBTITLE = "ACMS Procedure Call Summary Report"

    EVENT PROCEDURE_CALL -
            /FACILITY = ACMS -
            /GROUP_BY = (PROCEDURE_INDEX) -
            /TYPE = DETAIL -
            /SUBTITLE = "ACMS Procedure Call Detail Report"
    ITEM PROCEDURE_INDEX /WIDTH = 9
    ITEM PROCEDURE_INDEX /REPORT_HEADER = "Procedure Index"
    RESTRICTION COLLECTION ACMS_COLL
    RESTRICTION EPID 2A8002DF,2A8002C1
    RESTRICTION IMAGE PAYROLL, INVENTORY
    RESTRICTION NODE ACMS1, ACMS2

Data from the MY_DATABASE formatted database is used for this report. The initial report command modifies several of the default qualifier values, such as the length of each report page and the date before which all data should be ignored. The /OPTIONS qualifier allows you to specify event and item qualifiers to either override the main qualifiers or provide additional restrictions to the report.

Based on the first part of the REPORT command, all subreports, unless otherwise specified, contain data from the data collection file with a timestamp date greater than or equal to January 1, 1991. The report is 80 columns wide and 66 lines long. The subreports are Summary Reports with all possible statistics displayed. The report is written to a file called ACMS.REPORT in your current directory.

The first subreport is based on the EXCHANGE_STEP event. The facility, a required qualifier, is ACMS. The type qualifier is overridden to generate a Frequency Report with a count displayed for each second during which at least one event occurrence was recorded. A subtitle, "ACMS Exchange Step Frequency Report," is displayed on the first page of the subreport. The following example shows the REPORT options required for the first subreport:

    EVENT EXCHANGE_STEP -
            /FACILITY = ACMS -
            /TYPE = FREQUENCY -
            /INTERVAL = SECONDS -
            /SUBTITLE = "ACMS Exchange Step Frequency Report"

The second subreport is a Summary Report based on the PROCEDURE_CALL event. Again, the facility name, ACMS, is a required qualifier. The statistics are divided into groups based on equivalent values of the PROCEDURE_INDEX item. The statistics type has been overridden from a default of ALL to include only MINIMUM, MAXIMUM, MEAN, and STANDARD DEVIATION. A subtitle is given to help distinguish the subreport from other sections of the main report. The following example shows the REPORT options required for the second subreport:

    EVENT PROCEDURE_CALL -
            /FACILITY = ACMS -
            /GROUP_BY = (PROCEDURE_INDEX) -
            /STATISTICS = (MINIMUM, MAXIMUM, MEAN, STANDARD_DEVIATION) -
            /SUBTITLE = "ACMS Procedure Call Summary Report"

The third subreport is a Detail Report based on the PROCEDURE_CALL event. The report is ordered by the PROCEDURE_INDEX item in ascending order. The item width is modified to use 9 columns. The report header is changed to "Procedure Index." In the report, "Start" is added to the beginning of the item header by DECtrace to signify that the value is based on the start event. DECtrace uses end event values for duration events if the item was collected on the end event. In this case, the PROCEDURE_INDEX item is only collected on the start event. If this had been a point event, the item header would not have been changed.

The RESTRICTION option allows you to create a very specific report. In this case, only data from nodes ACMS1 and ACMS2, for collection ACMS_COLL, from processes 2A8002DF and 2A8002C1, and from images PAYROLL and INVENTORY is displayed. The following example shows the REPORT options required to generate the third subreport:

    EVENT PROCEDURE_CALL -
            /FACILITY = ACMS -
            /GROUP_BY = (PROCEDURE_INDEX) -
            /TYPE = DETAIL -
            /SUBTITLE = "ACMS Exchange Step Detail Report"
    ITEM PROCEDURE_INDEX /WIDTH = 9
    ITEM PROCEDURE_INDEX /REPORT_HEADER = "Procedure Index"
    RESTRICTION EPID 2A8002DF,2A8002C1
    RESTRICTION IMAGE PAYROLL, INVENTORY
    RESTRICTION NODE ACMS1, ACMS2

If many samples of data were generated, and you have interactive SQL or interactive RDO on your system, you can speed up report generation by adding an index to the PROCEDURE_CALL relation. The SQL syntax to do this is:

    SQL> CREATE INDEX MY_CLIENT_PC_INDEX ON
    SQL> EPC$1_253_PROCEDURE_CALL(IMAGE_RECORD_ID,
    SQL> COLLECTION_RECORD_ID, PROCEDURE_INDEX_START);

If you have interactive RDO, the syntax is:

    RDO> DEFINE INDEX MY_INDEX FOR EPC$1_253_PROCEDURE_CALL
    RDO> TYPE IS SORTED.
    RDO> IMAGE_RECORD_ID ASCENDING.
    RDO> COLLECTION_RECORD_ID ASCENDING.
    RDO> PROCEDURE_INDEX ASCENDING.
    RDO> END MY_INDEX INDEX.
    RDO> COMMIT;

Note that adding an index causes a delay in merging another data collection file into this formatted database, and the index does not help if you want to use GROUP_BY with a different set of items. You can drop the index before merging the databases by entering either of the following sets of commands:

    SQL> DROP INDEX MY_INDEX;
    SQL> COMMIT;

or

    RDO> DELETE INDEX MY_INDEX;
    RDO> COMMIT;

See the DECtrace documentation for a layout of the formatted database.

Example 15-4 shows the report generated by the commands described in Example 15-3. The final page of the report contains an index for the report.

Example 15-4 Sample DECtrace Customized Report Based on ACMS Data

    12-APR-1991 08:13             ACMS Reports                             Page 1
    Selection: DEBIT_CREDIT_SEL                                   DECtrace V1.0-0

    Event:  EXCHANGE_STEP   In Facility:  ACMS            Version:  V3.2-0

                          ACMS Exchange Step Frequency Report

    Time Period          Occurrences

    17-SEP-1990 15:30:33           1
    17-SEP-1990 15:30:45           1
    17-SEP-1990 15:30:48           1

    12-APR-1991 08:13             ACMS Reports                             Page 2
    Selection: DEBIT_CREDIT_SEL                                   DECtrace V1.0-0

    Event:  PROCEDURE_CALL  In Facility:  ACMS            Version:  V3.-0

                          ACMS Procedure Call Summary Report

    Proc Idx : 1

               Elapsed  BUFFERED IO    CPU TIME  CURREN    DIRECT IO   PAGEFAULTS
                                                 T PRIO

    Minimum       0.30            0           6       7            6            1
    Maximum       0.47            0           7       7            8            2
    Mean          0.25         0.00        4.33    4.66         4.66         1.00
    Std Dev       0.23         0.00        3.78    4.04         4.16         1.00

               PAGEFAULT     Proc Idx      VIRTUAL    GLOBAL WS   PRIVATE WS
                     IOs                      SIZE

    Minimum            0            1         9679          775         1407
    Maximum            2            1         9679          776         1407
    Mean            0.66         0.66      6452.66       517.00       938.00
    Std Dev         1.15         0.57      5588.17       447.73       812.33

                 WORKING
                 SET SIZ

    Minimum         4096
    Maximum         4096
    Mean         2730.66
    Std Dev      2364.82

                   ================= Grand Total ====================

               Elapsed  BUFFERED IO    CPU TIME  CURREN    DIRECT IO   PAGEFAULTS
                                                 T PRIO

    Minimum       0.30            0           6       7            6            1
    Maximum       0.47            0           7       7            8            2
    Mean          0.25         0.00        4.33    4.66         4.66         1.00
    Std Dev       0.23         0.00        3.78    4.04         4.16         1.00

               PAGEFAULT     Proc Idx      VIRTUAL    GLOBAL WS   PRIVATE WS
                     IOs                      SIZE

    Minimum            0            1         9679          775         1407
    Maximum            2            1         9679          776         1407
    Mean            0.66         0.66      6452.66       517.00       938.00
    Std Dev         1.15         0.57      5588.17       447.73       812.33

    12-APR-1990 08:13             ACMS Reports                             Page 3
    Selection: DEBIT_CREDIT_SEL                                   DECtrace V1.0-0

                 WORKING
                 SET SIZ

    Minimum         4096
    Maximum         4096
    Mean         2730.66
    Std Dev      2364.82

    12-APR-1991 08:13             ACMS Reports                             Page 4
    Selection: DEBIT_CREDIT_SEL                                   DECtrace V1.0-0

    Event:  PROCEDURE_CALL  In Facility:  ACMS            Version:  V3.2-0
      For Collections:  ACMS_COLL
      For Nodes:  ACMS1, ACMS2
      For Images:  PAYROLL, INVENTORY
      For EPIDs:  2A8002DF, 2A8002C1

                            ACMS Procedure Call Detail Report

    Timestamp                  Elapsed

    17-SEP-1990 15:30:33.24       0.47

    17-SEP-1990 15:30:33.71

    Start Appl Spec

    DBCR_ALL_APP

    BUFFERED IO     CPU TIME  CURREN    DIRECT IO   PAGEFAULTS    PAGEFAULT
                              T PRIO                                    IOs

             59          340       8          231         1778           84

             59          347       7          239         1780           86

        Start
    Procedure
        Index

            1

    Start Server Name

    DBCR_ALL_SERVER_01

    Start Step Name

    DBCR_PROCESSING

    Start Sub Name

    SYSTEM

    Start Task Name

    DBCR_ALL_TSK_DBCR

        VIRTUAL    GLOBAL WS   PRIVATE WS      WORKING
           SIZE                                SET SIZ

           9679          769         1405         4096

           9679          775         1407         4096

    12-APR-1990 08:13             ACMS Reports                             Page 5
    Selection: DEBIT_CREDIT_SEL                                   DECtrace V1.0-0

    Timestamp                  Elapsed

    17-SEP-1990 15:30:45.71       0.30

    17-SEP-1990 15:30:46.01

    Start Appl Spec

    DBCR_ALL_APP

    BUFFERED IO     CPU TIME  CURREN    DIRECT IO   PAGEFAULTS    PAGEFAULT
                              T PRIO                                    IOs

             59          347       8          239         1780           86

             59          353       7          245         1781           86

        Start
    Procedure
        Index

            1

    Start Server Name

    DBCR_ALL_SERVER_01

    Start Step Name

    DBCR_PROCESSING

    Start Sub Name

    SYSTEM

    Start Task Name

    DBCR_ALL_TSK_DBCR

        VIRTUAL    GLOBAL WS   PRIVATE WS      WORKING
           SIZE                                SET SIZ

           9679          775         1407         4096

           9679          776         1407         4096

    12-APR-1990 08:13                    Index                             Page 6
    Selection: DEBIT_CREDIT_SEL                                   DECtrace V1.0-0

    Report Index

    Facility Name                Event Name                        Page

    ACMS                         EXCHANGE_STEP                     1
    ACMS                         PROCEDURE_CALL                    2
    ACMS                         PROCEDURE_CALL                    4

# 15.6 ACMS Database Relations

This section describes the event-data relations in the formatted database for the ACMS ALL collection class. This information is provided so that you can write customized reports based on data in the formatted database.

Table 15-9 shows the APL_RESPONSE relation.

Table 15-9 Columns for Table EPC$1_253_APL_RESPONSE
Column Name | Data Type | Domain
--- | --- | ---
COLLECTION_RECORD_ID | SMALLINT | COLLECTION_RECORD_ID_DOMAIN
IMAGE_RECORD_ID | INTEGER | IMAGE_RECORD_ID_DOMAIN
CONTEXT_NUMBER | INTEGER | CONTEXT_NUMBER_DOMAIN
TIMESTAMP_START | DATE VMS |
TIMESTAMP_END | DATE VMS |
APPL_SPEC_START | VARCHAR(39) |
APPL_SPEC_START_STR_ID | INTEGER | STR_ID_DOMAIN
TASK_NAME_START | VARCHAR(31) |
TASK_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
APL_USERNAME_START | VARCHAR(12) |
APL_USERNAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
SUB_USERNAME_START | VARCHAR(12) |
SUB_USERNAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
EXCH_STEP_NAME_START | VARCHAR(31) |
EXCH_STEP_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
CROSS_FAC_14_START | INTEGER |
CROSS_FAC_14_END | INTEGER |

Table 15-10 shows the APL_RESPONSE_ST relation. An index is provided for this relation. It is defined with column STR_ID, duplicates are allowed, and the type is sorted.

Table 15-10 Columns for Table EPC$1_253_APL_RESPONSE_ST
Column Name | Data Type | Domain
--- | --- | ---
STR_ID | INTEGER | STR_ID_DOMAIN
SEGMENT_NUMBER | SMALLINT | SEGMENT_NUMBER_DOMAIN
STR_SEGMENT | VARCHAR(0) |

Table 15-11 shows the COMPRESSED_MSG relation.

Table 15-11 Columns for Table EPC$1_253_COMPRESSED_MSG
Column Name | Data Type | Domain
--- | --- | ---
COLLECTION_RECORD_ID | SMALLINT | COLLECTION_RECORD_ID_DOMAIN
IMAGE_RECORD_ID | INTEGER | IMAGE_RECORD_ID_DOMAIN
CONTEXT_NUMBER | INTEGER | CONTEXT_NUMBER_DOMAIN
TIMESTAMP_POINT | DATE VMS |
ORIGINAL_SIZE | INTEGER |
COMPRESSED_SIZE | INTEGER |
COMPRESSN_RATIO | INTEGER |
MESSAGE_SRC | VARCHAR(15) |
MESSAGE_SRC_STR_ID | INTEGER | STR_ID_DOMAIN
MESSAGE_DEST | VARCHAR(15) |
MESSAGE_DEST_STR_ID | INTEGER | STR_ID_DOMAIN

Table 15-12 shows the COMPRESSED_MSG_ST relation. An index is provided for this relation. It is defined with column STR_ID, duplicates are allowed, and the type is sorted.

Table 15-12 Columns for Table EPC$1_253_COMPRESSED_MSG_ST
Column Name | Data Type | Domain
--- | --- | ---
STR_ID | INTEGER | STR_ID_DOMAIN
SEGMENT_NUMBER | SMALLINT | SEGMENT_NUMBER_DOMAIN
STR_SEGMENT | VARCHAR(0) |

Table 15-13 shows the EXCHANGE_STEP relation.

Table 15-13 Columns for Table EPC$1_253_EXCHANGE_STEP
Column Name | Data Type | Domain
--- | --- | ---
COLLECTION_RECORD_ID | SMALLINT | COLLECTION_RECORD_ID_DOMAIN
IMAGE_RECORD_ID | INTEGER | IMAGE_RECORD_ID_DOMAIN
CONTEXT_NUMBER | INTEGER | CONTEXT_NUMBER_DOMAIN
TIMESTAMP_START | DATE VMS |
TIMESTAMP_END | DATE VMS |
APPL_SPEC_START | VARCHAR(39) |
APPL_SPEC_START_STR_ID | INTEGER | STR_ID_DOMAIN
TASK_NAME_START | VARCHAR(31) |
TASK_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
APL_USERNAME_START | VARCHAR(12) |
APL_USERNAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
SUB_USERNAME_START | VARCHAR(12) |
SUB_USERNAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
STEP_NAME_START | VARCHAR(31) |
STEP_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
REQ_FORM_NAME_START | VARCHAR(31) |
REQ_FORM_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
DEVICE_NAME_START | VARCHAR(8) |
DEVICE_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
SEND_ID_START | VARCHAR(31) |
SEND_ID_START_STR_ID | INTEGER | STR_ID_DOMAIN
RECEIVE_ID_START | VARCHAR(31) |
RECEIVE_ID_START_STR_ID | INTEGER | STR_ID_DOMAIN
CROSS_FAC_14_START | INTEGER |
CROSS_FAC_14_END | INTEGER |

Table 15-14 shows the EXCHANGE_STEP_ST relation. An index is provided for this relation. It is defined with column STR_ID, duplicates are allowed, and the type is sorted.

Table 15-14 Columns for Table EPC$1_253_EXCHANGE_STEP_ST
Column Name | Data Type | Domain
--- | --- | ---
STR_ID | INTEGER | STR_ID_DOMAIN
SEGMENT_NUMBER | SMALLINT | SEGMENT_NUMBER_DOMAIN
STR_SEGMENT | VARCHAR(0) |

Table 15-15 shows the FORMS_ENABLE relation.

Table 15-15 Columns for Table EPC$1_253_FORMS_ENABLE
Column Name | Data Type | Domain
--- | --- | ---
COLLECTION_RECORD_ID | SMALLINT | COLLECTION_RECORD_ID_DOMAIN
IMAGE_RECORD_ID | INTEGER | IMAGE_RECORD_ID_DOMAIN
CONTEXT_NUMBER | INTEGER | CONTEXT_NUMBER_DOMAIN
TIMESTAMP_START | DATE VMS |
TIMESTAMP_END | DATE VMS |
APPL_SPEC_START | VARCHAR(39) |
APPL_SPEC_START_STR_ID | INTEGER | STR_ID_DOMAIN
APPL_NODE_START | VARCHAR(15) |
APPL_NODE_START_STR_ID | INTEGER | STR_ID_DOMAIN
SUB_USERNAME_START | VARCHAR(12) |
SUB_USERNAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
DEVICE_NAME_START | VARCHAR(8) |
DEVICE_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
REQ_FORM_NAME_START | VARCHAR(31) |
REQ_FORM_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
CROSS_FAC_14_START | INTEGER |
CROSS_FAC_14_END | INTEGER |

Table 15-16 shows the FORMS_ENABLE_ST relation. An index is provided for this relation. It is defined with column STR_ID, duplicates are allowed, and the type is sorted.

Table 15-16 Columns for Table EPC$1_253_FORMS_ENABLE_ST
Column Name | Data Type | Domain
--- | --- | ---
STR_ID | INTEGER | STR_ID_DOMAIN
SEGMENT_NUMBER | SMALLINT | SEGMENT_NUMBER_DOMAIN
STR_SEGMENT | VARCHAR(0) |

Table 15-17 shows the FORMS_REQUEST relation.

Table 15-17 Columns for Table EPC$1_253_FORMS_REQUEST
Column Name | Data Type | Domain
--- | --- | ---
COLLECTION_RECORD_ID | SMALLINT | COLLECTION_RECORD_ID_DOMAIN
IMAGE_RECORD_ID | INTEGER | IMAGE_RECORD_ID_DOMAIN
CONTEXT_NUMBER | INTEGER | CONTEXT_NUMBER_DOMAIN
TIMESTAMP_START | DATE VMS |
TIMESTAMP_END | DATE VMS |
APPL_SPEC_START | VARCHAR(39) |
APPL_SPEC_START_STR_ID | INTEGER | STR_ID_DOMAIN
APPL_NODE_START | VARCHAR(15) |
APPL_NODE_START_STR_ID | INTEGER | STR_ID_DOMAIN
SUB_USERNAME_START | VARCHAR(12) |
SUB_USERNAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
DEVICE_NAME_START | VARCHAR(8) |
DEVICE_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
TASK_NAME_START | VARCHAR(31) |
TASK_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
EXCH_STEP_NAME_START | VARCHAR(31) |
EXCH_STEP_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
REQ_FORM_NAME_START | VARCHAR(31) |
REQ_FORM_NAME_START_STR_ID | INTEGER | STR_ID_DOMAIN
SEND_ID_START | VARCHAR(31) |
SEND_ID_START_STR_ID | INTEGER | STR_ID_DOMAIN
RECEIVE_ID_START | VARCHAR(31) |
RECEIVE_ID_START_STR_ID | INTEGER | STR_ID_DOMAIN
CROSS_FAC_14_START | INTEGER |
CROSS_FAC_14_END | INTEGER |

Previous | Next | Contents | Index