Informatica Interview Question Part-8 - ETL- iNFORMATICA DEVELOPER

Friday, June 21, 2019

Informatica Interview Question Part-8


·         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.

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.

·         TABLES
·         VIEWS
·         INDEXES
·         SYNONYMS
·         SEQUENCES

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.

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)

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;

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

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 )

The query is as follows:
select * from my_table where rownum <= n
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


     B-tree index
2.   B-tree cluster index
3.   Hash cluster index
4.   Reverse key index
5.   Bitmap index
6.   Function Based index

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.

     XML Source Qualifier
·         XML Parser
 ·         XML Generator

ls  -lt (sort by last date modified) 
ls –ltr (reverse)
ls –lS (sort by size of the file)

grep –v “^$” filename

Email variable – %a (attach the file) %g – attach session log file

$> ps –ef

Q. How can i display only and only hidden file in the current directory?

ls -a|grep "^\."

# head -10 logfile

# tail -10 logfile

    Run once – Set 2 parameter date and time when session should start.
2.   Run Every – Informatica server run session at regular interval as we configured, parameter Days, hour, minutes, end on, end after, forever.
3.   Customized repeat – Repeat every 2 days, daily frequency hr, min, every week, every month.

No comments:

Post a Comment