Regarding Space Management In Teradata

Analytics
Enthusiast

Regarding Space Management In Teradata



I am unable to Create the Table is Edwtest_Ads_Temp .but it is allowing to insert the new rows.
I have checked the space .Still 879 Mb Space is available.
And even no amp is full.

I have used following queries to get the conclusion.

1)sel sum(maxpermspace),from DBC.DataBaseSpace where Databaseid='00007408'XB;( '00007408'XB is Database id for the database I am referring)

Result:=2122317760.00 Bytes

2) sel sum( currentperm) from DBC.TableSize where databasename='Edwtest_ads_Temp' ;

Result= 1200550400.00 Bytes

So Sum Of Current Perm Space is Less than Maxpermspace

3)
sel max(currentperm) from DBC.TableSize group by vproc where databasename='Edwtest_ads_Temp' ;

In above query Max Of currentpermspace of each amp is less than the Maxpermspace/80(80 is number of amps)

Amp is full only when it reaches =26528972=2122317760.00(Max Perm Space)/80

Amp number which is occupied highest space is 50 I.e. 10132992.00 Bytes

could you please clarify
9 REPLIES
Senior Apprentice

Re: Regarding Space Management In Teradata

Hi Praneet,

DBC.TableSize accessed in your third query reports currentperm per table per AMP.
So those 10132992 Bytes on AMP 50 are used by the largest table within that database, because you used MAX instead of SUM:
"SUM(currentperm) from DBC.TableSize group by vproc"

Better use

select max(currentperm), max(maxperm)
from dbc.diskspace
where databasename='Edwtest_ads_Temp'

to see if a single AMP is close to the limit.

Dieter
Enthusiast

Re: Regarding Space Management In Teradata

I have seen the DDl of Diskspace it is taking only tables which are having tableid=DBSpace.TableID = '000000000000'XB

This tableid belongs to Table name ALL.

Could you explain why should we consider only those table which belongs to ALL?

Senior Apprentice

Re: Regarding Space Management In Teradata

Hi Praneet,
dbc.databasespace is the base table used by dbc.tablesize, dbc.diskspace and dbc.allspace

The ALL table is a dummy and represents all tables within a database -> dbc.diskspace

It's a bit confusing because dbc.databasespace and dbc.allspace include info on both the table level and the database level within the same result set.

Dieter
Enthusiast

Re: Regarding Space Management In Teradata


in the first reply you said I should have taken Sum(currentpermspace) instead of max(currentpermspace).
But from the second queruy I would be know the current Perm space.There I am taking sum(CurrentPermspace).

I should get error (No More room in Database) only when

1.Cuurent Perm Space reaches the MaxPerm Space
2. If there is any amp is full.

3) if dbc has no space.

To verify First two I have used these three queries.

My first quey will check how much space is allocated to that database
second query will check how much currentperm space is beeen utilised.

Third query will check for the whther amp is full?

Anywas dbc has space because I am able to create a table in other database.
Teradata Employee

Re: Regarding Space Management In Teradata

No. As Dieter indicated, you get the "No more room in database" error when the database CurrentPerm on any AMP reaches the MaxPerm for that AMP. None of your three queries is testing that condition. Dieter's suggested check of dbc.DiskSpace does.

Enthusiast

Re: Regarding Space Management In Teradata

Yes I understood.

Thanks a lot to dnoeth and Fred Pluebell
Teradata Employee

Re: Regarding Space Management In Teradata

Hi All,

Can anybody please tell me what is the significance of tablename "ALL" in dbc.allspace?

Teradata Employee

Re: Regarding Space Management In Teradata

Please start a new topic for new questions.

Rows with Tablename "All" are track database space. Consider using views DBC.TableSizeV and DBC.DiskSpaceV.

Re: Regarding Space Management In Teradata

Can you explain how you arrange space in Teredata, i am getting confused how to get my data correct place, please suggest me some ideas