error 2644no more room in database when creating a multiset table, but ok to create a volatile table

Database

error 2644no more room in database when creating a multiset table, but ok to create a volatile table

Hi All,

 

I am new to teradata and not very clear of how the backend of teradata works. I am really appreciate if you can help me with me question.

I was trying to generate a multiset table in my database, which is of 100G size, however I got an error 2644 saying that no more room in my database. It's strange since my database has 100G empty space.

Then I changed multiset table to a multiset volatile table, the same query executes fast and no problem occured.

I don't understand what's happening here,  why no space for a multiset table, but once I changed to volatile table it works?

 

Thanks a lot for your help!

 

  • space
  • Teradata
  • volatile table
4 REPLIES
nr1
N/A

Re: error 2644no more room in database when creating a multiset table, but ok to create a volatile t

Hi,

Volatile Table uses spool space and not the PERM space thats why its not giving the problem.

I suspect about your PERM space availibility. Please recheck. Also there is possibility of highly skewed data across the amps .

So, please check for skewness.

 

Thanks,

nr

 

 

Tags (1)
  • Tags:

Re: error 2644no more room in database when creating a multiset table, but ok to create a volatile t

@nr1

Thanks a lot for your answer, actually the skewness is also the problem of my concern. The skewness is high for this table, but I don't know what to make it smaller. Could you provide some suggestions?

 

I am also wondering whether the low speed of execution of the query is resulted from the high skewness.

 

Thanks a lot!

nr1
N/A

Re: error 2644no more room in database when creating a multiset table, but ok to create a volatile t

Hi,

You Can check skewness by below query to see which AMP is getting how many records :

SEL HASHAMP(HASHBUCKET(HASHROW(<PI COLUMN>) AS AMP_No,COUNT(*) AS Number_of_Records
FROM <DATABASE>.<TableName>
GROUP BY 1;

Choose PI columns wisely. If you want even distribution of data, please use above query to check the distribution of data and accordingly you can choose your PI.

Thanks,

nr

N/A

Re: error 2644no more room in database when creating a multiset table, but ok to create a volatile t

Hi Lorraine,

no doubt the long time is directly caused by a high skew,

an odd distribution among AMPs is synonym of poor performance and inserts may feed up an AMP when others stay unused.

how many AMPs on your system ?

Pierre