All Forums Database
yphillips 4 posts Joined 04/08
01 Apr 2008
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.00Can 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

dhirajpalse 36 posts Joined 01/08
03 Apr 2008

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;

Dhiraj

You must sign in to leave a comment.