The Post describes these initial administrative tasks,
- Logging in to the operating system
- Setting up the database administrator user
- Creating a spool reserve database
- Setting up Teradata Administrator
- Establishing Viewpoint alerts
- Changing the Password Expire parameter
Logging in to the Operating System
Teradata Database administrators who need access to system nodes should log on to the operating system as a user who is a member of the tdtrusted group or as the root user
- Root access is required to start Teradata Database and install new versions. Users can perform all other Teradata administrative functions, including switching to a new Teradata version once it is installed, as a member of the tdtrusted group
- The tdtrusted group is created automatically at installation of Teradata Database. Tdtrusted access allows nearly all command-line monitoring and administration of Teradata. The advantages of using tdtrusted include greater security because root access is limited and greater accountability because administrative user actions are recorded under each user account rather than as the root user.
- Non-root accounts that administer Teradata must be members of the tdtrusted group on all Teradata Database nodes. Each customer site should determine how to implement this within site-specific account management policies. Tdtrusted logons rely on standard UNIX account management facilities.
When you first install Teradata Database, it has only one user. This user is called DBC, and from it all other future databases and users in the system are created.
User DBC also initially owns all the space in the entire system. As you create new databases and users, any permanent space you grant them is subtracted from available permanent space in user DBC. Because all space ultimately comes from user DBC, user DBC owns all database objects.
User DBC contains data like a database, but unlike a database it has a password. The usable (PERM) disk space in DBC initially reflects the entire system hardware capacity, minus space for the following system users, databases, and objects:
- All the space available for your databases, users, journals, data tables, indexes, stored procedures, functions, and temporary tables. DBC owns all unallocated space.
- Crashdumps user
- SysAdmin user
- Sys_Calendar database
- TD_SYSFNLIB database
- SQLJ, SYSLIB, and SYSUDTLIB databases for external routines and user-defined types.
- SYSSPATIAL database for geospatial data types. For details, seeSQL Geospatial Types.
- DBCExtension database for Global and Persistent (GLOP) sets.
- SystemFE user for field engineers
- TDPUSER user
- The system Transient Journal (TJ) which stores the before-change image of every data row involved in a transaction, the row ID of every inserted row, and other control records used by the system to automatically recover data from transaction aborts or to complete transactions.
- The system catalog tables of the Data Dictionary and the suite of user-accessible views defined on those tables
Caution:Never alter the privileges for user DBC. Changing DBC privileges may cause installation, upgrade, maintenance, or archive procedures to end abnormally and consequently require Teradata Customer Support to correct the problem.
Set Up the Teradata Administrator GUI
From the principal administrative client, access Teradata Administrator, clickStart>Programs>Teradata Administrator.
- ThePlease Select a Data Source dialog box appears. Do the following:
- In the left field, highlight the alias name for your system.
- In theRoot Database field enter DBC.
- InWhat to Load, select All Databases and Users and click OK.
In the Teradata Database Connect dialog box do the following:
- In the Username field enter DBC.
- In the Password field, enter the user DBC password provided by your Teradata Customer Service representative.
- Do not change other fields. ClickOK.
Set Preview Mode as the default so that SQL statements generated from using the GUI fields and controls will appear in the Query Window before the statement is executed.
- From the Teradata Administrator menu, clickTools>Options.
- Click the Confirmation
- Check the Preview SQL before submitting check box and click OK.
Note: Setting Preview Mode as the default is optional, but it will help new Teradata Database users to become familiar with Teradata Database SQL statements. You can send generated SQL without previewing it at any time by unchecking the Preview SQL before submitting check box.
SET UP THE DATA DIRECTORY FOR TERADATA ADMINISTRATOR
The Teradata Administrator data directory stores generated SQL for later reference and use. The default location is: Documents and SettingsNetwork_UserIdMy DocumentsTeradataTeradata ManagerData.
If you can use the default location, skip this procedure.
If you want to change the location, use the following procedure to relocate the data directory.
- From the Teradata Administrator main window, clickTools>Options.
- Click theDirectory
Note the following directory display fields:
- Old Path– The current path to the data directory
- New Path– The new path to the data directory
Navigate to the desired directory.
- The directory path changes automatically in theNew Path
- ClickOK to save your path changes and exit the dialog box.
Setting Up the Database Administrator User
Create the principal database administrator, user SYSDBA (or the name your site uses), and grant space and privileges to the user before proceeding with database implementation. User SYSDBA can then create databases, tables, users, and other objects in the space it owns.
Creating User SYSDBA
From an administrative client, log on to Teradata Administrator as user DBC.
- Create the database administrator as follows:
- From the menu, clickTools>Create>User.
- Specify values for the following fields and controls:
The Query Window pops up and displays the generated CREATE USER statement, for example:
CREATE USER "SYSDBA" FROM "DBC"
AS PERM = 2e5 * (hashamp() + 1);
PASSWORD = "temp"
SPOOL = spool_space
PROFILE = DBADM
STARTUP = ''
NO BEFORE JOURNAL
NO AFTER JOURNAL
Note: Some options appear in the SQL that are not specified in the procedure. These are system defaults and should remain as they are. For example Before Journal and After Journal are checked No by default.
Creating the SYSDBA Profile
Profiles determine certain system resources available to member users, and can also set password control parameters.
- From an administrative client, log on to Teradata Administrator as user DBC.
Create the profile for the database administrator, user DBADMIN:
- From the menu, clickTools>Create>Profile.
- Specify values for the following fields and controls:
CREATE PROFILE "sysdba" AS
ACCOUNT=('$H-DBC-ADMIN,' account__str2',' account_str3')
PASSWORD = password(EXPIRE=90,
GRANTING DATABASE PRIVILEGES TO USER SYSDBA
- Log on to Teradata Administrator as user DBC.
- Grant object access privileges to system tables and other objects owned by user DBC.
- From the main window, clickTools>Grant/Revoke>Object Rights.
The Query window appears and displays the generated GRANT statement:
GRANT EXECUTE, SELECT ON "DBC" TO "SYSDBA" WITH GRANT OPTION.
Creating a Spool Reserve Database
- Teradata strongly recommends that you give sufficient space under user DBC to be used as spool. There are two ways to do this: set aside permanent space to remain unused or create an empty database to hold the space. The advantage of allocating space under DBC to be used for spool is that the space will also be available for other system uses. However, there is also a risk that you may forget to maintain this reserved space and inadvertently assign it to other users and databases for PERM space usage. This could then result in a physical out-of-spool condition if there is no spool space left when processing queries.
- An alternative is to create a database under DBC and allocate permanent space to be unused to hold the reserved spool space. Assigning permanent space to DBC to be permanently unused is reserving this physical space for spool usage. The advantage of creating a database to hold spool space means that space will always be set aside and available for spool usage.
- A specific example of why it is important to reserve space for DBC is the use of spool space by system tables. Spool limits are allocated for each user or profile and actual space must be available for spool usage when a query is being processed. Sometimes, however, system tables like Transient Journal are allowed to use more space than is available in DBC. When DBC tries to use more space than it has, the system prevents new logons and refuses any data definition language SQL statements. This happens because logons and DDL statements require the system to add additional rows to DBC tables.
- To avoid this situation, reserve plenty of space in DBC. This is even more necessary if you are using access logging or database query logging because the tables produced can use up a lot of space. Monitor the space usage for the logging functions often and regularly.
- Be sure to not only allocate enough space to DBC but also to regularly clean up old tables by deleting rows from them or archiving them instead if you need the data for later.
- To create a spool reserve database, submit a CREATE DATABASE statement and specify the amount of space you want to keep in reserve as the PERM parameter.
- Do not create objects or store data in this database. As long as the reserve database remains empty, its PERM allocation remains available for use as spool space.
- For example, assume you created an administrative user named DBADMIN. Since the space of this user is under your control, you can use it to create a child database named Spool_Reserve that never contains tables, as follows:
- Log on to Teradata Database as user DBC.
- Submit the following statement:
- CREATE DATABASE Spool_Reserve FROM DBADMIN AS PERM = n ;
- where n is the number of bytes that is a specific percentage you have decided is appropriate.
- Quit the session and log off.
GUIDELINES FOR RESERVING MINIMUM SPOOL SPACE
- Teradata Database dynamically allocates available permanent space as spool space when necessary. To make sure that reducing permanent space does not impact transaction processing, Teradata recommends that you reserve permanent space for spool requirements.
- At minimum, the average uncompressed and non-fallback protected database should reserve 40% of space relative to CURRENTPERM. (For example, if CURRENTPERM is 100 GB, spool space will be 40 GB for a total MAXPERM of 140 GB.)
- You may increase the reserve as needed depending on your actual workload. Use the following table to help you estimate your spool space requirements. Note that the percent of spool space suggested is relative to CURRENTPERM.
USING VIEWPOINT ALERTS
- Before setting up Viewpoint alerts, make sure you have:
- Installed a Teradata Viewpoint server and connected it to your system.
- Enabled the RSS data collection system on your Teradata system.
- Created the Teradata Viewpoint server logon user (viewpoint) in the database and given it the privileges necessary to logon and execute database monitoring and management functions.
- Configured at least one administrative user to access and administer Teradata Viewpoint and enabled the Viewpoint data collectors.
You can configure Teradata Viewpoint to perform alert actions when the database achieves or exceeds certain defined thresholds. You can specify the following alert actions individually or in any combination:
- Write a message to the alert log
- Send an email message to a specified address
- Communicate the alert to a specified third-party application
- Run a specified BTEQ script or other program
Each alert action set has a unique name and is triggered according to the rules for any alert that specifies the action set name.
A Teradata Viewpoint alert is based on:
- A threshold specified for a monitoring parameter, for example, when a database exceeds 95% space usage.
- The time period during which the parameter must be over threshold to invoke the alert.
- The name of an Action Set, for example, the alert notification, which defines who should receive the alert, and how the message is delivered.
- A message that the system delivers to the list of users defined in the Action Set, if the threshold and time criteria are met.
To configure a sample alert:
- From the Teradata Viewpoint main portal, selectAdmin>Teradata Systems.
- From the Setup menu, selectAlerts, and then configure an alert, for example, a System Health alert that sends a message if the system is down, similar to the following example.
- Check theEnabled box and click Apply.
Setting Up Alert Actions
The alert setup defines alert notification parameters and alert actions.
- From the main Teradata Viewpoint portal, selectAdmin>Setup.
- UseDelivery Settings/Email to configure communication with the host SMTP email server.
- Select the Teradata system for which you are configuring the alert.
- UseAlerts Presets/Action Sets to set up common alert actions, for example, an email message to all administrative users. Then you can set up monitoring parameters that invoke the associated alert action whenever Teradata Viewpoint detects that a parameter was exceeded.
Note: Specifying one or more roles in the action sends an email to each role member and is the most efficient way of identifying email recipients. You can list email addresses for any recipients that are not Teradata Viewpoint users.
SETTING UP TERADATA VIEWPOINT ALERTS FOR SPACE USAGE
You can use Teradata Viewpoint to regularly monitor space usage, and catch most space problems. However, it may be useful to setup a few alerts in areas where space problems can seriously affect database function.
The following table contains some examples of common space usage alerts.
|Alert||Setup Parameters||Response to Alert Occurrence|
|Crashdumps SpaceThe crashdumps database needs enough space to contain possible large data dumps that occur during crashes, and which are used in system recovery.||
Alert rules:•Only include databases = crashdumps•Current Perm Max% > 70Note: The threshold is set low because a crashdump can be very large.
•Do not run twice in 5 minutes
|•Add perm space to the crashdumps database.•Purge the crashdumps database of unneeded data and back up needed data to external media.|
|DBC SpaceUser DBC contains all system tables and logs. Critical system functions depend on DBC having enough space.Note: You can set the same alert parameters for DBCMNGR and SystemFE, which perform other critical system functions.||
Alert rules:•Only include databases = DBC•Current Perm Max% > 80Note: Use 90% for SystemFE.
•Do not run twice in 5 minutes
|Add perm space to the database.|
|Set up three alerts to identify when a database exceeds important space thresholds.•All DB 90%•All DB 95%•All DB 97%||
Alert rules:•All databases•Current Perm Max%
> percentAlert Action:•Email (Pager for critical alerts)•Do not run twice in 5 minutes
Severity: (apply one of the following to the related alert)
|You should allocate additional perm space to a database as soon as possible after perm space usage exceeds 90%.Databases that trigger the High or Critical alert levels before you can respond to a Medium alert are growing very fast and should receive more space immediately.You can investigate which tables are using database perm space.|