BTEQ Return code 2

Tools & Utilities
Enthusiast

BTEQ Return code 2

 I have a BTEQ script which causes a scheduled job to fail (due to return code 2). The job step completes successfully, but believes it has failed (expecting return code 0). The job step is run as a .cmd/.txt file pairing as follows:

 

Contents of .cmd file:

set datestr=%date:~10,4%%date:~4,2%%date:~7,2%
set timestr=%time:~0,2%%time:~3,2%%time:~6,2%

if "%timestr:~0,1%" == " " set timestr=0%timestr:~1,5%

BTEQ < \\NetworkFilePath\ScriptFileName.txt > \\NetworkFilePath\Log_Files\LogFileName_%datestr%-%timestr%.log

 

Contents of .txt file:

sessions 50;
errlimit 200;

.RUN file "\\NetworkFilePath\LoginFile.txt"

/* clean up from last processing */
.set errorlevel 3807 severity 0
DROP TABLE db.TableName1;
DROP TABLE db.TableName2;
.set errorlevel 3807 severity 8


/* Run various queries - create tables, inserts etc. */


.quit

 

Contents of Log File:

BTEQ 14.10.00.09 Fri Sep 28 15:50:32 2018 PID: 6372

+---------+---------+---------+---------+---------+---------+---------+----


sessions 50;
+---------+---------+---------+---------+---------+---------+---------+----
errlimit 200;
+---------+---------+---------+---------+---------+---------+---------+----

.RUN file "\\NetworkFilePath\LoginFile.txt"
+---------+---------+---------+---------+---------+---------+---------+----
.logon TDIDWcop1.unix.gsm1900.org/APPH_MKT,

*** Logon successfully completed.
*** 50 Sessions logged on.
*** Teradata Database Release is 15.00.06.13
*** Teradata Database Version is 15.00.06.13
*** Transaction Semantics are BTET.
*** Session Character Set Name is 'ASCII'.

*** Total elapsed time was 54 seconds.

-- series of queries were successfully completed...

.quit
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 2




contents of logon file:
.logon datasource/UserName, password;

Obviously, the logon was successful, but am I missing something? Why do I get return code 2 upon exit? This causes the scheduled job to fail…


Accepted Solutions
Teradata Employee

Re: BTEQ Return code 2

The redirection suggestion was only to ensure a complete log file is generated so that you could tell what failed. You figured out the "ERRLIMIT 200;" instruction is the problem. There is no such command in BTEQ and if you were to actually LOGON first you will instead get a Failure 3706 from the DBS -- as its not a valid SQL statement either. You can replicate this by using BTEQ interactively. Just do a LOGON and then try to issue "ERRLIMIT 200;" So the next question you should ask yourself is why is the "ERRLIMIT 200;" instruction is in the script in the first place ? Maybe it can simply be removed so your script runs clean ? Could it be that the script you are working on was originally based on the command set of some other the Teradata utility such as FASTLOAD ?

 

Barring inability to correct the ERRLIMIT instruction, you can always add an ERRORLEVEL option to the QUIT command. Although this particular warning has set the default RC for exit to 2, it has not altered the ERRORLEVEL value that has been reached. In general, ERRORLEVEL is only set for database errors or failures. So you will get RC=0 instead as long as this is the only warning or error that occurs.

 

Also ... if you are going to clean up this script, please note: A leading period character is needed to definitively identify an instruction as a BTEQ command rather than an SQL request. Some of the commands that have been available for decades unfortunately do not require it. But all new commands do and it is in general a best practice to include it. For when a period is not included, BTEQ assumes the instruction is not a BTEQ command but rather an SQL request and will submit it for processing by the database instead. Even if it happens to be at worst ignorable, it requires an unnecessary client/server exchange and also makes a script more difficult to understand by others who may have to support it.

1 ACCEPTED SOLUTION
10 REPLIES
Teradata Employee

Re: BTEQ Return code 2

Try including redirection of stderr to stdout when invoking bteq so you can see all the messages BTEQ generates in your log file.

For example...

bteq < input.txt > output.log 2>&1

Enthusiast

Re: BTEQ Return code 2

Hi Suzanne, Thanks for you reply...I am not certain that I understand...do I literally just add '2>&1' to my .cmd file content?

 

Resulting in the following: 

BTEQ < \\NetworkFilePath\ScriptFileName.txt > \\NetworkFilePath\Log_Files\LogFileName_%datestr%-%timestr%.log 2>&1

Enthusiast

Re: BTEQ Return code 2

or is 2>&1 some sort of an HTML code???

Teradata Employee

Re: BTEQ Return code 2

Yes, you would append it to the bteq call.

2 means stderr and 1 means stdout.

You have only been redirecting stdout to the log file. I'm suggesting you also redirect stderr to the same log file.

https://docs.microsoft.com/en-us/previous-versions/windows/it-pro/windows-xp/bb490982(v=technet.10)

https://en.wikipedia.org/wiki/Standard_streams

https://support.microsoft.com/en-us/help/110930/redirecting-error-messages-from-command-prompt-stder...

 

Enthusiast

Re: BTEQ Return code 2

I understand now...thanks. This will send additional error messages, but I don't believe that it will solve the failure problem. will it suppress the error message?

 

I already know what the error message is...the jist is 'warning...failed to (or must) log on before sending commands', as I have seen this warning in log files before. 

 

The .logon command is the first statement in the BTEQ script, is there some sort of error tolerance setting that I need to insert to suppress the warning from sending a return code other than 0?

Enthusiast

Re: BTEQ Return code 2

I tested adding the 2>&1 after the log file path in the .cmd file. 

As suspected, I did receive additional information in the log file...

 

BTEQ 14.10.00.09 Mon Nov 05 14:18:56 2018 PID: 9820

+---------+---------+---------+---------+---------+---------+---------+----


sessions 50;
*** Warning: Expected a '.' before the command
+---------+---------+---------+---------+---------+---------+---------+----
errlimit 200;
*** Warning: You must log on before sending SQL requests.
+---------+---------+---------+---------+---------+---------+---------+----

 

Should these two statements be located after the logon statement? or should they be preceded by '.'? or is there something else wrong?...

 

thanks.

Enthusiast

Re: BTEQ Return code 2

my job still failed...

 

end of log file:

 

.quit
*** You are now logged off from the DBC.
*** Exiting BTEQ...
*** RC (return code) = 2

Teradata Employee

Re: BTEQ Return code 2

The redirection suggestion was only to ensure a complete log file is generated so that you could tell what failed. You figured out the "ERRLIMIT 200;" instruction is the problem. There is no such command in BTEQ and if you were to actually LOGON first you will instead get a Failure 3706 from the DBS -- as its not a valid SQL statement either. You can replicate this by using BTEQ interactively. Just do a LOGON and then try to issue "ERRLIMIT 200;" So the next question you should ask yourself is why is the "ERRLIMIT 200;" instruction is in the script in the first place ? Maybe it can simply be removed so your script runs clean ? Could it be that the script you are working on was originally based on the command set of some other the Teradata utility such as FASTLOAD ?

 

Barring inability to correct the ERRLIMIT instruction, you can always add an ERRORLEVEL option to the QUIT command. Although this particular warning has set the default RC for exit to 2, it has not altered the ERRORLEVEL value that has been reached. In general, ERRORLEVEL is only set for database errors or failures. So you will get RC=0 instead as long as this is the only warning or error that occurs.

 

Also ... if you are going to clean up this script, please note: A leading period character is needed to definitively identify an instruction as a BTEQ command rather than an SQL request. Some of the commands that have been available for decades unfortunately do not require it. But all new commands do and it is in general a best practice to include it. For when a period is not included, BTEQ assumes the instruction is not a BTEQ command but rather an SQL request and will submit it for processing by the database instead. Even if it happens to be at worst ignorable, it requires an unnecessary client/server exchange and also makes a script more difficult to understand by others who may have to support it.

Enthusiast

Re: BTEQ Return code 2

Hi Suzanne, Thanks again for your thoughtful and thorough responses. After identifying the two commands before the .logon statement as the issue, I removed them all from the scripts for this business program. I compared these scripts to ones (for other programs) that have been running successfully for some time; and none of them have the sessions/errors statements, so I assume that they are not needed and removed it from all scripts. My job ran successfully from end to end. :)