All Forums Tools
terankit 77 posts Joined 03/12
24 Jul 2012
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

ulrich 816 posts Joined 09/09
25 Jul 2012

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"

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
25 Jul 2012

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.

ulrich 816 posts Joined 09/09
25 Jul 2012

can you share the mload log?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
25 Jul 2012

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.

ulrich 816 posts Joined 09/09
25 Jul 2012

can you share the perl code as well?

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

ulrich 816 posts Joined 09/09
25 Jul 2012

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

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
25 Jul 2012

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'.

terankit 77 posts Joined 03/12
25 Jul 2012

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.

ulrich 816 posts Joined 09/09
25 Jul 2012

 

"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...

 

 

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
25 Jul 2012

How would I know that this is the last record?so that I can write it as "line" in place of line\n?

terankit 77 posts Joined 03/12
25 Jul 2012

The MLOAD script is:

.LOGTABLE logtable;
.RUN FILE /work/user/user1;

DROP TABLE tab_WT;
DROP TABLE tab_ET;
DROP TABLE tab_UV;

.BEGIN MLOAD
  TABLES tab    WORKTABLES  tab_WT
    ERRORTABLES tab_ET
                tab_UV
;
 
 .LAYOUT INPUTLAYOUT;
 .FIELD IN_STRTGY_SRC_CD     1   CHAR(2)  ;
 .FIELD IN_TACTIC_ID      3   CHAR(50) ;
 .FIELD IN_TRGT_TYP_CD      53  CHAR(3)  NULLIF IN_TRGT_TYP_CD=' ';
 .FIELD IN_EVNT_ACTVY_TYP_CD     56  CHAR(2)  NULLIF IN_EVNT_ACTVY_TYP_CD=' ';
 .FIELD IN_EVNT_ACTVY_TYP_CD_VRSN   58  CHAR(2)  NULLIF IN_EVNT_ACTVY_TYP_CD_VRSN=' ';
 .FIELD IN_ENCODING_IND      60  CHAR(1)  NULLIF IN_ENCODING_IND=' ';
 .FIELD IN_FIELD_DESC               61  CHAR(17) NULLIF IN_FIELD_DESC=' ';
 .FIELD IN_FILENAME      78  CHAR(50) NULLIF IN_FILENAME=' ';
 .FIELD IN_FILENAME_SUFFIX     128 CHAR(8)  ;
 .FIELD IN_VNDR_TRANSMISSION_CD     136 CHAR(2)  ;
 .FIELD IN_EMAIL_NOTIF_ADDR     138 CHAR(30) ;
 .FIELD IN_PROCESS_TYP      168 CHAR(1)  ;
 .FIELD IN_USRID             169 CHAR(8)  ;
 .FIELD IN_FILLER             177 CHAR(1)  NULLIF IN_FILLER=' ';
 .FIELD IN_ATTEMPT             178 CHAR(2)  NULLIF IN_ATTEMPT=' ';
 .FIELD IN_ERRREC_IND             180 CHAR(1)  NULLIF IN_ERRREC_IND=' ';
 .FIELD IN_TIMESTAMP             181 CHAR(26) NULLIF IN_TIMESTAMP=' ';
 

.DML LABEL INSERT_FINEREC;

INSERT INTO tab      (
 STRTGY_SRC_CD
 ,TACTIC_ID
 ,TRGT_TYP_CD
 ,EVNT_ACTVY_TYP_CD
 ,EVNT_ACTVY_TYP_CD_VRSN
 ,ENCODING_IND
 ,FIELD_DESC
 ,FILENAME
 ,FILENAME_SUFFIX
 ,VNDR_TRANSMISSION_CD
 ,EMAIL_NOTIF_ADDR
 ,PROCESS_TYP
 ,USRID
 ,CNTRL_ID
 ,EMAIL_IND
 ,ITERATION_ERR_REC
 ,VALIDATION_ERR_REC
 ,ATTEMPT_NO
 ,LOAD_TIMESTAMP
 ,CNTRL_DTL_MOVED_IND
 )
VALUES (
 :IN_STRTGY_SRC_CD,
 :IN_TACTIC_ID,
 :IN_TRGT_TYP_CD,
 :IN_EVNT_ACTVY_TYP_CD,
 :IN_EVNT_ACTVY_TYP_CD_VRSN,
 :IN_ENCODING_IND,
 :IN_FIELD_DESC,
 :IN_FILENAME,
 :IN_FILENAME_SUFFIX,
 :IN_VNDR_TRANSMISSION_CD,
 :IN_EMAIL_NOTIF_ADDR,
 :IN_PROCESS_TYP,
 :IN_USRID,
 NULL,
 NULL,
 NULL,
 :IN_ERRREC_IND,
 :IN_ATTEMPT,
 :IN_TIMESTAMP,
 NULL
);
 
.IMPORT INFILE /work/scripts/cmpbypass/temp/finerec1.txt
      FORMAT UNFORMAT
      LAYOUT INPUTLAYOUT
      APPLY INSERT_FINEREC;

.END MLOAD;

.LOGOFF;

ulrich 816 posts Joined 09/09
25 Jul 2012

unformat is likely to create the issue.

check

http://forums.teradata.com/forum/tools/mload-vartext-format

Freds last statement

"UNFORMAT expects no record delimiter; if there is one you can define it as FILLER so it is ignored."

So you can either add a filler with char(1) - at the end or you switch to TEXT

And don't forget to have a \n IN EACH ROW!

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
25 Jul 2012

I have used FORMAT TEXT.

The error I am getting is:

**** 10:32:59 UTY0817 MultiLoad submitting the following request:
     BEGIN TRANSACTION;
**** 10:32:59 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 0;
**** 10:32:59 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 0;
**** 10:33:00 UTY0817 MultiLoad submitting the following request:
     CHECKPOINT LOADING INTERVAL 0;
**** 10:33:01 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);
**** 10:33:01 UTY4015 Access module error '35' received during 'read' operation
     on record number '5': 'EOF encountered before end of record'
**** 10:33:01 UTY1803 Import processing statistics
     .                                       IMPORT  1     Total thus far
     .                                       =========     ==============
     Candidate records considered:........           5.......           5
     Apply conditions satisfied:..........           5.......           5
     Candidate records not applied:.......           0.......           0
     Candidate records rejected:..........           0.......           0
     ========================================================================
     =                                                                      =
     =          Logoff/Disconnect                                           =
     =                                                                      =
     ========================================================================
**** 10:33:07 UTY6212 A successful disconnect was made from the RDBMS.
**** 10:33:07 UTY2410 Total processor time used = '0.399718 Seconds'
     .       Start : 10:32:47 - WED JUL 25, 2012
     .       End   : 10:33:07 - WED JUL 25, 2012
     .       Highest return code encountered = '12'.
 

terankit 77 posts Joined 03/12
25 Jul 2012

And If I will use UNFORMAT, then what size should I give for FILLER at the end?

ulrich 816 posts Joined 09/09
25 Jul 2012

Option 1:

remove the \n from all rows and use UNFORMAT

Option 2:

add the \n to ALL rows - in your last LOG you can see the impact of Once missing \n - and use TEXT

Option 3:

 

add the \n to ALL rows - in your last LOG you can see the impact of Once missing \n - and use UNFORMAT and add a Fille CHAR(1) at the end of the layout definition.

 

In all three options you need to change your load file!

 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

terankit 77 posts Joined 03/12
25 Jul 2012

Thanks a lot to you Ulrich..its working now. I used \n with each record and then FORMAT TEXT.

One more query i have :-).

I have few fields defined as Integer or SmallInt. If there is Alphabet for these fields in File then how can I handle that.Is there anything like NULLIF field = " ".

 

Thanks again.

You must sign in to leave a comment.