mload for multiple tables

Database
Enthusiast

mload for multiple tables

hello all,
i have an mload script which is giving error.
please help in resolving that.

mload script:
----------------------
.logtable banklog;
.logon xxxx/abcd,xyz;

.begin mload
tables accounts,customer,trans
worktables wt_accounts,wt_customer,wt_trans
errortables
er_accounts uv_accounts,
er_customer uv_customer,
er_trans uv_trans;
.layout record_layout;
.filler in_code * char(1);

.field in_accno * char(5);
.field in_number * char(10);
.field in_city * char(10);

.field in_custno * char(5);
.field in_lname * char(10);
.field in_fname * char(10);

.field in_transno * char(5);
.field in_accid * char(10);

.dml label insert_acct;
insert into accounts values(:in_accno,:in_number,:in_city);
.dml label insert_cust;
insert into customer values(:in_custno,:in_lname,:in_fname);

.dml label insert_trans;
insert into trans values(:in_transno,:in_accid);
.import infile b1.txt
layout record_layout
format text
apply insert_acct where in_code ='A'
apply insert_cust where in_code ='C'
apply insert_trans where in_code ='T';
.end mload;
.logoff;

flat file data:
----------------
A100 20 bangalore
C1000 james madas
T200007001

error:
-------
UTY 4203:attempted to access out of range input data in field 'in_custno' ,file
'file.txt' ,record number '1';

in my example the row with starting code 'A' should go to 'accounts' table,
with 'C' should go to 'customer' table, with 'T' should go to 'trans' table.

please help me in successfull execution of this script.

Thanks all.
9 REPLIES
Enthusiast

Re: mload for multiple tables

1. It seems there is a problem in the declaration of the file.
Try one thing, define everything as VARCHAR.

2. your layout definition doesn't match with the data file.I guess all fields in the data file must
be separated by a delimiter(eg: space,tab,|).

3. Try splitting the data manually as the layout definiton you will figure out what i mean.

It will be great if you can provide the table ddl.
Enthusiast

Re: mload for multiple tables

Hi Indra,
the format used is 'text'.thats why we need to use 'char'.
'filler' wont work with 'varchar'.
Junior Supporter

Re: mload for multiple tables

If you must adhere to the 'format text' you may try this with the data you provided (not tested):

.logtable banklog;
.logon xxxx/abcd,xyz;

.begin mload
tables accounts,customer,trans
worktables wt_accounts,wt_customer,wt_trans
errortables
er_accounts uv_accounts,
er_customer uv_customer,
er_trans uv_trans;
.layout record_layout;
.field in_code 1 char(1);

.field in_accno 2 char(3);
.FILLER filler01 * char(1);
.field in_number * char(2);
.FILLER filler02 * char(1);
.field in_city * char(10);

.field in_custno 2 char(4);
.FILLER filler03 * char(1);
.field in_lname * char(5);
.FILLER filler04 * char(1);
.field in_fname * char(5);

.field in_transno 2 char(5);
.field in_accid * char(10);

.dml label insert_acct;
insert into accounts values(:in_accno,:in_number,:in_city);
.dml label insert_cust;
insert into customer values(:in_custno,:in_lname,:in_fname);

.dml label insert_trans;
insert into trans values(:in_transno,:in_accid);
.import infile b1.txt
layout record_layout
format text
apply insert_acct where in_code ='A'
apply insert_cust where in_code ='C'
apply insert_trans where in_code ='T';
.end mload;
.logoff;

HTH

Cheers.

Carlos.
Enthusiast

Re: mload for multiple tables

Hi CarlosAL!
Thanks for your reply.
please let me know is there any alternate way other than 'format text'
to achieve the task .

MLOAD task
----------------
1.different length rows (from different ODSs) are there in a single flatfile.
2.perticular record should go to perticular table only(on the base of 'in_code' as in my example).

Enthusiast

Re: mload for multiple tables

Hi CarlosAL,
I tried to run your script, but its giving errors. Can you please post the script with corrections
needed.

Thank you.
Enthusiast

Re: mload for multiple tables

I guess one quick solution is:

As you have a indicator which in your case is in_code you can split the file in UNIX into three different files and then have three simple mloads.

If you want to bundle them together then you can have a .scr script in unix to call these three mloads in sequence and then call the new master .scr through the scheduling tool.

Thanks
Jeet

Re: mload for multiple tables

Hi,

I want to load Data into Two Different Tables form one flat file.

The following one is the code.

.LOGTABLE IQMODEL.EMP_LOC_LT1;

.LOGON dbc/idwloadctl,idwloadctl07;

.BEGIN IMPORT MLOAD

 TABLES      IQMODEL.EMPLOYEE,IQMODEL.LOCATION

 WORKTABLES  IQWORK.EMP_WT1,IQWORK.LOC_WT1

 ERRORTABLES IQWORK.EMP_ET1 IQWORK.EMP_UV1,

             IQWORK.LOC_ET1 IQWORK.LOC_UV1 ;

 .LAYOUT EMP_LOC_LO;

 .FIELD employee_id    * VARCHAR(1200);

 .FIELD first_name     * VARCHAR(1200);

 .FIELD last_name      * VARCHAR(1200);

 .FIELD email          * VARCHAR(1200);

 .FIELD phone_number   * VARCHAR(1200);

 .FIELD hire_date      * VARCHAR(1200);

 .FIELD job_id         * VARCHAR(1200);

 .FIELD salary         * VARCHAR(1200);

 .FIELD manager_id     * VARCHAR(1200);

 .FIELD department_id  * VARCHAR(1200);

 .FIELD loc_id         * VARCHAR(1200);

 .FIELD loc_desc       * VARCHAR(1200);

 .DML LABEL app_emp;

 INSERT INTO IQMODEL.EMPLOYEE

 (

 employee_id      =:employee_id,

 first_name       =:first_name,

 last_name        =:last_name,

 email            =:email,

 phone_number     =:phone_number,

 hire_date        =:hire_date,

 job_id           =:job_id,

 salary           =:salary,

 manager_id       =:manager_id,

 department_id    =:department_id

 );

 .DML LABEL app_loc;

 INSERT INTO IQMODEL.LOCATION

 (

 loc_id         =:loc_id,

 loc_desc       =:loc_desc

 )

 .IMPORT INFILE '/staging/idw/qenv/test/subbiah/two_tables_data.txt'

  LAYOUT EMP_LOC_LO

  FROM 2

  FORMAT VARTEXT '|'

  APPLY app_emp

  APPLY app_loc;

 .END MLOAD;

 .LOGOFF;

When I exevute this  Im getting the followind error

**** 23:30:55 UTY1415 DML 'APP_EMP' is never referenced.

**** 23:30:55 UTY1415 DML 'APP_LOC' is never referenced.

Please Put in your Inputs People to solve this.

Junior Contributor

Re: mload for multiple tables

There's a missing semicolon before the .IMPORT

Re: mload for multiple tables

Thanks Dnoeth.It Works after including the Semicolon.