I have a stored procedure that was written for Oracle. I am curious how to convert it to a Teradata-friendly format:
create procedure RegionSelect (cur_OUT OUT PKGENTLIB_ARCHITECTURE.CURENTLIB_ARCHITECTURE) as
BEGIN OPEN cur_OUT FOR select * from Region Order By RegionId; END;
Can someone advise?
What is this SP supposed to do?
Simply returning a result set, then it's:
CREATE PROCEDURE RegionSelect ()
DYNAMIC RESULT SETS 1
DECLARE cur_OUT CURSOR WITH RETURN ONLY FOR
select * from Region Order By RegionId;
Yes, it simply returns a result set. Thank you for the guidance; I was in particular unclear how to handle the out parameter but this helps to clarify.
I have a quick question on Stored procedures in Teradata.
Will a Stored procedure uses --teradata parallelism architecture?. How ?.
Will a Stored procedure uses multiple SESSIONS ?. If yes, how do you define a session limit ?.
I believe it is always be a single session window ?.
I heard SP slow down the ETL batch window ?.
A Stored Procedure in Teradata is rewritten as a C program and then compiled into a shared object on Linux. When you CALL the SP it's actually calling an exported function of that shared object.
Any SP specific syntax like WHILE will run on a single PE in a single thread (and use a single session), but the SELECTs/UPDATEs/etc. submitted by that SP will run in parallel like any other SQL.
So the SQL itself will not be faster or slower, but stupid usage of LOOPs or cursors result in serial processing and such can be slower than set-based processing.
Can you please provide more info. on below stmt.
**When you CALL the SP it's actually calling an exported function of that shared object **
While calling exported function of that shared object , how many sessions will get allocated , where that info maintained .
Will it get change in every TD relaese.?