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:
- Start Warehouse Manager Client.
- Double-click on the AS/400 data source.
- From the Manage menu, select Auditing. The following dialog box will appear:
- 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)
- 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)
- Select Start Auditing. Once auditing starts, the Start Auditing button changes to Stop Auditing.
- 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:
- Select Reports | Audit Queries and the specific query on which you want to gather data.
- After selecting the desired query, Query will prompt you for a login to your default data source.
- 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.
- Select Yes and then choose the data source that Auditing was run against.
- 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
