Informatica Mapping maps null values in Oracle source into single space value in SAP HANA target

Question

Hello StackOverflow community,

I am facing a perplexing issue with an Informattica mapping involving an Oracle source DB table and a SAP HANA DB target table.

In this mapping , null values from a non-key column in the source table are unexpectedly mapped into a single space (' ') in the corresponding column of the target table.

EDIT: I have found out the reason - that's not my focus anymore.
All I want to know now is why setting ABAPVARCHARMODE to TRUE doesn't seem to affect the mapping.
When testing the parameter effectiveness using SAP HANA Studio's SQL console, it is behaving expectedly. (See figure 9+10 below)
Is data input through ODBC that much diffrent from running that DML in the console?

There is almost no mapping logic
Expression blocks contain default value in case of null value - which is indeed a single space character ' '
and according to the default value field in the DDL of the target DB (an empty string)- the mapping should result with the key field filled- and the non-key field should contain an empty string.

Both Database providers have an ODBC driver , and Informatica is connected to them in this manner.

To help diagnose the problem - I'm including the following:

1. DDL of the source table in Oracle

CREATE TABLE source_table(
 key_field NUMBER(19) not null,
 cellular varchar2(15),
 ...extra fields...
);
ALTER TABLE source_table ADD PRIMARY KEY key_field;

_{Figure 1}

2. DDL of the target table in SAP HANA

CREATE COLUMN TABLE "TARGET_TABLE"(
 "MANDT" NVARCHAR(3) DEFAULT '000' NOT NULL,
 "KEY_FIELD" BIGINT CS_FIEXED DEFAULT 0 NOT NULL,
 "CELLULAR" NVARCHAR(15) DEFAULT '' NOT NULL,
 "UNMAPPED_FIELD" NVARCHAR(3) DEFAULT '' NO NULL,
 CONSTRAINT "TARGET_TABLE~0" PRIMARY KEY( "MANDT,"KEY_FIELD" )
);

_{Figure 2}

3. "Proof of Null" of the source data - in the form of the issued query and the results

SELECT key_field,
       cellular,
       RAWTOHEX(cellular) AS binary_cellular,
       CASE WHEN cellular IS NULL THEN 'ITS NULL' ELSE 'NOT NULL' END AS is_cellular_null
FROM   source_table
WHERE  key_field = '21201550228'

_{Figure 3}

and the resuts:

KEY_FIELD	CELLULAR	BINARY_CELLULAR	IS_CELLULAR_NULL
21201550228			ITS NULL

_{Figure 3}

4."Proof of space" of the target data - after running the mapping

SELECT mandt,
       key_field,
       cellular,
       BINTOHEX(cellular) AS binary_cellular,
       CASE WHEN cellular IS NULL THEN 'ITS NULL' ELSE 'NOT NULL' END AS is_cellular_null,
       unmapped_field,
       BINTOHEX(unmapped_field) AS binary_unmapped_field,
       CASE WHEN unmapped_field IS NULL THEN 'ITS NULL' ELSE 'NOT NULL' END AS is_unmapped_fld_null
FROM   target_table
WHERE  mandt = '700' AND
       key_field = '21201550228'

_{Figure 4}

and the results:

MANDT	KEY_FIELD	CELLULAR	BINARY_CELLULAR	IS_CELLULAR_NULL	UNMAPPED_FIELD	BINARY_UNMAPPED_FIELD	IS_UNMAPPED_FLD_NULL
700	21201550228		20	NOT NULL			NOT NULL

_{Figure 5}

5. General description of the mapping (sorry no pictures, intra-net only )

"source_table" Source Definition -> 
    SQ_SOURCE_TABLE Source Qualifier -> 
        Expression -> 
            "target_table" Target Definition

_{Figure 6}

The source qualifier defined as

SELECT key_field,
       cellular,
       ...all extra fields...
FROM   source_table
WHERE  key_field = '21201550228'

_{Figure 7}

The expression is only there to:

• Entering a constant '700' into the 'MANDT' extra key field of the target table
• Pass through the 'KEY_FIELD' field from the SQ
• Pass through the 'CELLULAR' field from the SQ
• Not passing through any of the other fields

Edit: Also :

• Provide a default value in case of null

6. An excerpt from the trace i've issued on HANA side while running the mapping

cursor_(generated id).execute(''' INSERT INTO "TARGET_TABLE" ("MANDT","KEY_FIELD","CELLULAR") VALUES ( ?, ?, ?) ''', (u'''700''', 21201550228, u''' '''))

_{Figure 8}

7. An attempted work-around that doesn't seem to work
   According to the documentation of ODBC parameters available in SAP HANA
   there's a parameter called ABAPVARCHARMODE, which when set with the value TRUE
   should transform any use of the single space literal ' ' in any SQL/DML into an empty string literal ''.
   We have configured this parameter inside the odbc.ini file on Informatica's machine.

• I have tried to ODBC trace the Informatica's machine - but the trace produced the prepared statement being used w/o bind variables
• I have tried to run the equivilant DML statement found in the trace (even though no single space was supposed to be there) on SAP HANA STUDIO's SQL console, making sure to turn on the ABAPVARCHARMODE beforehand, and delete the record inserted by the mapping beforehand

SET 'ABAPVARCHARMODE' = 'TRUE'

INSERT INTO "TARGET_TABLE" ("MANDT","KEY_FIELD","CELLULAR") VALUES ( '700', 21201550228, ' ');

COMMIT;

SELECT mandt,
       key_field,
       cellular,
       BINTOHEX(cellular) AS binary_cellular,
       CASE WHEN cellular IS NULL THEN 'ITS NULL' ELSE 'NOT NULL' END AS is_cellular_null
FROM   target_table
WHERE  mandt = '700' AND
       key_field = '21201550228'

_{Figure 9}

producing

MANDT	KEY_FIELD	CELLULAR	BINARY_CELLULAR	IS_CELLULAR_NULL
700	21201550228			NOT NULL

_{Figure 10}

as expected

Answer 1

Without having the option to test any of the following, I believe this is what is happening:

1. the data in Oracle is an empty space character
2. Oracle treats this as NULL but the value stored (and returned to the ODBC driver) is an empty space
3. Informatica maps the value from Oracle VARCHAR2(15) to HANA NVARCHAR(15) and I guess that this is where the additional space character is inserted
4. the HANA driver inserts the data as provided.

Note, that ABABVARCHARMODE does not affect the inserted values. It affects filtering/selecting data and is meant to mimic the selection behavior of ABAP on HANA level. It is not a universal "replace all single space characters with empty space characters" transformation.

Correction: the ABABVARCHARMODE does work as the universal replacer for single space characters - if those are part of a string literal. If, for some reason, a single space is indeed required, it may be included e.g. by concatenating a string with the function CHR(32) that will generate a single space. 

5. Consequently, the single space is inserted and HANA (unlike Oracle) does not tread an empty string as NULL.

A quick search on Google brought up this KB article HOW TO: Convert a string value with only spaces to NULL using a PowerCenter expression which might be a solution to this issue.