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

Difference between Stop and Abort in Informatica

Abort Request:
1. User sends the abort request either through workflow monitor or PMCMD utility.

2. It immediately kills the DTM process and user been notified for session aborted.
3. Later, it fires a request to the source database, waits for the source database to roll-back.
4. It fires a request to the target database, waits for the target database to roll-back.
5. It fires a request to the Informatica repository database to release the locks on session object.


Note: Each session uses SHARED/LOCKED memory blocks, but the ABORT function kills just the CODE threads, leaving the memory LOCKED and SHARED and allocated.

Stop Request:
1. User sends the stop request either through workflow monitor or PMCMD utility.
2. It fires a request to the source database, waits for the source database to roll-back.
3. It then cleans up the buffers in memory by releasing the data.
4. It fires a request to the target database, waits for the target database to roll-back.
5. It fires a request to the Informatica repository database to release the locks on session object.
6. It immediately kills the process DTM process and user notified for session aborted.

Note: The bigger the data in the source query, the more time it takes to "roll-back" the source query, to maintain transaction consistency in the source database. So when ever a request is passed, it waits for the entire process to roll-back before notifying the user.

Challenges faced during 711 to 861 migration (Informatica)

Infrastructure:
1. Latency issue: We had a performance impact when our target database for the current setup was residing outside the application server. (20% increase in runtime)
2. Character conversion issue: Make sure your code page setting across the layers like application (Informatica) --> System runtime(OS) --> database (Oracle) are uniform.
We migrated from IBM AIX to Solaris server, because of change in system runtime library we found their were few difference in character conversion.
3. Memory consumption: We noticed a increase in memory consumption for any DTM process (5-10 % Increase) running in current setup(861), reason may be we migrated form 32 bit machine to 64 bit machine.
4. Increase in repository database space required for 861, when compared against similar number of Informatica objects in 711.
711 ---- Approx. 900 MB ( 1800 Workflows)
861 ---- Approx. 1.3 GB ( 1800 Workflows)
5. The growth of Informatica repository logging were higher in case of 861.
711 -- Approx. 650 MB (For 3 Months)
861 -- Approx. 1 GB (For 3 Months)

Database:
1. In some cases the query performance was very slow, so we were forced to change the query used within informatica session override(after the upgrade).

Informatica:
After up-gradation from Informatica 7.1 to Informatica 8.6 we may get the below issues, so we requested our developers to cross check the mapping or workflow has part of their testing.
1. Fixed width flat files are read incorrectly shifting the alignments of some of the columns.
2. We may get below error when session properties treat Source Rows as: Update enabled.
Reason would be updating column datatype of VARCHAR2 with a precision of 4000.
WRT_8118 ERROR: Target table [MCK_CHCK_ADDR_TB] does not allow UPDATE Row # [0] in bad file
3. Session with an XML target and the Validate Target option enabled writes invalid XML when the xs:choice element is included in the XSD file.
ERROR WRITER_1_*_1 XMLW_31205 Warning: Failed to validate element [EmpID], group [X_Root], row [0], error [Value is NULL but element
is not nillable.]. Discarding row.
So please revalidate the xsd again.
4. When using a pass through port in XML Generator transformation, it gives 0 or null in the output.
5. Cross check the Dynamic Lookup transformation, ensure that the ports are projected out of the Lookup transformation in the mappings.
This will ensure that they are part of data cache.
6. When starting a workflow from the Workflow Manager Navigator pane, the Workflow Manager first fetches each and every session before starting the workflow.
Workaround: In the Workflow Manager, start the workflow from Workflow Designer pane instead of starting from Navigator pane.
7. Large values in a fixed width flat file source BigInt column are skipped when read by a PowerCenter 8.6 session.
To resolve this, change the column in the file source definition to String(19) and convert it in the mapping to BigInt using the TO_BIGINT function.

Line sequential buffer length (Informatica)

I've configure Line sequential buffer length parameter to 4096 in my session.In my log session file I've this :FR_3071 Maximum Line sequential buffer length is 4098.
I don't understand what this line mean ?
Note: Query was taken from a Informatica related forum.
Reason:
Suppose you take below sample record as your input feed.
"7525 XX XXXXXX*0*10S*QQQQ*XX*YYY YYYY*US*USD*Common Stock*N.A.*XXXXX*JT*N.A.* *Machinery-Diversified"
Total length of the record is 104 characters (Bytes) and suppose if you have defined the "Line sequential buffer length" parameter to 100,then session would fail for
FR_3071 Maximum Line sequential buffer length is 100. (6.x/7.x version)
FR_3093 Record length [104] is longer than line sequential buffer length [100]. (From 8.x version)
How to calculate Line sequential buffer length:
Some how you need to find out the expected maximum record length and add 30% more, define that as a value for "Line sequential buffer length", just to neutralize a common solution irrespective of your code page setting. Because the way length of a character treated in ASCII and UNICODE are different.
Solution:
To avoid the above error change your default setting for "Line sequential buffer length".
Open the session properties.
Go to the General tab.
Select the Advanced Options button in the lower left corner.
Increase the Line Sequential Buffer Length setting so that it is larger than the largest row in the source file (including the header row).

What if you get a flat file that has no line feed (LF) or carriage return (CR) characters to mark the end of the record ?
1. Define a fixed width source definition.
2. Set Line Sequential buffer length to the length of the record.
3. In the flat file properties set the Number of Bytes to Skip Between Records to 0.