Spaces inbetween data getting trimmed off

[krishobby]

Hi All,

I am loading the DB2 table with one of the extract files we get using the parallel jobs. The file has a character data with values like "ABC XYZ", when the data is loaded into the table the value becomes "ABC XYZ". The spaces inbetween are getting trimmed off. When we loaded the same data using DB2 Import it loads exactly as it is in the file like "ABC XYZ".

So I was wondering what could cause this,only thing we do in preprocessing is triming of the ^M characters before loading the files. We use sed to do trimming the ^M Characters.

Regards,
Chris

[ray.wurlod]

Can you post your example inside Code tags so we can see the differences?

Also explain precisely what function you are using to eliminate the ^M characters.

[krishobby]

This is the pipe delimited input file we get with four fields and the FIELD3 has spaces inbetween the data and End of the line we have the ^M Character (<CTRL>+V+M)

FIELD1|FIELD2|FIELD3|FIELD4
ABC|DEF|EFG<SPACE><SPACE><SPACE>IJK|123^M
XYZ|WYC|ABC<SPACE><SPACE>12|345^M

Its a simple pass through job with no transformations but when the data is loaded into the tables the table has

FIELD1|FIELD2|FIELD3|FIELD4
ABC|DEF|EFG<SPACE>IJK|123
XYZ|WYC|ABC<SPACE>12|345

which is, FIELD3 trims off multiple spaces into just one space in between the data.

We use sed 's/\^M//g' <oldfile>newfile to trim off the ^M character.

Let me know if any more detail is required.

[panchusrao2656]

what is happening if the field3 has only spaces, we had a similar issue while loading to Oracle using Load option ( bulk load). Whenever we have all spaces, it simply trims all spaces and loaded a NULL into Target. Can you confirm what is hapening with DB2 when you have all spaces in a field.

Thank You

[karrisuresh]

Can we use a load option when loading data from file to table where the transformer in between is used to change the datatype

I have a problem,
like,

Hi Suresh Garu,
My source is file and target is oracle
when I am usng the upsert method
the records are getting populated in the target
but when I am using the load method
the job is getting aborted giving the following error
Oracle_Enterprise_2,2: sh: line 1: sqlldr: command not found

The call to sqlldr failed; the return code = 32,512;

Unable to access the log file.

Oracle_Enterprise_2,2: The runLocally() of the operator failed.

pls find the attachment also
thanks

what is happening if the field3 has only spaces, we had a similar issue while loading to Oracle using Load option ( bulk load). Whenever we have all spaces, it simply trims all spaces and loaded a NULL into Target. Can you confirm what is hapening with DB2 when you have all spaces in a field.

Thank You

[ArndW]

With Oracle loads the APT_ORACLE_LOAD_DELIMITED setting affects how spaces are automatically trimmed, but that doesn't happen in DB2

[ray.wurlod]

What happens if you run that sed command over the file in UNIX, without DataStage in the mix? Are the internal spaces trimmed or unaffected?

Trying to isolate the location of the problem.

[krishobby]

Hi Ray,

Tried all the options to isolate the problem, I did the preprocessing outside Datastage in UNIX and no luck. Also tried loading the file without trimming off the ^M characters, and still this particular column the spaces were trimmed off inbetween the data. Any more suggestions?

[krishobby]

Hi Ray,

Tried all the options to isolate the problem, I did the preprocessing outside Datastage in UNIX and no luck. Also tried loading the file without trimming off the ^M characters, and still this particular column the spaces were trimmed off inbetween the data. Any more suggestions?

[ArndW]

...I did the preprocessing outside Datastage in UNIX and no luck...

Does that mean that the extra spaces didn't get trimmed out in the preprocessing?

If you add another output to a stage right before the DB2 write and output your FIELD3 value does it still have duplicate spaces?
What stage and method are you using to load to DB2?

The problem is odd, as DB2 varchar() will autotrim trailing spaces but not remove duplicate internal ones.