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 .. 55 56 57 58 59 60 < 61 > 62 63 64 65 66 67 .. 218 >> Next

SHOWFSP command Reports all or specified data tables that will reach a Free Space Percentage limit after PACKDISK is run. (See "Gaining Space with PACKDISK" on page 3-19.)

3 - 12

Teradata RDBMS Database Administration Chapter 3: Space Considerations

Monitoring Disk Space Activity

Tools Parameter Description
System views (to underlying DBC.DataBaseSpace system table) DBC.DiskSpaceX Returns AMP information at the database level about disk space usage (including spool) for any database or account owned by the requesting user.
DBC.TableSizeX Returns AMP information at the table level about disk space usage (excluding temporary and spool) for any table or account owned by the requesting user.
DBC.AllSpace Returns AMP information about disk space usage (including spool) for any database, table, or account, at the database level plus the table level.

Resetting Peak Values in DBC.DataBaseSpace

Each of the system views listed above references the non-hashed DBC.DatabaseSpace table. From time to time, you need to clear out the peak values accumulated in the DBC.DataBaseSpace table. These values must be reset to zero to restart the data collection process.

Teradata RDBMS provides the DBC.ClearPeakDisk macro to reset the PeakPerm and PeakSpool values in the DBC.DataBaseSpace table. To review the definition of ClearPeakDisk, enter:

SHOW MACRO DBC.ClearPeakDisk ;

Teradata RDBMS returns the contents of the CREATE MACRO DDL:

REPLACE MACRO ClearPeakDisk AS ( UPDATE DatabaseSpace SET PeakPermSpace = 0, PeakSpoolSpace = 0 ALL ;) ;

You execute the ClearPeakDisk macro to reset the peak values for the next collection period.

Note: Before invoking the macro, you may want to save the peak values, either in a separate collection-periods table or off-line.

Example

To zero out the PeakPerm and PeakSpool values in the DatabaseSpace table for the next data collection period, enter:

EXEC DBC.ClearPeakDisk;

Teradata RDBMS returns the number of rows changed:

*** Update completed. 3911 rows changed. *** Time was 4 seconds.

3 - 12 Teradata RDBMS Database Administration

Chapter 3: Space Considerations

Monitoring Disk Space Activity

Using the System Views to Determine Current Space

The following paragraphs explain how to interpret the contents of the space reporting views to determine your current space usage and allocation.

For information on all the system views, see Teradata RDBMS Data Dictionary.

DiskSpace View

The DBC.DiskSpace(x) view returns AMP information about disk space usage at the database/user level. It also can report spool space usage.

DiskSpace figures are calculated only for the space owned by the user submitting the query. To find information about the full system, log on as the topmost user in the hierarchy (usually your site administrative user).

Example

The statement in the example calculates the percentage of disk space defined for and consumed by each database and user owned by the user submitting the statement.

The result shows that Finance has the highest percentage of utilized space, at 98.46%, and that SystemFE has the lowest, at 7.07%.

Note: In the example, NULLIFZERO is used to avoid a divide exception.

SELECT DatabaseName ,SUM(MaxPerm) ,SUM(CurrentPerm) ,((SUM(CurrentPerm))/ I NULLIFZERO (SUM(MaxPerm)) * 100)

(FORMAT 'zz9.99%', TITLE 'Percent // Used') FROM DBC.DiskSpace GROUP BY 1 ORDER BY 4 desc;

The response is similar to the following:

Percent

DataBaseName Sum(MaxPerm) Sum(CurrentPerm) Used

Finance 1, 824, 999, 996 1,796, 817, 408 98 46
Mdata 12, 000, 000, 006 8,877, 606, 400 73 98
DBC 2, 067, 640, 026 321, 806, 848 15 56
CrashDumps 300, 000 38, 161, 408 12 72
SystemFe 1, 000, 002 70, , 656 7 07

3 - 12

Teradata RDBMS Database Administration Chapter 3: Space Considerations

Monitoring Disk Space Activity

TableSize View

The TableSize[X] view provides AMP information about disk space usage at the table level, optionally (using viewnameX) only for those tables the requesting user owns or has SELECT privileges on.

Example

In this example, the SELECT statement looks for poorly distributed tables by displaying the CurrentPerm figures allocated on each AMP to every table in the User database. Each table is reported separately, ordered by name and by AMP.

The result displays two tables. Table Employee is evenly distributed across all AMPs in the system. The CurrentPerm figures range from 4,096 bytes to 30,208 bytes on different AMPs. Notice the results show that:

CurrentPerm is similar across all vprocs for Employee_upi_onempid

The table Employee_nupi_ondept is poorly distributed

SELECT vproc AS AMP, TableName (FORMAT 'X(20)'), CurrentPerm FROM DBC.TableSize WHERE DatabaseName = 'USER' ; ORDER BY TableName, AMP ;

The response is similar to the following:

AMP TableName CurrentPerm

0 EMPLOYEE_UPI_ONEMPID 18, 944
1 EMPLOYEE_UPI_ONEMPID 18, 944
2 EMPLOYEE_UPI_ONEMPID 18, 944
Previous << 1 .. 55 56 57 58 59 60 < 61 > 62 63 64 65 66 67 .. 218 >> Next