Handling of CHAR(x) Data: Differences between 5.2 and 7.x?

[ray.wurlod]

One of my clients has a number of jobs that extract data from CHAR(x) columns and use these to populate hashed files.
In DataStage 5.2, these seem to be handled as VarChar; there is no need to Trim but no trailing spaces appear.
In DataStage 7.0.1, running exactly the same job, trailing spaces appear.

Has anyone else experienced this?

What it means is that reference lookups against these hashed files fail, because the non-padded data in the input stream fails to be matched against the padded data in the hashed file.

There are 284 jobs affected; does anyone know whether there is a quick way (apart from hacking the DSX to change Char to VarChar for the hashed files' column definitions) to solve this problem?

[roy]

Hi,
this seems funny Ray, AFAIR(emember) even in 5.2 hash files put data as it is read with nothing done on them.
this means that even if you put in a char(10) a 20 caracters string it is stored with all 20 characters and in the case you state if varchar no trim is done on the actual string, so if you got "abcd " it is exactly what you'll get in the hash file if you use varchar with no other transformation.
did your client by any chance upgraded anything else besides DS?
maybe a new DB where the data now has trailing blanks?
or maybe a new DB client, that behaves differently?
I think the way is as you said a hack or if possible a DB change on the input data. (or the hard work which is actually doing it)

Good Luck,

[estevesm]

I have the same problem:

Table1 has Col1 as VARCHAR2(20) (Oracle). A Lookup on that column returns a 4096-byte long string, precisely 1 character + 4095 spaces. If I put a TRIM() in the lookup SQL I still get the maximum allowable varchar2.

So, I'll join Ray on this one...

[roy]

Hmmm,
can you specify the configuration ?
(DB, OS , etc'...)
also do you use dynamic hash files?
what is the stage used to read from DB populating the hash file?
are the hash file using create/delete or clear before insert?
if you use the view data option on the DB stage output link do you see the trailing spaces?

[ray.wurlod]

In my case the difference turned out to be in Red Brick changing how CHAR data types are handled in SELECT statements between versions 6.10 and 6.20; prior to 6.10 they had an automatic TRIM applied, from 6.20 this ceased to be done.

The problem was not in DataStage at all.

Sorry about the delay in posting this; and now I had to go back and check my facts.

The moral seems to be always to use Trim() when loading key columns in hashed files, and always to use Trim() in reference key expressions, just in case.