|
|
|
Forum Member
 
Group: Forum Members
Last Login: 10/2/2009 2:55:27 PM
Posts: 36,
Visits: 13
|
|
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
Vincent Gabrielli
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 8/12/2009 6:03:17 PM
Posts: 177,
Visits: 64
|
|
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 = filename
using (: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 .
Leo Issac "Wants to Learn More!"
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 11/22/2006 11:55:00 PM
Posts: 30,
Visits: 1
|
|
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;
SarathyG
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/2/2009 2:55:27 PM
Posts: 36,
Visits: 13
|
|
Thank you for the examples... much appreciated
Vincent Gabrielli
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/11/2007 12:54:00 PM
Posts: 2,
Visits: 1
|
|
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.
Swathi Teradata SQL certified.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/5/2009 2:58:22 PM
Posts: 134,
Visits: 13
|
|
Comment on SwathyG post
We can load fixed length data file so long as text file and the column length are in sync.
For Swathy_Teradata post
use
.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.log
Hope this helps.
Vinay
|
|
|
|
|
Forum Member
Group: Forum Members
Last Login: 10/2/2009 2:55:27 PM
Posts: 36,
Visits: 13
|
|
Thank you for all your assistance.. I have used some of the suggestions and
works very well... great team approach 
Vincent Gabrielli
|
|
|
|
|
Forum Newbie
Group: Forum Members
Last Login: 9/11/2007 12:54:00 PM
Posts: 2,
Visits: 1
|
|
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.
Swathi Teradata SQL certified.
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 7/10/2009 6:28:52 PM
Posts: 505,
Visits: 546
|
|
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
|
|
|
|
|
Supreme Being
Group: Forum Members
Last Login: 11/5/2009 4:40:02 PM
Posts: 717,
Visits: 466
|
|
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
|
|
|
|