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 .. 63 64 65 66 67 68 < 69 > 70 71 72 73 74 75 .. 218 >> Next


It is important that you update DBC.Acctg on a regular basis to clear out values and reset the accumulators, especially if you use ASE codes, as they can cause the table to grow more quickly. (For details, see "Using Account String Expansion (ASE)" on page 5-27.) Also, it is important to keep a historical summary of AMPUsage data for the purposes of performance tuning and capacity planning.

Teradata RDBMS Database Administration

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

Maintaining Your Data Dictionary Logs and Tables

Therefore, use the following process to first save the data, then clear out DBC.Acctg and reset the counters:

Step Action

1 To control growth, at relatively short intervals use Teradata Manager (for

instructions, see Teradata Manager User Guide) to:

Step Action
a Summarize DBC.AMPUsage data
b Save the summaries to a history table
c Delete the detail rows from DBC.Acctg

2 At longer intervals, archive the entire pre-defined set of system tables, using ARC with an all-AMPs dump of database DBC. (For a list of the tables that are archived, see "Archiving the Data Dictionary" on page 8-23.)

Note: If you archived data using a tool other than ARC, such as FastExport or BTEQ, then ARC cannot reload it. If you cannot use the tool that created the archive to reload it, try the BTEQ IMPORT command.

3 After the accumulated AMP data is successfully summarized, and, if necessary, archived:

Reset the CPUTime and DiskIO counters to zero for all users. An example statement to accomplish this task is:

UPDATE DBC.AMPUsage SET CPUTime = 0 ,DiskIO = 0 ,ALL ;

For more information about clearing account values, see the Teradata RDBMS Data Dictionary.

Purging the System Logs

The system does not automatically purge The ResUsage log, the DBQL logs, or the tables underlying the AccessLog, LogOnOff, and Software_Event_Log views.

You or another authorized user, such as the security administrator or NCR field support personnel, should archive (as desired) and then delete information that is older than 60 to 90 days, or some interval that suits you, from the following:

DBC.ResUsage

DBC.AccLogTbl

DBC.DBQL logs,which include:

DBC.DBQLogTbl

DBC.DBQLObjTbl

4 - 16

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

Maintaining Your Data Dictionary Logs and Tables

DBC.DBQLSQLTbl

DBC.DBQLStepTbl

DBC.DBQLSummaryTbl

(The rules tables DBC. DBQLRuleTbl and DBC.DBQLRuleCountTbl are not part of your log maintenance list. These tables are automatically maintained by the Teradata SQL BEGIN/END QUERY LOGGING statements; an error is returned if you attempt to delete their contents.)

DBC. Event_Log

DBC.SW_Event_Log

QCD.DataDemographics (if you use QCF with the SQL COLLECT DEMOGRAPHICS statement, you need to explicitly delete rows from this table, DataDemographics, in your user-defined QCD database)

Note: Entries in DataDemographics are deleted automatically when you use the INSERT EXPLAIN WITH STATISTICS AND DEMOGRAPHICS statement. For more information, see "Teradata SQL Statements for Query Analysis" on page 13-6 and "COLLECT DEMOGRAPHICS" in Teradata RDBMS Reference, Volume 4.

Purging Expired Recovery Control (RC) Catalogs

It is a good idea to purge the following tables when the associated removable media is expired and overwritten:

DBC.RCEvent

DBC.RCConfiguration

DBC.RCMedia

Reducing the Size of DBC.AccessRights and DBC.Accounts

Although the Teradata RDBMS automatically maintains the following tables, you can reduce their size based on how you set up and grant rights to your users, roles, and profiles:

DBC.AccessRights table

DBC.Accounts table

For more information on how and when these tables are populated, see:

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

Chapter 6: "Controlling Access"

Teradata RDBMS Security Administration

Teradata RDBMS SQL Reference, Volume 1 and Volume 4

Correcting DBC.DataBasesSpace and DBC.DBase Values

As a result of very rare types of system failures, you might need to correct inconsistencies in the system tables DBC.DataBasesSpace and DBC.DBase. Use

Teradata RDBMS Database Administration

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

Maintaining Your Data Dictionary Logs and Tables

the Update DBC and Update Space utilities only when you need to perform these tasks.

The purpose and affect of each utility is introduced in the following paragraphs.

For instructions and more details on each utility, see Teradata RDBMS Utilities. For descriptions of the system tables and the views that access them, see Teradata RDBMS Data Dictionary.

Update Space Utility

The Update Space utility examines the storage descriptors and recalculates the current usage of permanent, temporary, and spool space used by:

A single database and its individual tables
Previous << 1 .. 63 64 65 66 67 68 < 69 > 70 71 72 73 74 75 .. 218 >> Next