bteq import not working

Connectivity
Enthusiast

bteq import not working

.LOGON a/b,pass123;
SET session dateform=ansidate;
SEL date;–to select current date
SEL time;–to select current time
CREATE VOLATILE TABLE retail_app.temptab1
(Column1 CHAR (10),
Column2 CHAR (10))
ON COMMIT PRESERVE ROWS;

select count(*) from retail_app.temptab1; — To check the count before insert.
.import report file= usera/b/myiportdatafile
.quiet on – remember it is “quiet” and not quite this is to limit output reporting
.repeat* – used with import to read until EOF is reached.
USING (col1 char(8), col2 char(8))
INSERT INTO retail_app.temptab1 (Column1, Column2)
values (:col1, :col2);
.quiet off – used to print output to logfile
select count(*)from retail_app.temptab1;
.QUIT ERRORCODE ;

$ksh -n abcd.ksh
abcd.ksh[6]: 0403-057 Syntax error at line 6 : `(' is not expected.

14 REPLIES
Junior Contributor

Re: bteq import not working

Is "abcd.ksh" the script from above?
How will ksh know that you want to call BTEQ?

Btw, this script will not work, unless "retail_app" is the user from LOGON, as a Volatile Table is always part of the user.

Dieter
Enthusiast

Re: bteq import not working

.LOGON a/b,passwd;
.IMPORT DATA FILE=sera/b/myiportdatafile/bteq_import_emp_tab_all.log, SKIP = 2
.QUIET ON
.REPEAT 30
USING
vempno (DECIMAL(10,0))
,vename (VARCHAR(30))
,vdeptno (DECIMAL(10,0))
,vsalary (DECIMAL(10,0))
INSERT INTO b.EMPloyee
( EMPNO,
ENAME,
DEPTNO,
SALARY )
VALUES
(:vempno,
:vename,
:vdeptno,
:vsalary);
.QUIT

This also does not work. Same error...
0403-057 Syntax error at line 24 : `(' is not expected.
After using it is throwing the error..
Junior Contributor

Re: bteq import not working

"0403-057 Syntax erro" is a Korn-Shell error message, but not Teradata.

How do you run that script?

bteq < abcd.ksh

Dieter
Enthusiast

Re: bteq import not working

i was trying to run using . abcd.ksh
Junior Contributor

Re: bteq import not working

Of course, if you run it using "bteq < abcd.ksh" you might want to rename it to "abcd.btq".

Btw, if you want to run it as a shell script it must look like this:

#!/bin/ksh
bteq <
your code here
EOF

This technique is called "inline redirection" or a "here-document", you can google for it.

Dieter
Junior Contributor

Re: bteq import not working

I just noticed that the forum software removed a part of my previous message:
Please use *two* consecutive < instead of one in the second line:

#!/bin/ksh
bteq < EOF
your code here
EOF

This technique is called "inline redirection" or a "here-document", you can google for it.

Dieter
Enthusiast

Re: bteq import not working

#!/usr/bin/ksh
bteq .logon ab/abc,$passwd <
.IMPORT VARTEXT ',' FILE=/aditya/bteq_import_emp_tab_all.txt, SKIP = 2
.QUIET ON
.REPEAT 30
USING
vempno (DECIMAL(10,0))
,vename (VARCHAR(30))
,vdeptno (DECIMAL(10,0))
,vsalary (DECIMAL(10,0))
INSERT INTO EMP1
( EMPNO,
ENAME,
DEPTNO,
SALARY )
VALUES
(:vempno,
:vename,
:vdeptno,
:vsalary);
.QUIT
EOF

---
file contains data like

2,RAMA,20,4000
7,SUJIT,10,7000
10,RANJAN,20,4700
4,RANU,30,6000
1,HARI,10,3000
11,UMESH,30,6900
6,SIBA,10,6300
---
on running the ksh it is throwing error as
*** Failure 2673 The source parcel length does not match data that was defi
ned.
Statement# 1, Info =2
*** Total elapsed time was 1 second.
---
emp1 table structure is
(
empno DECIMAL(10,0),
ename VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
DeptNo DECIMAL(10,0),
salary DECIMAL(10,0))
PRIMARY INDEX ( empno );

---

Enthusiast

Re: bteq import not working

Data file
===========

cat /import/abc/bteq_import_emp_tab_all.txt

2,RAMA,20,4000
7,SUJIT,10,7000
10,RANJAN,20,4700
4,RANU,30,6000
1,HARI,10,3000
11,UMESH,30,6900
6,SIBA,10,6300
8,RAKESH,20,3800
9,RAMESH,20,4700
5,KUNU,30,7000
3,RAJA,20,5000

CREATE SET TABLE b.emp1 ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
empno DECIMAL(10,0),
ename VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
DeptNo DECIMAL(10,0),
salary DECIMAL(10,0))
PRIMARY INDEX ( empno );

running the below ksh "bteq_import.ksh"
----------------------

bteq .LOGON a/b,pass123; <
.IMPORT VARTEXT ',' FILE=/import/abc/bteq_import_emp_tab_all.txt, SKIP = 2
.QUIET ON
.REPEAT 30
USING
vempno (DECIMAL(10,0))
,vename (VARCHAR(30))
,vdeptno (DECIMAL(10,0))
,vsalary (DECIMAL(10,0))
INSERT INTO b.EMP1
( EMPNO,
ENAME,
DEPTNO,
SALARY )
VALUES
(:vempno,
:vename,
:vdeptno,
:vsalary);
.QUIT
EOF

running the ksh
. bteq_import.ksh

*** Logon successfully completed.
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
.IMPORT VARTEXT ',' FILE=cat /import/abc/bteq_import_emp_tab_all.txt, SKIP = 2
2 input rows skipped.
+---------+---------+---------+---------+---------+---------+---------+----
.QUIET ON
*** Type QUIET OFF; to resume output.
+---------+---------+---------+---------+---------+---------+---------+----
.REPEAT 30
+---------+---------+---------+---------+---------+---------+---------+----
USING
vempno (DECIMAL(10,0))
,vename (VARCHAR(30))
,vdeptno (DECIMAL(10,0))
,vsalary (DECIMAL(10,0))
INSERT INTO test.EMP1
( EMPNO,
ENAME,
DEPTNO,
SALARY )
VALUES
(:vempno,
:vename,
:vdeptno,
:vsalary);

*** Starting Row 2 at Wed Jan 12 10:09:05 2011

*** Failure 2673 The source parcel length does not match data that was defi
ned.
Statement# 1, Info =2
*** Total elapsed time was 1 second.
Enthusiast

Re: bteq import not working

.IMPORT VARTEXT ',' FILE=/import/abc/bteq_import_emp_tab_all.txt, SKIP = 2
.QUIET ON
.REPEAT 30
USING
vempno (DECIMAL(10,0))
,vename (VARCHAR(30))
,vdeptno (DECIMAL(10,0))
,vsalary (DECIMAL(10,0))
INSERT INTO b.EMP1
( EMPNO,
ENAME,
DEPTNO,
SALARY )
VALUES
(:vempno,
:vename,
:vdeptno,
:vsalary);
.QUIT
EOF

running the ksh
. bteq_import.ksh

*** Logon successfully completed.
*** Transaction Semantics are BTET.
*** Character Set Name is 'ASCII'.

*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
.IMPORT VARTEXT ',' FILE=cat /import/abc/bteq_import_emp_tab_all.txt, SKIP = 2
2 input rows skipped.
+---------+---------+---------+---------+---------+---------+---------+----
.QUIET ON
*** Type QUIET OFF; to resume output.
+---------+---------+---------+---------+---------+---------+---------+----
.REPEAT 30
+---------+---------+---------+---------+---------+---------+---------+----
USING
vempno (DECIMAL(10,0))
,vename (VARCHAR(30))
,vdeptno (DECIMAL(10,0))
,vsalary (DECIMAL(10,0))
INSERT INTO b.EMP1
( EMPNO,
ENAME,
DEPTNO,
SALARY )
VALUES
(:vempno,
:vename,
:vdeptno,
:vsalary);

*** Starting Row 2 at Wed Jan 12 10:09:05 2011

*** Failure 2673 The source parcel length does not match data that was defi
ned.
Statement# 1, Info =2
*** Total elapsed time was 1 second.