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.
Return Code = 08 ( BTEQ returns a return code of 04 for the following Teradata error codes )
CLI0530 -Character set name or code unknown.
2123- A segment could not be read successfully.
2538 -A disk read error occurred in the tables area.
2541- End of hash code range reached.
2632- All AMPs own sessions for this Fast/MultiLoad
2639 - Too many simultaneous transactions.
2641 %DBID.%TVMID was restructured. Resubmit.
2644 No more room in database %DBID.
2654 Operation not allowed: %DBID.%TVMID is being restored.
2805 Maximum row length exceeded in %TVMID.
2809 Invalid recovery sequence detected.
2815 Apparent invalid restart of a restore.
2818 Invalid lock to dump table without after image journaling.
2825 No record of the last request was found after Teradata Database restart.
2826 Request completed but all output was lost due to Teradata Database restart.
2827 Request was aborted by user or due to statement error.
2828 Request was rolled back during system recovery.
2830 Unique secondary index must be dropped before restoring table.
2835 A unique index has been invalidated. Resubmit request.
2837 Table being fast loaded; no data dumped.
2838 Table is unhashed; no data dumped.
2840 Data rows discarded due to inconsistent hash codes.
2843 No more room in data base.
2866 Table was recovery aborted; no data dumped.
2868 This permanent journal table is damaged; no data dumped.
2920 Delete journal and AMP down without dual.
2921 No saved subtable for journal %DBID.%TVMID.
2926 No more room in %DBID.%TVMID.
3001 Session is already logged on.
3111 The dispatcher has timed out the transaction.
3116 Response buffer size is insufficient to hold one record.
3119 Continue request submitted but no response to return.
3120 The request is aborted because of a Teradata Database recovery.
3523 %FSTR does not have %VSTR access to %DBID.%TVMID.
3524 %FSTR does not have %VSTR access to data base %DBID.
3566 Data base does not have a PERMANENT journal.
3596 RESTORE Teradata Database invalid if table, view or macro exists outside of
Teradata Database.
3598 Concurrent change conflict on data base; try again.
3603 Concurrent change conflict on table; try again.
3613 Dump/restore, no hashed nonfallback tables found.
3656 Journal table specified no longer exists.
3658 ROLLBACK/ROLLFORWARD table specifications are invalid.
3705 Teradata SQL request is longer than the Simulator maximum.
3802 Database “%VSTR” does not exist.
3807 Table/view “%VSTR” does not exist.
3824 Macro “%VSTR” does not exist.
3873 “%VSTR” is not a journal table.
3877 NO FALLBACK specified and the table is FALLBACK.
3897 Request aborted due to Teradata Database restart. Resubmit.
3916 Requested information not in dictionary.
5495 Stored Procedure %VSTR does not exist.
Return Code = 12 ( BTEQ returns a return code of 04 for the following Teradata error codes )
CLI0001 Parameter list invalid or missing.
CLI0002 Invalid number of parameters received.
CLI0003 Error validating HSIRCB.
CLI0004 Error validating HSICB.
CLI0005 Error validating HSISPB.
CLI0006 Invalid destination HSICB detected.
CLI0007 Invalid destination RCB detected.
CLI0008 DBCFRC unable to free RCB/HSICB control blocks because they are not
contiguous in storage.
CLI0009 Invalid DBCAREA pointer or id.
CLI0010 ECB already waiting.
2971 The AMP lock table has overflowed.
2972 No table header exists for table.
How to handle Errors :
BTEQ alos maintains the return code value in an internal Atrribute called ERRORLEVEL which you can later test and take necessary action depending upon the return code.
For any database error message which is not given above, BTEQ assigns a default return code of 8 to it. Also there is a way to specify a different return code to it.
. SET ERRORLEVEL UNKNOWN SEVERITY N
This BTEQ return codes can be used to Test and Branch accordingly
For an example.
SELECT * FROM TABLE1;
.IF ERRORLEVEL >= 14 THEN .QUIT 17
Also we can change the severity level of different error codes.
example :
.SET ERRORLEVEL 2168 SEVERITY 4
(2173,3342,5262 ) SEVERITY 8
.SET ERRORLEVEL UNKNOWN SEVERITY 14
BTEQ error level severity has no immediate impact on operations unless the error checking is incorporated in the scripts. The MAXERROR attribute sets threshhold value. So if any error occurs for which the return codes in greater than the MAXERROR the scripts terminates.
.SET MAXERROR 12
Note :
If you do not specify a MAXERROR value, BTEQ jobs execute until one of the following
conditions occurs:
• End-of-file for the primary command input file is encountered.
• A QUIT command is processed.
• A fatal error is detected.
• When BTEQ receives an I/O abend, system error messages either appear in the MVS
JES job log or are displayed on the CMS terminal.
• When BTEQ receives an I/O error or an abend, the SAS/C runtime library produces an
LSCX message that may provide more information about the error.
For more information on I/O errors and abends, refer to “I/O Errors and Abends”
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.
Return Code = 08 ( BTEQ returns a return code of 04 for the following Teradata error codes )
CLI0530 -Character set name or code unknown.
2123- A segment could not be read successfully.
2538 -A disk read error occurred in the tables area.
2541- End of hash code range reached.
2632- All AMPs own sessions for this Fast/MultiLoad
2639 - Too many simultaneous transactions.
2641 %DBID.%TVMID was restructured. Resubmit.
2644 No more room in database %DBID.
2654 Operation not allowed: %DBID.%TVMID is being restored.
2805 Maximum row length exceeded in %TVMID.
2809 Invalid recovery sequence detected.
2815 Apparent invalid restart of a restore.
2818 Invalid lock to dump table without after image journaling.
2825 No record of the last request was found after Teradata Database restart.
2826 Request completed but all output was lost due to Teradata Database restart.
2827 Request was aborted by user or due to statement error.
2828 Request was rolled back during system recovery.
2830 Unique secondary index must be dropped before restoring table.
2835 A unique index has been invalidated. Resubmit request.
2837 Table being fast loaded; no data dumped.
2838 Table is unhashed; no data dumped.
2840 Data rows discarded due to inconsistent hash codes.
2843 No more room in data base.
2866 Table was recovery aborted; no data dumped.
2868 This permanent journal table is damaged; no data dumped.
2920 Delete journal and AMP down without dual.
2921 No saved subtable for journal %DBID.%TVMID.
2926 No more room in %DBID.%TVMID.
3001 Session is already logged on.
3111 The dispatcher has timed out the transaction.
3116 Response buffer size is insufficient to hold one record.
3119 Continue request submitted but no response to return.
3120 The request is aborted because of a Teradata Database recovery.
3523 %FSTR does not have %VSTR access to %DBID.%TVMID.
3524 %FSTR does not have %VSTR access to data base %DBID.
3566 Data base does not have a PERMANENT journal.
3596 RESTORE Teradata Database invalid if table, view or macro exists outside of
Teradata Database.
3598 Concurrent change conflict on data base; try again.
3603 Concurrent change conflict on table; try again.
3613 Dump/restore, no hashed nonfallback tables found.
3656 Journal table specified no longer exists.
3658 ROLLBACK/ROLLFORWARD table specifications are invalid.
3705 Teradata SQL request is longer than the Simulator maximum.
3802 Database “%VSTR” does not exist.
3807 Table/view “%VSTR” does not exist.
3824 Macro “%VSTR” does not exist.
3873 “%VSTR” is not a journal table.
3877 NO FALLBACK specified and the table is FALLBACK.
3897 Request aborted due to Teradata Database restart. Resubmit.
3916 Requested information not in dictionary.
5495 Stored Procedure %VSTR does not exist.
Return Code = 12 ( BTEQ returns a return code of 04 for the following Teradata error codes )
CLI0001 Parameter list invalid or missing.
CLI0002 Invalid number of parameters received.
CLI0003 Error validating HSIRCB.
CLI0004 Error validating HSICB.
CLI0005 Error validating HSISPB.
CLI0006 Invalid destination HSICB detected.
CLI0007 Invalid destination RCB detected.
CLI0008 DBCFRC unable to free RCB/HSICB control blocks because they are not
contiguous in storage.
CLI0009 Invalid DBCAREA pointer or id.
CLI0010 ECB already waiting.
2971 The AMP lock table has overflowed.
2972 No table header exists for table.
How to handle Errors :
BTEQ alos maintains the return code value in an internal Atrribute called ERRORLEVEL which you can later test and take necessary action depending upon the return code.
For any database error message which is not given above, BTEQ assigns a default return code of 8 to it. Also there is a way to specify a different return code to it.
. SET ERRORLEVEL UNKNOWN SEVERITY N
This BTEQ return codes can be used to Test and Branch accordingly
For an example.
SELECT * FROM TABLE1;
.IF ERRORLEVEL >= 14 THEN .QUIT 17
Also we can change the severity level of different error codes.
example :
.SET ERRORLEVEL 2168 SEVERITY 4
(2173,3342,5262 ) SEVERITY 8
.SET ERRORLEVEL UNKNOWN SEVERITY 14
BTEQ error level severity has no immediate impact on operations unless the error checking is incorporated in the scripts. The MAXERROR attribute sets threshhold value. So if any error occurs for which the return codes in greater than the MAXERROR the scripts terminates.
.SET MAXERROR 12
Note :
If you do not specify a MAXERROR value, BTEQ jobs execute until one of the following
conditions occurs:
• End-of-file for the primary command input file is encountered.
• A QUIT command is processed.
• A fatal error is detected.
• When BTEQ receives an I/O abend, system error messages either appear in the MVS
JES job log or are displayed on the CMS terminal.
• When BTEQ receives an I/O error or an abend, the SAS/C runtime library produces an
LSCX message that may provide more information about the error.
For more information on I/O errors and abends, refer to “I/O Errors and Abends”
No comments:
Post a Comment