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?
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.
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.
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.
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
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
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);
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.
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?