The space accounting and space management infrastructure is enhanced to manage the space limits at a global (or system) level and augment the AMP-level space management. These enhancements are referred to as global space accounting. System limits are now soft limits. The soft limit is specified as a percentage and can be set at the system level in DBS Control. The soft limit setting applies to permanent, spool, and temporary space.
Previously, the space accounting system assumed even distribution of rows to the AMPs. Database and user space limits were set at the AMP level to the defined maximum limit values divided by the number of AMPs in the system. Space was managed and monitored at the AMP level. Space limits were hard limits. Transactions where usage exceeded the hard limit, such as long running load jobs, had to be resubmitted after increasing the hard limit.
The CREATE DATABASE, MODIFY DATABASE, CREATE USER, and MODIFY USER statements are extended to specify a skew limit percentage that allows maximum AMP space usage to exceed the per-AMP quota, that is, the global maximum space limit divided by the number of AMPs. The space limit is set at the global level and monitored at the AMP level as data is added to or deleted from tables to ensure that the total usage does not exceed the global level space limit. The skew factor is a percentage that actual usage can exceed the per-AMP quota. Space is allocated to AMPs as necessary, permitting usage up to the per-AMP skew limit.
AMP utilities such as UpdateSpace, UpdateDBC, Reconfig, system migration and Rebuild are enhanced to support global space accounting. Reconfig is enhanced to handle the changes to space distribution to DBC, and optionally, all users and databases.
- Database Administrators can manage and monitor space allocation using Viewpoint utility space monitoring and administration portlets, then take corrective action to adjust the space allocation.
- Soft limits provide improved transaction reliability under boundary conditions for space limits. Database Administrators can respond to alerts by taking necessary corrective action to adjust limits, enabling applications to complete without interruption.
- When space usage is skewed across AMPs, Database Administrators can manage space more efficiently by setting appropriate skew percentages rather than defaulting to even space allocation across all AMPs.
- Database Administrators can add space to provide for system expansion. You can adjust the space for all users and databases in the system.
- Database Administrators can use a DBS Control to restore a DBC to a target system that is larger than the source system to take advantage of this proportional space distribution.
- Reconfig is enhanced to handle the changes to space distribution to DBC and, optionally, all the users and databases. By default, all new space is distributed to DBC. You can specify how much of the new space to distribute to databases and users proportionately.
- The global space accounting method is recommended when the actual space usage is expected to be non-uniform across the AMPs on which database or user tables reside. For example, large unknown data sets that may have unexpected skew. Space settings can be set to the optimum levels.
- Two new procedures, FixCurrentSpace and FixAllocatedSpace, are provided to help manage the various current and allocated space fields in the DatabaseSpace table for a database. You can use FixCurrentSpace as alternative to Update Space utility.
- CREATE DATABASE and MODIFY DATABASE PERMANENT, SPOOL, and TEMPORARY parameters now include the SKEW option.
- CREATE USER and MODIFY USER PERMANENT, SPOOL, and TEMPORARY parameters now include the SKEW option.
- Database Administration
- SQL DDL Syntax and Examples
- SQL Functions, Operators, Expressions, and Predicates
- Data Dictionary