AE DB Reporting Tool

The AE DB Reporting Tool utility (ucydbrt) provides evaluation reports about tasks within your AE system. The results depend on whether you query object contents (definitions), future executions (forecasts), or past executions (statistics). For example, you can list all jobs that were processed in a specific period, or were created by a specific user. Queries always refer to one client.

Important!

  • Extensive queries require time and affect performance negatively.
  • AutoForecast records are required for querying future executions, see Autoforecast.
  • AE script considers only lines in the Event Process page of time events which contain the ACTIVATE_UC_OBJECT script function.
  • If an Include object is not available in the client that should be queried, it is searched in system client 0.
  • Script variables and Variable objects are not queried.
  • Ensure that the general entry for agent specifications (such as <WIN>) is selected in objects when you set the host by using the :PUT_ATTscript statement. Otherwise, the result can be a distorted one because you cannot see in which objects the host was changed.
  • Time and date entries in the AE database comply with UTC time zone. For the evaluations, this data is converted to the time zone of the client in which the query is defined. If it does not include time-zone specifications, the system uses the time zone of client 0.

This page includes the following:

Creating Queries by Using the Reporting Tool GUI

This utility has a graphical user interface where you define and store your evaluation criteria called queries. You can define as many queries as you like. Once the queries are defined, you can start the utility in batch mode. Data is retrieved from the client in which the query is defined. CSV and HTML formats are available for your report files and you can specify a stylesheet of your choice for HTML which is integrated into the report file.

The graphical user interface of this utility is available on UNIX and Windows. Use the following command to start it: java -jar ./ucyrepg.jar

To Create Queries

  1. Select New query in the File menu or right-click the corresponding submenu item in the left window half. An assistant opens and guides you through the various definitions that can be made.
  2. Assign a significant query name and a client. A period must be assigned in forecasts and statistics. If you select the "Today" option, the utility uses the current date for creating the report. If you select "Now", the current date plus the time are used.
  3. Select the fields that should be shown in the evaluation report. Click the arrow buttons to sort the columns.
    Tip: To have columns automatically sorted, set the fixFieldOrder= parameter to 1 in the INI file.
  4. In the next dialog, you can specify the filter criteria that should be used as the basis for the evaluation.
    Tip: Specify the object type Job in the filter criteria if the fields that should be shown in the evaluation report occur in jobs. For example: Login object. The utility automatically inserts this filter criterion.
  5. Determine the report's name, the maximum number of lines that should be output and the output order. You can define the report's format (CSV or HTML), and in HTML you can even integrate a stylesheet.
  6. Click Finish to store your definitions.

Stored queries are listed in the left window area in their corresponding evaluation areas. You can change your definitions at any time by double-clicking a query. The right window then shows all entries where you can make your changes. You can even open several queries at a time. The tabs at the upper edge can be used to navigate between the individual form sheets.

Querying File Formats

The utility stores your queries in the "queries" subfolder as XML files. The file endings depend on the area that has been queried:

  • *.d.xml - definition
  • *.f.xml - forecast
  • *.s.xml - statistics

Example

The following example shows a query that returns a list of all a client's jobs that use a specific host (WIN01) in the evaluation report.

  1. Create a new query, assign a suitable name and select a client.
  2. Select the following fields that should be displayed in the output file: object name, object type, host and host type.
  3. Selection criteria: After selecting "Host" as a field in the evaluation report, the filter "Object type corresponds to JOBS" is automatically inserted. You can select a specific host by adding the criterion "Host corresponds to WIN01".
  4. Determine a name for the output file and the maximum number of lines.
  5. Call the utility in batch mode and indicate the created query file as a parameter to create the evaluation report.

Creating Evaluation Reports

The utility creates reports in batch mode. Call it from the command line by using the following parameters:

UCYBDBRT -Xquery file [-Cclient -IINI file-Llanguage -Rreference date -S-Ooutput file-Tfile type]

All parameters are optional, only the query file must be specified. For a list of all values, see AE DB Reporting Tool (UCYBDBRT.EXE).

In batch mode, this utility reads the information for the evaluation report directly from the AE database. You can specify the parameters for the database in the corresponding configuration file, see Utility DB Reporting Tool.

The following line shows an example. An evaluation report is created from the query that is defined for client 100 in the file jobtop10.d.xml.

UCYBDBRT -C0100 -XC:\AUTOMIC\Utilities\evaluation_reports\jobtop10.d.xml

The information that is provided in the report depends on the fields you defined in your query. The following fields are available for all the three evaluation areas, for definitions, forecasts and statistics, unless otherwise stated:

  • Archive 1
    First archive key
  • Archive 2
    Second archive key
  • Calendar in the workflow
    Name of the workflow's Calendar object. Available for definitions and forecasts
  • Calendar in the schedule
    Name of the superordinate Schedule's Calendar object
  • Calendar event in the workflow
    Name of the calendar event in the workflow. Available for definitions and forecasts
  • Calendar event in the schedule
    Name of the superordinate schedule's calendar event
  • Changed by
    Name of the User object. Available for definitions
  • Changed on
    Date of last object modification. Format: YYYY-MM-DD [HH:[MM:[SS]]]. Available for definition
  • Created by
    Name of the User object. Available for definitions
  • Earliest start time
    Earliest start time in the format YYYY.MM.DD HH:MM:SS.
  • End time
    End time in the format HH:MM:SS. Available for statistics
  • Expected end
    Expected end time in the format YYYY.MM.DD HH:MM:SS. Available for forecasts
  • Expected runtime
    Expected runtime in seconds.
  • Expected start time
    Expected start time in the format YYYY.MM.DD HH:MM:SS. Available for forecasts
  • Host
    Name of the agent in jobs
  • Host type
    Job type. Allowed values:
    • BS2000
    • JMX
    • MVS
    • NSK
    • OS400
    • R3
    • UNIX
    • VMS
    • WINDOWS
  • Int. account
    Internal account
  • Interval
    Interval of the Time-Event object. Available for definitions and statistics
  • Job content
    Text contained in the script. Available for definitions and forecasts
  • Workflow
    Name of the superordinate workflow
  • Last used on
    Date of last object usage. Format: YYYY-MM-DD [HH:[MM:[SS]]]. Available for definitions
  • Login object
    Name of the Login object in jobs
  • Login: user
    Login information of the Login object in jobs
  • Object name
    Name of the object
  • Object type
    Short form of the object type (such as JOBP)
  • Output device (R3_ACTIVATE_REPORT)
    Name of the output device
  • Report (R3_ACTIVATE_REPORT)
    Name of the report.
  • Return code
    Return code. Available for statistics
  • RunID
    Number of the execution. Available for statistics
  • Runtime
    Duration of the execution in seconds. Available for statistics
  • SAP client
    Client in SAP in Login objects of jobs
  • SAP user
    Name of the SAP user in Login objects of jobs
  • Schedule
    Name of the superordinate Schedule object
  • Start time in the schedule
    Start time in the format HH:MM:SS
  • Start time
    Start time in the format HH:MM:SS. Available for statistics
  • Status
    • For filter criteria: One or more system return codes are separated by commas (for example, 1900, 1920)
    • In the analysis report: The long texts are output instead of the numbers (for example, ENDED_OK).
      • Available for forecasts and statistics
  • Time event
    Name of the TIME Event object. Available for definitions and statistics.
  • Title
    Title of the object
  • Type of parent object
    Object type of the parent object
  • Variant (R3_ACTIVATE_REPORT)
    Name of the variant
  • Version
    Version number of the object. Available for definitions and statistics

XML Files of Queries

Queries are stored in the "queries" sub-folder in the form of XML files that the utility generates. No manual adjustment is required for these entries that include the following parameters:

  • <?xml version="1.0" encoding="UTF-8" standalone="no"?>
    XML Declaration
  • <QUERY version="Automation Engine version">
    Automation Engine version of the utility which created the query file
  • <file css="style sheet" type="format">report file</file>
    Evaluation report
    • "style sheet" = directory and name of the style sheet that should be integrated in the HTML file
    • "format" = file format of the report
      Allowed values: "HTML" and "CSV"
    • Report file = directory and name of the file in which the evaluation information is written. The directory must be an existing one.
  • <client>client</client>
    Client in which the evaluation should be made
  • <max_rows>lines</max_rows
    Maximum number of lines the report can have. Allowed values: "1" - "99999"
  • <source>query type</source>
    Evaluation area. Allowed values:
    • DEFINITION
    • FORECAST
    • STATISTIC
  • <reference>
    Period for statistical and forecasting evaluations
  • <date>date</date>
    Date in the format YYYY-MM-DD or the particular value TODAY. If TODAY is used, the utility uses the latest date for report creation.
  • <offset>time range</offset>
    Number of hours to be added to the date (forecasts) or subtracted from it (statistics). Therefore, this value includes an algebraic sign (+/-).
  • </reference>
    Ending of the XML element <reference>
  • <selection>
    Beginning of the elements about evaluation criteria
  • <include name="criterion" value="value" />
    The individual evaluation criteria are listed as a value pair
  • </selection>
    Ending of the XML element <selection>
  • <output order="order" sort="sorting">
    Beginning of the elements for the output columns in the report
    • "order" - ascending or descending order
      Allowed values: "ascending" and "descending"
    • "sorting" - column determining how elements are sorted
  • <field name="column" />
    Output columns to be included in the report
  • </output>
    Ending of the XML element <output>
  • </QUERY>
    Ending of the XML element <QUERY>

Example of a Query File

<?xml version="1.0" encoding="UTF-8" ?>
<QUERY version="12.1.0">
<file type="CSV">Output_SAPjobs.html</file>
<client>0098</client>
<max_rows>1000</max_rows>
<source>DEFINITION</source>
<selection>
<include name="OBJECT_TYPE" value="JOBS" />
<include name="JOB_TYPE" value="R3" />
<include name="OBJECT_NAME" value="*T01*" />
</selection>
<output order="ascending" sort="OBJECT_NAME">
<field name="OBJECT_NAME" />
<field name="LOGIN" />
<field name="SAP_CLIENT" />
<field name="SAP_USER" />
<field name="MODIFIED_DATE" />
<field name="LAST_USE_DATE" />
</output>
</QUERY>

See also: