Books
in black and white
Main menu
Share a book About us Home
Books
Biology Business Chemistry Computers Culture Economics Fiction Games Guide History Management Mathematical Medicine Mental Fitnes Physics Psychology Scince Sport Technics
Ads

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 74 75 76 77 78 79 < 80 > 81 82 83 84 85 86 .. 218 >> Next


5 - 40 Teradata RDBMS Database Administration

Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

System Accounting Functions

ASE Considerations and Restrictions

Enabling ASE

The information collected when using ASE can be very helpful in analysis, but you should take care not to create a bigger problem than you are trying to solve.

For this reason, NCR recommends that you do not use the &T variable for OLTP applications, with TPump, or in a default account string.

Some key points to remember when using ASE are:

You must determine the measurement rate you need and the users you wish to monitor.

Each different user/account string or profile/account string pair results in a new row being inserted in AMPUsage.

Collection activity occurs on all AMPs involved with the request.

Performance impact of ASE can vary greatly depending upon granularity requested and the types of requests submitted.

Be sure to summarize and save history rows as needed and then clear the DBC.Acctg accumulators (see "Example 2: Resetting DBC.Acctg Counters with DBC.AMPUsage" on page 5-38)

The following sections discuss how to enable ASE and code the ASE variables, and give examples of ASE usage and some information on how ASE interacts with certain Teradata RDBMS utilities. For more discussion on the performance impact of ASE, see "Usage Rules for ASE Variables" on page 5-31 and Teradata RDBMS Performance Optimization.

ASE is an optional feature. To enable it, you define one or more of the ASE variables in an account string, either directly in the user definition, or in the definition of the profile assigned to the user.

Examples

You assign two account IDs to user TAW. The first entry is the default.

MODIFY USER TAW ACCOUNT=('&D&Hacct4 0 7','acct4 0 7');

If TAW logs on using the default account ID, date and hour monitoring occur for each SQL request. At query execution time, Teradata replaces &D with the current date, and replaces &H with the current hour.

To turn off monitoring, TAW must do one of the following:

Enter 'acct4 07' each time he logs on

Change the account during a session with:

SET SESSION ACCOUNT='acct4 0 7';

5 - 40

Teradata RDBMS Database Administration Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

Defining Accounts

In another case, you assign two account IDs to user DTG. However, the first (default) account ID does not specify date and hour expansion:

MODIFY USER DTG ACCOUNT = ('acct101', '&D&Hacct101');

If DTG logs in using the default account ID, date and hour logging do not occur. To initiate them, DTG must type &D and &H in an account ID string, either when he logs in or during the session:

.logon DTG, mypassword, '&D&Hacct101'

SET SESSION ACCOUNT='&D&Hacct101'

In another case, assume you omit the ASE variables in the two account IDs for user AM1:

MODIFY USER AM1 ACCOUNT=('acct101', 'acct102'); Therefore, AM1 cannot invoke the ASE feature at any time.

Coding ASE Variables

ASE substitution variables in the user account string generate values for system date, system time, and other information. The values are substituted for the variables at logon and/or query execution time.

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 30 characters. The position of a variable within the account definition indicates whether the expanded value will be truncated, as noted in the variable table.

If you do not want truncation of ASE values, position the ASE codes before any of the account ID characters (for example, '$L&D&Hacct17024').

The ASE substitution variables are as follows:

For this variable . The system inserts into the account string the . The format of the value is .
&D date the SQL request was received. Note: If &D is in position 26 or higher of the account definition, truncation occurs. You can take advantage of this truncation to monitor resources on a yearly or monthly basis. YYMMDD
Teradata RDBMS Database Administration 5 - 29 Chapter 5: Setting Up Users, Profiles, Accounts and Accounting

System Accounting Functions

For this variable . The system inserts into the account string the . The format of the value is .
&H hour of day the SQL request was received. Note: If &H is in position 30 of the account definition, truncation occurs. 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. HH (24 hour clock)
&I logon host ID, current session number, and sequential request number. LLLLSSSSSSSSSRRRRRRRRR
&L logon timestamp. The system establishes the value inserted into DBC.AMPUsage at logon time. This value does not change until the user logs off and then logs on again. Note: If &L is in position 17 or higher of the account definition, truncation occurs. 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.LogOnOff. YYMMDDHHMMSS.hh
Previous << 1 .. 74 75 76 77 78 79 < 80 > 81 82 83 84 85 86 .. 218 >> Next