The system resolves these variables at logon or at SQL statement execution time. For example, $M00MARB&D becomes $M00MARB170704 for a logon at July 4, 2017, under the MAR batch (B) account.
The ASE variables may be used in any combination and in any order, subject to the constraints on length and position. These are:
- The maximum length of an account string is 128 characters.
- The increasing levels of granularity that result when additional rows are written to the DBC.Acctg table.
There is a minor increase in overhead associated with managing the frequency with which the DBC.Acctg table is cleared.
Teradata recommends that if you use DBQL, you should only use &D and &H jointly (&D&H).
| Variable | Format | Description | Length |
|---|---|---|---|
| &D | YYMMDD |
Date. The system substitutes the date it received the SQL request into the account string. |
6 |
| &H | HH (24 hour clock) | Hour. The system substitutes the hour of day it received the SQL request. This is useful for identifying the large resource users during peak periods. In charge back systems, use &H to give users preferential billing rates for running queries during off-peak time periods. If you use the &H variable without the &D variable, the system sums statistics collected for a specified hour on one day with existing statistics for the same hour on other days. |
2 |
| &I | LLLLSSSSSSSSSRRRRRRRRR | The system substitutes the logon host ID, current session number, and sequential request number. All the queries within one stored procedure CALL command are reported under one request number. The request number is the client request number, which is the request of the CALL.
If you need more detail about queries within a stored procedure, the information will be available in DBC.DBQLogTbl.RequestNum. |
22 |
| &L | YYMMDDHHMMSS.hh | Logon date-time substitution. The system substitutes the logon timestamp. This value does not change until the user logs off and then logs on again. Because there is only one logon string for each session pool, the &L option generates only one row per session, regardless of the number of users connected to the pool. If a group of users share user IDs and passwords, the system accumulates all DBC.AMPUsage statistics under the same user ID. In this case, use the &L option to generate separate statistics and to monitor the LogonSource field of DBC.LogOnOffV. |
15 |
| &T | HHMMSS (24 hour clock) | Time substitution. The system substitutes the time of day it received the SQL request. Using &T can be very resource intensive. If you notice an impact on system performance, delete rows from DBC.Acctg and discontinue using &T.
This variable allows for one second granularity, causing the system to write a row for virtually every individual SQL request. If the system receives two or more SQL requests for the same user/account ID pair in the same second, the system sums AMP usage statistics. This summation can be any combination of subsecond requests, or a combination of subsecond requests with a longer request. If the system receives a multistatement request, each individual SQL statement in the request has the same timestamp; therefore, the row written to DBC.AMPUsage contains the summation of the statistics of the individual statements. Using the &T variable without the &D variable causes the system to sum statistics collected for a specified time on one day with existing statistics for the same time on other days. |
6 |