Informatica Interview Question Part-8

Q. What is generated key and generated column id in normalizer transformation?

· The integration service increments the generated key (GK) sequence number each time it process a source row. When the source row contains a multiple-occurring column or a multiple-occurring group of columns, the normalizer transformation returns a row for each occurrence. Each row contains the same generated key value.

· The normalizer transformation has a generated column ID (GCID) port for each multiple-occurring column. The GCID is an index for the instance of the multiple-occurring data. For example, if a column occurs 3 times in a source record, the normalizer returns a value of 1, 2 or 3 in the generated column ID.

Q. What is difference between SUBSTR and INSTR?

INSTR function search string for sub-string and returns an integer indicating the position of the character in string that is the first character of this occurrence.

SUBSTR function returns a portion of string, beginning at character position, substring_length characters long. SUBSTR calculates lengths using characters as defined by the input character set.

Q. What are different Oracle database objects?

· TABLES

· VIEWS

· INDEXES

· SYNONYMS

· SEQUENCES

· TABLESPACES

Q. What is @@ERROR?

The @@ERROR automatic variable returns the error code of the last Transact-SQL statement. If there was no error, @@ERROR returns zero. Because @@ERROR is reset after each Transact-SQL statement, it must be saved to a variable if it is needed to process it further after checking it.

Q. What is difference between co-related sub query and nested sub query?

Correlated subquery runs once for each row selected by the outer query. It contains a reference to a value from the row selected by the outer query.

Nested subquery runs only once for the entire nesting (outer) query. It does not contain any reference to the outer query row.

For example,

Correlated Subquery:

Select e1.empname, e1.basicsal, e1.deptno from emp e1 where e1.basicsal = (select max(basicsal) from emp e2 where e2.deptno = e1.deptno)

Nested Subquery:

Select empname, basicsal, deptno from emp where (deptno, basicsal) in (select deptno, max(basicsal) from emp group by deptno)

Q. How does one escape special characters when building SQL queries?

The LIKE keyword allows for string searches. The ‘_’ wild card character is used to match exactly one character, ‘%’ is used to match zero or more occurrences of any characters. These characters can be escaped in SQL. Example:

SELECT name FROM emp WHERE id LIKE ‘%\_%’ ESCAPE ‘\’;

Use two quotes for every one displayed. Example:

SELECT ‘Franks”s Oracle site’ FROM DUAL;
SELECT ‘A ”quoted” word.’ FROM DUAL;
SELECT ‘A ””double quoted”” word.’ FROM DUAL;

Q. Difference between Surrogate key and Primary key?

Surrogate key:

1. Query processing is fast.

2. It is only numeric

3. Developer develops the surrogate key using sequence generator transformation.

4. Eg: 12453

Primary key:

1. Query processing is slow

2. Can be alpha numeric

3. Source system gives the primary key.

4. Eg: C10999

Q. How does one eliminate duplicate rows in an Oracle Table?

Method 1:

DELETE from table_name A
where rowid > (select min(rowid) from table_name B where A.key_values = B.key_values);

Method 2:

Create table table_name2 as select distinct * from table_name1;
drop table table_name1;
rename table table_name2 as table_name1;

In this method, all the indexes,constraints,triggers etc have to be re-created.

Method 3:

DELETE from table_name t1
where exists (select ‘x’ from table_name t2 where t1.key_value=t2.key_value and t1.rowid > t2.rowid)

Method 4:

DELETE from table_name where rowid not in (select max(rowid) from my_table group by key_value )

Q. Query to retrieve Nth row from an Oracle table?

The query is as follows:

select * from my_table where rownum <= n
MINUS
select * from my_table where rownum < n;

Q. How does the server recognize the source and target databases?

If it is relational - By using ODBC connection

FTP connection - By using flat file

Q. What are the different types of indexes supported by Oracle?

B-tree index

2. B-tree cluster index

3. Hash cluster index

4. Reverse key index

5. Bitmap index

6. Function Based index

Q. Types of Normalizer transformation?

There are two types of Normalizer transformation.

VSAM Normalizer transformation

A non-reusable transformation that is a Source Qualifier transformation for a COBOL source. The Mapping Designer creates VSAM Normalizer columns from a COBOL source in a mapping. The column attributes are read-only. The VSAM Normalizer receives a multiple-occurring source column through one input port.

Pipeline Normalizer transformation

A transformation that processes multiple-occurring data from relational tables or flat files. You might choose this option when you want to process multiple-occurring data from another transformation in the mapping.

A VSAM Normalizer transformation has one input port for a multiple-occurring column. A pipeline Normalizer transformation has multiple input ports for a multiple-occurring column.
When you create a Normalizer transformation in the Transformation Developer, you create a pipeline Normalizer transformation by default. When you create a pipeline Normalizer transformation, you define the columns based on the data the transformation receives from another type of transformation such as a Source Qualifier transformation.

The Normalizer transformation has one output port for each single-occurring input port.

Q. What are all the transformation you used if source as XML file?

XML Source Qualifier

· XML Parser

· XML Generator

Q. List the files in ascending order in UNIX?

ls -lt (sort by last date modified)

ls –ltr (reverse)

ls –lS (sort by size of the file)

Q. How do identify the empty line in a flat file in UNIX? How to remove it?

grep –v “^$” filename

Q. How do send the session report (.txt) to manager after session is completed?