16.10 - FixCurrentSpace Procedure - Teradata Database

Teradata Database Administration

Product
Teradata Database
Release Number
16.10
Release Date
April 2018
Content Type
Administration
Publication ID
B035-1093-161K
Language
English (United States)

Purpose

Counts the permanent, temporary, spool, and persistent spool space used by each object in a database. For databases that use global space allocation, the allocations across the AMPs are fixed. If any AMP has excessive space, this procedure deallocates space appropriately.

Use this procedure to fix phantom spool problems or correct inconsistencies in the DBC.DATABASESPACE table, which might occur because of rare types of system failures.

Syntax



Syntax Elements

Use the following syntax elements for FixCurrentSpace:
Syntax Element Description
SYSLIB The name of the database where the function is located.
FixSpaceOption
The type of current space to be updated. Specify one of the following options:
  • P (permanent)
  • T (temporary)
  • S (spool)
  • PS (persistent spool)
  • A (all options)
It cannot be NULL.
AllDatabases Update current space for all databases. Specify one of the following values:
  • Y
  • N
  • NULL
AllProxyUsers Update current space for all proxy users. Specify one of the following values:
  • Y
  • N
  • NULL
Only proxy users with an assigned profile qualify for the fix space operation.
FixDatabaseName Target database name. It is required if both AllDatabases and AllProxyUsers are not specified (have a value of N or NULL). FixDatabaseName should be NULL if either AllDatabases or AllProxyUsers has a value of Y.
FixedDatabaseCount Output count of databases whose current space was fixed.
ErrInfo This is NULL if the fix space operation was successful. Otherwise, it provides information about the error that caused the procedure to stop before the operation completed.

Authorization

You must have EXECUTE privileges on the stored procedure or on the SYSLIB database.

Argument Types

Expressions passed to this procedure must have the following data types:
  • FixSpaceOption= CHAR(2) CHARACTER SET LATIN
  • AllDatabases= CHAR(1) CHARACTER SET LATIN
  • AllProxyUsers= CHAR(1) CHARACTER SET LATIN
  • FixDatabaseName= VARCHAR(128) CHARACTER SET UNICODE

Usage Notes

FixCurrentSpace performs the same functions for space fields as the UpdateSpace utility. Because this procedure both calculates current space use and deallocates unused space, it may take some time. FixCurrentSpace uses AMP worker tasks, unlike the UpdateSpace utility, which has separate, dedicated tasks executing the work. For this reason, consider using the UpdateSpace utility instead of the FixCurrentSpace procedure if there is heavy workload in the system.

Example

The following example shows how to execute the procedure to update all the current space fields for all databases.

call SYSLIB.FixCurrentSpace('A', 'Y', 'N', null, cnt, err);