BTEQ is not returning proper error code when run file is not present.

Database
Enthusiast

BTEQ is not returning proper error code when run file is not present.

Hi, Bteq experts,

I am running the following beteq script. I noticed that if the bteq script is not present ( /path/to/my_none_existing_bteq.bteq ), the script still return success instead of error.

In other words, .EXIT ERRORCODE is not working as expected if the run file is missing. Any idea what is the fix? or how should we capture such error and report it properly?

bteq << EOF 

.logon $DB/$USER, $PASS;

.set width 4000;

.set underline off;

.set titledashes off;

.set heading '';

.set timemsg query;

.set maxerror=1

.run file /path/to/my_none_existing_bteq.bteq

.EXIT ERRORCODE

.quit

EOF

Regards,

Qun

7 REPLIES
Enthusiast

Re: BTEQ is not returning proper error code when run file is not present.

Here is the output from the script

BTEQ 13.10.00.08 Thu Jan 10 09:17:52 2013

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

.logmech LDAP

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

.logon eat1-pdw/qli,

 *** Logon successfully completed.

 *** Teradata Database Release is 13.10.05.03                   

 *** Teradata Database Version is 13.10.05.03a                    

 *** Transaction Semantics are BTET.

 *** Session Character Set Name is 'ASCII'.

 *** Total elapsed time was 4 seconds.

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

.set width 4000;

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

.set underline off;

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

.set titledashes off;

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

.set heading '';

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

.set timemsg query;

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

.set maxerror=1

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

.run file /path/to/my_none_existing_bteq.bteq

 *** Error: Open failed because: No such file or directory

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

.EXIT ERRORCODE

 *** You are now logged off from the DBC.

 *** Exiting BTEQ...

 *** RC (return code) = 0

Teradata Employee

Re: BTEQ is not returning proper error code when run file is not present.

ERRORCODE is the database error code from the most recent database request - and since the last database request was a successful LOGON, ERRORCODE is zero.

Perhaps you want to use ERRORLEVEL, which is the highest severity code encountered.

Also note that "MAXERROR = 1" means that the script will automatically exit if ERRORLEVEL > 1 but will continue if ERRORLEVEL <= 1.

Enthusiast

Re: BTEQ is not returning proper error code when run file is not present.

Qun and Fred-I need some guidance. I am trying to invoke teradat sql scipt via unix. However, my id has an ldap authentication. I am to run it interactively (bteq then enter...then .logmech ldap enter ...then .logon xxx/userid enter then password) but not via a ksh driver file. Can you share your ksh script as an example. I reviewed what you had posted as but I dodnot see the logmech and ldap written anywhere in it while the output shows it. It will be great if you can help
Teradata Employee

Re: BTEQ is not returning proper error code when run file is not present.

Better to create a new thread for a new question.

In batch mode (within a script), the password goes on the .logon rather than on a separate line (as interactive mode):

.logmech ldap

.logon tdpid/username,password

Enthusiast

Re: BTEQ is not returning proper error code when run file is not present.

thanks Fred. I will give it a whirl.

Enthusiast

Re: BTEQ is not returning proper error code when run file is not present.

So my script should look like. Added the syntax for authentication and access particulars(should it be followed by semicolon?). Is there a general rule around when to put a semicolon. Following code has semicolon at some commands.

bteq << EOF

.logmech ldap;

.logon tdpid/username,password;

.set width 4000;

.set underline off;

.set titledashes off;

.set heading '';

.set timemsg query;

.set maxerror=1

.run file /path/to/my_bteq_file.sql

.EXIT ERRORCODE

.quit

EOF

Enthusiast

Re: BTEQ is not returning proper error code when run file is not present.

#!/usr/bin/ksh

bteq <<-EOF

.logmech ldap

.logon xxxx/xxxxxxxx,xxxxx

.set width 4000;

.set underline off;

.set titledashes off;

.set heading '';

.set timemsg query;

.set maxerror=1

.run file /path_to_bteq//test_bteq01.sql

.EXIT ERRORCODE

.quit

EOF

Thanks Fred. So the above code works. Now I want to keep the password particulars in a central file. Is there a way to do it