DSXchange

Unable to trim the data while loading into target using db2 bulk load stage. The target column is CHAR datatype. But data gets trimmed when i use DB2 API stage. Can some one help me on fixing this issue.

You cannot trim a CHAR column. A Char(3) column always contains 3 characters, a TRIM(' ':' ':' ') put into a Char(3) column remains as 3 spaces.

What is the exact problem?

The problem is we need to trim the data while we load into DB2 bulk stage. It is loaded with spaces for CHAR column. But with API stage we are able to load the data porperly for the same CHAR column.Is there any additional property or environmental variable that need to be defined for Bulk stage.

Could you please give an example of what is not working? As noted before, a CHAR() column cannot be trimmed of excess spaces, so I am unclear as to what the problem is that you are experiencing.

Hi,

please find the sample value when i loaded the data using API stage and Bulk stage.

API Stage:
Column1 (CHAR 15)
--------------------
0717866

Bulk Stage:
Column1 (CHAR 15)
--------------------
0717866--------

Note: i hve given --- to indicate the spaces

The sample given in Bulk stage is loaded with 7 digits where as remaining place values are padded with spaces. But this s not happening for same Char (15) column using API stage. I completly understand that a CHAR column be trimmed. but am not sure how the data loaded using API got trimmed.

Thanks

Even if the source data in the API were trimmed without trailing blanks, they would be re-inserted by the database, since a Char(15) column must contains 15 characters.
Is there is a difference in database content between the two methods?

Yes,

when i load the data thr API, and select the value cursor points upto 7 digits for CHAR 15 column and in BULK stage upto 7 digits values are loaded and remaining 8 place values are padded with spaces.

API Stage:
Column1 (CHAR 15)
--------------------
0717866

Bulk Stage:
Column1 (CHAR 15)
--------------------
0717866--------

Note: i hve given --- to indicate the spaces

My point is that after the API stage inserts the data into the DB it will have the exact same value as with the bulk stage.

Perhaps the API stage is padding with something other than spaces? As noted, it cannot behave as if it were a varchar so something must be there... what is your PADCHAR?

hi,

I used $APT_STRING_PADCHAR=0x0 set at project level

Check and see if your field is indeed being padded with that character, in Oracle I would use the dump() function, hopefully there is some kind of DB2 equivalent.

Replace your API stage with a Sequential file and write into it as a test. Open the file in Unix and check if the char column is padded with null character or something else.

Char type will be padded with spaces as Craig/ArndW have already mentioned.