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.
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.
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
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.