Repository Query to pull details from expression transformation

My friend had a requirement to list all the port names where a particular input port is used in the expression (variable/output) within the Expression Transformation.

Example:

Port1   --- Input Port

Port2   IIF (Port1=’0’,1,0)

So the repository query to retrieve details about Port2 is as below.

select c.subject_area,c.mapping_name,b.instance_name,a.field_name,a.expression

from REP_WIDGET_FIELD a,REP_widget_inst b,REP_all_mappings c

where

a.widget_id=b.widget_id

and b.widget_type=5 ( 5 is for “Expression Transformation” )

and b.mapping_id=c.mapping_id

and c.mapping_name= <Mapping Name>

and a.expression like '%<Input Port Name>%'

Informatica Repository Service on Oracle RAC (Real Application Clusters)

As we know Power Center 8.x & 9.x supports grid architecture, but I was curious to know about Informatica Power Center behavior with Oracle Gird (RAC) as repository. Below are few questions that popped up in my mind, and over the weekend had a chance to discuss with my friends.

Oracle RAC:

Oracle Real Application Clusters (RAC) enables 24 x 7 availability of database applications. RAC provides two method of failover for reliable data access.

• Connection failover
• Transparent Application Failover (TAF)

1.       On failover, what happens within repository service (Oracle RAC)?

Repository Service is starting and detects that one of the instances of the RAC has gone down, and then connection is automatically transferred to other active nodes.

2.       Does Oracle gird functionality anyway help’s Power Center Gird?

There is no impact on application logic, but OCI libraries helps in migrating database connection from one database node to other incase of failover.

3.       Do we need to configure anything within Informatica?

Informatica does not have special parameters to handle Oracle RAC failover instance. It must be configured in Oracle.

4.       Why Informatica recommends only Connection failover, and not TAF?

When the Repository Service writes to the repository tables and before the commit occurs, the connection is failed over, then the inserts alone will be rolled back, potentially leaving the table in a corrupt state.  The failover within RAC should be transparent to Informatica. If any update or insert fails in the middle and RAC didn't failover, Oracle would return failure status to Repository service.

The TAF guarantees session failover. However, it does not guarantee the same for DML statements.

5.       Does Oracle RAC support the failover of DML statements?

No.

Yet to get opportunity to really work on Oracle RAC environment, but completely understood the concept behind the integration.  Please if you have any questions related to RAC integration post it here, I will try to understand and answer you. Thanks!!

Parallel commit using ODBC connection (Informatica)

During our GreenPlum integration with Informatica PowerCenter, we tried testing the ODBC connection (instead of Native) to cross check the performance. As usual ODBC performance was bad compare to Native connection. 

But with below custom property, we were able to overwrite the multiple commit property of a driver and almost gave Native connection throughput.

OptimizeODBCWrite=No à

When Informatica connects it queries the driver to ask if it supports array reads/writes. Some drivers say yes but they may not have a large enough array. Example we were using the Greenplum target database, by setting this flag PowerCenter doesn’t uses the array set to write to the target, hence it will process more number of records.  

OptimizeODBCRead=No 

this is for reading the data for source. 

Invoking Informatica B2B Code through command utility

 ############################################

 echo "+++++++++++Started DT@ `date`++++++++++++++++++"
$B2B_service_name=$1
$B2B_input_file=$2
$B2B_output_file =$3
 echo $input_file
 cd $path
 . /setEnv.sh 

 CM_console $B2B_service_name -f$B2B_input_file -o$B2B_output_file >>$log_fileStorage 2>&1
 
 echo "+++++++++++Completed DT@  `date`++++++++++++++++"

 ############################################ 

 CM_console B2B command utility.

  B2B_service_name  = B2B Service Name
 B2B_input_file= Input file name with complete path
 B2B_output_file= Output file name with complete path