Use of the property FORWARDING REJECTED ROWS in an Update Strategy transformation??

When we use the update strategy transformation we have the option to reject the row based on a criteria. We usually use DECODE or IIF to specify the criteria for rejecting the row[Treat Source Row should be selected as Data Driven at the session level]

IIF( ( RESIDENT_SK=0), DD_REJECT, DD_UPDATE )

The following table lists the constants for each database operation and their numeric equivalent:

Insert	DD_INSERT (Numeric Value 0)
Update	DD_UPDATE (Numeric Value 1)
Delete	DD_DELETE (Numeric Value 2)
Reject	DD_REJECT (Numeric Value 3)

Now coming to the Forwarding Rejected Row Option in update strategy, this option is used to forward the rejected rows into the bad file which we define at session level. If you disable this option, the rejected records won't be available in the bad file (reject file).

However the rejected rows will be available in session log (Normal logging level) even though the forwarding rejected row option is enabled\disabled.

These records in the reject file help us to understand the reason for rejection and validate the records not satisfying the business requirement. This helps to change the input data the next time we load the target

Structure of Informatica Bad Files or Reject Files

There are two types of Indicators in the reject file. One is the Row Indicator and the other is the Column Indicator.

List of Values of Row Indicators:

Row Indicator	Indicator Significance	Rejected By
0	Insert	Writer or target
1	Update	Writer or target
2	Delete	Writer or target
3	Reject	Writer
4	Rolled-back insert	Writer
5	Rolled-back update	Writer
6	Rolled-back delete	Writer
7	Committed insert	Writer
8	Committed update	Writer
9	Committed delete	Writer

Informatica Repository Query to Find All the Source and Target connections in Your Respository

The below Informatica repository query can be used to get the Source Connections, Target connections of all the instances used in the informatica.

Apart from that we have details of the Folder, mapping name ,session name , and the connection name as shown in Informatica.

SELECT DISTINCT 

C.SUBJECT_AREA FOLDER_NAME, 
C.MAPPING_NAME MAPPING_NAME, 
C.SESSION_NAME SESSION_NAME, 
E.READER_WRITER_TYPE, 
E.INSTANCE_NAME, 
E.CNX_NAME, 

CASE 
WHEN E.WIDGET_TYPE = 2 
THEN 'TARGET CONNECTION' 
ELSE 
CASE 
WHEN E.WIDGET_TYPE IN (1, 3, 56, 45, 55, 84) 
THEN 
'SOURCE CONNECTION' 
ELSE 
NULL 
END 
END CNX_TYPE 

FROM INFA_REPO.REP_VERSION_PROPS A, 
     INFA_REPO.REP_USERS B, 
     INFA_REPO.REP_LOAD_SESSIONS C, 
     INFA_REPO.REP_REPOSIT_INFO D, 
     INFA_REPO.REP_SESS_WIDGET_CNXS E 

WHERE ( A.USER_ID = B.USER_ID 
AND C.SESSION_ID <> D.REPOSITORY_ID 
AND C.SESSION_ID = A.OBJECT_ID 
AND C.SUBJECT_ID = A.SUBJECT_ID 
AND A.OBJECT_TYPE = 68 
AND C.SESSION_ID = E.SESSION_ID 
AND C.SESSION_VERSION_NUMBER = 
E.SESSION_VERSION_NUMBER)

DATAWAREHOUSE CONCEPTS- TOP 10 TOPICS OF 2014

This post is just a recap of the most discussed topic in Dwhlaureate for the year 2014.

1. VIRTUAL COLUMN IN ORACLE 11G 
   Virtual Column is one of the new features introduced in Oracle 11g….. 

2. OBIEE 11.1.1.7- New Features 
   OBIEE 11.1.1.7 has been released by Oracle and they included more than two hundred enhancements, designed to add new functionality for improved performance and deliver an enhanced end user experience….. 

3. Hierarchical Retrieval In ORACLE or Hierarchical Queries 
   There is provision to display the reporting pattern in an organization….. 

4. Invisible Indexes - Oracle 11g New Feature 
   Before 11g, Oracle didn’t have the facility to make an Index Invisible or visible…… 

5. “ VoltDB ” - NewSQL DBMS For High Velocity Applic... 
   VoltDB is a high performance scalable RDBMS and NewSQL Database primarily for Big Data, high velocity OLTP and Real-time analytics designed by…… 

6. Introduction To NewSQL 
   NEWSQL is a new class of Databases products that offers high performance and scalability like NoSQL for online transaction processing and also 

7. What is Massively Parallel Processing (MPP) With the advent of Big Data sets mainstream technologies like Massively Parallel Processing (MPP) systems is experiencing vital growth…. 

8. Oracle Database 12C New Feature-Fetch& Offset 
   In this post we will discuss about a new feature that was introduced in Oracle 12c to limit the rows returned in an easier and effective manner compared to methods …. 
   
   
9. How to Unlock the Locked Table in ORACLE 
   Oracle puts locks while performing any DDL or DML operation on oracle tables. When table locks is present on any tables in Oracle we cannot run DDL on those tables. 

10. Informatica Scenario: How to Load Data in Cyclic Order 
    In this scenario we will load the records present in the DEP table to Flat files in a cyclic order such that the first record will go to TargetFile01 ,2nd to TargetFile 02 ,3rd to Target File03 and 4th record again to TargetFile01. 
    
    
    
    
    If you like this post, please share it on google by clicking on the Google +1 button.
    
    
    Please go through our latest post TOP 6 BIG DATA TRENDS IN THE NEAR FUTURE
    
    

Informatica PowerCenter Session Partitioning-Type of Informatica Partitions

Informatica provides you the option of enhancing the performance of the Informatica session by the The PowerCenter® Partitioning Option. After tuning all the performance bottlenecks we can further improve the performance by addition partitions.

We can either go for Dynamic partitioning (number of partition passed as parameter) or Non-dynamic partition (number of partition are fixed while coding). Apart from used for optimizing the session, Informatica partition become useful in situations where we need to load huge volume of data or when we are using Informatica source which already has partitions defined, and using those partitions will allow to improve the session performance.

The partition attributes include setting the partition point, the number of partitions, and the partition types.

Partition Point:
There can be one or more pipelines inside a mapping. Adding a partition point will divide this pipeline into many pipeline stages. Informatica will create one partition by default for every pipeline stage. As we increase the partition points it increases the number of threads. Informatica has mainly three types of threads –Reader, Writer and Transformation Thread.

The number of partitions can be set at any partition point. We can define up to 64 partitions at any partition point in a pipeline. When you increase the number of partitions, you increase the number of processing threads, which can improve session performance. However, if you create a large number of partitions or partition points in a session that processes large amounts of data, you can overload the system.

You cannot create partition points for the following transformations:

• Source definition

• Sequence Generator

• XMLParser

• XML target

• Unconnected transformations

The partition type controls how the Integration Service distributes data among partitions at partition points. The Integration Service creates a default partition type at each partition point. 

Type of partitions are :

1. Database partitioning,

2. Hash auto-keys

3. Hash user keys

4. Key range

5. Pass-through

6. Round-robin.

Database Partitioning

For Source Database Partitioning, Informatica will check the database system for the partition information if any and fetches data from corresponding node in the database into the session partitions. When you use Target database partitioning, the Integration Service loads data into corresponding database partition nodes.

Use database partitioning for Oracle and IBM DB2 sources and IBM DB2 targets. 

Pass through

Using Pass through partition will not affect the distribution of data across partitions instead it will run in single pipeline.which is by default for all your sessions. The Integration Service processes data without redistributing rows among partitions. Hence all rows in a single partition stay in the partition after crossing a pass-through partition point.

Key range

Used when we want to partition the data based on upper and lower limit. The Integration Service will distribute the rows of data based on a port or set of ports that we define as the partition key. For each port, we define a range of values.Based on the range that we define the rows are send to different partitions

Round robin partition is used to when we want to distributes rows of data evenly to all partitions

Hash auto-keys: The Integration Service uses a hash function to group rows of data among partitions. The Integration Service groups the data based on a partition key.

Hash user keys: The Integration Service uses a hash function to group rows of data among partitions. We define the number of ports to generate the partition key.

Informatica Scenario: How to Load Data in Cyclic Order using Informatica

Informatica Scenario:

To Load source records from DEP table to target in a cyclic order.

In this scenario  we will load the records present in the DEP table to Flat files in a cyclic order such that the first record will go to TargetFile01 ,2^nd to TargetFile 02 ,3^rd to Target File03 and 4^th record again to TargetFile01.

For implementing this we can make use of sequence generator  with properties as given below.

You can route the records based on the NEXT VAL of sequence Generator using a Router as given below.

The overall mapping will look like this.

Below is the expected format in which target files will be created.

Please read more of such scenarios at http://dwhlaureate.blogspot.in/2013/02/informatica-scenarios-based-questions.html