Need to set Return Code upon "EOF on stdin"

Tools & Utilities
Enthusiast

Need to set Return Code upon "EOF on stdin"

I am experiencing a sporadic problem with BTEQ skipping all the commands from STDIN.

The script is a shell script that does a bunch of set up and then calls BTEQ, piping in the commands that are in the same file.

Here's my script (at least part of it):

# ----------------------------- Execute the main script-----------------------------------

echo " <--------- BTEQ: START ----------> "

echo

bteq<<-[EOF] > ${LogDir}/${Bteq_Log}

.SET SESSIONS 1;

.SET WIDTH 254;

.SET ERROROUT STDOUT;

.SET ERRORLEVEL (0, 2641) SEVERITY 0;

.SET ECHOREQ ON;

.SET RETCANCEL ON;

.SET RETLIMIT 20;

.run file=${MAIN_DIR}/logon_BTEQ_EIS.txt;

.IF ERRORCODE != 0 THEN .quit ERRORCODE;

...

.QUIT 0

[EOF]

For a normal run, the log file looks like this:

BTEQ 12.00.00.04 Thu Jun 30 14:02:19 2011
 
+---------+---------+---------+---------+---------+---------+---------+----
.SET SESSIONS 1;
+---------+---------+---------+---------+---------+---------+---------+----
.SET WIDTH 254;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET ERROROUT STDOUT;
 *** Error messages now directed to STDOUT.
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET ERRORLEVEL (0, 2641) SEVERITY 0;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET ECHOREQ ON;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET RETCANCEL ON;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.SET RETLIMIT 20;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.run file=/xfer/BMP/extracts/TERADATA/bteq-scripts/eis/logon_BTEQ_EIS.txt;
+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+---------+-
.logon TeraProd/BTEQ_EIS,
...
.QUIT 0
 *** You are now logged off from the DBC.
 *** Exiting BTEQ...
 *** RC (return code) = 0

When things go wrong, it looks like this:

BTEQ 13.10.00.06 Wed May  1 23:12:43 2013
 
+---------+---------+---------+---------+---------+---------+---------+----
 *** BTEQ exiting due to EOF on stdin.

 *** Exiting BTEQ...
 *** RC (return code) = 0

You can see that it doesn't even execute the very first statement after calling BTEQ.  I haven't been able identify what is cuasing the problem so I can fix it.

More important, though, is that I detect when this happens so we can take corrective action.  Unfortunatley, BTEQ exits sucessfully in this case.  What I'd like to do (if possible) is to specify that reaching the EOF without logging off BTEQ first results in a non-zero return code. 

Does anyone know if this can be done (changing the return code)?  One thing I considered was setting the RC to a non-zero value right ways, but the fact that we don't even execute the first statment seems to make that impossible.  I'm not sure there's a way to do that anyway.

I'm also open to any other ideas for detecting this situation.

Thanks,

Jimmy

Tags (3)
5 REPLIES
Teradata Employee

Re: Need to set Return Code upon "EOF on stdin"

This looks to me a shell scripting error. When you provide a bunch of statements through shell like this, make sure that the input bunch is properly read and submitted to bteq by your shell script.

When you say, 

bteq << -EOF > blabla

your shell will scan the script till "-EOF" word and feed it to bteq. But if shell cannot determine what it has to provide, then it send no data, resulting bteq terminating with no error. BTEQ's behaviour is correct as it has nothing to execute.

Try the following in your script.

bteq << [EOF] > ${LogDir}/${Bteq_Log}
.SET SESSIONS 1;
.SET WIDTH 254;
.SET ERROROUT STDOUT;
.SET ERRORLEVEL (0, 2641) SEVERITY 0;
.SET ECHOREQ ON;
.SET RETCANCEL ON;
.SET RETLIMIT 20;
.run file=${MAIN_DIR}/logon_BTEQ_EIS.txt;
.IF ERRORCODE != 0 THEN .quit ERRORCODE;
...
.QUIT 0
[EOF]

Note that "[EOF]" is matched. Also, there is a newline after last "[EOF]".

Please post it to shell scripting forums if still getting the error. This is not a problem with bteq.

Enthusiast

Re: Need to set Return Code upon "EOF on stdin"

I am also facing the same issue,

It seems after entering the BTEQ its not coming back to shell.

Ma script is like

unix cmd1

unix cmd2

bteq <<[Test]

.logmech ${TD_MECHANISMNAME}

.logon ${TD_DBCNAME}/${TD_USERNAME},${TD_PASSWORD}

some SQL Statements

.logoff

[Test]

unix cmd3

Here it never comes to cmd3, eventhough i logged off its trying to execute the unix cmd in Database and show you should logon before running, if i try quit or exit it comes out of the shell scritpt itself..  

what am i missing??

Re: Need to set Return Code upon "EOF on stdin"

Try .QUIT instead of .LOGOFF

Re: Need to set Return Code upon "EOF on stdin"

If you do .logoff alone, the current session alone will end(i.e you will only be logged off from your userid only), but teradata engine is still open and all the forth coming commands will go into the teradata engine. As you have logged off, and still inside the teradata engine, unix cmd3 is trying to get executed in teradata itself and since you have logged off, it is asking you to login again.

For your unix command to run, the code should return to unix shell. So for this use .QUIT instead of .LOGOFF as .QUIT will automatically automatically logoff your current session and teminate from teradata engine returning to unix shell. All the unix commands following .QUIT will now be executed in unix shell.

If you want to use unix commands inside your bteq,i.e

bteq <<[Test]

SEL * FROM schemaname.tablename;

[Test]

If you want to use a unix command say remove a file, inside a bteq, use

.os rm filename

E.g:

bteq <<[Test]

SEL * FROM schemaname.tablename;

.os rm filename

[Test]

Note: Not all unix commands will work inside bteq even if prefixed with .os

Enthusiast

Re: Need to set Return Code upon "EOF on stdin"

Guys,

Thanks for your suggestions :-)  But wat i had done is i left a blank line between the .logoff and [test], thats what causing the issue.

Now that  i had removed that blank line everything is working fine...