·
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.
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
·
TABLESPACES
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;
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);
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;
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)
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
MINUS
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
1.
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.
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
1.
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