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?
Handling of CHAR(x) Data: Differences between 5.2 and 7.x?
Moderators: chulett, rschirm, roy
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
Handling of CHAR(x) Data: Differences between 5.2 and 7.x?
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
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,
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,
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Re: Handling of CHAR(x) Data: Differences between 5.2 and 7.
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...
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...
Marcelo Esteves Silva
Senior Technology Officer
Securities Lending Technology - Investor Services
JPMorganChase
Senior Technology Officer
Securities Lending Technology - Investor Services
JPMorganChase
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?
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?
Roy R.
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
Time is money but when you don't have money time is all you can afford.
Search before posting:)
Join the DataStagers team effort at:
http://www.worldcommunitygrid.org
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.