DSXchange

We just applied Datstage 8.1 Fixpack 1 on our AIX server and found one rather disheartening bug, and i thought I'd report it here as well.

We have some old table definitions in DB2 where the datatype was foolishly set to char instead of varchar, and we haven't honestly gotten around to converting them to varchar.

Anyway, the datstage job took varchar source fields and inserted them into char target fields.

The column definitions for the source stage was varchar, and the column definitions for the target stage was char.

Datastage pads space in the char field with ascii nulls. (chr(0))

So effectively before patching, when we did a "select length(fieldname) from tablename) for a 6 character value in the char(12) field, you'd get 6. Now it actually returns 12.

Even worse, this is a primary key field. So even queries like "select * from table where pk=val" did not work any more. We had to do a mass update statement to clear it all up, but it's still worth noting.

Double check before applying this patch that you don't have any char datatypes that are narrower than the field width. It will mess up your data and make it useless.

For example, for whatever reason the people who designed this table set country code to be char(6) even though it should be a char(2) field. So doing a basic "select * from table where country='US' " would return zero rows because it's all right padded with ascii nulls.

This is an unacceptable bug IMO. We're logging a ticket with IBM right now.

Did you do a clean install or an upgrade in place?

As far as I can remember APT_STRING_PADCHAR has always defaulted to 0x0 out of the box. It can be changed at the project level or job level to some other value such as 0x20 (space).

Are you saying that you used to have an override for APT_STRING_PADCHAR and the update lost it?

Mike

We did an upgrade - it must have overwritten the setting... That's bizarre...

That behaviour has been reported here before, from what I recall.

So that's what it was. The previous guy who set up this server had overridden that value, and the fixpack did not preserve the configuration value.

Still a rather unpleasant "feature" of the fixpack, but thanks for setting us straight...

Once we changed it from 0x0 to 0x20 everything worked fine.

I guess some databases prefer 0x0 as padchars? DB2 doesn't seem to be one of them eh...

0x0 as a default stems from the fact that this is the C language string termination character. C code will read the string only as far as the first \0 character. It's not anything to do with DB2.