All Forums Tools
TeraJaguar 14 posts Joined 09/06
18 Sep 2006
Can BTEQ Prompt User for Password in Scripts

I'm using a BTEQ script called from a Korn Shell Script toi automate the process of running SQL via BTEQ on Unix. The user simply types the shell script name followed by their SQL input file name and the script seds the file name into the BTEQ script. No problems there I've gotten it to work just fine.Here is the issue. we can't have user passwords in a file. It can't even been in a very temporary file that lasts the life of the BTEQ job and then gets deleted..... Is there any way to get BTEQ to prompt the end user for the password? I've tried just leaving out the password and putting everything else in the .logon command but I only get an error messageHere is my current Shell and BTEQ scripts:Shell:#!/bin/kshfil=$1;scmd=s/\$infil/$fil/ gsed $scmd ~/bin/cmds.scr > tmpbteq < tmpBteq cmds.scr file:.set session transaction BTET.logon edw/uid,passwd;.export report file=$infil.out;.run file= $infil;.export reset;.quit;Thanks for all your help!

TeraJaguar 14 posts Joined 09/06
03 Oct 2006

Well....... I guess by the nuber of views that this is something of interest to a great many of us..... but based on all the feedback (or lack thereof) from the Teradata team working with us for a UDB to Teradata conversion I'm guessing this just isn't possible right now.Unfortuantely Teradata is pushing more people to SQL Assistant at the same time our company is trying to eliminate all client installed apps....Thanks for looking!

SarathyG 31 posts Joined 09/06
05 Oct 2006

alternatively you can use the 'ftppwd' utility in UNIX. where in, you could store your personalized pairs of DBServername and Passwd and retrive them, dynamically from the BTEQ script. hope dis would help.

- Sarathy G

Nicolas 4 posts Joined 08/06
05 Oct 2006

bteq cannot prompt the user for anything but korn shell can.your script becomes:#!/bin/ksh#prepare your scriptfil=$1;scmd=s/\$infil/$fil/gsed $scmd ~/bin/cmds.scr > tmp#prompt for username and passwordecho "enter username:"read usrecho "enter password:"read pass#launch bteq passing username and password in the command linebteq < tmp .logon edw/$usr,$pass#end of scriptAs for your cmds.scr script, the only thing you have to do is remove the .logon line:.set session transaction BTET.export report file=$infil.out;.run file= $infil;.export reset;.quit;

TeraJaguar 14 posts Joined 09/06
05 Oct 2006

Nicolas,I'll definitely give that a shot. Gets around my password issue very nicely. Thanks also to the person who sent the other suggestion! You will become plan B if I have trouble getting nicolas' idea to work.Thanks, I'll post the results soon!

TeraJaguar 14 posts Joined 09/06
06 Oct 2006

That idea worked great. I added a stty -echo and a stty echo before and after the read of the password to blank out the pasword on the screen. Thanks again!

TeraJaguar 14 posts Joined 09/06
19 Oct 2006

Well it worked up to a point. Since we pass the userid and password on the same line as the BTEQ command when you do a ps -ef you get the eniter command line back! That's right with the userid and password!Ooops. On Solaris we got around this by creating a link that points to the bteq file and executing the link. The link is about 50 bytes long and it overflows the ps CMD buffer on Solaris.On AIX the CMD buffer is huge. It just keeps wrapping so we aren't able to overflow it with the "whack it with a hammer" approach above. Sigh....Anyone know how to shorten up the CMD buffer that the ps command access on AIX?Thanks again for all the great suggestions.TeraJag

Fred 1096 posts Joined 08/04
20 Oct 2006

Why not just use a "here document"?#!/bin/ksh#prompt for username and passwordecho "enter username:"read usrecho "enter password:"read passbteq <<_END_.logon edw/$usr,$pass.set session transaction BTET.export report file=$1.out;.run file=$1;.export reset;.quit;_END_

TeraJaguar 14 posts Joined 09/06
23 Oct 2006

That did it. Works great and no passwords to be found.Thanks!

TeraJaguar 14 posts Joined 09/06
04 Jan 2007

Just wanted to update this thread with the final script. This script allows for versioning the output files and an alternate directory to place the output in. Meets our need of not storing passwords in a file on the system.if [ $# -lt 1 ];then echo usage: `basename $0` yourjob.sql + optional directory echo For output in current directory: tdsql yourjob.sql echo For output in specified location: tdsql yourjob.sql /camsrvr/largefile/userid/ exit 9fidir=""if [ $# -gt 1 ];then dir=$2fi# File name for outputtt=$1ex1="out"out1="$dir$tt.$ex1"i=1while [ -f $out1 ]do out1="$dir$tt.$ex1.$i" (( i = $i + 1 ))doneecho >$out1#File name for nohup outputtl=$1ex3="log"log="$1.$ex3"i=1while [ -f $log ]do log="$tl.$ex3.$i" (( i = $i + 1 ))doneecho >$log#prompt for uname and passwordecho "Enter Teradata User Name: "read usrecho "Enter Password: "stty -echoread passstty echonohup bteq <<_END__ >$log 2>&1&.set session transaction BTET.logon edwprod/$usr,$pass.export report file=$out1 ;.run file=$1;.export reset;.quit;_END_stty echo

pinkupinks 1 post Joined 03/11
29 Mar 2011

I want to prompt for database name, but couldn't find it.
Please help..

sauravrout 1 post Joined 03/13
14 Mar 2013

What if, i am using a LDAP based id?

You must sign in to leave a comment.