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

3 EMPLOYEE_UPI_ONEMPID 19, , 968
0 EMPLOYEE_NUPI_ONDEPT 4, ,096
1 EMPLOYEE_NUPI_ONDEPT 30, 208
2 EMPLOYEE_NUPI_ONDEPT 15, 360
3 EMPLOYEE NUPI ONDEPT 12, 288

AllSpace View

The AllSpace[X] view provides AMP information about disk space usage at the table level and the database level.

AllSpace[X} always reports a system table named ALL in addition to each user table. (A user table can be a data table, a secondary index, a join index, a hash index, a stored procedure, or a permanent journal.) The contents of ALL reports space at the database level.

3 - 12 Teradata RDBMS Database Administration

Chapter 3: Space Considerations

Monitoring Disk Space Activity

Example

In the example below, the SELECT statement requests the MaxPerm and the CurrentPerm figures for each table in a database named Payroll. For the purposes of this example:

No stored procedure or permanent journal exists in the Payroll database.

No secondary, join, or hash index is defined on the Employee data table.

Tables do not have MaxPerm space; only databases and users do. Therefore, the MaxPerm figure for the Employee table is zero bytes, and the MaxPerm figure for ALL is the amount of permanent space defined for the Payroll database.

Because Payroll has only one table, Employee, the number of bytes on each AMP is the same for ALL and for Employee.

The example response reports AMP disk space usage (including spool) at both the database and the table level.

Included is the special system table ALL, which is used to report the database level (TableName ALL, TableID = '000000000000' XB).

Note: MaxPerm is always 0 (zero) for tables, since a permanent (PERM) space limit is not defined at the table level but rather at the database/user level.

To list by AMP vproc all tables contained in the user's space, submit this query (substituting a valid database name):

SELECT vproc AS

AMP, TableName (FORMAT 'X(10)'), MaxPerm, CurrentPerm

FROM DBC.AllSpace

WHERE DataBaseName = Payroll

ORDER BY TableName, AMP ;

AMP TableName MaxPerm CurrentPerm

0 ALL 125.000 2, 048
1 ALL 125.000 2, 048
2 ALL 125.000 2, 560
3 ALL 125.0000 2, 048
0 EMPLOYEE 0 2, 048
1 EMPLOYEE 0 2, 048
2 EMPLOYEE 0 2, 560
3 EMPLOYEE 2, 048

3 - 12

Teradata RDBMS Database Administration Chapter 3: Space Considerations

Monitoring Disk Space Activity

Finding CurrentPerm

NCR recommends that you query the following views to find the accurate MAX(CurrentPerm) and SUM(CurrentPerm):

IF you use this view ... THEN available space is reported at .
DBC.DiskSpace the database level.
DBC.TableSize the table level.

Caution: NCR recommends that you do not try to use DBC.AllSpace view to find

SUM(CurrentPerm), because it adds the sum of every database to the sum of every table throughout the configuration. Thus, the amount it reports is double the actual perm size. This can be dangerously misleading.

DiskSpace reports on a database, while TableSize reports on the tables within a database. Thus:

TableSize reports less MAX(CurrentPerm) than DiskSpace.

Both views report the same SUM(CurrentPerm)

This difference is explained in the examples and results comparison below.

Example of MAX(CurrentPerm) and SUM(CurrentPerm) Queries

Use TableSize to report the perm of tables in a particular database and DiskSpace to report the perm of the database.

TableSize reports MAX(CurrentPerm) and SUM(CurrentPerm) on all tables in the specified database. For example, assume you enter the following:

SELECT MAX(CurrentPerm)lSUM(CurrentPerm)

FROM DBC.TableSize

WHERE DatabaseName = USERA ;

For the purposes of our example, the space is reported as follows:

MAX(CurrentPerm) SUM(CurrentPerm)

4,096 17,408

DiskSpace reports MAX(CurrentPerm) and SUM(CurrentPerm) at the database level:

SELECT MAX(CurrentPerm)lSUM(CurrentPerm)

FROM DBC.DiskSpace

WHERE DatabaseName = USERA ;

3 - 12 Teradata RDBMS Database Administration

Chapter 3: Space Considerations

Monitoring Disk Space Activity

For the puposes of our example, the response is as follows:

MAX(CurrentPerm) SUM(CurrentPerm)

6,144 17,408

I Comparing DBC.DiskSpace and DBC.TableSize Results

The amounts returned by DiskSpace and TableSize compare as follows:

Space DBC.DiskSpace[X} View DBC.TableSize[X] View
SUM(CurrentPerm) Reports the space consumed by all the tables in the specified or all databases. This amount should agree with the SUM(CurrentPerm) reported by TableSize. Reports the total for all user tables in the specified or all database. The total is found by adding together the bytes consumed by each table. This amount should agree with the SUM(CurrentPerm) reported by DiskSpace.
MAX(CurrentPerm) Returns results at the database level. MAX is the PERM defined for the database/user in the CREATE or MODIFY statement. Returns the remainder of defined PERM space minus SUM space. This may or may not agree with MAX(CurrentPerm) returned by DiskSpace.
Previous << 1 .. 56 57 58 59 60 61 < 62 > 63 64 65 66 67 68 .. 218 >> Next