Informatica Cloud DB2 for i cdc connection type SQL Override not working

user_9087250 0
Viewed 93

Source for the Informatica Cloud is of type DB2 for I cdc. There are few tables that contain # in their column names. If the mapping was run with a column name containing # in the name then the mapping fails.

Example : If there is an Employee table with the column First#Name then used SQL Override

So to eliminate # from the column name tried using SQL Override to alias the column name.

Used a select statement that contains a column list for the same table. Sample SQL statement for the same:

Select First#Name as First_Name
       Last#Name  as Last_Name
       .
       .
       .
       .
       .
       .
       .
       Employee;

But still, the column name is being fetched with a # symbol and this is breaking the mapping.

Any solution how the # can be aliased _ in the column name?

informatica-cloud etl db2
2 Answers

You have two options -

  1. You can enclose column names with double uotes "col_name".
Select "First#Name" as First_Name
       "Last#Name"  as Last_Name
       .
       Employee;
  1. If this doesnt solve the issue, please do not mention any sql override and connect only required columns. Informatica should automatically built sql and fetch data.
  2. If above two doesnt work, then you need to change some settings in DB2 so as to handle the special char in column name. I have not tested this so i can not gurantee this.
user_64038160 
Select First#Name AS First_Name,Last#Name  AS Last_Name,Column3,Column4,Column5,Column6,Column7,Column8 FROM Employee;

The SQL query should be in such a way that there are no spaces except when required and the entire query should be present in a single line.

Remove all the spaces (Except the below mentioned ones) and new line characters from the query. That will solve the issue.

Where spaces can be left over:

  1. After the SELECT statement
  2. Before the FROM keyword
  3. After the FROM keyword
  4. Before and after the AS keyword when aliasing the columns that have special characters (Here 2 spaces will be utilized, one before the AS keyword and second after the AS keyword)
user_164966380
Related