what are conditional statements in aggregator transformation?

The conditional statements are the statements that we can include in the aggregate function itself.

While performing aggregate calculations.

EX:-

Sum (IIF(dept=’HR’,salary,0))   

here if dept is HR only will do SUM.

When cache files deleted for incremental aggregation what happens? 

When cache files not available in informatica server, then session won't fail, it will recreate the cache automatically. But it takes more time compared to normal run, reason is recreation of cache takes some time.

Can we select both sorted input and incremental aggregation at a time? 
No, we can't select. Reason is if you select sorted input it performs calculations in memory. When you select incremental aggregation it performs calculations on existing cache values.

Thanks 

Hari Babu

What are the output files that the Informatica server creates during the session running?

Informatica server log: Informatica server (on UNIX) creates a log for all status and error messages (default name: pm.server.log). It also creates an error log for error messages. These files will be created in Informatica home directory.

Session log file: Informatica server creates session log file for each session. It writes information about session into log files such as initialization process, creation of sql commands for reader and writer threads, errors encountered and load summary. The amount of detail in session log file depends on the tracing level that you set.

Session detail file: This file contains load statistics for each target in mapping. Session detail includes information such as table name, number of rows written or rejected. You can view this file by double clicking on the session in monitor window.

Performance detail file: This file contains information known as session performance details which helps you where performance can be improved. To generate this file select the performance detail option in the session property sheet.

Reject file: This file contains the rows of data that the writer does not write to targets.

Control file: Informatica server creates control file and a target file when you run a session that uses the external loader. The control file contains the information about the target flat file such as data format and loading instructions for the external loader.

Post session email: Post session email allows you to automatically communicate information about a session run to designated recipients. You can create two different messages. One if the session completed successfully the other if the session fails.

Indicator file: If u use the flat file as a target, you can configure the informatica server to create indicator file. For each target row, the indicator file contains a number to indicate whether the row was marked for insert,update,delete or reject.

Output file: If session writes to a target file, the informatica server creates the target file based on file properties entered in the session property sheet.

Cache files: When the informatica server creates memory cache it also creates cache files. For the following circumstances informatica server creates index and datacache files.

Aggregator transformation

Joiner transformation

Rank transformation

Lookup transformation

Regards,

Hari Babu

PMCMD Command

1. Scheduling a workflow 
pmcmd command syntax for scheduling the workflow is shown below:
pmcmd scheduleworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name
This command just schedules the workflow for the next run.

2. Start workflow 
The following pmcmd command starts the specified workflow:
pmcmd startworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name
This triggers the work-flow immediately

3. Stop workflow 
Pmcmd command to stop the infromatica workflow is shown below:
pmcmd stopworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name

4. Start workflow from a task 
You can start the workflow from a specified task using the following syntax:
pmcmd startask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name -startfrom task-name

5. Stopping a task. 
The following pmcmd command stops the specified task instance:
pmcmd stoptask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name task-name

6. Aborting workflow 
The following pmcmd commands are used to abort workflow:
pmcmd abortworkflow -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name

7. Aborting specific task
The following pmcmd commands are used to abort a specific task in a workflow:
pmcmd aborttask -service informatica-integration-Service -d domain-name -u user-name -p password -f folder-name -w workflow-name task-name

Regards,
Hari Babu

How to calculate count of duplicate records in Informatica?

Source 

Name, no, salary

Hari, 10, 10K

Babu, 20, 30K

Hari, 10, 10k

Naidu, 10, 40k

Target 

Name,No,Salary, count

hari ,10,10k,          2

Babu, 20 ,30K      1

naidu,10 ,40k       1

• Create an expression transformation, connect all ports from source qualifier to expression.

• Add other output port to expression with O_ENAME

• O_ENAME=ENAME

• Create aggregator transformation, connect all ports from expression to aggregator.

• In the aggregator, groupby on all columns

• Write a expression on O_ENAME

• O_ENAME=count(ENAME)

• Connect all ports from aggregator target

• connect O_ENAME to COUNT in target  

output will be 

hari,  10,    10k,     2

Babu, 20 ,30K      1

naidu,10 ,40k       1

Regards,
Hari Babu

UNIX Interview Question

1. Listing files

>ls

add.sh

logfile.txt

prime.pl

2. Displaying the contents of a file.

>cat file.txt

This is a sample unix file

Learning about unix server is awesome

3. Displaying first few lines from a file.

>head -5 logfile.dat

4. Creating a file.

touch new_file.txt

7. copying the contents of one file into another.

>cp file1 file2

8. Renaming and moving the files.

Renaming file

>mv file.txt new_file.txt

Moving the file to another directory.

>mv new_file.txt tmp/

9. Finding the number of lines in a file

>wc logfile.txt

21  26 198 logfile.txt

10. Replace text with Awk command

>cat file.txt

Learn unix

Learn linux

awk '{if($2=="unix") {$2="fedora"} print $0}' file.txt

Learn fedora

Learn linux

11. how to  print the date on the terminal?

>date

Mon Jan 23 01:37:51 PST 2012

12. Write a unix/linux date command to print the date in GMT/UTC time zone?

>date -u

Mon Jan 23 09:40:21 UTC 2012

13. Write a unix/linux date command to display only the date part and ignore the time part?

 >date '+%m-%d-%Y'

01-23-2012

Informatica Interview Questions

What are the limitations of joiner transformation?

• You cannot use a joiner transformation when input pipeline contains an update strategy transformation.
• You cannot use a joiner if you connect a sequence generator transformation directly before the joiner.

What are the different types of joins?

• Normal join: In a normal join, the integration service discards all the rows from the master and detail source that do not match the join condition.
• Master outer join: A master outer join keeps all the rows of data from the detail source and the matching rows from the master source. It discards the unmatched rows from the master source.
• Detail outer join: A detail outer join keeps all the rows of data from the master source and the matching rows from the detail source. It discards the unmatched rows from the detail source.
• Full outer join: A full outer join keeps all rows of data from both the master and detail rows

What is joiner cache?
When the integration service processes a joiner transformation, it reads the rows from master source and builds the index and data cached. Then the integration service reads the detail source and performs the join. In case of sorted joiner, the integration service reads both sources (master and detail) concurrently and builds the cache based on the master rows.

Why joiner is a blocking transformation?

When the integration service processes an unsorted joiner transformation, it reads all master rows before it reads the detail rows. To ensure it reads all master rows before the detail rows, the integration service blocks all the details source while it caches rows from the master source. As it blocks the detail source, the unsorted joiner is called a blocking transformation.

Regards,

Hari Babu

Informatica Interview Questions on Union Transformation

1. What is a union transformation?

A union transformation is used merge data from multiple sources similar to the UNION ALL SQL statement to combine the results from two or more SQL statements.

2. As union transformation gives UNION ALL output, how you will get the UNION output?
Way 1:-
Pass the output of union transformation to a sorter transformation. In the properties of sorter transformation check the option select distinct. 
way 2:-
we can pass the output of union transformation to aggregator transformation and in the aggregator transformation specify all ports as group by ports.

3. What are the guidelines to be followed while using union transformation?
The following rules and guidelines need to be taken care while working with union transformation:

• You can create multiple input groups, but only one output group.
• All input groups and the output group must have matching ports. The precision, datatype, and scale must be identical across all groups.
• The Union transformation does not remove duplicate rows. 
• You cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.

4. Why union transformation is an active transformation?

Union is an active transformation because it combines two or more data streams into one. Though the total number of rows passing into the Union is the same as the total number of rows passing out of it, and the sequence of rows from any given input stream is preserved in the output, the positions of the rows are not preserved, i.e. row number 1 from input stream 1 might not be row number 1 in the output stream. Union does not even guarantee that the output is repeatable.

Regards,
Hari Babu

Informatica Interview Questions on Sorter Transformation

1. What is a sorter transformation?

Sorter transformation is used to sort the data. You can sort the data either in ascending or descending order according to a specified sort key.

2. Why sorter is an active transformation?

As sorter transformation can suppress the duplicate records in the source, it is called an active transformation.(Distinct option)

3. How to improve the performance of a session using sorter transformation?

Sort the data using sorter transformation before passing in to aggregator or joiner transformation. As the data is sorted, the integration service uses the memory to do aggregate and join operations and does not use cache files to process the data.


Regards,

Hari Babu

Informatica Interview Questions on Aggregator Transformation

1.Why cannot we use sorted input option for incremental aggregation?
In incremental aggregation, the aggregate calculations are stored in historical cache on the server. In this historical cache the data need not be in sorted order.  If you give sorted input, the records come as presorted for that particular run but in the historical cache the data may not be in the sorted order. That is why this option is not allowed.

2.How the NULL values are handled in Aggregator?

You can configure the integration service to treat null values in aggregator functions as NULL or zero. By default the integration service treats null values as NULL in aggregate functions.

Regards,
Hari Babu