JDK policies for Informatica products from v9.5.0 onwards

1.   For Solaris, RedHat Linux, Suse Linux and Windows, JDK is packaged with Informatica 9.5.0. However, starting from Informatica 9.5.0 JDK is not packaged with the installer for HP-UX, AIX and zLinux platforms.

2.    The supported JDK pack has to be downloaded and installed on the server as a pre-requisite before installing Informatica. For more information on the supported JDK version, refer to Release Notes documentation.

3.    Informatica installer will fail during startup if the supported JDK version is not pre-installed on the server. $INFA_JDK_HOME environment variable should be set pointing to the supported JDK version. For more information on the environment variable configuration, refer to the Installation and Configuration Guide.

How to split column values into multiple rows based on a delimiter - Informatica PowerCenter

Question posted on Informatica Forum:

Can you please suggest me logic on how to make single row into multiple rows?

CURRENT OUTPUT

                    COLUMN-A     COLUMN-B      COLUMN-C

ROW1          1                         2                    MY||SUPPORT||INFORMATICA

 EXPECTED OUTPUT

                    COLUMN-A     COLUMN-B      COLUMN-C

ROW1          1                         2                    MY

ROW2          1                         2                    SUPPORT

ROW3          1                         2                    INFORMATICA

The number of rows to be generated depending on COLUMN-C. 

in this example in column-c there are 3 concatenated values so number of rows to generated are 3 and column-a and column-b should be unique for 3 rows...

like if there are 4 concatenated values in column-c then number of rows to be generated are 4.

Solution:

select CASE  WHEN  a.l = 1

              THEN  substr(col, 1, instr(col,',',1,a.l)-1)

               ELSE  substr(col, instr(col,',',1,a.l-1)+1,

                              CASE WHEN instr(col,',',1,a.l)-instr(col,',',1,a.l-1)-1 > 0

                                   THEN instr(col,',',1,a.l)-instr(col,',',1,a.l-1)-1

                                   ELSE length(col)

           END

           ) END EXPECTED_OUTPUT

          from

(select replace('MY||SUPPORT||INFORMATICA','||',',') col from dual)t,

(SELECT level l FROM DUAL CONNECT BY LEVEL <=

(SELECT Max(length(replace('MY||SUPPORT||INFORMATICA','||',',')) - length(replace('MY||SUPPORT||INFORMATICA','||',''))+1) FROM dual )) a

Oracle Database as source: Use above SQL query

Flat File Source: Use above SQL query in SQL Transformation and pass the column value as input.

Note:- Query is just for your reference, and you may have to change the query as per your requirement.

Reading data from Oracle VARRAY data type field - Informatica PowerCenter

VARRAY is an Oracle data type used to support columns containing multivalued attributes

Syntax for Varray:

CREATE OR REPLACE TYPE Vcol1 AS VARRAY(5) OF VARCHAR2(20);

/

CREATE TABLE varray_table (Field1 number, Field2 Vcol1);

Requirement: (Below was the requirement posted in a forum?)

Data in Table 1:

- F1 = "A;B;C"

- F2 = "Informatica"

- F3 = "Power"

But when I run the workflow and the mapping to extract this data, Informatica extract this data and load it in Table 2 like that

 ( F1 is a field to type "Array" and dimension 3 )

Data in Table 2 :

- F1 = "A"        =>        F1[0]

- F2 = "B"        =>        F1[1]

- F3 = "C"        =>        F1[2]

My problem is that I want to get same data in the same field in Table 1 and Table2 :

I want this in my Table 2 :

- F1 = "A;B;C"

- F2 = "Informatica"

- F3 = "Power"

Solution:

Informatica doesn't support "Varry table data type". It will be imported as Varchar2 by default within Informatica.

so make sure to change the source qualifier's sql query as below. (Assuming Oracle 11g been used)

WITH T AS (

   SELECT ROWID a, F2, F3, T2.COLUMN_VALUE col

   FROM Table1 T1, TABLE(T1.F1) T2)

   SELECT LTRIM (SYS_CONNECT_BY_PATH(COL,';'),';') COL, F2, F3

   FROM (

   SELECT a, F2, F3, COL, ROW_NUMBER() OVER ( partition by a order by a) FILA

   FROM T)

   WHERE CONNECT_BY_ISLEAF=1

   START WITH FILA =1

   CONNECT BY PRIOR a = a

   and prior FILA+1=FILA

  

Output:

F1          F2               F3

A;B;C     Informatica  Power

Varchar2 and nvarchar2 --- Informatica driver option

Difference b/w varchar2 and nvarchar2:




varchar2 -- stores data in the databases base character set

nvarchar2 -- stores data in the national character set



Now we could have a column with data stored in 7 bit ascii and other column with

data stored in utf8 in the same database.

Rather than make everything multi-byte, we can just store the small amount of multi-byte(If required) within the same database.

Otherwise single byte data is simple, fixed width, smaller & faster.



Enable the below option if you are using Informatica’s Data Direct Driver to read the exact database data type. ( To retain Varchar2 and char2 )

Informatica by default treats all the string data types to multi-byte, example varachar2 will be nvarchar2 in Informatica.

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