NLS READ FIXEDWIDTH

[just4geeks]

One of my jobs aborted after getting 50 such warnings.

Code: Select all

nls_put_fixedwidth() - data for column RETAIL exceeds column width (5), row 1212 (approx), data = 32732 

Can anyone tell me what the error means? Thanks for your time...

[chulett]

A 'put' would generally mean a write, not a read. Sounds like you are writing to a fixed-width file and you are sending more data to a column than will fit in it. This is not allowed in a fixed-width file.

[just4geeks]

A 'put' would generally mean a write, not a read. Sounds like you are writing to a fixed-width file and you are sending more data to a column than will fit in it. This is not allowed in a fixed-width file.

Yeah, in my job I read data off a database and dump them in a text file with fixed width records. But I know for sure that column RETAIL is 5 char long. I checked it in the database. Also, the job did dump some records in the text file. When I looked up data 32732, I found that some rows did contain that value. As I looked for more 32732's I got the following error,

Code: Select all

job_extraction..SF_Data.DataFile: nls_read_fixedwidth() - row 2573, column RETAIL, required column missing

[DSguru2B]

Is this the last column in order? Some other data in some previous column must have taken up enough space as to not allow for another column. Check for each column, one by one.

[ralleo]

With the first error message, I have come across some server error messages where if the fieldname or header , i.e. RETAIL, 6 character is longer than your defined column width 5, you get such errors. Trying increasing it to 6 and see what happens.

Are you reading from a comma delimited file to a fixed-width file?
On your second error message, if you are missing say a field in the comma delimited file, you can get this message, i.e. you are missing a comma some where in the file you are reading from.

Ritchie



--------------------------------------------------------------------------------------
Throw a lucky man into the sea and he would come up with a fish in his mouth.

[ray.wurlod]

That the error is being generated by nls_read_fixedwidth() informs us that the error is occurring during import.

Add a reject-handling link to the Sequential File stage that is reading the file to capture any row that fails to satisfy the main output link's metadata, and direct those rows to - for example - a Peek stage for further analysis.

They will be given to you as a raw string - because there is no information about how to parse them into columns. You can write this to a file, or parse it using alternative metadata with a Column Import stage.

[just4geeks]

Is this the last column in order? Some other data in some previous column must have taken up enough space as to not allow for another column. Check for each column, one by one.

No, this is not the last column in order. It lies in between two columns in a group of 7 columns.

How do I check if data in another column is flowing over to another column? I say this, because data in other columns in the fixed width file all conform to the column width.

[just4geeks]

That the error is being generated by nls_read_fixedwidth() informs us that the error is occurring during import.

Add a reject-handling link to the Sequential File stage that is reading the file to capture any row that fails to satisfy the main output link's metadata, and direct those rows to - for example - a Peek stage for further analysis.

That sounds like a good idea. I use Server jobs. Is there a equivalent of Peek Stage in Server jobs?

[just4geeks]

With the first error message, I have come across some server error messages where if the fieldname or header , i.e. RETAIL, 6 character is longer than your defined column width 5, you get such errors. Trying increasing it to 6 and see what happens.

Are you reading from a comma delimited file to a fixed-width file?
On your second error message, if you are missing say a field in the comma delimited file, you can get this message, i.e. you are missing a comma some where in the file you are reading from

I renamed the column name to RETAL. And I get the error again.

I am reading from a Oracle database and dumping the results into a fixed width file.

[ralleo]

In your transformer, create a stage variable to test the number of characters in RETAIL column, i.e.
if len(trim(RETAIL) > 5 Then "N" else "Y".

Use this as a constraint and write to to a sequential file. You can then view the values to see why they are failing.


Ritchie.

--------------------------------------------------------------------------------
I always dreamed of being an F1 driver but have caught the DataStage bug.

[ray.wurlod]

That the error is being generated by nls_read_fixedwidth() informs us that the error is occurring during import.

Add a reject-handling link to the Sequential File stage that is reading the file to capture any row that fails to satisfy the main output link's metadata, and direct those rows to - for example - a Peek stage for further analysis.

That sounds like a good idea. I use Server jobs. Is there a equivalent of Peek Stage in Server jobs?

Alas, no. I've never seen mention of nls_read_fixedwidth() in terms of selecting from Oracle before, so this message might have come when DataStage is checking the metadata of your write to a fixed-width file. It's that, I think, that threw me - it's more difficult to set up fixed-width reading in parallel jobs, which is where I normally get to see this particular message.
Is SFData a Sequential File stage or an Oracle stage?

[chulett]

Alas, no. I've never seen mention of nls_read_fixedwidth() in terms of selecting from Oracle before, so this message might have come when DataStage is checking the metadata of your write to a fixed-width file.

The problem definition is a little confusing. First mention of the 'nls_put_fixwidth' problem apparently came from reading Oracle and writing to a flat file. The 'nls_read_fixedwidth' error then came when I assume the OP tried a View Data on those same records? Perhaps in another job?

In your output Sequential File stage, do you have the 'First line is column names' option checked? This would be the first place to check as the column names themselves must fit into the column sizes defined for a fixed-width file. Since you don't throw this error right off the bat I'm assuming that's not the case.

What is the metadata you have for the RETAIL column in Oracle? In your job? In all stages? You could very well have a Char(5) at the target end and something like a bigger Char field on the source side for all we know. Without a proper trim those trailing spaces will cause your error but not be all that obvious to the naked eye. Does the problem get solved if you change your derivation just before the Sequential File stage to substring the value? In essence:

Code: Select all

Link.Retail[1,5]

Of course, use your real link and column name there.

[ray.wurlod]

"Job aborted after 50 warnings" appears to rule out View Data as the culprit.

[just4geeks]

The 'nls_read_fixedwidth' error then came when I assume the OP tried a View Data on those same records? Perhaps in another job?

In your output Sequential File stage, do you have the 'First line is column names' option checked?

What is the metadata you have for the RETAIL column in Oracle? In your job? In all stages?

Does the problem get solved if you change your derivation just before the Sequential File stage to substring the value?

I did a view data on those records in the same job and not in a different job.

I do not have the 'First line is column names' option checked.

The metadata is char(5) all throughout the job.

I am running the job with a reject link as Ray suggested. After which, I will post the results here, and then run the job with derivation changed as Craig suggested.

[chulett]

No, the original 'culprits' were the 50 put messages. View data introduced the 'read' errors during diagnosis. As far as I can tell.