How to free some space in Teradata Database

We may come across situation in which we may be encountering errors related with non availability of Storage Space in Teradata. For example:

“NO MORE ROOM IN DATABASE”

or

“NO MORE SPOOL SPACE IN DATABASE”

How to free some space in Teradata Database

So what one should do in such cases? We are talking about situations when we really have Storage Space shortage in our Teradata Environment. Adding more physical Storage Space is not the best option available all the time. So what one must do in order to free some existing occupied space ? Sharing below few possible methods of releasing some occupied space:

1. Implementing Single Value or Multi-Value Compression is considered as a very good practice for occupying much less space for same amount of data. In some cases, it can really show huge space saving.
2. Dropping of unwanted Secondary Indexes will result in removing Secondary Index Sub-table hence releasing some much required Storage space.
3. Purging of some DBC tables like ACCESSLOG or DBQL tables may free some space. These tables should not hold data for infinite period. Some purging strategy should be followed for these tables.
4. Try identifying intermediary work tables and deleting them. If the Work Tables are used only for intermediary calculations then implement purging strategy for such tables once the work is done.
5. Deleting unwanted Journals may also save some space.
6. If the tables are created with FALLBACK option and the data is not that critical and can be build again in DataWareHouse , then change the tables to NO FALLBACK. This will help in saving huge amount of data as it requires twice the table size with FALLBACK option enabled.
7. If you are facing issues due to non-availability of SPOOL Space, then try optimizing your SQL queries , remove any PRODUCT JOIN and collect stats on columns participating in Joins conditions. Implementing Compression may also help in overcoming SPOOL Space error.
8. Sometimes we create back-up of tables before doing any table loading in order to verify the data with old back up tables. If data is correct and we don’t need back-up tables then such tables should be dropped.
9. If there are some tables which are not used anymore , try Archiving them ; move such tables to other inexpensive Disk hence saving some space in actual disk.
10. If nothing is working for you, consult your DBA for increasing the Space allocated to any Database.

Which method you prefer generally and why for making some space in Teradata database? If I miss any method here, feel free to add by leaving a comment.

Real time issue in Teradata

This error will happen when you update the target with multiple rows from the source.
If you defined primary index field for your target and if you defined those fileds in update query condition, this error will be obvious one.

For example,

UPDATE tbl_emp
FROM
(SELECT
id,
name
FROM tbl_dept) DEPT
SET name = DEPT.name
WHERE
tbl_emp.id = DEPT.id;

In the above code, if tbl_dept has non-unique values and tbl_emp is a set table, then the error will appear.

Solution:
You can render the query with DISTINCT in the select query.

UPDATE tbl_emp
FROM
(SELECT DISTNCT
id,
name
FROM tbl_dept) DEPT
SET name = DEPT.name
WHERE
tbl_emp.id = DEPT.id;