literal spaces treated as a key

travissolt · Post by **travissolt** » Mon Feb 26, 2007 12:17 pm

The system we are acessing is old and allows literal spaces ' ' as part of the key on some of their tables. During the change data capture it works fine because it compares a null to a null and finds no difference. However when it goes to load the changes into the final table it will not allow the NULL value to be loaded (even though it is really ' ' in the oracle table). I know that I can manipulate the data upfront but we are supposed to pass the data forward as is and I am wondering if datastage can be modified to accept literal spaces.

The below code snippet came out of the unix ctl log for the job. The PANEL field is the one in question (it can contain values of ' '). I can tell that for other nullable fields (such as CONTRACTTYPE below) that if it is blank datastage will bring it in as NULL. Is it safe to say the same thing will happen for my PANEL field even though it is not spelled out. Could this be modified?

PANEL POSITION(46:54) ,
CONTRACTTYPE POSITION(108:110) NULLIF CONTRACTTYPE =BLANKS,

Thanks

kumar_s · Post by **kumar_s** » Mon Feb 26, 2007 5:47 pm

Btw, space is not NULL. Where do you find the space character is getting changed as Null? Is the field a Varchar datatype?

chulett · Post by **chulett** » Mon Feb 26, 2007 7:10 pm

Isn't there some kind of equivalent to sqlldr's PRESERVE_BLANKS setting? Otherwise, spaces get trimmed and you can end up with a null field. Assuming we're talking Oracle here.

travissolt · Post by **travissolt** » Tue Feb 27, 2007 8:19 am

Im not saying that the space is a null its a really 3 spaces in a varchar2(9) datatype. Somewhere in datastage it is converting the spaces to nulls during the parallel job. After a change data capture it tries to insert null values into the change table when really it should be trying to insert the ' ' as the key value. The job bombs out and gives me an error that datastage cannot insert null into a key field. For right now I have it fixed in that I am changing the null to '0' using the ISNULL function but my audit log now has the key field as a '0' as opposed to the ' '. I am sure it will work.....but if they want to retain the values in the source system I am wondering if there is away to keep the field as spaces.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 27, 2007 3:36 pm

Set APT_STRING_PADCHAR to space (\x20) rather than to NUL (\x00).

travissolt · Post by **travissolt** » Tue Feb 27, 2007 4:24 pm

Is that something I will need to get the datastage admin to change or can I bring it in as a parameter and change it. When i bring APT_STRING_PADCHAR in as an environment variable it has 0x0 as the default. My guess is 2x0 for the relacement.

kumar_s · Post by **kumar_s** » Tue Feb 27, 2007 5:13 pm

Changing in Adminstrator will affect your whole project. Sourcing that variable in your job and assigining a different variable will affect only that particular job. You need to decide on how it should reflect. In the variable, you can simple give a space ' ' rather than giving it in Hex value.

ray.wurlod · Post by **ray.wurlod** » Tue Feb 27, 2007 7:39 pm

Not 2x0, but rather 0x20 if you're going to use the hex representation.

travissolt · Post by **travissolt** » Wed Feb 28, 2007 9:16 am

I used $APT_ORACLE_PRESERVE_BLANKS set to True as a parameter at the job level and it has worked so far. Thanks

travissolt · Post by **travissolt** » Thu Mar 01, 2007 2:00 pm

The $APT_ORACLE_PRESERVE_BLANKS works but unfortunatley it pads all spaces for all fields. So if a field had 36 characters and only used 10 the parallel job brings in all 36 (the 10 plus the 26 spaces). Basically it will not work. For now we have decided to default all literal spaces to '0' and just relay the message to the user community.

travissolt · Post by **travissolt** » Thu Mar 01, 2007 4:56 pm

Ok I just want to get the right resolution out here in case someone has the same issue. The literal spaces come into datastage fine and can flow through a transformation,lookup or filter as in our flow. The problem shows up in the final load to the oracle table. In our process we were using the LOAD option which apparently uses a datastage ORACLE SQL loader process and within that process of datastage it has the statement NULLIF 'FIELDNAME' =BLANKS. If we change the final load option to UPSERT then the key_ID containing the ' ' spaces can still be inserted as a field and the null issue goes away. Thought I would let you know our final resolution. So if you have source systems that have spaces as key fields you will need to use the upsert option when finally loading to an oracl table.
Thanks