Unix Script to Copy a file in Informatica

Lets now see the steps involved in Copying a file and placing in a destination required using the unix script from informatica.

1) Create a command task in your workflow or Open the Pre session command/post session command Of your Session property.

2) Now Provide the Script name in the space provided where we are going to place our unix script to do the above job. Script name should be given in the following way :

sh pathname/filename.sh

3) Now , Write the following script into the Script file to obtain the required result

. OSPENV
cp pathname/filename pathname/filename

4) Execute the script by running the workflow in informatica to see the required result.

Unix Script to Create a empty file in Target

Lets see the steps involved in creating a empty file in the target folder using the unix script.

1) Create a Command Task in the Workflow
2) Provide the Command name and Script file name in the Space provided. This will call the script during run time .
3) Now Write the following Unix script in the Script file .

. ospenv
Touch Filepath/filename

4) The above command will create a 0kb empty file in the target folder specified by us.

Pre Session Command in Informatica

Lets see how Pre session command can be given in a Informatica Workflow.


The Below Screen shows a session where the pre session command is going to be added.

Now right click on that session -> click on edit

Now click on Components tab

We can see many Properties under this tab. Now select "Non Reusable" option from the drop down next to Pre session command property.

Now Select the Pencil mark present in Value column to specify the command script which we are going to use.

Click on "Add new command" button and specify the path and name of the script in the space provided

Now Click on "Apply" and "Ok".

Based on the Order we have given , the Command Scripts get executed before the Start of the session.
We can have as many scripts as we want (commands) to be run be run before session.

Pls chk our : Post Session command

scenario6

I have a scenario where i need to load a table called "name_address" first for storing all the claimant name with a generation of a number (using given formula).

This number will be used in other main tables like claim table , transaction .. etc

Can u please help me whether constraint based loading will be useful or i need to specify target load plan.

Scenario5

Source              
Col_1    Col_2       
D1       A1          
D1       A2          
D2       A3         
D2       A4           

D2       A5           

D3       A6           
D3       A7 

Target 
Col_1    Col_2   
D1     A1-A2 
D2    A3-A4-A5 
D3     A6-A7


Answer :

The Following can be obtained by using a Sorter transformation , Expression transformation and a aggregator transformation.

Let me show the mapping now.,

This shows the mapping for the above scenario.
Now lets see them in detail
1) Usual Sorter on Col1
2) Expression t/f like below

3) Aggregator t/f as below

Create different flatfiles as output

We have a scenario in our project where i have to create different flatfiles as output based on the company number

The issue is 
1) we run this workflow once in every month
2) New company numbers and corresponding records may be added anytime during the month ., So each time wen a company is added we have to create a flatfile for that also
3) The name of the flatfile should be "Loss history report for" + company number
4) One more thing is every month wen the workflow runs , the flatfiles have to be places in that month folder . eg : In january the files shud be placed in Jan2012 folder.
The folders will be already present.,
5) Is it possible to create the folders also inside target every month ?

Does anyone have answer for this?

Java Transformation scenario2

Here i will show the mapping for scenario where we need the input records from emp table to be inserted into target thrice.

  

One more requirement is to change ename to "java" || job

They both are implemented the below way :

check out this link for other java scenario

Java Transformation

Let me show the Simple mapping involving Java transformation

Here the source is emp table and the target is rangasp table in oracle.

The requirement here is to change all the employee name to "java".
The java code has been written in such a way that the ename of all records will be changed to "java"

The left side contains all the input and output ports.
Generaterow() is used to generate a row for each records.

The output is something like this :

eno ename
721 java
678 java
899 java

Please do comment and ask if you have any doubts!! thankyou

Scenario 4

Input :

empno    ename    job
7219     Sri      CLERK
7782     Uma    MANAGER
7839     Ragu   PRESIDENT
7934     Sendil   CLERK

Output :

Repeation of the rows three times and also change the ename as "Srivatsan"
empno    ename    job
7219     Srivatsan CLERK
7219     Srivatsan CLERK
7219     Srivatsan CLERK
7782     Srivatsan MANAGER
7782     Srivatsan MANAGER
7782     Srivatsan MANAGER
7839     Srivatsan PRESIDENT
7839     Srivatsan PRESIDENT
7839     Srivatsan PRESIDENT
7934     Srivatsan CLERK
7934     Srivatsan CLERK
7934     Srivatsan CLERK


Post your mapping , one lucky winner will be selected!!

Transaction control Transformation

Lets discuss about transaction control transformation in this video :

I will show you a scenario using transaction control statement and explain you wen it has to be used.

Here we can see three components

1) expression trans

2) transaction ctrl t/f

3) target

The source for us is the emp table of scott schema

the requirement is to split the source rows into different flatfiles based on the dept number.


that is:

dept no : 10 , 20 ,30 need to splited and saved in seperate files

We can ask a question like "why cant this be done easily by a router"

yep a "good ques : the answer is "wen the dept no changes dynamically how we will be able to get the router groups to change??

So this is how this scenario is handled..

here we have filed where we are checking this condn : IIF(v_DEPTNO_2 != DEPTNO, 0, 1)

if a dept number comes for the first time then this will be zero and our transaction control t/f will create a flatfile with corresp rows.

filename is a field used to provide name of flatfile which need to be generated dynamically.

Based on the column where we gave condn the tc t/f does the following commiting activity
IIF(DEPTNO_SPLITTING = 0, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

here wenever a new dept number comes a commit is been done and a flatfile is created and one imp note is "the input has to be sorted initially"

In this way the files are created on each dept numbers

Thankyou , please comment!!

Normalizer transformation scenario

How can we do this:
StudentName Subject1 Marks1 Subject2 Marks2
Sam Maths 100 English 80
John Maths 85 English 75

to the target:

StudentName Subject Marks
Sam Maths 100
Sam English 80
John Maths 85
John English 75

Answer :

In this way we can get the above output

Create a Stored Procedure in Oracle

Please comment if you like the video

Import Stored procedure in Informatica

Please comment if you like the video.. Thanks!!!

How will you approach this : 3

Input :

Student Name	Subject Name	Marks
Srivatsan	Maths	100
Sendil	Maths	95
Ragu	Physical Science	96
Muthu	Maths	75
Uma	Life Science	98
Raja	Life Science	92
Jothi	Physical Science	97
Karthick	Life Science	91

Output :

The output is to team up based on top marks . Each team should have 2 members

Students Name	Team Name	Marks
Srivatsan/Uma	Team1	100/98
Sendil/Raja	Team2	95/92
Ragu/Jothi	Team3	96/97
Muthu/Karthick	Team4	75/91

How will you approach this : 2

Input :

Student Name	Maths	Life Science	Physical Science
Sam	100	70	80
John	75	100	85
Tom	80	100	85

Output :

Student Name	Subject Name	Marks/Avg Marks
Sam	Maths	100/85
Sam	Life Science	70/90
Sam	Physical Science	80/83.33
John	Maths	75/85
John	Life Science	100/90
John	Physical Science	85/83.33
Tom	Maths	80/85
Tom	Life Science	100/90
Tom	Physical Science	85/83.33