My source file has an account number column which is defined as varchar(18). The account number always contains 16 numbers and two trailing spaces. The account number is read into a staging table column which is also defined as varchar(18). When the data arrives in the staging table, the trailing spaces have become leading spaces. This only happens when the varchar(18) column contains all numbers and trailing spaces. If I use test data that conatins alpha characters, the trailing spaces remain trailing spaces. Obviously I can put the trailing spaces back using the rpad function but I am not expecting this behaviour.
Has anyone else observed anything similar? I am using Data Services 12.2.1.3.
is your source file fixed width format or delimited format, Excel?
from the description looks like the number is getting formatted, DI will not do any kind of formatting to the data, is the column mapping direct mapping from source file to database table ?
how are you verfiying the data has leading spaces and not trailing after its loaded to the target ?
The source file is a multiple fixed width file (i.e. Header record, data and footer record) translated using a COBOL copybook.
I agree that it looks like the number is getting fomatted and I also agree that I would not expect data services to do anything to a varchar column.
The final target is a fixed width file and I know that the trailing spaces become leading spaces by comparing the input and output files. Also if I look at the data in the staging tables I can see the trailing spaces disappearing.
what is the following DSConfig.txt parameter value ?
ANSI_Varchar_Behavior
if its TRUE, and your source data has blanks(spaces) the blanks will be retained and loaded to target
if its FALSE, and your source data has blanks (spaces) the blanks will be trimmed and loaded to target
whether the blank should be leading or trailing for fixed width file format will be determined by the value of the option “blank Padding” in the file format, by default its leading
I tried you case but for me whether the column has all numbers or mix of digits and character the behavior is as per the blank padding option value
Cobol Copybook can be used as reader only, check the blank padding option for target fixed width file format
can you try loading to target fixed width file, using a row gen transform and varchar column with the same data that you are reading from cobol copybook ?
Well, loading to the fixed width file with ‘Blank padding’ set to trailing results in the correct output format.
However, the job validates that the account number is in the correct format i.e. match_pattern( Account_Number, '9999999999999999 ’ ) and because the trailing spaces are lost when the staging table is loaded, all accounts fail validation.
I’m working around using the RPAD function to put the spaces back into the column, but I don’t think I should have to do this.
But I don’t think is solution is anything to do with DSConfig settings.
If the input data is in thid format '1234567890123456 ', the trailing spaces are lost. If the input data is in this format '123456789012345G ', the trailing spaces remain. So adding a character to the data changes the behaviour.
It looks like Data Services is treating the numeric data as a number and if a character is included it treats the data as varchar.
Very strange and quite annoying as we’ve found this behaviour all over the place in our other jobs.
after testing with the ATL and sample file that is attached to the incident, I don’t think the problem is with varchar field having all digits or digits and chars
the varchar data is getting right trimmed in the COBOL reader, to verify this add a query transform after cobol copy book in DF and call length function on the varchar column and dump the output along with the column to a XML or file and check the length, the length of each column will be after excluding the blanks
I loaded the data to database table ,and verified if the banks are getting loaded or not by using following query
SELECT DATALENGTH(column_name) from table_name
the length is same for all the rows, the blanks are getting trimmed
loading the data to delimited file or XML also has data as truncated
only place where data appear as number right aligned and number and char left aligned is in view data, and the viewer is doing the formatting it has nothing to do with data having trailing spaces or not
looks like for COBOL COPY BOOK the varchar data is always right trimmed, I will have to check if this is by design or a bug
The helpdesk has issued an update. They say that ALL fixed width files have trailing blanks trimmed. This really doesn’t sound correct. I’ll be doing some more tests next week.
(BOB member since 2004-06-09)
(BOB member since 2004-01-30)
(BOB member since 2004-12-17)