Issue with Special Character Data
Posted: Tue Nov 23, 2021 12:41 am
Hi Members,
We have DataStage jobs where data is being sourced from .dat file using Sequential File stage and after a step by step processes and transformations data is loaded into DB2 table. All extract and transformation jobs are loading data into Datasets and the loading job uses a DB2 Connector as the target stage to load data into DB2.
There is a column in which the string contains diacritics (such as ý, â, û etc.) along with English alphabets. While loading into DB2, this diacritics are getting converted to other character ('☐') in the DB2 table.
We performed below steps to identify the issue-
1. Checked intermediate jobs where data is loaded into Datasets. We loaded dataset records into text files and found that special characters are flowing correctly in those files
2. In the DB2 load job, we replaced DB2 Connector with Sequential File stage and loaded data into a text file and special characters are loaded correctly.
3. In the DB2 load job, we set the Unicode in Extended property and as well as NCHAR as the column data type in separate runs. Currently the data type is set to CHAR(20).
4. Set environment variable APT_STRING_PADCHAR at the loading job with value = 0x20
5. Inserted one record with special character into the table by running INSERT statement in the database directly and the character was loaded correctly.
6. Hardcoded the string in the Transformer and DB2 Connector stage of the loading job and found that string was loaded correctly in the DB2 table.
NLS has been set to Project Default (UTF-8) and the DS version is 11.7 running on Unix platform.
We are not able to identify the cause of the issue and rectify it. Please help.
Thanks & Regards,
Abhishek Roy
We have DataStage jobs where data is being sourced from .dat file using Sequential File stage and after a step by step processes and transformations data is loaded into DB2 table. All extract and transformation jobs are loading data into Datasets and the loading job uses a DB2 Connector as the target stage to load data into DB2.
There is a column in which the string contains diacritics (such as ý, â, û etc.) along with English alphabets. While loading into DB2, this diacritics are getting converted to other character ('☐') in the DB2 table.
We performed below steps to identify the issue-
1. Checked intermediate jobs where data is loaded into Datasets. We loaded dataset records into text files and found that special characters are flowing correctly in those files
2. In the DB2 load job, we replaced DB2 Connector with Sequential File stage and loaded data into a text file and special characters are loaded correctly.
3. In the DB2 load job, we set the Unicode in Extended property and as well as NCHAR as the column data type in separate runs. Currently the data type is set to CHAR(20).
4. Set environment variable APT_STRING_PADCHAR at the loading job with value = 0x20
5. Inserted one record with special character into the table by running INSERT statement in the database directly and the character was loaded correctly.
6. Hardcoded the string in the Transformer and DB2 Connector stage of the loading job and found that string was loaded correctly in the DB2 table.
NLS has been set to Project Default (UTF-8) and the DS version is 11.7 running on Unix platform.
We are not able to identify the cause of the issue and rectify it. Please help.
Thanks & Regards,
Abhishek Roy