All Forums Database
Kishore_1 208 posts Joined 03/10
20 Jul 2010
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)?

Jim Chapman 449 posts Joined 09/04
20 Jul 2010

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.

drmkd17 54 posts Joined 10/12
04 Apr 2016

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. 

dnoeth 4628 posts Joined 11/04
04 Apr 2016

dbc.FunctionsV.ExecProtectionMode -> U,P

Dieter

drmkd17 54 posts Joined 10/12
04 Apr 2016

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?

dnoeth 4628 posts Joined 11/04
04 Apr 2016

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-Details.009.002.html#ww19266190

Dieter

drmkd17 54 posts Joined 10/12
11 Apr 2016

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

dnoeth 4628 posts Joined 11/04
11 Apr 2016

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;

 

Dieter

drmkd17 54 posts Joined 10/12
11 Apr 2016

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
 

dnoeth 4628 posts Joined 11/04
11 Apr 2016

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

Dieter

KhurramInayat 3 posts Joined 12/13
05 May 2016

Hi,
I have some functions with DBC.FunctionsV.ExecProtectionMode = 'S' and I can't find any information about this.
Does anyone know what 'S' represents in perspective of protected mode?
Any references to relevant reading material that has this information would also be helpful, because I couldn't find such info in the TD 14 manuals.
 
Thanks,
Khurram

Fred 1096 posts Joined 08/04
06 May 2016

'S' indicates one of the "Embedded Services" System Functions in TD_SYSFNLIB

You must sign in to leave a comment.