Can i write Srored Procedure in tearadata

UDA
Enthusiast

Can i write Srored Procedure in tearadata

Hello,

I want to load the data from the file with validations. I am planning to use stored procedure since Mload is does not support validations. Where do i write stored proceduere and how do i excute the store procedure in Tearadata. Any help would be appreciated. Thank you.

Rao
6 REPLIES
Enthusiast

Re: Can i write Srored Procedure in tearadata

Rao,

What are you trying to do with the stored procedure? If you are trying to use it to validate the data as it's being loaded one record at a time, I would recommend that you don't do that. This will be a very slow process on Teradata and you'll have to run with multiple sessions to get any kind of throughput at all.

If, however, you are first loading the data to a staging table and then calling the stored procedure to validate all of the data in the staging table at once, then that should be ok.

You can write a stored procedure in any of the client tools (SQL Assistant, BTEQ, etc.). The syntax to create/replace a procedure is in the manuals. You call the procedure with a "CALL" statement.

Hope that helps.
Enthusiast

Re: Can i write Srored Procedure in tearadata

Thank you Barry. Actually we are receiving file from out side vendor and we have mload process to update/insert that data into production tables. Since they are sending wrong data we decided to validate before loading into production. Since mload doesn't support all the validations we are thinking of different approach to that. As you mentioned we are creating staging table to load the data and writing a BTEQ to validate and load the data. I hope this will work without any problems. Thank you for your sugesstion. Let me know if i am doing right? Thank you. Rao
Enthusiast

Re: Can i write Srored Procedure in tearadata

Sounds like you're on the right track Rao. Whenever I hear somone talk about using a stored procedure in the load process, I get concerned that they're going to try to execute the stored procedure to validate each row as it's being loaded, or that they're going to create a big cursor in the stored procedure and then go through it and validate the rows one at a time. Either of these approaches would be extremely slow on Teradata, so I'm glad to hear that you're not doing that and instead validating the data after loading to a staging table.

Good luck!
Enthusiast

Re: Can i write Srored Procedure in tearadata

Thank you Barry. Appreciate your help.
Enthusiast

Re: Can i write Srored Procedure in tearadata

Rao,

Why dont you write a pre-processor or an i-mod at the time of loading to get a performance boost?

Regards

Fan

Re: Can i write Srored Procedure in tearadata

Hi,

Can I declare a cursor with a dynamic WHERE clause ?

CREATE Procedure SP_TEST(IN vMetric VARCHAR(20))
BEGIN
DECLARE Test_Cursor CURSOR FOR
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE :vMetric > 0;
END

When I can this SP I will call with columns names as

- call SP_TEST ('Basic_salary')
Cursor should evaluate the SELECT statement as
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE Basic_salary > 0;

- call SP_TEST ('Bonus')
Cursor should evaluate the SELECT statement as
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE Bonus > 0;

- call SP_TEST ('Tax')
Cursor should evaluate the SELECT statement as
SELECT Name, Basic_salary, Bonus, Tax
FROM Employee
WHERE Tax > 0;

Any help, please ?

Sanky