ASE can be used to implement a simple charge back utility. It provides a way to determine how much system CPU time and disk activity is consumed by a user each day. By adding a few special characters to a user's account name, you can extract detailed information from system tables about what that user has done. Vantage expands these special characters into such things as the session number, request number, date or time when the account name is written to a system table.
At the completion of each SQL step, Vantage always updates the DBC.Acctg table with statistics about the request. These statistics include the total CPU time in seconds and the number of logical disk I/Os used by the request. This statistical information is summarized by adding it to an existing row that contains the same username and account name.
Configuration
The following example modifies the account string of each user you want to track. You preface each account string with the text CB&D. You can add any additional account information after these four characters if you wish.
When you add a date to the account name, the account name effectively changes each day and a new row is written to the DBC.Acctg table. This row contains the total number of CPU seconds and total number disk I/Os for each request that was submitted on that date.
The &D is an ASE token that expands to the current date in the format YYMMDD. You can use CB to indicate that the account is being tracked for charge back.
You can modify an existing account string for a user using the following SQL command:
MODIFY USER JANETJONES AS ACCOUNT = ('CB&D');
Example
SELECT ACCOUNTNAME, USERNAME, SUM(CPUTIME), SUM(DISKIO) FROM DBC.AMPUSAGE WHERE SUBSTR(ACCOUNTNAME, 1, 2) = 'CB' GROUP BY USERNAME, ACCOUNTNAME ORDER BY USERNAME, ACCOUNTNAME;
Result:
*** Query completed. 11 rows found. 4 columns returned. *** Total elapsed time was 2 seconds. AccountName UserName Sum(CpuTime) Sum(DiskIO) -------------- ------------- ------------------ --------------- CB060902 JANETJONES 1,498.64 3,444,236 CB060903 JANETJONES 934.23 1,588,764 CB060904 JANETJONES 883.74 924,262 CB060905 JANETJONES 214.99 200,657 CB060902 JOHNSMITH 440.05 396,338 CB060903 JOHNSMITH 380.12 229,730 CB060904 JOHNSMITH 112.17 184,922 CB060905 JOHNSMITH 56.88 99,677 CB060902 SAMOREILLY 340.34 410,178 CB060903 SAMOREILLY 70.74 56,637 CB060902 WEEKLY 3,498.03 7,311,733
If we wanted to charge $0.25 per CPU second and bill for the month of September 2006, we could use the following query to generate the bill:
SELECT USERNAME, SUM(CPUTIME)*0.25 (FORMAT '$$ZZZ,ZZZ,ZZ9.99') FROM DBC.AMPUSAGE WHERE SUBSTR(ACCOUNTNAME, 1, 6) = 'CB0609' GROUP BY 1 ORDER BY 1 WITH SUM(CPUTIME)*0.25 (FORMAT '$$ZZZ,ZZZ,ZZ9.99', TITLE 'Grand Total:');
Result:
*** Query completed. 4 rows found. 2 columns returned. *** Total elapsed time was 2 seconds. UserName (Sum(CpuTime)*0.25) ------------------------------ ------------------- JANETJONES $882.90 JOHNSMITH $247.33 SAMOREILLY $102.77 WEEKLY $874.51 ------------------- Grand Total: $2,107.51
Charge Back and Overhead
From a CPU perspective charge back entails very little overhead. The accounting table is already being updated at the completion of each statement. The only cost is the creation of a new row in the table for each user each day. From a space perspective, the accounting table will grow by one row for each user each day. Periodic cleanup can constrain this growth.