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


To do this, submit a CREATE DATABASE statement and specify the amount of space you want to keep in reserve as the PERM parameter.

Be sure never to create objects or store data in this database. As long as the reserve database remains empty, its PERM allocation remains available for use as spool space.

For example, assume you created an administrative user on your Teradata RDBMS named SysDBA. Since the space of this user is under your control, you can use it to create a child database named Spool_Reserve that will never contain tables, as follows: |

Step Action
1 Log on to Teradata RDBMS as user DBC.
2 Enter: CREATE DATABASE SysDBA.Spool_Reserve AS PERM = xxxx ; where xxxx is 15-20% of your total available space.
3 Quit the session and log off.

For more information on determining the allocation of spool space, see "Sizing Spool Space" in Teradata RDBMS Database Design.

3 - 10 Teradata RDBMS Database Administration

Chapter 3: Space Considerations

Defining Temporary Space Limits

Defining Temporary Space Limits

Temporary space is used to hold materialized global temporary tables. It is allocated at the database, user, or profile level, not the table level.

You define a temporary space limit with the TEMP parameter of a CREATE/ MODIFY PROFILE or CREATE/MODIFY USER/DATABASE statement.

Note: A profile definition overrides any user definition, it does not augment it.

A TEMP limit defined in a profile takes effect immediately upon completion of the MODIFY USER statement that assigns the profile to an existing user.

If the user is logged on, the profile specification affects the session.

The maximum and default limits for temp allocation are determined as follows:

IF you

THEN

specify TEMP in a CREATE/MODIFY USER/DATABASE statement

IF a profile .

does not apply

applies

THEN the limit may not exceed:...

the limit of the immediate owner of the user or database.

the limit of the user who submitted the CREATE/MODIFY PROFILE statement, determined as follows:

If that user has a profile, the limit in the profile

If a profile does not apply to that user, the limit in the CREATE/MODIFY USER statement for that user

If no TEMP is defined for that user, the limit of that user's immediate owner

3 - 10

Teradata RDBMS Database Administration Chapter 3: Space Considerations

Defining Temporary Space Limits

IF you

THEN

do not specify a TEMP limit in a CREATE/MODIFY USER/ DATABASE statement

IF a profile ... THEN the limit is inherited from ...
does not apply the limit for the immediate owner of the user.
applies the profile specification.
applies but the SPOOL parameter is NULL or NONE the specification for the user who submitted the CREATE/MODIFY PROFILE statement, determined as follows: If that user has a profile, the profile specification If a profile does not apply to that user, the specification in the CREATE/MODIFY USER statement for that user. If no TEMP is defined for that user, the specification for that user's immediate owning database or user.

The following track the space for global temporary tables:

Type Description
MAXTEMP MaxTemp specifies the limit of space available for global temporary tables. The value may not exceed the limit of: The creator or modifier of the profile, when setting TEMP in a profile The immediate owner of the user being created or modified, if a profile does not apply If you do not specify a value and the user is associated with a profile, MaxTemp defaults to the value of the profile, if defined. If the profile TEMP is set to NULL or NONE, or the user is not associated with a profile, MaxTemp defaults to the value of the user's parent.
CURRENTTEMP This is the amount of space currently in use by Global Temporary Tables.
PEAKTEMP This is the maximum temporary space used since the last session. Note: Temporary space is released when the session terminates.

Teradata RDBMS Database Administration

3 - 11 Chapter 3: Space Considerations

Monitoring Disk Space Activity

Monitoring Disk Space Activity

To find the current cylinder capacity and/or space allocation for one or more tables or databases, you can use the following:

Tools Parameter Description
SHOW TABLE statement tablename Shows the current table definition. If DATABLOCKSIZE or FREESPACE was defined, the response shows the value specified in the most recent ALTER TABLE or CREATE TABLE statement. If no value is specified, the option is not included; the global default (the values specified in DSBCONTROLGDO) is in effect.
Ferret Utility SHOWSPACE command For the specified table(s) and/or vproc(s), displays: Number of cylinders allocated, by type: - permanent - journal - temporary - spool Average utilization per cylinder per type Number and percentage of available free cylinders Number and percentage of bad cylinders
SHOWBLOCKS command For every table and/or subtable specified, can display statistics about Distribution of data block sizes (by range of number of sectors) Data block size (minimum, average, maximum) Total number of data blocks Total number of cylinders
Previous << 1 .. 54 55 56 57 58 59 < 60 > 61 62 63 64 65 66 .. 218 >> Next