All Forums Tools
TdMan 91 posts Joined 01/07
29 Aug 2007
SQL assistant vs BTEQ

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

leo.issac 184 posts Joined 07/06
30 Aug 2007

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).

BBR2 96 posts Joined 12/04
30 Aug 2007

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

30 Aug 2007

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

TdMan 91 posts Joined 01/07
03 Sep 2007

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?

joedsilva 505 posts Joined 07/05
03 Sep 2007

"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

TdMan 91 posts Joined 01/07
06 Sep 2007

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?

joedsilva 505 posts Joined 07/05
06 Sep 2007

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)

06 Sep 2007

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.

TdMan 91 posts Joined 01/07
07 Sep 2007

that was much clear explanation

Dharman 1 post Joined 08/07
20 Sep 2007

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 - 1and net_Used in ('GS' , '3G' ) and dlvry_tchnlgy_cd in ('VC', 'SM', 'VI') group by 2,3order 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?

Kks 128 posts Joined 03/06
22 Sep 2007

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

anill 26 posts Joined 08/07
18 Oct 2007

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

rayravi-5064 5 posts Joined 10/07
18 Oct 2007

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

rayravi-5064 5 posts Joined 10/07
18 Oct 2007

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

rluebke 65 posts Joined 11/05
09 Nov 2007

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

michaldlouhy 4 posts Joined 06/09
01 Jul 2009

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 :-).

Raja_KT 1246 posts Joined 07/09
15 Jul 2009

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

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

klnsreenivas 12 posts Joined 12/11
13 May 2013

Without opening BTEQ window can we run any BTEQ commands via VBScript or JDBC?
Can anyone please Tell me the ODBC or JDBC connection string for BTEQ

K.L.N.Sreenivas

tomnolan 594 posts Joined 01/08
14 May 2013

>>> Without opening BTEQ window can we run any BTEQ commands via VBScript or JDBC?
 
No, you cannot execute all BTEQ commands via JDBC. Specifically, you cannot execute any of the "dot commands" -- the commands prefixed by a dot or period character.
 
>>> Can anyone please Tell me the ODBC or JDBC connection string for BTEQ
 
No, that is not possible. BTEQ is an application. Neither the Teradata JDBC Driver nor the Teradata ODBC Driver connect to BTEQ.

Mikec 28 posts Joined 03/09
14 May 2013

An alternative approach (that uses JDBC under the covers) is to use the ANT SQL Task [http://ant.apache.org/manual/Tasks/sql.html]. There are various articles on DevX (Search Friday Night Project) that describe the use of this technique as an alternative to BTEQ scripting. 

tomnolan 594 posts Joined 01/08
15 May 2013

The Apache Derby "ij" interactive SQL tool is included with Oracle (Sun) JDK 6.0 and JDK 7.0 and can be used with the Teradata JDBC Driver.
 
http://developer.teradata.com/blog/tomnolan/2013/05/using-the-apache-derby-ij-tool-with-the-teradata-jdbc-driver
 

Hitesh88 1 post Joined 01/13
22 May 2013

About the scheduling of query, i would like to mention the about the teradata's tool for scheduling query, available is "Teradata Query Scheduler Viewer".
You can use this tool to schedule the query on daily/weekly/monthly/hourly or any other basis also.

anubha_harrison 8 posts Joined 11/11
30 May 2013

Hi Sreenivas,
You can directly make use of Bteq scripts and execute it from your operating system (such as Windows and Unix). The output can be directed to the screen or to any output file. You dont need to open the bteq window for this.
The bteq scripts can also be triggered from the scheduler used by your organization. You may have to write a job or wrapper script and schedule it in the Standard scheduler used by your firm.
Scheduler -> Job or Unix Script -> Bteq

peterson10011 2 posts Joined 06/13
07 Jun 2013

Thanks for giving this useful information.

You must sign in to leave a comment.