Help with pulling data from IBM Mainframe into Teradata

Database

Help with pulling data from IBM Mainframe into Teradata

I am new to TD and I am trying to pull data from a flat file on the Mainframe into Teradata DB. Below is one sample row (with column names) that i have in my mainframe file. I need to insert the data to a table, but also making sure to remove the "," in between values.

:EMPNO, :NAME, :DEPTNO, :PHONENO, :JOBTITLE, :SALARY
1,'Yolanda',10,2990,'support',1000.00

Can any one help with the script below or direct me to something? This is the error that I am receiving: UTY0005 Bad data in the IMPORT command at position 15

.LOGTABLE IA_COGNOS_TEMP.YLP_TABLES5;
.LOGON TXXX/id,pword;
.BEGIN IMPORT MLOAD
TABLES EMPLOYEE;
.LAYOUT LAYOUTNAME;
.FIELD EMPNO * SMALLINT;
.FIELD NAME * VARCHAR(12);
.FIELD DEPTNO * SMALLINT;
.FIELD PHONENO * SMALLINT;
.FIELD JOBTITLE * VARCHAR(12);
.FIELD SALARY * DECIMAL(8,2);
.DML LABEL INSERTDML;
INSERT INTO EMPLOYEE
(EMPNO = :EMPNO
,NAME = :NAME
,DEPTNO = :DEPTNO
,PHONENO = :PHONENO
,JOBTITLE = :JOBTITLE
,SALARY = :SALARY);
.IMPORT INFILE 'TS.EMPLOYEE.TEST'
FORMAT VARTEXT ',' DISPLAY ERRORS NONSTOP
LAYOUT LAYOUTNAME
APPLY INSERTDML;
.END MLOAD;
.LOGOFF;

Thanks in advance,

Yolanda
1 REPLY
Enthusiast

Re: Help with pulling data from IBM Mainframe into Teradata

Hi Yolanda,

As per my knowledge, whenever we try to load vartext file, we should have all the fields as Varchar in the layout section.

Also if you could remove the single quotes in your data file for text fields, it would be better.

I think foll. script should work

.LOGTABLE IA_COGNOS_TEMP.YLP_TABLES5;
.LOGON TXXX/id,pword;
.BEGIN IMPORT MLOAD TABLES EMPLOYEE;

.LAYOUT LAYOUTNAME;

.FIELD EMPNO * VARCHAR(5);
.FIELD NAME * VARCHAR(12);
.FIELD DEPTNO * VARCHAR(5);
.FIELD PHONENO * VARCHAR(5);
.FIELD JOBTITLE * VARCHAR(12);
.FIELD SALARY * VARCHAR(8);

.DML LABEL INSERTDML;

INSERT INTO EMPLOYEE
(EMPNO = :EMPNO
,NAME = :NAME
,DEPTNO = :DEPTNO
,PHONENO = :PHONENO
,JOBTITLE = :JOBTITLE
,SALARY = :SALARY);

.IMPORT INFILE 'TS.EMPLOYEE.TEST'
FORMAT VARTEXT ',' DISPLAY ERRORS NONSTOP
LAYOUT LAYOUTNAME
APPLY INSERTDML;

.END MLOAD;
.LOGOFF;