How does Teradata handle Stored proc's

Tools & Utilities
Enthusiast

How does Teradata handle Stored proc's

Hello fellow Teradata clients, I need some clarification and understanding concerning stored procedures (SP).
I will give you an actual example of what is going on…

Database: My_Views
Total allocated space: 50 mbytes
Used space: 20 mbytes (contains 54 procedures and 128 views)
% used: 40

there should be enough space to accommodate adding a new SP? Correct? Well when I try to compile the SP I get an error message “error message “2644: No more room in database My_Views” the database is only 40 % used????

I need to understand why when compiling this new SP, I get this error message the actual SP is 25mbytes in size not very big.. how does Teradata handle compiling stored procedures. Any information on this will be greatly appreciated, I looked online and in my books could not find anything pertinent to this issue.
1 REPLY
rgs
Enthusiast

Re: How does Teradata handle Stored proc's

How many AMPs in your system? You say that the database has 50 mbytes of which 20 mbytes are used. That 50 mbytes is spread out equally on all AMPs. For example if you have 10 AMPS there would only be 5 mbytes available for each AMP. Now to get to the heart of the matter. A SP is stored totally on one AMP source and object. Think of it as one BLOB (although that is not what it is). So you need enough remaining space on the AMP it picks to hold the source plus object. One of your AMPs might already be close to the limit. Maybe it got more than its share of the 54 procedures you already have on the database. So you need to increase the size of the database. I think the SP is hashed to an AMP based on its name.