Teradata Stored Procedure Help

Database
The Teradata Database channel includes discussions around advanced Teradata features such as high-performance parallel database technology, the optimizer, mixed workload management solutions, and other related technologies.
Fan

Teradata Stored Procedure Help

Same Teradata Stored Procedure calling in different web services at same time will
screw up performace hiting Teradata Server multiple times.
Is there aant way to Improve Performance?

5 REPLIES
Senior Apprentice

Re: Teradata Stored Procedure Help

This is probably due to the way the logic within the SP is written (e.g. WRITE LOCK on table level?), without details it's hard to tell.

Fan

Re: Teradata Stored Procedure Help

Thanks Dnoeth. As we are also planning to use Row lock for view which are called in the SP.

Will let you know if I see still that issue occuring.

 

Regards,

Senior Apprentice

Re: Teradata Stored Procedure Help

It depends on the view definition what happens when you apply a Row Lock on a view. 

If there's a Lock Table within it it will apply this.

Tourist

Re: Teradata Stored Procedure Help

Hi Dnoeth

I have created a procedure SAI_OPERATIONS.fastloadcleanup with below defination :-

 

create PROCEDURE SAI_OPERATIONS.fastloadcleanup

(

   IN DB_NM   VARCHAR(30),   -- DB that holds tables being created.

   IN TBL_NM VARCHAR(30),   -- Table name.

   IN TBL_NM_BKP VARCHAR (30)   -- TABLE THAT HOLDS BACKUP

   )

   MAIN: BEGIN

DECLARE SQL_TXT VARCHAR(150) DEFAULT 'CREATE TABLE ';

DECLARE SQL_TXT1 VARCHAR (150) DEFAULT 'DROP TABLE';

DECLARE SQL_TXT2 VARCHAR (150) DEFAULT 'CREATE TABLE';

 

SET SQL_TXT=SQL_TXT || TRIM(DB_NM) || '.' || TRIM(TBL_NM_BKP) || ' ' || 'AS' || ' ' || TRIM(DB_NM) || '.' || TRIM(TBL_NM) || ' ' || 'WITH STATS' || ';'; ---- backup of main table

CALL DBC.SysExecSQL(SQL_TXT);

 

SET SQL_TXT1=SQL_TXT1 || TRIM(DB_NM) || '.' || TRIM(TBL_NM) || ';' ;   ------ drop main table

CALL DBC.SysExecSQL (SQL_TXT1);

 

SET SQL_TXT2=SQL_TXT2 || TRIM(DB_NM) || '.' || TRIM(TBL_NM) ||' ' || 'AS' || ' ' || TRIM(DB_NM) || '.' || TRIM(TBL_NM_BKP) || ' ' || 'WITH STATS' || ';'; ------- recreate main table

call dBC.SysExecSQL (SQL_TXT2);

end main

;

 

It has got created but when I am passing parameter it is throwing below error:-

call SAI_OPERATIONS.fastloadcleanup ('SAI_OPERATIONS','main','main_back');

CALL Failed. 3706:  FASTLOADCLEANUP:Syntax error: expecting the keyword DATA.      ( ### Here main Is a dummy table created by me)

 

 

call SAI_OPERATIONS.fastloadcleanup (SAI_OPERATIONS,main,main_back);

CALL Failed. 3810:  Column/Parameter 'SAI_OPERATIONS.fastloadcleanup.SAI_OPERATIONS' does not exist. 

 

Please suggest

Senior Apprentice

Re: Teradata Stored Procedure Help

As the error message indicates, the keyword DATA or NO DATA is missing.

 

Double check the text submitted by sysexecsql if contains valid SQL (hint: #2 & #3 are invalid, too).

 

And why are the database & table name parameters only VarChar(30) when Teradata suppports up to 128?