Teradata Forums / Teradata / Teradata Tools and Utilities / SQL assistant vs BTEQ / Latest Posts

RE: SQL assistant vs BTEQ

Raja_kt — Wed, 15 Jul 2009 08:11:58 GMT

Most of the work I do is in unix platform(bteq,mload,tpump,fastload). In bteq we can conveniently put our programmingin unix environment and it looks good. In windows platform I dont appreciate it since I cannot play much with the quality delivery.Thanks and regards,Raja K Thaw

RE: SQL assistant vs BTEQ

michaldlouhy — Wed, 01 Jul 2009 05:34:40 GMT

Hi, you can execute SQL scripts using BTEQ this way:1st file (BTEQ.bat) contains:---------------------------"c:\Program Files\NCR\Teradata Client\bin\bteq.exe" ( "SCRIPT_NAME.txt" ) "SCRIPT_NAME.log" -- instead of () use <>, for some reason this forum deletes that part of the string if i use <>2nd file (SCRIPT_NAME.txt) that is executed looks like this:--------------------------------------------------------.SET SESSION CHARSET 'CHARSET_NAME'.SET WIDTH 130;.SET ERRORLEVEL UNKNOWN SEVERITY 16;.SET ERROROUT STDOUT;.SET MAXERROR 1-- either calls logon script or looks like.LOGON DATABASE_CONNECTION_STRING/USER_NAME,PASSWORD;-- main SQL logic is here (inserts, deletes, updates..).IF ERRORCODE > 0 THEN .QUIT 8;.QUIT 0Hope that helps :-).

RE: SQL assistant vs BTEQ

Ron Luebke — Fri, 09 Nov 2007 15:21:07 GMT

Boy is that an understatement. In almost every instance we have run, BTEQ is much faster than SQL Assistant. I'm not sure why, but I would think that using native CLIv2 would incur less overhead than ODBC/OLE DB.R

RE: SQL assistant vs BTEQ

rayravi — Thu, 18 Oct 2007 08:07:14 GMT

Hi Job_Agent, There is no difference of where you do COLLECT STATS . It serves the same purpose as in BTEQ and SQL assistant.P.S. BTEQ may be slightly faster as it is CLI than an ODBC, if you are working in a big Client-server network. Again here I am just guessing*Regards,Rayravi

RE: SQL assistant vs BTEQ

rayravi — Thu, 18 Oct 2007 08:02:49 GMT

Hi DM, Yes, you can do all of that using BTEQ.1.To run your script in BTEQ, your should first put it in a file and save it as It can be run from the UNIX/LINUX prompt as $ bteq < file_name.bteq This will invoke the script in bteq2.I am not very sure scheduling in BTEQ, but you can use a unix shell wrapper around your script to schedule it3.There is no need to create a BAT file. a UNIX shell would suffice.4.You can put your logon details in the following format in beginning your script file..logon TDPID/USERNAME,PASSWD;5.The output can be had by redirecting :$ bteq logHope these things would helpRegards,Rayravi

RE: SQL assistant vs BTEQ

anill — Thu, 18 Oct 2007 03:19:57 GMT

Hi friends.Thaks for all I cleared max doubts of giving replys.can u any one give the simple script for BTEQ and tell me how to execute that script, tell me step by step.Thaks and RegardsAnil Reddy

RE: SQL assistant vs BTEQ

Job_Agent — Sat, 22 Sep 2007 12:34:23 GMT

is there any difference in collecting statistics between SQl Assistant and BTEQ

RE: SQL assistant vs BTEQ

Dharman — Thu, 20 Sep 2007 02:05:53 GMT

I have just started to write script.

need help.

1. I created a script in teradata. " select count(distinct medtd_service_no), net_Used, dlvry_tchnlgy_cd
from MEDIATED_CALL_HIST
where medtd_call_start_dt between date - 7 and date - 1
and net_Used in ('GS' , '3G' )
and dlvry_tchnlgy_cd in ('VC', 'SM', 'VI')
group by 2,3
order by net_Used,dlvry_tchnlgy_cd;"
2. I would like to schedule this script?
3.How do I do this?
4. do i need create a bat file? how do I create / write?
5. where do i include my login details?
6. Like to have an output file as well?

RE: SQL assistant vs BTEQ

TdMan — Fri, 07 Sep 2007 05:00:58 GMT

that was much clear explanation

RE: SQL assistant vs BTEQ

Arun Kumar K — Thu, 06 Sep 2007 04:42:32 GMT

Hi,
After all the things said in the past replies, I noticed one major point missing in the replies which is a major difference between the 2 tools mentioned:

1. Conditional logic can be applied in BTEQ whereas SQL Assitant can use only single statements with no conditional logic (.if statements).
2. The output of BTEQ reult sets are more proper to formatting statements. Any formatting applied to BTEQ outputs are exactly as specified. Whereas SQL Assitant applies its own formatting to the result set. Hence you cannot assure that the format of the string from SQL Assitant is the right one as specified in the outputting call string.
3. The import or export facility is very bare minimal in SQL Assitant whereas BTEQ's import export functionality is much more versatile and flexible and programmable.

A BTEQ script is nothing but a simple file in UNIX or Windows environment which can be run using the following command:
bteq < infle > outfile where infile is the BTEQ script and outfile is the output or log file.
Now how you call the BTEQ command above is your option whether you use a cron or scheduler or do a one time prompt call etc.

Hope that helps.

RE: SQL assistant vs BTEQ

joedsilva — Thu, 06 Sep 2007 04:13:31 GMT

Bteq has no scheduler, it's just a utility to execute SQLS, do a little bit of formating, export/import small scale data and capable of doing teeny-weeny if else conditions. ... nothing more (that I could remember :o) ...

you will have to use a scheduler to run bteq scripts at a certain time ... like cron/at in unix, windows scheduler etc ... (and tones of other third party schedulers available out there)

RE: SQL assistant vs BTEQ

TdMan — Thu, 06 Sep 2007 02:58:51 GMT

so does bteq have a scheduler in it?
can we schedule the bteq scripts to our requirements using the bteq tool itself or get it done through the script in bteq?

RE: SQL assistant vs BTEQ

joedsilva — Mon, 03 Sep 2007 05:40:28 GMT

"Bteq" script is nothing but a bunch of SQLs and any other Bteq commands (like logon, transaction mode, width etc etc .... ) wrapped in a file. (most of us save it with an extension of .bteq but it doesn't matter).

This is mostly useful for batch processing when you can put in all the SQLs and bteq command in a file and use bteq to run it from a scheduler ...

bteq < processdata.bteq

RE: SQL assistant vs BTEQ

TdMan — Mon, 03 Sep 2007 04:53:30 GMT

I found the replies very useful.

Next my doubt is do we have to instal any utility (BTEQ) to run the bteq queries or what is the means to run bteq scripts?

RE: SQL assistant vs BTEQ

Nisith Bhattacharya — Thu, 30 Aug 2007 21:20:36 GMT

Like to add that
BTEQ supports Teradata Specific SQL such as OLAP Function( With by , With, RANK etc), which are not supported by any client utility connected using ODBC.
Although there are other equivalent functions available for ODBC, but using ODBC always produces a relational output.

Thanks

RE: SQL assistant vs BTEQ

Vinay Bagare — Thu, 30 Aug 2007 17:11:58 GMT

Besides Very useful information provided by Jim..
BTEQ can be run in a batch mode for exportin,importing (small volumes of data).
I can also be used to run useful reports. It can be installed both on Unix and Windows machine.

While SQL assistant does allow import/exports but I find BTEQ to be more useful.

Thanks,
Vinay Bagare

RE: SQL assistant vs BTEQ

LeoIssac — Thu, 30 Aug 2007 12:38:55 GMT

BTEQ uses CLIv2 while SQL Assistant uses ODBC driver to connect to Teradata Server.The basic difference between the two is that BTEQ is a command driven utility while SQL Assistant is a GUI.Both used to submit queries to Teradat.

BTEQ has its own command using which you can create and run scripts to run queries in a batch.BTEQ supports conditional logic (IF..THEN construct).

SQL assistant vs BTEQ

TdMan — Wed, 29 Aug 2007 05:52:11 GMT

Can anyone tell me the difference btw running a query or set of query in sql assistant and BTEQ?