MLOAD is assuming one extra line and failing

Tools & Utilities
Enthusiast

MLOAD is assuming one extra line and failing

Hi All,

I am creating a text file using perl script and then using that txt file for MLOAD.

While writing the first record, I am writing the line as it is. Aftre that I am writing as"\nline" (so that the new record will start from new line).

During MLOAD, it is assuming one record extra and giving error that Field not found.

I am not finding any special character (new line,EOF etc) at the end of the records.

Can you please let me know how to handle this situation.

Thanks,

Terankit

16 REPLIES
Senior Supporter

Re: MLOAD is assuming one extra line and failing

I am not sure about this. Could you share a test perl script, ddl and mload script?

My first guess would be that all lines need to end with a \n.

But your last line doesn't.

So might be worth to write always "line\n"

Enthusiast

Re: MLOAD is assuming one extra line and failing

Hi Ulrich,

yes, that is why I am writing the first line as "line" and then all the lines as "\nline".In my file there is no new line after last line.No special character.

Its a big problem for me.I got this issue at very wrong time. Please help.

Thanks.

Senior Supporter

Re: MLOAD is assuming one extra line and failing

can you share the mload log?

Enthusiast

Re: MLOAD is assuming one extra line and failing

In my file there are 6 lines.

MLOAD log is saying:

considered recoreds: 7

satisfied: 6

out of range "first field" in 7th record.

I can post the exact log in some time. till then, i hope this may give you some idea.

Thanks.

Senior Supporter

Re: MLOAD is assuming one extra line and failing

can you share the perl code as well?

Senior Supporter

Re: MLOAD is assuming one extra line and failing

And pease just try once to add a \n at the last line as well...

Enthusiast

Re: MLOAD is assuming one extra line and failing

Please find the MLOAD log:

**** 20:20:31 UTY0817 MultiLoad submitting the following request:

     BEGIN TRANSACTION;

**** 20:20:31 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 20:20:31 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 20:20:32 UTY0817 MultiLoad submitting the following request:

     CHECKPOINT LOADING INTERVAL 0;

**** 20:20:33 UTY0817 MultiLoad submitting the following request:

     USING Ckpt(VARBYTE(1024)) INSERT DTZTAW.LOGTABLE_CMP_PARM_MLOAD  (Logtype,

     Seq, MLoadSeq, MLoadImpSeq, MLoadSrcSeq,

     MiscInt1,MiscInt2,MiscInt3,MiscInt4,

     MiscInt5,MiscInt6,MiscInt7,MiscInt8,CkptInterval,MLoadCkpt) VALUES (110, 1,

     0, 1, 0, 0, 0, 0, 0, 0, 0, 0, 0, 0, :Ckpt);

**** 20:20:33 UTY4203 Attempted to access out of range input data in field

     'IN_TACTIC_ID', file '/work/scripts/cmpbypass/temp/finerec.txt',record

     number '7'.

**** 20:20:33 UTY1803 Import processing statistics

     .                                       IMPORT  1     Total thus far

     .                                       =========     ==============

     Candidate records considered:........           7.......           7

     Apply conditions satisfied:..........           6.......           6

     Candidate records not applied:.......           0.......           0

     Candidate records rejected:..........           0.......           0

     ========================================================================

     =                                                                      =

     =          Logoff/Disconnect                                           =

     =                                                                      =

     ========================================================================

**** 20:20:40 UTY6212 A successful disconnect was made from the RDBMS.

**** 20:20:40 UTY2410 Total processor time used = '0.398518 Seconds'

     .       Start : 20:20:20 - TUE JUL 24, 2012

     .       End   : 20:20:40 - TUE JUL 24, 2012

     .       Highest return code encountered = '12'.

Enthusiast

Re: MLOAD is assuming one extra line and failing

If I will use line\n for the last line then there willl be an empty line at the end which will create issue. that is why I kept it as /nline.

Senior Supporter

Re: MLOAD is assuming one extra line and failing

"Attempted to access out of range input data in field 'IN_TACTIC_ID'"

Is IN_TACTIC_ID the first column in the file?

Do you have unicode chars in your file? or additional \n within the fields?

So, without sharing the DDL, the file and mload script it remains a lot of guessing.

And:

did you ever try it with a \n at the end of each line?

I attached a very simple test.

test1 loads a file with 6 rows - each has a \n at the end.

test1 succeed.

test2 loads a file with 6 rows - the last one has no \n at the end

test2 is loading only 5 rows.

check the logs...