Where are Stored Procedures stored ?

Database
Enthusiast

Where are Stored Procedures stored ?

A colleague asked me this question and I thought the answer was in dbc, not the actual database. However I couldn't find a definitive answer so I thought I'd asked.
The reason for the question is that he has created a large stored procedure that has caused a large amount of skew. He ran the following query where he used the sp name for 'tablename'.

SELECT Vproc
, CurrentPerm
FROM dbc.TableSize
WHERE DatabaseName = 'your_database'
AND TableName = 'your_sp_name'
ORDER BY 1
;
Is it valid to use a stored procedure nME when querying dbc.tablesize ? From the query above and the result I would assume stored procedures are stored in the actual database. If so, what determines the Primary Index and can it be changed for each SP ?

The database in question has a lot of procedures and relatively little data, but he has had to assign a large amount of perm space due to the skewing.

Many thanks.
4 REPLIES
Junior Contributor

Re: Where are Stored Procedures stored ?

SPs are compiled into DLLs (Windows) or Shared Objects (Unix), those objects are stored within the owning database.
As it's fallback, you always see two AMPs with the same permspace size.

And if you want to know where the source code is stored, just submit a "EXPLAIN SHOW PROCEDURE ...".

Dieter
Enthusiast

Re: Where are Stored Procedures stored ?

Thanks Dieter,
Yes, there were two of equal size. I ran the explain and noted that the Primary Index retrieve is based on the PI of "RowType". Therefore, I assume that a single SP has one RowType value that will hash to a single AMP (with fallback), and I've noticed that other SPs hash to different AMPs, based on their "RowType".
Is is possible for a single SP to be distributed over multiple AMPs ?
The issue we have is that this database has one relatively large Stored Procedure that, based on the size of it's AMPs, is dictating the size of the overall database.
The only work around I can see is to redesign the SP into smaller chunks, or just live with the extra space requirements (but it gets significant on very large systems with many AMPS).
Thanks for your input.
Enthusiast

Re: Where are Stored Procedures stored ?

How much space are you allocating for your stored procedure database that you are consuming all of the space on one or two AMPS for the storage of the stored procedure? (Just curious)
Win
Teradata Employee

Re: Where are Stored Procedures stored ?

Hi Dieter,

After using the "EXPLAIN SHOW PROCEDURE ...", I only received the following information:

  1) First, we do an INSERT into Spool 1.
2) Next, we do an INSERT into Spool 1.
3) We do an INSERT into Spool 1.
4) We do an all-AMPs RETRIEVE step from Spool 1 (Last Use) by way of
an all-rows scan into Spool 2 (group_amps), which is built locally
on the AMPs. Then we do a SORT to order Spool 2 by the sort key
in spool field1 (NULL). The result spool file will not be cached
in memory.
5) Finally, we send out an END TRANSACTION step to all AMPs involved
in processing the request.
-> The contents of Spool 2 are sent back to the user as the result of
statement 1.

May I know where is the source code is stored?

Thank you.

Best regards,

Win