Informatica Scenario and SQL Scenario Question

Scenario:

Source data looks like below

 Target 1 (Event number of occurrences)

A has come 2 times in the source, it should go to Target 1

Target 2 (odd number of occurrences)

If source data repeats odd number of times then it should go to Target 2

Solution:

create table test_odd_even_src

(

col1 varchar2(3)

);

create table test_odd_tgt

(

col1 varchar2(3),

col2 int

);

create table test_even_tgt

(

col1 varchar2(3),

col2 int

)

;

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('B');

insert into test_odd_even_src values('C');

insert into test_odd_even_src values('D');

Mapping Looks like this:

Aggregator Transformation:

Joiner:

Router Transformation:

Now try to insert 2 more records into source and run the session again (Before that truncate target tables)

insert into test_odd_even_src values('A');

insert into test_odd_even_src values('B');

Source Data after inserting 2 new records.

select * from test_even_tgt

select * from test_odd_tgt

SQL Scenario:

Source
id,color
a1,yellow
a2,unknown
a1,green
a3,unknown
a1,unknown
a2,blue

Target
id,color
a1,yellow
a1,green
a3,unknown
a2,blue

Data Preparation:

create table test_color_src

(

id varchar2(30),

color varchar2(30)

);

create table test_color_tgt

(

id varchar2(30),

color varchar2(30)

);

insert into test_color_src values('a1','yellow');

insert into test_color_src values('a2','unknown');

insert into test_color_src values('a1','green');

insert into test_color_src values('a3','unknown');

insert into test_color_src values('a1','unknown');

insert into test_color_src values('a2','blue');

Solution:

Source Data:

SQL Query and Its output:

select id,color from test_color_src where color <> 'unknown'

union

(select id,'unknown' from test_color_src where color = 'unknown'

MINUS

select id,'unknown' from test_color_src where color <> 'unknown'

)

Thanks for Reading my blog..!!

Informatica Scenario: How to get salary and commission as column values when they both mentioned in same column data in source with type?

SRC:

Source data looks like below. Here Type=S means salary and Type=C means commission. 



Target:

we need salary and commission should come as columns against empid. 



Mapping Flow:
Below is the SQL logic:

SELECT S.EMPID, S.AMT AS SAL, C.AMT AS COM
  FROM (SELECT *
          FROM test_emp_sal_com
         WHERE TYPE = 'S') S
       LEFT OUTER JOIN (SELECT *
                          FROM test_emp_sal_com
                         WHERE TYPE = 'C') C
          ON C.EMPID = S.EMPID

same logic has been implemented using Informatica and the below is the mapping data flow.

same logic can also be used by using lookup on source. 

How to get previous row value in Informatica using expression transformation?

Source:

customers table:

Customers table has data for columns cust_id, Year, City columns like below.

Expected Target Value:

Prev city column need to be populated as the previous city like below.

Implementation:

Use order by columns CUST_ID and Year in source qualifier transformation or have sorter transformation to sort these 2 columns.

Connect the columns to expression transformation and create columns as below:

CUST_ID (i/o)= CUST_ID

YAER (i/o)= YAER

CITY (i/o)= CITY

v_cust_id (variable)=v_prev_Cust_ID

v_old_city (variable)=v_prev_city

o_prev_City (output)=IIF(v_cust_id=v_prev_Cust_ID,v_old_city,null)

v_prev_city (variable)=CITY

v_prev_Cust_ID (variable)=CUST_ID

Mapping will look like below:

Informatica Repository Queries to get connection names, command tasks and event wait file names

Query1: 

Query to get folder name, workflow name, session names and all the connections used in sessions.

SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
       T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
       C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
       V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
       C.SESS_EXTN_OBJECT_TYPE
  FROM REP_TASK_INST T,
       REP_SESS_WIDGET_CNXS C,
       REP_WORKFLOWS WF,
       V_IME_CONNECTION V
 WHERE T.TASK_ID = C.SESSION_ID
   AND WF.WORKFLOW_ID = T.WORKFLOW_ID
   AND C.CNX_NAME = V.CONNECTION_NAME
   AND WF.SUBJECT_AREA = <FOLDER NAME>
 
   Note: If SESS_EXTN_OBJECT_TYPE=78 then its reader, If 79 then its writer
 
 Query2:
   
   Query to get commands used in command tasks including the commands used as pre/post session commands.
 
   SELECT DISTINCT T.SUBJECT_ID, F.SUBJECT_AREA AS FOLDER_NAME,
                W.TASK_NAME AS WORKFLOW_NAME, T.TASK_NAME AS CMD_TASK_NAME,
                CMD.PM_VALUE AS CMD_NAME, CMD.EXEC_ORDER,
                CMD.VAL_NAME AS CMD_NUMBER, T.TASK_ID, T.TASK_TYPE,
                T.RU_PARENT_ID
           FROM OPB_TASK_VAL_LIST CMD, OPB_TASK T, OPB_TASK W, REP_SUBJECT F
          WHERE T.TASK_ID = CMD.TASK_ID
            AND T.SUBJECT_ID = F.SUBJECT_ID
            AND T.TASK_TYPE = 58
            AND T.RU_PARENT_ID = W.TASK_ID
            AND F.SUBJECT_AREA = <FOLDER NAME>
       ORDER BY F.SUBJECT_AREA, W.TASK_NAME, T.TASK_NAME, CMD.EXEC_ORDER
 
Query3:

Query to get the event wait file names used in event wait tasks.

  SELECT    DISTINCT F.SUBJECT_AREA AS FOLDER_NAME, W.TASK_NAME AS WORKFLOW_NAME,
                T.TASK_ID, T.TASK_TYPE, T.TASK_NAME AS EVENTWAIT_NAME,
                T.RU_PARENT_ID, W.TASK_ID
           FROM OPB_TASK T, OPB_TASK W, REP_SUBJECT F
          WHERE T.RU_PARENT_ID = W.TASK_ID
            AND T.TASK_TYPE = 60
            AND T.SUBJECT_ID = F.SUBJECT_ID
            AND F.SUBJECT_AREA = <FOLDER NAME>

Query to Access Error Table when Infa Session fails while loading into Teradata target

    We may get error - '2652: Operation not allowed: <Error Table> is being loaded' while you are trying to access error table in Teradata.

Screen Shot of Error:

We need to lock the table in order to access this error table and below is the query

locking row for access  SELECT * FROM WRK.INFA_ET2_979525041610790

Here INFA_ET2_979525041610790 is error table name and WRK is database name.

TPT load: Session failed with error: 'dmapper/trans/srcpipe.cpp'

       We are using Informatica 9.1.0 HotFix2 version , SQL server is source database and Teradata is target database. We changed the load type from ODBC to Teradata Parallel Transporter (TPT) and we got the below error when we start the session with TPT stream load. 

Error in Session Log:

Message:  *********** FATAL ERROR : An unexpected condition occurred in file [/export/home/builds/pc9x_root/910HF/build/powrmart/server/dmapper/trans/srcpipe.cpp] line [1268].  Aborting the DTM process.  Contact Informatica Global Customer Support. ***********  

Screenshot of failure

Reason for failure:

We have given Pre-SQL but TPT load does not support Pre/Post SQL.

Solution:

We removed Pre-SQL and ran the session and it got succeeded.

Number of affected records is not equal to number of records inserted

I have come across a situation where number affected records in session run properties is not matching with the number of records actually inserted into the table. Target database is Teradata and connection is ODBC. 

Details of the problem:

Number of affected records is not equal to number of records inserted into the table. 
what happened to remaining records? Is some roll back happened? 

Below are the screen shots of the issue:

Session Run Properties and Number of records in database:

Session Run Properties

Number of records in target table

Reason: 

By default session mode of ODBC connection is 'Teradata'. When the session mode is Teradata then Teradata rolls back the transaction when it encounters an error. In the above situation, we had error records so Teradata rolled back the transaction hence the number of records in session properties is not equal to number of records inserted into table. 

Solution: 

Change the session mode to 'ANSI' by adding SessionMode=ANSI in DSN of the ODBC connection in odbc.ini file.

Note: changing the session mode to ANSI may fail other sessions if any session is using stored procedures complied in session mode=Teradata. 

UTC columns in Infa Repository tables

You might have seen some UTC columns in Informatica repository tables. 


Eg: 'PARENT_SOURCE_UTC_LAST_SAVED' column in 'REP_ALL_SOURCES' view. Datatype for this column is 'Number'. Have you ever wondered about the value of this column?


This is one of the formats of date and this number denotes 'number of seconds from 1/1/1970' 

We can use the below query to find the time when an action was performed on the repository using the below logic on the UTC time columns that we have in Infa repository.

Will help us to get the time irrespective of the timezone (PST/PDT)

Just replace the number in bold in below query with the UTC value.

select

FROM_TZ (

cast ( (to_date( '01/01/1970 00:00:00', 'mm/dd/yyyy hh24:mi:ss') + 1300192052/86400) as timestamp(0) )

, '+00:00')

AT TIME ZONE 'US/Pacific'

from dual

Thanks Subbu for above query.

How to get relational connection details used in Informatica?

Below is the query to get relation connection details in Informatica 8series.

select * from V_PC8X_CONNECTION where CONNECTION_NAME IN
(select DISTINCT CONNECTION_NAME from REP_SESSION_CNXS where SUBJECT_AREA='FOLDER_NAME');


If you want to get connection details used in particular folder then you need to pass the FOLDER_NAME as input to the above query.
Above query will give details like Connection name, Host name, database type, user name, Connection string and more.

How to find out whether 'Push Down Optimization' option is enabled or not?

Below is query on repository database to check whether 'Push Down Optimization' option is enabled or not

Select T.* from OPB_TASK T, OPB_TASK_ATTR A where T.TASK_ID=A.TASK_ID AND A.ATTR_ID=107 AND ATTR_VALUE=1

How to find target load type(Whether it is 'Bulk' or 'Normal' ) in session properties?

SELECT E.SUBJ_NAME AS FOLDERNAME, 

D.TASK_NAME AS SESSIONNAME, 

A.SESSION_ID AS SESSIONID, 

B.INSTANCE_NAME AS INSTANCENAME, 

DECODE (C.ATTR_VALUE, '0', 'NORMAL', '1', 'BULK', NULL) 

AS TARGETLOADTYPE 

FROM INFA_REP.OPB_SESSION A, 

INFA_REP.OPB_SWIDGET_INST B, 

INFA_REP.OPB_EXTN_ATTR C, 

INFA_REP.OPB_TASK D, 

INFA_REP.OPB_SUBJECT E 

WHERE A.SESSION_ID = B.SESSION_ID 

AND A.SESSION_ID = C.SESSION_ID 

AND C.ATTR_ID = 3 

AND B.SESS_WIDG_INST_ID = C.SESS_WIDG_INST_ID 

AND A.SESSION_ID = D.TASK_ID 

AND D.SUBJECT_ID = E.SUBJ_ID 

AND E.SUBJ_NAME='FOLDER_NAME'

How to change Target file name with session run time?

Doubt:

My flat file name is Tgt_File.txt

Every time when i run my session the target file name should change as Tgt_File_07-06-2011 like that.

Solution:

If u want to change the Target file name everytime u run, then u need to pass Target file name as parameter - $OutputFile1$Param_DATE_TO_ATTACH.csv 

$OutputFile1 - is ur Static part of ur Target file name(Note: $OutputFile_Something - $OutputFile this is mandatory in parameter name)

$Param_DATE_TO_ATTACH - date to attach or any string (Note:$Param_Something -$Param this is mandatory in parameter name) 

.csv - file type.

How to check whether 'Fail Parent if this Task fails' option is checked or not?

Below is the query to know whether 'Fail Parent if this task Fails' option is checked or not.
SELECT * FROM OPB_TASK_INST 
WHERE TO_NUMBER(BITAND(BIT_OPTIONS,16))<>0
use the below query to know parent task(workflow or worklet where session resides) .
Note: Check the version number. one which is having the max version number is the latest task.

SELECT T.SUBJECT_ID,T.TASK_ID,
T.TASK_TYPE,
T.TASK_NAME AS PARENT_TASK,
T.VERSION_NUMBER,I.WORKFLOW_ID,
I.INSTANCE_ID,I.TASK_ID,
I.TASK_TYPE,
I.INSTANCE_NAME AS SESSION_NAME,
I.VERSION_NUMBER
FROM 
OPB_TASK T,OPB_TASK_INST I
WHERE T.TASK_ID=I.WORKFLOW_ID 
AND T.IS_VISIBLE=1 
AND TO_NUMBER(BITAND(I.BIT_OPTIONS,16))<>0

To get all objects under Folder from Repository Tables

Query to get workflow, Session, Mapping, SRC instance, Target instance, SRC (Physical) and Target (Physical) names by passing folder name as input.

SELECT F.SUBJ_NAME        AS FOLDER_NAME,

       WF.TASK_NAME       AS WORKFLOW_NAME,

       SESS.INSTANCE_NAME AS SESSION_NAME,

       M.MAPPING_NAME     AS MAPPING_NAME,

       SRC.INSTANCE_NAME  AS SOURCE_NAME,

       TGT.INSTANCE_NAME  AS TARGET_NAME,

       PS.SOURCE_NAME     AS PHY_SRC_NAME,

       PT.TARGET_NAME     AS PHY_TARGET_NAME

FROM   OPB_SUBJECT F,

       (SELECT TASK_NAME,TASK_ID,

               SUBJECT_ID,IS_VISIBLE,

               TASK_TYPE,MAX(VERSION_NUMBER)

        FROM   OPB_TASK

        GROUP  BY TASK_NAME,TASK_ID,

                  SUBJECT_ID,IS_VISIBLE,

                  TASK_TYPE) WF,

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID, INSTANCE_NAME

        FROM   OPB_TASK_INST

        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN 

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,VERSION_NUMBER

                     FROM

               (SELECT WORKFLOW_ID,INSTANCE_ID,

                       TASK_ID,

                       MAX(VERSION_NUMBER) AS VERSION_NUMBER

               FROM    OPB_TASK_INST

                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                 TASK_ID))) WL,

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,INSTANCE_NAME,

               TASK_TYPE

        FROM   OPB_TASK_INST

        WHERE  ( WORKFLOW_ID, INSTANCE_ID, TASK_ID, VERSION_NUMBER ) IN 

       (SELECT WORKFLOW_ID,INSTANCE_ID,

               TASK_ID,VERSION_NUMBER

                          FROM

               (SELECT WORKFLOW_ID,INSTANCE_ID,

                       TASK_ID,

                       MAX(VERSION_NUMBER) AS VERSION_NUMBER

               FROM    OPB_TASK_INST

                       GROUP  BY WORKFLOW_ID,INSTANCE_ID,

                                 TASK_ID))) SESS,

       (SELECT SESSION_ID,MAPPING_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_SESSION

        GROUP  BY SESSION_ID,MAPPING_ID) S,

       (SELECT MAPPING_NAME,IS_VISIBLE,

               MAPPING_ID,MAX(VERSION_NUMBER)

        FROM   OPB_MAPPING

        GROUP  BY MAPPING_NAME,IS_VISIBLE,

                  MAPPING_ID) M,

       (SELECT MAPPING_ID,INSTANCE_NAME,

               WIDGET_TYPE,WIDGET_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_WIDGET_INST

        GROUP  BY MAPPING_ID,INSTANCE_NAME,

                  WIDGET_TYPE,WIDGET_ID) SRC,

       (SELECT MAPPING_ID,INSTANCE_NAME,

               WIDGET_TYPE,WIDGET_ID,

               MAX(VERSION_NUMBER)

        FROM   OPB_WIDGET_INST

        GROUP  BY MAPPING_ID,INSTANCE_NAME,

                  WIDGET_TYPE,WIDGET_ID) TGT,

       OPB_SRC PS,

       OPB_TARG PT

WHERE  WF.IS_VISIBLE = 1

       AND WF.SUBJECT_ID = F.SUBJ_ID

       AND WF.TASK_ID = WL.WORKFLOW_ID

       AND WF.TASK_TYPE = 71

       AND ( WL.TASK_ID = SESS.WORKFLOW_ID

              OR WL.WORKFLOW_ID = SESS.WORKFLOW_ID )

       AND WF.IS_VISIBLE = 1

       AND SESS.TASK_TYPE IN ( 58, 68 )

       AND SESS.TASK_ID = S.SESSION_ID

       AND S.MAPPING_ID = M.MAPPING_ID

       AND M.IS_VISIBLE = 1

       AND SRC.MAPPING_ID = M.MAPPING_ID

       AND SRC.WIDGET_TYPE = 1

       AND TGT.MAPPING_ID = M.MAPPING_ID

       AND TGT.WIDGET_TYPE = 2

       AND TGT.WIDGET_ID = PT.TARGET_ID

       AND SRC.WIDGET_ID = PS.SRC_ID

       AND F.SUBJ_NAME = 'FOLDER_NAME'