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 .. 59 60 61 62 63 64 < 65 > 66 67 68 69 70 71 .. 218 >> Next

• Password attributes defined at the system level in DBC.SecurityDefaults (see "Customizing Your Password Controls" on page 6-21)

Note: If you re-create a profile with the same name after it was dropped, users defined for that profilename will use the profile parameters at the next logon. The effect of a re-I creation is not immediate.

4 - 4

Teradata RDBMS Database Administration 4 - 13 Chapter 4: Using Data Dictionary Tables and Views

Protected Data Dictionary Tables

This information is not used by the software, but is maintained in DBC.DBase for historical purposes. Many of the system views return data stored in DBase.

When you precede your DDL or DCL statement with the EXPLAIN modifier, you can view the updates to the data dictionary tables.

The EXPLAIN modifier is a helpful function that allows you to understand what happens when you execute an SQL statement:

• The statement is not executed

• The processing is described, including sorts, use of indexes and spool files, how many response rows are estimated, and so forth

• The type of locking used, and on what objects, is described For example:

EXPLAIN CREATE TABLE DBA01.Department ( Department_Number SMALLINT ,Department_Name CHAR(3 0) NOT NULL ,Budget_Amount DECIMAL (10,2) ,Manager_Employee_Number INTEGER ) UNIQUE PRIMARY INDEX (Department_Number) ;

Explanation:

1. First, we lock DBA01.Department for exclusive use.

2. Next, we lock a distinct DBC."pseudo table" for write

on a RowHash for deadlock prevention, we lock a distinct DBC."pseudo table" for read on a RowHash for deadlock prevention, we lock a distinct DBC."pseudo table" for write on a RowHash for deadlock prevention, and we lock a distinct DBC."pseudo table" for write on a RowHash for deadlock prevention.

3. We lock DBC.AccessRights for write on a RowHash, we

lock DBC.TVFields for write on a RowHash, we lock DBC.TVM for write on a RowHash, we lock DBC.DBase for read on a RowHash, and we lock DBC.Indexes for write on a RowHash.

4. We execute the following steps in parallel:

•We do a single-AMP ABORT test from DBC.DBase by way of the unique primary index.

•We do a single-AMP ABORT test from DBC.TVM by way of the unique primary index with no residual conditions.

Teradata RDBMS Database Administration

4 - 13 Chapter 4: Using Data Dictionary Tables and Views

Protected Data Dictionary Tables

• We do an INSERT into DBC.TVFields (no lock required). •We do an INSERT into DBC.TVFields (no lock required). •We do an INSERT into DBC.TVFields (no lock required). •We do an INSERT into DBC.TVFields (no lock required). •We do an INSERT into DBC.Indexes (no lock required). •We do an INSERT into DBC.TVM (no lock required). •We INSERT default privileges to DBC.AccessRights for DBA01.Department.

5. We create the table header.

6. Finally, we send out an END TRANSACTION step to all

AMPs involved in processing the request. No rows are returned to the user as the result of statement 1.

For more information on using EXPLAIN, see Teradata RDBMS SQL Reference, Volume 6.

4 - 6

Teradata RDBMS Database Administration 4 - 13 Chapter 4: Using Data Dictionary Tables and Views

Dictionary Views and Tables for the Administrator

Dictionary Views and Tables for the Administrator

A comprehensive suite of pre-defined views return the most critical columns of the dictionary tables. (For complete details of each system view, including column names, values, output format, and underlying system table, see Teradata RDBMS Data Dictionary.)

The SQL CREATE VIEW statements are supplied in a BTEQ script called DIPVIEW. Typically, these are run and the views created during installation. If not, you can execute this script on an ad hoc basis by running the Database Initialization Program (DIP) utility. (See Appendix E: "Database Initialization" and the chapter titled "DIP Utility" in Teradata RDBMS Utilities).

Note: An exception is the DBC.IdCol table. Because this table only has a few columns, no view is defined for it. User DBC has SELECT privilege directly on the table. If another user needs to access that table, such as your administrative user, log on as DBC and explicitly GRANT SELECT ON DBC.IdCol to that user.

Most of the dictionary views reference more than one table. They also allow you to limit access to data dictionary information and provide a consistent image of the data stored in the data dictionary. Most views also record the time of each action (for details, see "Data Dictionary Timestamp" on page 4-13).

Because system views . They serve to ...
• Re-title columns • Reduce the number of columns returned to those of normal interest • Format columns clarify the dictionary tables and avoid reporting unnecessary details.
• Join related dictionary tables • Select and project relevant rows and columns simplify the generation of meaningful responses and reports.
make direct access to the underlying dictionary tables unnecessary protect the dictionary tables from inadvertent corruption by unauthorized users.
Previous << 1 .. 59 60 61 62 63 64 < 65 > 66 67 68 69 70 71 .. 218 >> Next