top button
Flag Notify
    Connect to us
      Site Registration

Site Registration

Default values in target tables

+2 votes
304 views

I have some mappings, where business entities are being populated after transformation logic.

The row volumes are on the higher side, and there are quite a few business attributes which are defaulted to certain static values.

Therefore, in order to reduce the data pushed from mapping, i created "default" clause on the target table, and stopped feeding them from the mapping itself. Now, this works out just fine when I am running the session in "Normal" mode. This effectively gives me target table rows, with some columns being fed by the mapping, and the rest taking values based on the "default" clause on the table DDL.

However, since we are dealing with higher end of volumes, I want to run my session in bulk mode (there are no pre-existing indexes on the target tables).

As soon as I switch the session to bulk mode, this particular feature, (of default values) stops working. As a result of this, I get NULL values in the target columns, instead of defined "default" values.

My question is-
1.Is this expected behavior ?
2.If not, am I missing out on some configuration somewhere ?
3.Should I be making a ticket to Oracle ? or Informatica ?

My configuration -

Informatica 9.5.1 64 bit, with Oracle 11g r2 (11.2.0.3) running on Solaris (SunOS 5.10)

Looking forward to help here...

posted Mar 19, 2014 by Madhavi Kumari

Share this question
Facebook Share Button Twitter Share Button LinkedIn Share Button

1 Answer

+1 vote

Seems that bulk mode in Informatica use "Direct Path" API in Oracle (see for example https://community.informatica.com/thread/23522 )

From this document ( http://docs.oracle.com/cd/B10500_01/server.920/a96652/ch09.htm , search Field "Defaults on the Direct Path") I gather that:

Default column specifications defined in the database are not available when you use direct path loading. Fields for which default values are desired must be specified with the DEFAULTIF clause. If a DEFAULTIF clause is not specified and the field is NULL, then a null value is inserted into the database.

This could be the reason of this behaviour.

answer Mar 19, 2014 by Shweta Singh
Similar Questions
+2 votes

I have some mappings, where business entities are being populated after transformation logic. The row volumes are on the higher side, and there are quite a few business attributes which are defaulted to certain static values.

Therefore, in order to reduce the data pushed from mapping, i created "default" clause on the target table, and stopped feeding them from the mapping itself. Now, this works out just fine when I am running the session in "Normal" mode. This effectively gives me target table rows, with some columns being fed by the mapping, and the rest taking values based on the "default" clause on the table DDL.

However, since we are dealing with higher end of volumes, I want to run my session in bulk mode (there are no pre-existing indexes on the target tables).

As soon as I switch the session to bulk mode, this particular feature, (of default values) stops working. As a result of this, I get NULL values in the target columns, instead of defined "default" values.

I wonder -
1.Is this expected behavior ?
2.If not, am I missing out on some configuration somewhere ?
3.Should I be making a ticket to Oracle ? or Informatica ?

my configuration -

Informatica 9.5.1 64 bit, with Oracle 11g r2 (11.2.0.3) running on Solaris (SunOS 5.10)

Looking forward to help here.

+1 vote

Do i need to write in PL/SQL or is it possible in SQL query?

0 votes

In informatica i receive dates from flat files in the format of dd-mm-yyyy and dd/mm/yyyy i need to convert all date to one format i.e dd-mm-yyyy using any expression and push into target ,so no rows gets rejected.how to proceed with them?

+2 votes

Suppose we have a Source Qualifier transformation that populates two target tables. How do we ensure TGT2 is loaded after TGT1?

+1 vote

Suppose we have two Source Qualifier transformations SQ1 and SQ2 connected to Target tables TGT1 and TGT2 respectively. How do you ensure TGT2 is loaded after TGT1?

...