All Forums Tools
14 Sep 2006
BTEQ examples

Fellow forum members, I need to build/write some BTEQ scripts to pickup data from laptop folders example e:\my_data\my_excel_file.txt and upload/create a file on our Teradata data mart.Are there any concrete examples of this BTEQ script that I can use.. I have never used BTEQ so do not send me very complex scripts, It would take me sometime to understand.. start slow and maybe in 3 mths I will be an expert LOL...Much appreciated

leo.issac 184 posts Joined 07/06
14 Sep 2006

You can use BTEQ's .IMPORT option to load data. This is very simple to use ,provided, your input file is in Indicdata or data format.The syntax for this looks like following.IMPORT DATA FILE = filenameusing (:field1,:field2,:field3,....,:fieldn)insert into targettable(column list)values(:field1, :field2,.......... :fieldn);You can learn more on BTEQ's Import and Export option from BTEQs reference Manual.Wish you best of luck .

SarathyG 31 posts Joined 09/06
15 Sep 2006

Hi Dude,since u wanna load the data from txt file, i believe it should be in delimited fmt.The following script uses ',' as field delimeter.Also, its capable of loaded 'n' number of records.Give it a try..LOGON demotdat/sarathy_user,*****;.IMPORT VARTEXT ',' FILE=C:\TD\data\ABC.TXT;.REPEAT * USING emp_no (VARCHAR(6)), emp_name (VARCHAR(25)), emp_street (VARCHAR(25)), emp_city (VARCHAR(25)) INSERT INTO sarathy_db.emp values ( :emp_no, :emp_name, :emp_street, :emp_city ); .QUIT;

- Sarathy G

15 Sep 2006

Thank you for the examples... much appreciated

swathi_teradata 2 posts Joined 09/06
26 Mar 2007

I have one more query here.when we import data from file everytime a record is inserted into the table a display message for executing query is shown up in spool. If i have 1000 records in input file it resutls in 1000 display's (Total elapsed time was 0.13 seconds.)Can anyone please help to suppress these display messages. Thanks in advance.

BBR2 96 posts Joined 12/04
27 Mar 2007

Comment on SwathyG postWe can load fixed length data file so long as text file and the column length are in sync.For Swathy_Teradata postuse .SET ERROROUT STDOUT in your bteq script.You could then redirect your output to a log file as in this case/usr/bin/bteq < /home/sunopsis/util/scripts/test.bteq > /home/sunopsis/util/log/test.logHope this helps.Vinay

27 Mar 2007

Thank you for all your assistance.. I have used some of the suggestions and works very well... great team approach :-)

swathi_teradata 2 posts Joined 09/06
27 Mar 2007

Thanks Vinay but the issue still exists. .SET ERROROUT STDOUT is helpful only when there are error messages. .REPEAT *USING emp_no (VARCHAR(6)),emp_name (VARCHAR(25)),emp_street (VARCHAR(25)),emp_city (VARCHAR(25))INSERT INTO sarathy_db.emp values(:emp_no,:emp_name,:emp_street,:emp_city);Above query in mainframes with 5 records in source file results in *** Starting Row 0 at 12:32:37 on Tue Mar 27, 2007 *** Total elapsed time was 0.10 seconds. *** Total elapsed time was 0.07 seconds. *** Total elapsed time was 0.02 seconds. *** Total elapsed time was 0.03 seconds. *** Total elapsed time was 0.01 seconds. *** Warning: Out of data. *** Finished at 12:32:37 on Tue Mar 27, 2007 *** Total number of statements: 5, Accepted : 5, Rejected : 0 *** Total elapsed time was 0.25 seconds. *** Total requests run successfully = 5 *** Successful requests per second = 20.395 In case the source file has 100 records then 100 messages corresponding to elapsed time are populated. I want only one response as *** Total elapsed time was 0.25 seconds.

joedsilva 505 posts Joined 07/05
28 Mar 2007

From your example it looks like you are running on windows, In case you are using unix for production, something like this might help ....bteq < mybteq.btq 2>&1 | awk '/^BTEQ/,/Starting Row/ /Finished at/,/$$$$$$$$$$$$$/' >mybteq.log

dnoeth 3246 posts Joined 11/04
28 Mar 2007

Hi Swathi,i don't know about mainframe BTEQ, but on Windows/Unix there are two solutions to suppress that "one output line per row":- using more than 1 session - using a ".PACK 1"Dieter

Dieter

Sharda 1 post Joined 04/07
02 Apr 2007

Hi Swati,.set quiet on might work for you. Sharda

furrutia 5 posts Joined 06/09
15 Jul 2009

Does anybody know how to suppress the same messages on a mainframe BTEQ? Thanks,Fernando Urrutia

identifymecnu 5 posts Joined 07/09
26 Aug 2009

Hi,Im trying to import data into tables from a file using BTEQ import.im facing weird errors while doing thisLike:if im using text file as input data file with ',' as delimiter as filed seperator im getting the error as below:*** Failure 2673 The source parcel length does not match data that was defined.or if im using EXCEL file as input data file im getting the error as below:*** Growing Buffer to 53200*** Error: Import data size does not agree with byte length. The cause may be: 1) IMPORT DATA vs. IMPORT REPORT 2) incorrect incoming data 3) import file has reached end-of-file.*** Warning: Out of data.please help me out by giving the syntax for BTEQ import using txt file as input data file and also the syntax if we use EXCEL file as the input data fileAlso is there any specific format for the input data file for correct reading of data from it.if so please give me the info about that.Thanks in advance:)

identifymecnu 5 posts Joined 07/09
07 Sep 2009

Some solution found out for the above query(Post #16675):I've used the text file with data in which i used ',' as the field seperator and also the datatypes for all the fields in the USING clause are changed to 'varchar' irrespective of their original datatypes:The code looks like this:.LOGON TDPP/login_name,password.import vartext ',' file = d:\input file path.txt.QUIET ON .REPEAT * USING COL1 (VARCHAR(2)) ,COL12 (VARCHAR(1)) ,COL13 (VARCHAR(56)) INSERT INTO table name ( COL11 ,COL12 ,COL13)VALUES ( :COL11 ,:COL12 ,:COL13.QUITthe code also works with fields seperated with a tab.but it works only for vartext ' ' (in the 2nd line of code)but doesnt work forvartext '\t'

hari@teradata 1 post Joined 04/11
08 Apr 2011

u should write vartext '\t' in the 2nd line of code

kattamadhu 6 posts Joined 02/11
10 Apr 2011

i am trying to import data ,but i am getting error
error: Use IMPORT to open a file first before
trying to read from it.

kattamadhu 6 posts Joined 02/11
10 Apr 2011

thank u for the above example ,but i am new to teradata
i am trying to import data i am getting error
error: Use IMPORT to open a file first before
trying to read from it.

naveenmartha 1 post Joined 06/11
09 Jun 2011

.QUIET ON will be helpfull for u swathi..

kunchala 1 post Joined 04/12
26 Apr 2012

which command does not support in BTEQ.

klnsreenivas 12 posts Joined 12/11
08 Nov 2012

Can anyboy explain about QUIET ON and .REPEAT * ?
 

K.L.N.Sreenivas

17 Nov 2012

You can get details on these commands from Teradata BTEQ Reference manual, but in brief these commands can be used for;
QUIET - This basically limits BTEQ output (to errors and request processing statistics only). Normally used for performance testing
REPEAT - Submits the SQL request for the number of times specified.

klnsreenivas 12 posts Joined 12/11
21 Nov 2012

Thank you,
 

K.L.N.Sreenivas

vishaman 8 posts Joined 05/13
09 Feb 2014

hi
I have a run file that has collect stats statement for hundred's of databases ,i want that when i execute the run file ,with .run command ,any tables on which stats collection could not be completed due to any error should be redirected to my logfile
Can someone please help me with this ?
 

dnoeth 3246 posts Joined 11/04
09 Feb 2014

Simply redirecting errors should work:
bteq < myfile > mylog.log 2> myerrors.log
And within BTEQ there .SET ERROROUT to switch from STDOUT to STDERR 

Dieter

You must sign in to leave a comment.