Loading to DB2 with PX

mfavero · Post by **mfavero** » Fri Apr 11, 2008 2:47 pm

I have written my first Parallel jobs and when I look at the data loaded to the DB2 table, the CHAR fields are padded with 'low values' (x'00') not spaces. I create a dataset in the first job and merge it with a seq file in the second and send the rejects (no match for the seq file) to a data set and just three columns to a small DB2 table.
I use the Transformer to send all columns to the the data set output link and split 2 columns and create a third column with a literal to the other output link which goes to the DB2 UDB API stage.
What am I doing to cause x'00' to pad the DB2 table? The dataset doesn't LOOK like it has any x'00's at the end.

ray.wurlod · Post by **ray.wurlod** » Fri Apr 11, 2008 4:48 pm

You are leaving APT_STRING _PADCHAR environment variable set to its default value (0x00). If you want space padding, bring this into your job as a job parameter and change its value to a single space character or, if you must, 0x20.

bcarlson · Post by **bcarlson** » Fri Apr 11, 2008 4:52 pm

You can also specify the pad character in the column layout. When you write to DB2, make sure the field length in DataStage is the same as the target field in DB2. If DataStage's definition is shorter, then it won't matter if the pad char is set. DB2 will automatically fill it with x00.

Brad.

bcarlson · Post by **bcarlson** » Fri Apr 11, 2008 5:06 pm

Okay, my bad. DB2 will pad as long as your char field in DataStage is padded. Sorry for the confusion. I did a test writing a char(5) to a char(10) in DB2 and it worked fine, all was space padded.

Brad.

mfavero · Post by **mfavero** » Sat Apr 12, 2008 9:12 pm

I find this all very strange when used to server of course, but I did determine that the Merge Stage caused the padding with x'00' on the key fields.

I managed to work around the problem by putting a transform stage with a Convert(from_Merge.column_name, char(00), ' '). This turned all the nulls to spaces.

I will try the APT_STRING _PADCHAR when I have time. You sure this isn't some kind of Thai dish or maybe far eastern musical instrument?

ray.wurlod · Post by **ray.wurlod** » Sun Apr 13, 2008 4:19 am

Top marks for the misleading etymology!