CA Datacom/DB's Accounting Facility enables you to accumulate statistics on system use for programs running under the MUF. You can use these statistics for accounting, billing, tuning, and monitoring security. You select the statistics you want to accumulate by defining accounting columns for a user-specified tables. CA Datacom/DB writes the use-statistics to the tables. You can access the tables using CA Dataquery.
You can use CA Dataquery interactively to select, join, sort, total, report graph the accounting data. You can also use CA Dataquery to purge selected rows. One method of simplifying the purge routines is to specify either month or year in the row definition and use a specific date as the selection criteria for deletion.
Defining the Accounting Tables
You and the CA Dataquery Administrator should meet to determine what statistics you want to keep. Then, you must define the accounting tables according to the description in the CA Datacom/DB Database and System Administration Guide.
The UIDnn accounting column can be used to capture detailed information about CA Dataquery use. The format of the User Information Block is as follows:
|
Column |
Offset |
Length |
Information |
|---|---|---|---|
|
UID01 |
1 |
3 |
DQ, unless CA Dataquery diagnostics are on, then it is $$$. |
|
UID04 |
4 |
8 |
(Character column.) The user accounting code from the DQU row. Assigned during user authorization. |
|
UID12 |
12 |
15 |
(Character column.) The name of the currently executing query, if any. This name may not be entirely accurate if, for example, a query has been fetched from the library, modified, and then executed. In this situation, the statistics reflect the modified query, and not the one on the library. |
|
UID27
|
27 |
1 |
(Binary column.) The processing stage code. The codes are as follows: 0 General processing - not query execution 1 FIND statement processing 2 SET statement processing 3 SORT statement processing 4 PRINT/DISPLAY statement processing |
|
28 |
3 |
Reserved. |
|
|
UID31
|
31 |
1 |
(Binary column.) The CA Dataquery system indicator code as follows: 0 Online 1 Batch |
|
32 |
1 |
Reserved |
The UIDnn column is defined as described in the CA Datacom/DB Database and System Administration Guide, where nn is the starting position relative to 01. Both CA Ideal and CA Dataquery use the UIDnn column. To avoid collecting data that you do not want, use a conditional statement when you define your table in CA Datacom Datadictionary.
You can use the Accounting Facility to monitor performance. To do so, you set up accounting tables to monitor the activities on your system. With the Accounting Facility, you can monitor such things as:
To use the Accounting Facility to monitor performance, you must follow the procedures discussed in the CA Datacom/DB Database and System Administration Guide. The following is an example of how you may use the Accounting Facility to monitor your performance.
Identifying a Problem Job
First, you want to look to see if you have a problem job. You can create an accounting table with the following:
To collect data on CA Dataquery jobs only.
To collect data on a particular user or group of users.
To collect data on a particular job.
To collect data about the physical I/Os.
To collect data about the logical I/Os.
To collect data about the number of requests issued to CA Datacom/DB
You can use CA Dataquery to report on the data collected.
Identifying the Problem Stage of a Job
If you decide that a particular job may be a problem, you can set up additional accounting tables to analyze that job. You can set up an accounting table to identify what stage of the job is causing the problem. This accounting table may include the following:
To collect data on CA Dataquery jobs only.
To collect data on a particular user or group of users.
To collect data on a particular job.
To collect data on a particular stage of a job.
To collect data about the physical I/Os.
To collect data about the logical I/Os.
To collect data about the number of requests issued to CA Datacom/DB
Identifying Reasons for CBS Optimizer Choices
Also, you can set up an accounting table to get detailed reasons why the Compound Boolean Selection Facility Optimizer has chosen a particular action. This accounting table may include following:
To collect data on CA Dataquery jobs only.
To collect data on a particular user or group of users.
To collect data on a particular job.
To collect data on a particular stage of a job.
To collect data about the Compound Boolean Selection Facility Optimizer reasons.
Identifying Scheduling Problems
If you think you may be having a scheduling problem, you can set up an accounting table to monitor how much wait time a job experiences. This accounting table may include the following:
To collect data on CA Dataquery jobs only.
To collect data on a particular user or group of users.
To collect data on a particular job.
To collect data on a particular stage of a job.
To collect the start date.
To collect the start time.
To collect the elapsed time between the time MUF receives the request and the time the request is returned to the user.
To collect the amount of time the job had to wait due to exclusive control, I/O, and so on.
Reporting the Accounting Data
You can create a meaningful report of the accounting data by creating a query that produces a report. See the CA Dataquery User Guide or CA Dataquery Reference Guide for more information.
Accounting Example
The Accounting Facility can provide a great deal of useful information about CA Dataquery usage. For example, suppose that you wanted to know the number of CA Datacom/DB requests generated by queries and how many actual I/O events that were needed to complete these requests. It would also be useful for this information to be grouped and ordered by query name and user name.
Following is an example of such a query and the resulting report. In it, the CA Dataquery User Table (DQU) is related to the Accounting Table by the CA Dataquery Accounting Code UIB04. Because of this relationship you can trace collected statistics to the actual CA Dataquery user that caused the activity to take place.
The following panel shows the text of the DQL query that was executed.
----------------------------------------------------------------------------- DATAQUERY: QUERY TEXT QUERY NAME: ACCOUNTING-SAMP ----------------------------------------------------------------------------- FIND ALL CAI-ACT-A04 RECORDS WITH UIB01 = 'DQ' RELATED BY UIB04 VIA ACCOUNTING-CODE TO DATAQUERY-DQU SORT BY (USER-NAME) CAI-ACT-A04 UIB12 PRINT TITLE1 'DATAQUERY USAGE STATISTICS' TITLE2 'DB REQUESTS AND I/O BY QUERY BY USER' DATAQUERY-DQU USER-NAME 'USER' CAI-ACT-A04 UIB12 'QUERY NAME' (REQS) 'DB /REQUESTS' PIC 'ZZZZZZZZZ9-' (EXCPS) 'DB /IOS' PIC 'ZZZZZZZZZ9-'
The following shows a sample of the report resulting from the execution of the ACCOUNTING-SAMP query.
May 5, 2000 DATAQUERY USAGE STATISTICS PAGE 1
16:31:05 DB REQUESTS AND I/O BY QUERY BY USER DETAIL
DB DB
USER QUERY NAME REQUESTS IOS
-------------------------------- --------------- ------------ ------------
CAI-INSTALL 4029 2471
CAI-PRINT-ITEMS 10 0
CAI-PRINT-RECPT 10 0
DBIO 460 28
DQ-ACCT-REPORT 3996 202
SAMPLE 106 12
TOTAL USER CAI-INSTALL
8611 2713
BOB TEST-BOB 629 47
TOTAL USER BOB
629 47
BUN CAI-PRINT-ACCTS 1002 70
CAI-PRINT-SALES 536 28
TOTAL USER BUN
1538 98
FULTON DBIO-PER-USER 4203 162
TOTAL USER FULTON
4203 162
GSMITH CAI-PRINT-CUST 1411 50
CAI-PRINT-ORDER 692 29
GEH-PAYROLL 5598 715
GEHTX 199 32
HARRIS 64 9
REQS-PER-FIND 960 46
RWH-TEST1 271 18
TOTAL USER GSMITH
9195 899
|
Copyright © 2014 CA.
All rights reserved.
|
|