Charge Back - Advanced SQL Engine - Teradata Database

Database Administration

Product
Advanced SQL Engine
Teradata Database
Release Number
17.05
17.00
Published
June 2020
Language
English (United States)
Last Update
2021-01-22
dita:mapPath
rgu1556127906220.ditamap
dita:ditavalPath
lze1555437562152.ditaval
dita:id
B035-1093
lifecycle
previous
Product Category
Teradata Vantageâ„¢

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');
Workload performance characters ($R00, $H00, $M00, $L00), if used, must be the first characters in the account string.

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;

*** 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:');
*** 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.