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

Teradata RDBMS Database Administration - NCR

NCR Teradata RDBMS Database Administration - NCR , 2004. - 616 p.
Download (direct link): teradatadatabaseadmin2004.pdf
Previous << 1 .. 166 167 168 169 170 171 < 172 > 173 174 175 176 177 178 .. 218 >> Next

In addition, the data in every DBQL table is protected with FALLBACK. This means that the data is always available unless two or more AMPs in the same clique fail simultaneously. (For details, see "AMP Clustering and Fallback" on page 7-32.)

Warning: Because dictionary tables are permanent, the contents of all logs remain until they are explicitly deleted. When you use DBQL, be sure to delete the rows from the logs on a regular basis to avoid running out of PERM space in DBC. For instructions, see "Maintaining The Logs" on page 14-28. (For instructions on checking available DBC space, see "Permanent Space Availability" on page 3-3.)

14 - 10 Teradata RDBMS Database Administration

Chapter 14: Tracking Processing Behavior with the Database Query Log (DBQL)

Logging Implementation and Validation

Maintaining The Logs

You can access the contents of the DBQL logs, but the object definitions and the remaining objects are protected as follows:

No user, including DBC and SystemFE, can access the DBQLAccessMacro or the DBQLRuleTbl or DBLRuleCountTbl.

No user can modify the DBQLAccessMacro or alter, drop, or update any of the DBQL tables.

You cannot delete rows from the DBQLRuleTbl or the DBQLRuleCountTbl, because these are manipulated by BEGIN QUERY LOGGING and END QUERY LOGGING processing.

However, when logged on as user DBC or SystemFE, you can delete rows in the DBQL logs (DBQLogTbl, DBQLObjTbl, DBQLSummaryTbl, DBQLSQLTbl, and DBQLStepTbl). This is necessary so you can control their size.

Note: Empty the DBQL logs as often as possible. Query logging consumes DBC PERM space and the rows remain in the DBQL tables until you remove them. Even though logging is not designed as a standard operation to be performed against all users all the time, the tables will fill up very quickly if you are tracking, for example, target objects for a large group of users.

When you are ready to use DBQL on your production queries, follow this procedure:



Create and maintain an executable BTEQ script file to submit (or keep a copy of) your final BEGIN QUERY LOGGING statements in case a Sysinit is ever required (for disaster recovery or to migrate to a new NCR platform).

After the database has been restored, you can start a BTEQ session and run the script to easily repopulate the DBQLRuleTbl and rebuild your rules cache.

Set up a regular schedule to periodically perform the following maintenance (for a list of all the DBC logs that need to be maintained on a regular basis, see "Maintaining Your Data Dictionary Logs and Tables" on page 4-15):



Regularly summarize the data you want to retain permanently for analysis and planning. You can save it in a user database, external flat files, applications, or BTEQ reports, and so forth. (Also, you can use Teradata Manager; see Teradata Manager User Guide.)

Then submit the statement DELETE * FROM DBQLtablename; on each of the DBQL log tables (excluding DBQLRuleCountTbl and DBQLRuleTbl) to empty them and free up DBC PERM space.

14 - 10

Teradata RDBMS Database Administration




b Chapter 15:

Tools for Managing Resources

This chapter discusses tools that are useful in administering your Teradata RDBMS in such a way as to minimize the occurrence of impeded performance, maximize throughput, and manage the consumption of resources.

Implemented through client software interfaces and SQL, several tools are provided with which you can:

Control I/O frequency

Maintain a well-behaved environment through increased control of:

Index efficiency

Query workload management

Account priority scheduling and resource allocation

More accurately predict the size of expansion

IF you want to . THEN use .
manage resource utilization through priorities and limits TDQM and Priority Scheduling to: Manage workload throughput Customize the delegation of resources among the various workload processes Achieve optimum balance of workload size and density
plan capacity needs relative to workload growth DBC.AMPUsage, ResUsage reports, and Teradata Performance Monitor to understand current and projected: Resource usage Performance trends for the purpose of accurately: Predicting the need for expansion Sizing additional hardware

Teradata RDBMS Database Administration

13 - 1 Chapter 15: Tools for Managing Resources

Managing I/O with Cylinder Read

Managing I/O with Cylinder Read

The Cylinder Read feature allows retrieval operations to run more efficiently by reading a list of cylinder-resident data blocks with a single I/O operation. This reduces I/O overhead from once per data block to once per cylinder. This can significantly reduce the time it takes to do a full-table scan of large tables.

A data block is a disk-resident structure that contains one or more rows from the same table and is the smallest I/O unit for the Teradata file system. Data blocks are stored in physical disk sectors or segments, which are grouped in cylinders.
Previous << 1 .. 166 167 168 169 170 171 < 172 > 173 174 175 176 177 178 .. 218 >> Next