Informatica - Java Transformation

This article explains the use of Java Transformation with an example.

We have input data as below.

And need to transform data in such a way that it creates 5 different records with EMI Sequence Number, due date and the outstanding or balance loan amount.

This transformation can be active or passive. When we think about transforming single record to multiple records, the first transformation comes to our mind is Normalizer Transformation. But for Normalizer transformation, a single record can be converted to fixed number of output records (Please refer my earlier post about Normalizer Transformation). In the example, different customers will have different tenure for loans. Lets see how this can be achieved with Java transformation.

Java Transformation: The ouput ports for the java transformation is created manually and uncheck the Input Check Box.

Below are the settings for the transformation.

And here is the java code written to transform the data under "On Input Row" tab. Code written under this tab will take each record and transform it.

Java Code

try
{
  DateFormat formatter = new SimpleDateFormat("dd/MM/yyyy");
  Calendar cal = Calendar.getInstance();
  int Yr, Mon;
  String DtStr;
  int loan_tenure;
  BigDecimal loan_amount, emi_amount;
   
  Date loan_st_dt = (Date)formatter.parse(LOAN_START_DT); 
  cal.clear();
  cal.setTime(loan_st_dt);
  cal.add(Calendar.MONTH, 1);
  Yr = cal.get(Calendar.YEAR);
  Mon = cal.get(Calendar.MONTH)+1;
  DtStr = "01/" + Mon +"/" + Yr;
  loan_st_dt = (Date)formatter.parse(DtStr);
  loan_tenure = 1;
  loan_amount = LOAN_AMT;
  emi_amount = LOAN_AMT.divide(new BigDecimal(LOAN_TNR), 6, RoundingMode.HALF_UP);

  do
  {
     loan_amount=loan_amount.subtract(emi_amount); 
     O_CUST_ID = CUST_ID;
     O_CUST_NM = CUST_NM;
     O_EMI_AMT = emi_amount;
     O_EMI_NUM = loan_tenure;
     O_EMI_DUE_DT = formatter.format(loan_st_dt);
     O_OS_EMI_AMT = loan_amount;
     generateRow();
     loan_tenure = loan_tenure +1;
     cal.clear();
     cal.setTime(loan_st_dt);
     cal.add(Calendar.MONTH, 1);
     loan_st_dt = (Date)cal.getTime();
  }while(loan_tenure <= LOAN_TNR);
}
catch (Exception e)
{
  System.out.println(e+" This is a wind message. - "+e.getMessage());
}

Expression Transformation: It converts the string to date.

Issues You might face are below:
Issue Snap:

Resolution:
Enable High Precision in Java Transformation and in the session properties.

Dowload Workflow

Thanks for reading my article !

Read More

Informatica - Error: FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]Inconsistent descriptor information.

This article tries to find solution for the below error scenario in Informatica.

Error Message:

WRT_8229    Database errors occurred:

FnName: Execute -- [Informatica][ODBC SQL Server Wire Protocol driver]Inconsistent descriptor information.

FnName: Execute -- [DataDirect][ODBC lib] Function sequence error

Error Scenario: When SMALLDATETIME column from source is moved to SMALLDATETIME column at target, all the records will be rejected at target.

Database: Both source and target are MS SQL Server 2008 database.

Solution:

1. Convert the SMALLDATETIME column to string using Expression Transformation. (SMALLDATETIME datatype length is 19 and the format is 'YYYY-MM-DD 24HH:MI:SS')

SUBSTR(DATE_COL, 1, 19) 

2.Change the Target Definition of the SMALLDATETIME column from SMALLDATETIME to VARCHAR(19) and keep the database table definition as it is.

3. Connect the converted date from expression to target and the issue is resolved.

It worked for me. You can also try the same !

Read More

Informatica - Normalizer Transformation Example

This article explains the use of Normalizer Transformation with an example.

We have source data as below.

And want output as below.

Normalizer Transformation: In this example, There are 4 different expense columns and it needs to be normalized. So define the output ports in the "Normalizer Tab" and set the "Occurs" to 4, since we have 4 columns to be normalized.

Expression Transformation: The "GK_" column (1, 2, 3, 4.....) is the sequence number for the normalized records and "GCID_" is the ordinal sequence of expense columns from the source (Here it is 1, 2, 3 and 4). Based on the "GCID_" value the Expense Type is calculated with DECODE function.

Download Workflow

Thanks for reading this article !

Read More

Informatica - Splitting a Flat File based on a column using Transaction Control

This article explains how to split records based on a particular column into multiple flat files using Transaction Control Transformation. Below given the Mapping and transformation Snapshots.

Mapping Snapshot

Sorter Transformation: Sorts the records based on column that used to split the file. In this example, it is JOB column.

Expression Transformation: After sorting based on JOB column. The first job column will be marked as '1' and rest marked as '0'. This helps the Transaction control to decide where to commit. It also calculates the file name for the group.

Transaction Control Transformation: It continue the transaction when the flag is 0 and commits the transaction before on 1.

Target Definition: Add the file name using the "Add Filename column to this table" button in the taget definition.

Download Workflow

Hope this helped you and thanks for reading this article !

Read More