Execution modes in teradata

Database
Enthusiast

Execution modes in teradata

I know there are two execution modes in teradata:

1.Protected execution mode
2.Non-protected execution mode

What is the difference between these two modes of execution?
How to know which execution mode is set for the teradata database(what query)?
How to change from one execution mode to another(query)?
Tags (1)
11 REPLIES
Enthusiast

Re: Execution modes in teradata

The execution modes pertain only to external stored procedures and UDFs. In protected mode, the function runs in a separate process; in unprotected mode the function is called directly by the AMP (actmain) process. The execution mode is determined by the procedure/function definition, and it may be altered using the ALTER FUNCTION or ALTER PROCEDURE statements. You can use SHOW FUNCTION to find out the current execution mode.
Enthusiast

Re: Execution modes in teradata

Hi,

I want to change the execution mode of the function to unprotected. I understand that I can use the alter statement as ALTER FUNCTION <funcitonname> EXECUTION NOT PROTECTED. 

but how do I find out what is the actual mode of the function. I am not able to view the execution mode when I do a show function. 

Please help. 

Junior Contributor

Re: Execution modes in teradata

dbc.FunctionsV.ExecProtectionMode -> U,P

Enthusiast

Re: Execution modes in teradata

Yeah.. I got it. Thanks Dieter. 

Do you think running functions in unprotected mode will have a huge performance impact for udf's like isdate?

Junior Contributor

Re: Execution modes in teradata

For simple C-functions protected mode might be 5 to 10 times slower & uses much more CPU than Unprotected.

http://www.info.teradata.com/HTMLPubs/DB_TTU_15_10/SQL_Reference/B035_1184_151K/Alter_Function-Detai...

Enthusiast

Re: Execution modes in teradata

Can one specify the execution mode of a UDF in the CREATE/REPLACE statement itself? What is the syntax ? Can anybody help?

Junior Contributor

Re: Execution modes in teradata

Whenever you CREATE or REPLACE a C-UDF it's created in protected mode, you need to

ALTER FUNCTION my_C-UDF EXECUTE NOT PROTECTED;
Enthusiast

Re: Execution modes in teradata

Hi Dieter , 

Thanks for the reply. That means We cannot create/replace a UDF in unprotected mode. Right??

Also, Can we change the execution mode of a SQLUDF to unprotected or it is restricted to an External UDF only. 

When i am trying to alter the execution mode of SQLUDF I am getting is error "Operation not allowed on SQLUDF" 

Thanks,

drmkd

Junior Contributor

Re: Execution modes in teradata

SQLUDFs are plain SQL, there's no compiled code, thus there's no protected mode.