Error Handling in BTEQ Teradata?

Error Handling in BTEQ Teradata?

BTEQ Return Codes:
Bteq return codes are the two digit values that BTEQ returns to the client operating system as a result of any error code occured in BTEQ session. Possible BTEQ return codes are as given below

Return Code Description
00 Job completed with no errors.
02 User alert to log on to the Teradata Database.
04 Warning error.
08 User error.
12 Severe internal error

The return code is decided by the Error messages that BTEQ receives from the Teradata database. Different Teradata database error codes are assigned a specific return code value.
The below table will give what different return codes are returned by BTEQ for different Error codes it receives from Teradata database.

For an Example : If you issue a SQL statement in BTEQ session to create a table which is alrady there, then Teradata database will return an error code of 3803 to the BTEQ session and in turn in BTEQ will send a return code of 04 to the client operating system where you have intiated the BTEQ session.

Return Code = 04 ( BTEQ returns a return code of 04 for the following Teradata error codes )
2580 - Mload not active on table %TVMID.
2667 - Statistics cannot be collected on an empty table. 
3534 - Index already exists.
3666 - This view has too many columns to store or retrieve comments.
3737 - Name is longer than 30 characters.
3747- No start-up string defined for this user.
3803 - Table “%VSTR” already exists.
3804 - View “%VSTR” already exists.
3805 - Macro “%VSTR” already exists.

A Sample Code Part Using BTEQ Conditional Logic:

The Bank offers a number of special services to its Million-Dollar customers.
-------------------------------------------------------------
DELETE FROM Million_Dollar_Customer ALL;
.IF ERRORCODE = 0 THEN .GOTO TableOK
CREATE TABLE Million_Dollar_Customer
(Account_Number INTEGER
,Customer_Last_Name VARCHAR(20)
,Customer_First_Name VARCHAR(15)
,Balance_Current DECIMAL(9,2));
.LABEL TableOK
INSERT INTO Million_Dollar_Customer
SELECT A.Account_Number
,C.Last_Name
,C.First_Name
,A.Balance_Current
FROM Accounts A INNER JOIN
Account_Customer AC INNER JOIN
Customer C
ON C.Customer_Number = AC.Customer_Number
ON A.Account_Number = AC.Account_Number
WHERE A.Balance_Current GT 1000000;
.IF ACTIVITYCOUNT > 0 THEN .GOTO Continue
.QUIT
.LABEL Continue
------------------------------------------------------------

What it does???

- DELETE all rows from the Million_Dollar_Customer table.
- IF this results in an error (non-zero), THEN create the table, ELSE attempt to populate using INSERT/SELECT.
- IF some rows are inserted (ACTIVITYCOUNT>0) THEN arrange services, ELSE terminate the job.

Posted in: ACTIVITY COUNT,blogger,blogging,blogspot,ERROR HANDLING,ERRORCODE=0,How to,SQL,TERADATA,TERADATA SQL,we-love-to-share.blogspot.com