Auditing is a function within Warehouse Manager Client that allows administrators to monitor query traffic. This function provides information that can help identify and optimize AS/400 usage. This article discusses how to Use the auditing function within Warehouse Manager Client.

Available audit information includes:

  • Users who are running queries.
  • ShowCase application used.
  • Job number and statement number.
  • Length of time a query ran and number of rows retrieved.
  • Library, file and member names used in query.
  • The text of the SELECT statement and whether a query used "arrival" method.
  • Whether a query used an existing index or a temporary index was created.
  • Whether an access path was rebuilt.
  • Whether a temporary file was used.
  • Whether a permanent index is recommended and, if so, on which key fields.

The audited information will use ShowCase audit "pre-canned" report information using Query/Report Writer. Auditing gives you the information you need to make decisions about how your company utilizes iSeries resources.

The ShowCase applications that can be audited are:

  • Query (including Modeler and Data View Manager)
  • Report Writer
  • Warehouse Builder
  • Warehouse Manager Client
  • Scheduled Report Server

The three steps to successful auditing are listed below..


STEP 1 - Run Auditing to gather data:

  1. Start Warehouse Manager Client.
  2. Double-click on the AS/400 data source.
  3. From the Manage menu, select Auditing. The following dialog box will appear:
  4. In the Auditing dialog box, specify a size and time limit of *NOMAX. (size and Time limit can be set to reduce the system usage)
  5. Specify whether you want to Append the resulting data to an existing file or Overwrite an existing file. (all users must be signed off to overwrite an existing file)
  6. Select Start Auditing. Once auditing starts, the Start Auditing button changes to Stop Auditing.
  7. The audit stops when the Time Limit is reached, the File Size Limit is reached, or the Stop Auditing button is selected.

Note: A profile with *ALLOBJ or *SECOFR authorities are required to run the auditing feature.


STEP 2 - Analyze the Auditing data:

ShowCase provides nine pre-built queries to assist in analyzing your data. These queries come in .DBQ and .RPT format. The sample queries will help you understand the following:

  • What information the end users are trying to gather.
  • What applications the end users are utilizing.
  • What suggestion(s) can be made to help your users run queries and reports more efficiently

To access the Audit Queries:

  1. Select Reports | Audit Queries and the specific query on which you want to gather data.
  2. After selecting the desired query, Query will prompt you for a login to your default data source.
  3. If your Audit is against a different data source, click Cancel and you will be asked if you want to choose a new data source.
  4. Select Yes and then choose the data source that Auditing was run against.
  5. Enter your login and password and the query will run and return the data to be analyzed.

Below are three different Audit Query Examples:

Example 1 - Queries which Build Temporary Indexes:

  • Displays Date/Time Query ran, User who submitted the job and Job Number assigned on AS/400.
  • Unique Statement Number, Index Advised, Fields indexes are advised on, Library and File names.
  • Reason for Temporary Index being used and Select Statement text (not displayed).

Note - The unique Statement Number in conjunction with the Job Number is used to compare the same Select Statement in different audit queries.

Example 2 - Query Governor:

  • Displays Date/Time Query ran, User who submitted the job and Estimated Processing Time.
  • Was Query Governor used (were interactive/batch limits set up for this user?)
  • Did Query Governor stop (did the time limits stop running of the query?)

Example 3 - Detailed Select Statement:

  • Displays Date/Time Query ran, User who submitted the job and Job Number assigned on AS/400.
  • ShowCase Application used, CPU time used, number of Rows Returned.
  • Full Select Statement, Library/Table used, Access Method, Join Position, Sub-select Number and Sub-select Nesting Level (items in last bullet not displayed in example).


STEP 3 - Using the Auditing Data:

The data collected can assist you in the following:

  • Better understanding what end users are trying to do:
    • Ensure library and table security is properly setup.
    • b. Do users have authority to the right applications?
    • c. Would data views benefit users?
  • Managing Resource Settings within Warehouse Manager Client:
    • a. Are users running in batch or interactively?
    • b. Should query limits be imposed?
    • c. Is the correct priority assigned?
  • Managing Indexes on the AS/400:
    • a. When should you use indexes?
    • b. Verify your indexes are being used.
    • c. Should permanent indexes be built?

 

Ref#: 1479857

 

 

 

 


Still have questions? We can help. Submit a case to Technical Support.

Last Modified On: April 21, 2017