Space

Space

Space (whether permanent, spool, or temporary) is measured in BYTES. The following table lists the three types of database space.

Type of Space	Description
Permanent (PERM)	Permanent space allocated to a user or database is a uniquely defined, logical repository for database objects, for example, tables, indexes, and journals. When an object is created or data rows are inserted, the system allocates space as needed from the PERM space of the immediate owner. The space is returned automatically when no longer needed. Note: Perm space allocated to a user or database that is currently unused is available for use as spool or temporary space. A database or user with no PERM space can still own views, macros, and triggers but cannot have objects that require space such as tables, UDFs, stored procedures, HIs, JIs, or journals.
Spool	Spool space holds intermediate query results or formatted answer sets to queries and volatile tables. The system can use unassigned PERM space for spool space. When a user creates new users or databases, the amount of spool space for those new objects must not exceed the spool space limit of their immediate owner. If you do not specify a spool space limit, a new user automatically inherits the spool limit of its parent. To more easily manage spool space, define the value for spool in a profile. You can then assign a profile to users and all of the users will inherit the spool space definition.
Temporary (TEMP)	Temp space defines the number of bytes the system will use to store data for global temporary tables. The value you specify must not exceed the value of the immediate parent at the time of creation. If you do not specify a value, the maximum value defaults to that of the parent. Note: Global temporary tables require both PERM and TEMP space. A minimum of 512 bytes of PERM space per AMP is required for the table header for the base global temporary table definition. Temp space is required for storing the actual rows. To more easily manage TEMP space, define the value for it in a profile. You can then assign a group of users to this profile and all of the users will inherit the TEMP space definition.

Problem Statement:

There are 3 users created under database which has PERM space 50 GB. Each user is allocated 5 GB PERM space & 20 GB Spool space.

Now, the PERM Space left in database- 35 GB.

I've created the another user for whom i allocated 20 GB PERM space.

Now, the PERM Space left in database- 15 GB ==> UNUSED space.

Even though  i allocated 20 GB spool space to each user, if i submit a sql query which takes more than 15 GB should be allocated since the unused space is only 15 GB ??

Solution:

Spool memory = unused perm + spool assigned. 

The particular user will have this much spool for his queries.

SPOOL is the currently not used perm space on system level, but not on user/database level.

The spool on a database level is only used for inheritance:

When there's no spool assigned for a new user he inherits the owner's spool size.

And when there's spool it may not exceed the owner's size.

Let us say if only 20 GB unused PERM space left at SYSTEM level. And there are 2 users in a system. If i run queries in both the users simultaneously and each is expected to take more than 15 GB spool space. In this scenario one query has to be aborted right? 

The system will run out of spool and return a 2507 error: "Out of spool space on disk", this probably affects all active queries.