literal spaces treated as a key
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
literal spaces treated as a key
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
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
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 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.
Impossible doesn't mean 'it is not possible' actually means... 'NOBODY HAS DONE IT SO FAR'
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 pm
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 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.
-
- Participant
- Posts: 51
- Joined: Mon Feb 05, 2007 1:53 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
Thanks