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 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.
If you use DBQL, Teradata recommends using only &D&H.
| Variable | Format | Description | Length |
|---|---|---|---|
| &D | YYMMDD | Date the system got the SQL request, which the system substitutes into the account string. | 6 |
| &H | HH (24 hour clock) | Hour the system got the SQL request, which the system substitutes into the account string. 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 is 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. Use the &L option to generate separate statistics and to monitor the LogonSource field of DBC.LogOnOffV. |
15 |
| &T | HHMMSS (24 hour clock) | Time of day the system got the SQL request, which the system substitutes into the account string. Using &T can be 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, which may cause the system to write a row for every SQL request. If the system gets 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 gets a multiple-statement 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 |