All Forums Tools
ksaikrishna 26 posts Joined 08/11
03 Dec 2014
Mload utility is loading unexpected data into table

Hi All,
I have build below Mload script to load the data into table.Sample data and final output in table  also given below
 
.LOGTABLE tdbname.Logtable001_mld;
.logon <Dbname>/usename,password;
drop table dbname.Employee;
drop table dbname.WT_Employee;
drop table dbname.ET_Employee;
drop table dbname.UV_Employee;
create table dbname.Employee(
EmpNo SMALLINT,
DeptNo SMALLINT,
Salary DECIMAL (8,2)
)
unique primary index(EmpNo ) ;
.BEGIN MLOAD TABLES dbname.Employee;
.LAYOUT Record_Layout;
.FIELD EmpNo * SMALLINT;
.FIELD DeptNo * SMALLINT;
.FIELD Salary * DECIMAL (2,2);
.DML LABEL insdml;
INSERT INTO dbname.Employee (EmpNo,DeptNo, Salary)VALUES(:EmpNo,:DeptNo, :Salary);
.IMPORT INFILE /home/WBTstudents/wbt7412/cust_multi_load_1.txt
format text
LAYOUT Record_Layout
APPLY insdml;
.END MLOAD;
.LOGOFF;
.QUIT;
 
 
Sample data in file :
201012000.52
221012000.52
 
DAta in table after loading :
12,338 12,337 0.49
12,850 12,337 0.49
 
 
 
 

Fred 1096 posts Joined 08/04
03 Dec 2014

.LAYOUT describes the input data, not the table into which you are loading. Your input file is text, not binary.
Looks like in your case EmpNo may be CHAR(4), DeptNo may be CHAR(2), and Salary may be CHAR(7)?

ksaikrishna 26 posts Joined 08/11
04 Dec 2014

Hi Fred,
I amend the code as you suggested .Still i am facing the same issue.
Can some one help me on this ?
 
Thanks,
Sai

Ivyuan 63 posts Joined 01/10
16 Dec 2014

You can try the following layout:
.FIELD EmpNo * char(4);
.FIELD DeptNo * char(4);
.FIELD Salary * char(3);
for this specific data file.
BTW, text format requires all CHAR or ANSIDATE data types.
 

You must sign in to leave a comment.