Counts Rows having Column length overflow(s)

Database
Teradata Employee

Counts Rows having Column length overflow(s)

Hello,

 

I am using Multiload to load a table from Delimited Text file and the error is Column length overflow(s) in row X for column XX. I need to figure out how many such rows exist in the file with same issue. 

 

1- Is there a way in Multiload to skip these rows and log into an error/log table?

2- What could the best possible workaround? 

 

Br,

Br,
Hassam

Accepted Solutions
Teradata Employee

Re: Counts Rows having Column length overflow(s)

Oh, so you're getting the error from an axsmod. If it gives you a row/column number that's a start. You might have to look at the file yourself and find fields that are too long. For example, an awk script to do this might look like:

 

#!/usr/bin/awk -f

BEGIN { FS = "|" }         # If the delimiter is '|'

length($1) > 20 { print "Record number", NR, "Field 1 length is", length($1) }   # whatever the max length should be

length($2) > 30 { print "Record number", NR, "Field 2 length is", length($2) }

<etc.>

1 ACCEPTED SOLUTION
4 REPLIES
Teradata Employee

Re: Counts Rows having Column length overflow(s)

These rows should go into the Application Error Table (UV_<tablename> by default).  See the Multiload manual from info.teradata.com, "Handling Teradata MultiLoad Errors," for details on how to read this table.

Teradata Employee

Re: Counts Rows having Column length overflow(s)

The error table doesn't contain any entry at the end of Mload execution.The error details I get:

UTY4014 Access module error '60' received during 'pmReadDDparse' operation: 'Column length error, row not returned !ERROR! Delimited Data Parsing error: Column length overflow(s) in row X for column XX'

Br,
Hassam
Teradata Employee

Re: Counts Rows having Column length overflow(s)

Oh, so you're getting the error from an axsmod. If it gives you a row/column number that's a start. You might have to look at the file yourself and find fields that are too long. For example, an awk script to do this might look like:

 

#!/usr/bin/awk -f

BEGIN { FS = "|" }         # If the delimiter is '|'

length($1) > 20 { print "Record number", NR, "Field 1 length is", length($1) }   # whatever the max length should be

length($2) > 30 { print "Record number", NR, "Field 2 length is", length($2) }

<etc.>

Teradata Employee

Re: Counts Rows having Column length overflow(s)

Use the DISPLAY ERRORS option on your .INPUT statement to log the records in error, as well as the NOSTOP option so that the first error won't terminate the job.