fload script not working.

Tools & Utilities
Enthusiast

fload script not working.

CREATE MULTISET TABLE test.emp_fload ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
empno integer,
ename VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
DeptNo integer,
salary integer,
doj DATE FORMAT 'YYYY-MM-DD')
PRIMARY INDEX ( empno );

/a/bte_fload_emp_tab_all.txt

2,RAMA,20,4000,2010-01-04
7,SUJIT,10,7000,2010-01-05
10,RANJAN,20,4700,2010-01-04
4,RANU,30,6000,2010-01-06
1,HARI,10,3000,2010-01-07
11,UMESH,30,6900,2010-01-04
6,SIBA,10,6300,2010-01-08
8,RAKESH,20,3800,2010-01-09
9,RAMESH,20,4700,2010-01-10
5,KUNU,30,7000,2010-01-11
3,RAJA,20,5000,2010-01-12

script
------
#!/usr/bin/ksh
#########################################################################################
SESSIONS 5;
TENACITY 5;
SLEEP 5;
ERRLIMIT 50;
bteq .logon $dbenv/$dbusr,$dbpwd <

.SET RECORD VARTEXT ",";

DEFINE
v_empno (INTEGER)
,v_ename (VARCHAR(30))
,v_deptno (INTEGER)
,v_salary (INTEGER)
,v_doj (DATE)

FILE=/a/bte_fload_emp_tab_all.txt ;

SHOW;
BEGIN LOADING test.emp_fload ERRORFILES
test.fload_ET , test.fload_UV
CHECKPOINT 1000;
INSERT INTO test.emp_fload VALUES(
:v_empno
,:v_ename
,:v_deptno
,v_salary
,v_doj);
EOF
END LOADING;
.LOGOFF;

on running the ksh it throws error as

*** Failure 2637 BEGIN LOADING executed without Fast Load Init.
Statement# 1, Info =0
*** Total elapsed time was 6 seconds.

+---------+---------+---------+---------+---------+---------+---------+----
INSERT INTO RETAIL_APP. emp_fload VALUES(
:v_empno
,:v_ename
,:v_deptno
,v_salary
,v_doj);
*** Failure 3857 Cannot use value (or macro parameter) to match 'v_empno'.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+---------+---------+----
EOF
END LOADING;

END LOADING;
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and the word 'EOF'.
Statement# 2, Info =7
*** Total elapsed time was 1 second.

*** RC (return code) = 8
5 REPLIES
Enthusiast

Re: fload script not working.

lessthansymbol EOF

.SET RECORD VARTEXT ",";

DEFINE
v_empno (INTEGER)
,v_ename (VARCHAR(30))
,v_deptno (INTEGER)
,v_salary (INTEGER)
,v_doj (DATE)

FILE=/a/bte_fload_emp_tab_all.txt ;

SHOW;
BEGIN LOADING test.emp_fload ERRORFILES
test.fload_ET , test.fload_UV
CHECKPOINT 1000;
INSERT INTO test.emp_fload VALUES(
:v_empno
,:v_ename
,:v_deptno
,v_salary
,v_doj);
EOF
END LOADING;
.LOGOFF;
.QUIT;

$. emp_fload_allnew.ksh
ksh: SESSIONS: not found.
ksh: TENACITY: not found.
ksh: SLEEP: not found.
ksh: ERRLIMIT: not found.
BTEQ 12.00.00.04 Wed Jan 12 11:21:13 2011

*** Logon successfully completed.

.SET RECORD VARTEXT ",";
*** Error: Unrecognized command RECORD
+---------+---------+---------+---------+---------+---------+---------+----

DEFINE
v_empno (INTEGER)
,v_ename (VARCHAR(30))
,v_deptno (INTEGER)
,v_salary (INTEGER)
,v_doj (DATE)

FILE=/a/bte_fload_emp_tab_all.txt ;

v_empno (INTEGER)
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and the word 'DEFINE'.
Statement# 2, Info =12
*** Total elapsed time was 1 second.
Enthusiast

Re: fload script not working.



+---------+---------+---------+---------+---------+--- ------+---------+----

SHOW;

DEFINE
v_empno (INTEGER)
,v_ename (VARCHAR(30))
,v_deptno (INTEGER)
,v_salary (INTEGER)
,v_doj (DATE)

FILE=/a/bte_fload_emp_tab_all.txt ;

+---------+---------+---------+---------+---------+--- ------+---------+----
BEGIN LOADING test.emp_fload ERRORFILES
test.fload_ET , test.fload_UV
CHECKPOINT 1000;
*** Failure 2637 BEGIN LOADING executed without Fast Load Init.
Statement# 1, Info =0
*** Total elapsed time was 6 seconds.

+---------+---------+---------+---------+---------+--- ------+---------+----
INSERT INTO test. emp_fload VALUES(
:v_empno
,:v_ename
,:v_deptno
,v_salary
,v_doj);
*** Failure 3857 Cannot use value (or macro parameter) to match 'v_empno'.
Statement# 1, Info =0
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+--- ------+---------+----
EOF
END LOADING;

END LOADING;
$
*** Failure 3706 Syntax error: expected something between the beginning of
the request and the word 'EOF'.
Statement# 2, Info =7
*** Total elapsed time was 1 second.

+---------+---------+---------+---------+---------+--- ------+---------+----
.LOGOFF;
*** You are now logged off from the DBC.
+---------+---------+---------+---------+---------+--- ------+---------+----
.QUIT;
*** Exiting BTEQ...
*** RC (return code) = 8
Teradata Employee

Re: fload script not working.

It is not 100% clear to me about the contents of the FastLoad script.
You have KSH syntax and BTEQ commands, and those are not allowed in FastLoad scripts.

And the "#" is not a valid comment character.

If I am misinterpreting your posts, please correct me.
-- SteveF
Enthusiast

Re: fload script not working.

done...thanks
bwb
Teradata Employee

Re: fload script not working.

Not directly on topic, but just to be sure you don't run into surprises later...

I notice that you're creating a MULTISET table. I'm not sure whether or not you expect duplicate rows. You should be aware that the FastLoad protocol (used by FastLoad and the TPT Load Operator) will silently discard duplicate rows. If you really want duplicate rows, you need to use a different protocol/utility: MultiLoad; TPump; the TPT Update, Stream, and Inserter Operators; and BTEQ all use methods that preserve duplicate rows when targeting a MULTISET table.