Functions

1 Character Functions:
1.1 LENGTH:
1.2 LPAD:
1.3 LTRIM:
1.4 RPAD:
1.5 RTRIM:
1.6 SUBSTR:

2 Conversion Functions:
2.1 TO_CHAR:
2.2 TO_DATE:
2.3 TO_DECIMAL:
2.4 TO_FLOAT:
2.5 TO_INTEGER:

3 Date Functions:
3.1 ADD_TO_DATE
3.2 DATE_COMPARE
3.3 DATE_DIFF
3.4 GET_DATE_PART
3.5 LAST_DAY
3.6 MAX
3.7 MIN
3.8 ROUND
3.9 SET_DATE_PART
3.10 TRUNC

4 Special Functions:
4.1 DECODE
4.2 IIF
4.3 ERROR:
4.4 LOOKUP:

5 Test Functions:
5.1 ISNULL
5.2 IS_DATE
5.3 IS_NUMBER
5.4 IS_SPACES

1 Character Functions:

1.1 LENGTH:
The LENGTH function returns the number of characters in a string, including
trailing blanks. It is available in the Designer and the Workflow Manager.
LENGTH (string)

Example: The following expression returns the length of each customer name:
LENGTH (CUSTOMER_NAME)

CUSTOMER_NAME------------RETURN VALUE
Leonardo------------------------8
NULL ---------------------------NULL
Edwin Britto ---------------------12

1.2 LPAD:
The LPAD function adds a set of blanks or characters to the beginning of a string,
to set a string to a specified length. It is available in the Designer and the
Workflow Manager.
LPAD (first_string, length [, second_string])

Example: The following expression standardizes numbers to five digits by padding

them with leading zeros.
LPAD (NUM, 5, '0')

NUM ----------------------------RETURN VALUE

1 --------------------------------00001
250 -----------------------------00250

1.3 LTRIM:
The LTRIM function removes blanks or characters from the beginning of a string.
It is available in the Designer and the Workflow Manager.
LTRIM (string [, trim_set])
LTRIM (string) removes the leading spaces or blanks from the string. When LTRIM
function is used with a trim set, which is optional, it removes the characters in the
trim set from the string.

Example: The following expression removes the leading zeroes in the port
ITEM_CODE.
LTRIM (ITEM_CODE,'0')

ITEM_CODE -----------------RETURN VALUE

006 ---------------------------6
0803 --------------------------803

* The LTRIM function can be nested when needed to remove multiple characters.

1.4 RPAD:
The RPAD function converts a string to a specified length by adding blanks or
characters to the end of the string. It is available in the Designer and the
Workflow Manager.
RPAD( first_string, length [, second_string ] )

Example: The following expression returns the string with a length of 5 characters,
appending the string ':' to the end of each word:
RPAD (WORD, 5, ':’)

WORD ------------------------RETURN VALUE

Date -------------------------- Date:
Time --------------------------Time:

1.5 RTRIM:
The RTRIM function removes blanks or characters from the end of a string. It is
available in the Designer and the Workflow Manager.
RTRIM (string [, trim_set])

The RTRIM function can be combined with the LENGTH function if the trailing
blanks are to be ignored. It can also be nested when needed to remove multiple
characters.

RTRIM (string) removes the trailing spaces or blanks from the string. When RTRIM
function is used with a trimset, which is optional, it removes the characters in the
trimset from the string.

For example,
RTRIM (ITEM_CODE,'10')
The above expression removes the characters 10 in the port ITEM_CODE.
ITEM_CODE -----------------------------RETURN VALUE
0610 -------------------------------------06
380 ---------------------------------------38

In the second example the function removes the trailing zero since the RTRIM
compares the first character in the trimset with the last character of the string,
since it does not match it takes the second character in the trimset and compares
with last character of the string. Since it matches it removes it.

1.6 SUBSTR:
The SUBSTR function returns a portion of a string. It is available in the Designer
and the Workflow Manager.
SUBSTR( string, start [, length ] )

The SUBSTR may not give the desired result if the string on which it is used is not
trimmed. Though it is always a good practice to trim the strings before using them
in any expression, it becomes extremely important to trim them if they are used
in a SUBSTR function.

For example, if there is a function

SUBSTR (NAME, 2,2)
It will not return the 2,3 characters of the NAME if the port has leading spaces. In
this case LTRIM becomes essential.

SUBSTR(LTRIM(NAME),2,2)
The SUBSTR function can also be used to get the last few characters as described
below.
SUBSTR(NAME,-3,3)
This function will return the last three characters of the string. But it may not
return the required last three characters if the port has trailing blanks, hence
RTRIM is essential.

SUBSTR(RTRIM(NAME),-3,3)
Hence it is always better to trim the strings before using them in a SUBSTR
function.

SUBSTR(LTRIM(RTRIM(NAME)),3,2)
The above expression will get the 3,4 character of the port NAME irrespective of
whether the port has leading or trailing blanks or not.

2 Conversion Functions:

2.1 TO_CHAR:
The TO_CHAR function converts numeric values and dates to text strings. It is
available in the Designer and the Workflow Manager.
TO_CHAR( numeric_value )
TO_CHAR (date [, format ] )

Example : The following expression converts the values in the SALES port to text:
TO_CHAR (SALES )
SALES --------------------RETURN VALUE
1800.03 ------------------'1800.03'
-22.57891 -----------------'-22.57891'

The following expression converts the dates in the DATE_PROMISED port to text
in the format MON DD YYYY:
TO_CHAR (DATE_PROMISED, 'MON DD YYYY' )

DATE_PROMISED ----------------------RETURN VALUE

Apr 1 1998 12:00:10AM -----------------'Apr 01 1998'
If we omit the format_string argument, TO_CHAR returns a string in the default
date format ‘MM/DD/YYYY’.

We can use Conversion functions with DATE functions in order to do some

calculations.
The following composite expression converts the string DATE_PROMISED to date,
adds 1 to it and then converts the same to text string with the format
YYYYMMDD.

TO_CHAR(ADD_TO_DATE(TO_DATE(DATE_PROMISED),'DD',1),'YYYYMMDD')

Test functions can also be used with Conversion functions.

The following expression uses IS_DATE along with TO_CHAR.
IS_DATE(TO_CHAR(DATE_PROMISED,'YYYYMMDD'))

* TO_CHAR returns NULL if invalid Date is passed to the function.

2.2 TO_DATE:
The TO_DATE function converts a character string to a date datatype in the same
format as the character string. It is available in the Designer and the Workflow
Manager.
TO_DATE( string [, format ] )

Example : The following expression returns date values for the strings in the
DATE_PROMISED port. TO_DATE always returns a date and time. If we pass a
string that does not have a time value, the date returned always includes the time
00:00:00. If we execute a session in the twentieth century, the century will be 19.
The current year on the machine running the Informatica Server is 1998:

TO_DATE( DATE_PROMISED, 'MM/DD/YY' )

DATE_PROMISED -------------------RETURN VALUE
'12/28/81' ----------------------------Dec 28 1981 00:00:00
NULL --------------------------------NULL

The format of the string must exactly be the format given in the TO_DATE
function.
* TO_DATE function fails if invalid date entries are given. To avoid this we must
use IS_DATE function to check if the string has a valid date to be converted.

2.3 TO_DECIMAL:
The TO_DECIMAL function converts any value (except binary) to a decimal. It is
available in the Designer.
TO_DECIMAL( value [, scale ] )

Example : This expression uses values from the port IN_TAX. The datatype is
decimal with precision of 10 and scale of 3:
TO_DECIMAL( IN_TAX, 3 )
IN_TAX ------------------------RETURN VALUE
'15.6789' ------------------------15.678
NULL ---------------------------NULL
'A12.3Grove'-------------------- 0

We can also use two conversion functions together in a single expression.

The following expression uses the functions TO_DECIMAL and TO_CHAR.
TO_DECIMAL(TO_CHAR(DATE_PROMISED,'YYYYMMDD'))

2.4 TO_FLOAT:
The TO_FLOAT function converts any value (except binary) to a double-precision
floating point number (the Double datatype). It is available in the Designer and
the Workflow Manager.
TO_FLOAT( value )

Example : This expression uses values from the port IN_TAX:

TO_FLOAT( IN_TAX )
IN_TAX ------------------------RETURN VALUE
'15.6789' ------------------------15.6789
NULL ---------------------------NULL

2.5 TO_INTEGER:
The TO_INTEGER function converts any value (except binary) to an integer by
rounding the decimal portion of a value. It is available in the Designer and the
Workflow Manager.
TO_INTEGER( value )

Example : This expression uses values from the port IN_TAX:

TO_INTEGER( IN_TAX )
IN_TAX -------------------------RETURN VALUE
'15.6789' -------------------------16
'60.2' -----------------------------60

3 Date Functions:

Date Format Strings in the Transformation Reference

D, DD, DDD, DAY, DY, J

Days (01-31). We can use any of these format strings to specify the entire day
portion of a date. For example, if we pass 12-APR-1997 to a date function, we can
use any of these format strings specify 12.

HH, HH12, HH24

Hour of day (0 to 23), where zero is 12 AM (midnight). We can use any of these
formats to specify the entire hour portion of a date. For example, if we pass the
date 12-APR-1997 2:01:32 PM, we can use HH, HH12, or HH24 to specify the hour
portion of the date.

MI
Minutes.

MM, MON, MONTH

Month portion of date (0 to 59). We can use any of these format strings to specify
the entire month portion of a date. For example, if we pass 12-APR-1997 to a date
function, we can use MM, MON, or MONTH to specify APR.
SS , SSSS
Second portion of date (0 to 59).

Y, YY, YYY, YYYY , RR

Year portion of date (1753 to 9999). We can use any of these format strings to
specify the entire year portion of a date. For example, if we pass 12-APR-1997 to a
date function, we can use Y, YY, YYY, or YYYY to specify 1997.

3.1 ADD_TO_DATE
The ADD_TO_DATE function adds a specified amount to one part of a date/time
value, and returns a date in the same format as the specified date.
Note: If we do not specify the year as YYYY, the Informatica Server assumes the
date is in the current century. It is available in the Designer and the Workflow
Manager.
ADD_TO_DATE( date, format, amount )

Example : The following expression adds one month to each date in the
DATE_SHIPPED port. If we pass a value that creates a day that does not exist in a
particular month, the Informatica Server returns the last day of the month. For
example, if we add one month to Jan 31 1998, the Informatica Server returns Feb
28 1998.

Also note, ADD_TO_DATE recognizes leap years and adds one month to Jan 29
2000:
ADD_TO_DATE( DATE_SHIPPED, 'MM', 1 )

DATE_SHIPPED ----------------RETURN VALUE

Jan 12 1998 12:00:30AM --------Feb 12 1998 12:00:30AM

The following expression subtracts 10 days from each date in the DATE_SHIPPED
port:
ADD_TO_DATE( DATE_SHIPPED, 'D', -10 )
DATE_SHIPPED --------------RETURN VALUE
Jan 1 1997 12:00:30AM -------Dec 22 1996 12:00AM
The following expression subtracts 15 hours from each date in the DATE_SHIPPED
port:
ADD_TO_DATE( DATE_SHIPPED, 'HH', -15 )

DATE_SHIPPED ------------------RETURN VALUE

Jan 1 1997 12:00:30AM ------------Dec 31 1996 9:00:30AM

In ADD_TO_DATE function, if the argument passed evaluates to a date that does

not exist in a particular month, the Informatica Server returns the last day of the
month.
The following expression reveals this.

ADD_TO_DATE( DATE_SHIPPED, 'MON', 3 )

DATE_SHIPPED -------------------RETURN VALUE
Jan 31 1998 6:24:45PM ------------Apr 30 1998 6:24:45PM

3.2 DATE_COMPARE
The DATE_COMPARE function returns a value indicating the earlier of two dates.
It is available in the Designer and the Workflow Manager.
DATE_COMPARE( date1, date2 )

Example : The following expression compares each date in the DATE_PROMISED

and DATE_SHIPPED ports, and returns an integer indicating which date is earlier:
DA DATE_COMPARE ( DATE_PROMISED, DATE_SHIPPED )
DATE_PROMISED ---------DATE_SHIPPED ----------------RETURN VALUE
Jan 1 1997 ------------------Jan 13 1997 --------------------- -1
Feb 1 1997 ------------------Feb 1 1997 ---------------------- 0
Dec 22 1997 -----------------Dec 15 1997 --------------------- 1

3.3 DATE_DIFF
The DATE_DIFF function returns the length of time between two dates, measured
in the specified increment (years, months, days, hours, minutes, or seconds). It is
available in the Designer and the Workflow Manager.
DATE_DIFF( date1, date2, format )
Example: The following expressions return the number of days between the
DATE_PROMISED and the DATE_SHIPPED ports:
DATE_DIFF DATE_DIFF ( DATE_PROMISED, DATE_SHIPPED, 'D' )
DATE_DIFF DATE_DIFF ( DATE_PROMISED, DATE_SHIPPED, 'DD' )

DATE_PROMISED ------------DATE_SHIPPED --------------RETURN VALUE

Jan 1 1997 12:00:00AM -------Mar 29 1997 12:00:00PM ----- -87.5
Mar 29 1997 12:00:00PM----- Jan 1 1997 12:00:00AM --------- 87.5

We can combine DATE functions and TEST functions so as to validate the dates.
For example, while using the DATE functions like DATE_COMPARE and
DATE_DIFF, the dates given as inputs can be validated using the TEST function
IS_DATE and then passed to them if valid.

3.4 GET_DATE_PART
The GET_DATE_PART function returns the specified part of a date as an integer
value, based on the default date format of MM/DD/YYYY HH24:MI:SS. It is
available in the Designer and the Workflow Manager.
GET_DATE_PART( date, format )

Example: The following expressions return the day for each date in the
DATE_SHIPPED port:
GE GET_DATE_PART ( DATE_SHIPPED, 'D' )
GEGET_DATE_PART ( DATE_SHIPPED, 'DD' )

DATE_SHIPPED -----------------RETURN VALUE

Mar 13 1997 12:00:00AM-------- 13
June 3 1997 11:30:44PM---------- 3
NULL NULL

3.5 LAST_DAY
The LAST_DAY function returns the date of the last day of the month for each
date in a port. It is available in the Designer and the Workflow Manager.
LAST_DAY( date )
Example : The following expression returns the last day of the month for each
date in the ORDER_DATE port:
LAST_DAY( ORDER_DATE )

ORDER_DATE -------------------------------------RETURN VALUE

Apr 1 1998 12:00:00AM ---------------------------Apr 30 1998 12:00:00AM
Jan 6 1998 12:00:00AM ---------------------------Jan 31 1998 12:00:00AM
DATE functions combine with Conversion functions also.
The following expression has LAST_DAY and TO_DATE functions nested or
combined together.
LAST_DAY( TO_DATE( GIVEN_DATE, 'DD-MON-YY' ))

3.6 MAX

The MAX function returns the latest date found in a group. It is available in the
Designer.
MAX( date, filter_condition )
We can return the maximum date for a port or group.
Example: The following expression returns the maximum order date for
flashlights:
MAX( ORDERDATE, ITEM_NAME='Flashlight' )

ITEM_NAME -----------ORDER_DATE
Flashlight ---------------Apr 20 1998
Regulator ---------------System May 15 1998
Flashlight ---------------Sep 21 1998
Diving Hood ------------Aug 18 1998
Halogen Flashlight ------Feb 1 1998
Flashlight ---------------Oct 10 1998

RETURN VALUE: Oct 10 1998

3.7 MIN
The MIN function returns the earliest date found in a group. It is available in the
Designer.
MIN( date, filter_condition )

Example: The following expression returns the oldest order date for flashlights:
MIN( ORDER_DATE, ITEM_NAME='Flashlight' )

ITEM_NAME --------------ORDER_DATE
Flashlight -------------------Apr 20 1998
Regulator System -----------May 15 1998
Flashlight -------------------Sep 21 1998
Diving Hood ----------------Aug 18 1998
Halogen Flashlight ----------Feb 1 1998
Flashlight -------------------Oct 10 1998
RETURN VALUE: ----------Feb 1 1998

3.8 ROUND
The ROUND function rounds one part of a date. It is available in the Designer and
the Workflow Manager.
ROUND( date [, format ] )

Example: The following expressions round the month portion of each date in the
DATE_SHIPPED port.
ROUND( DATE_SHIPPED, 'MM' )
ROUND( DATE_SHIPPED, 'MON' )

DATE_SHIPPED ---------------RETURN VALUE

Jan 15 1998 2:10:30AM --------Jan 1 1998 12:00:00AM

Similarly the ROUND function can be used to round off Year, Day or Time
portions.

3.9 SET_DATE_PART
The SET_DATE_PART function sets one part of a date/time value to a specified
value. It is available in the Designer and the Workflow Manager.
SET_DATE_PART( date, format, value )
Example: The following expressions change the month to June for the dates in the
DATE_PROMISED port. The Informatica Server displays an error when we try to
create a date that does not exist, such as changing March 31 to June 31:
SET_DATE_PART( DATE_PROMISED, 'MM', 6 )
SET_DATE_PART( DATE_PROMISED, 'MON', 6 )

DATE_PROMISED -----------------RETURN VALUE

Jan 1 1997 12:15:56AM -------------Jun 1 1997 12:15:56AM
NULL -------------------------------NULL

Similarly the SET_DATE_PART function can be used to round off Year, Day or Time
portions.

3.10 TRUNC

The TRUNC function truncates dates to a specific year, month, day, hour, or
minute. It is available in the Designer and the Workflow Manager.
TRUNC( date [, format ] )

Example: The following expressions truncate the year portion of dates in the
DATE_SHIPPED port:
TRUNC( DATE_SHIPPED, 'Y' )
TRUNC( DATE_SHIPPED, 'YY' )

DATE_SHIPPED ---------------RETURN VALUE

Jan 15 1998 2:10:30AM --------Jan 1 1998 12:00:00AM

Similarly the TRUNC function can be used to truncate Month , Day or Time
portions.
The functions TRUNC & ROUND can be nested in order to manipulate dates.

4 Special Functions:

4.1 DECODE
The DECODE function searches a port for the specified value. It is available in the
Designer and the Workflow Manager.
DECODE( value, first_search, first_result [, second_search, second_result ]…[,
default ] )

Example: We might use DECODE in an expression that searches for a particular

ITEM_ID and returns the ITEM_NAME:

DECODE( ITEM_ID, 10, 'Flashlight',

14, 'Regulator',
20, 'Knife',
40, 'Tank',
'NONE' )

ITEM_ID RETURN VALUE

10 Flashlight
14 Regulator
17 NONE

4.2 IIF
The IIF function returns one of two values we specify, based on the results of a
condition. It is available in the Designer and the Workflow Manager.

IIF( condition, value2 [, value2 ] )

Example : IIF( SALES <>=90,'A',
(IIF(MARKS>= 75,'B',
(IIF(MARKS>=65,'C',
(IIF(MARKS>=55,'D',
IIF(MARKS>=45,'E',
'F'))))))))

The same result can be obtained with

DECODE(TRUE,
MARKS>=90,'A',
MARKS>=75,'B',
MARKS>=65,'C',
MARKS>=55,'D',
MARKS>=45,'E',
'F')
When the number of conditions increase we will be able to appreciate the
simplicity of the DECODE function and the complexity of the IIF function.
In both the cases , If MARKS>90 it will return 'A' though it satisfies all the
conditions given. It is because it returns when the first condition is satisfied.
Therefore even if a port satisfies two or more the conditions it will take only the
first one. Therefore Ordering is important in IIF and DECODE functions.

4.3 ERROR:
The ERROR function causes the Informatica Server to skip a record and throws an
error message defined by the user. It is available in the Designer.
ERROR( string )

Example : The following example shows how you can reference a mapping that
calculates the average salary for employees in all departments of your company,
but skips negative values. The following expression nests the ERROR function in
an IIF expression so that if the Informatica Server finds a negative salary in the
Salary port, it skips the row and displays an error:

IIF( SALARY <>

SALARY ------------RETURN VALUE

10000 ------------- 10000

-15000 ----------'Error. Negative salary found. Row skipped.'

The below example combines two special functions, a test Function and a
conversion function.
IIF(IS_DATE(DATE_PROMISED,'MM/DD/YY'),TO_DATE(DATE_PROMISED),ERROR('I
nvalid Date'))

4.4 LOOKUP:

The LOOKUP function searches for a particular value in a lookup source column. It
is available in the Designer.

LOOKUP( result, search1, value1 [, search2, value2]… )

Example : The following expression searches the lookup source :[Link] for a
specific item ID and price, and returns the item name if both searches find a
match:

LOOKUP( :[Link].ITEM_NAME, :[Link].ITEM_ID, 10, :[Link],

15.99 )

ITEM_NAME ----------ITEM_ID -------------PRICE

Regulator ----------------5 ----------------------100.00

Flashlight -----------------10 --------------------15.99

5Test Functions:

5.1 ISNULL The ISNULL function returns whether a value is NULL. It is available in
the Designer and the Workflow Manager.

ISNULL( value )

Example : The following example checks for null values in the items table:

ISNULL ISNULL ( ITEM_NAME )

ITEM_NAME -------------RETURN VALUE

Flashlight------------------- 0 (FALSE)

NULL -----------------------1 (TRUE)

'' -----------------------------0 (FALSE) Empty string is not NULL

5.2 IS_DATE

The IS_DATE function returns whether a value is a valid date. It is available in the
Designer and the Workflow Manager. IS_DATE( value ) Example : The following
expression checks the INVOICE_DATE port for valid dates:

IS_DATE( INVOICE_DATE )
This expression returns data similar to the following:

INVOICE_DATE -----------------RETURN VALUE

NULL ------------------------------NULL

180 ---------------------------------0 (FALSE)

'04/01/98' --------------------------0 (FALSE)

'04/01/1998 00:12:15' ------------- 1 (TRUE)

'02/31/1998 12:13:55' ---------------0 (FALSE) (February does not have 31 days)

'John Smith' --------------------------0 (FALSE)

This function can also be used to validate a date for a specified format for which
the syntax is

IS_DATE( value, format )

If the format is not specified, ‘MM/DD/YYYY’ is taken as the default format.

5.3 IS_NUMBER The IS_NUMBER returns whether a string is a valid number. It is

available in the Designer and the Workflow Manager.

IS_NUMBER( value )

Example : The following expression checks the ITEM_PRICE port for valid
numbers: IS_NUMBER( ITEM_PRICE )

ITEM_PRICE --------------RETURN VALUE

123.00 ----------------------1 (True)

-3.45e+3 --------------------1 (True)

'' -----------------------------0 (False) Empty string

+123abc ----------------------0 (False)

ABC --------------------------0 (False)

-ABC-------------------------- 0 (False)

NULL -------------------------NULL

5.4 IS_SPACES

The IS_SPACES function returns whether a value consists entirely of spaces. It is

available in the Designer and the Workflow Manager.

IS_SPACES( value )

Example : The following expression checks the ITEM_NAME port for rows that
consist entirely of spaces:

IS_SPACES IS_SPACES ( ITEM_NAME )

ITEM_NAME ----------------------RETURN VALUE

Flashlight--------------------------- 0 (False)

-------------------------- 1 (True)

Regulator system-------------------- 0 (False)

===================================

LENGTH: LENGTH (CUSTOMER_NAME)

LPAD: LPAD (NUM, 5, '')
LTRIM: LTRIM (ITEM_CODE,'')
RPAD: RPAD (WORD, 5, ':’)
RTRIM: RTRIM (ITEM_CODE,'1')
SUBSTR: SUBSTR( string, start [, length ] )

good practice to trim the strings SUBSTR (NAME, 2,2)

SUBSTR(LTRIM(NAME),2,2)
SUBSTR(NAME,-3,3)
SUBSTR(RTRIM(NAME),-3,3)
SUBSTR(LTRIM(RTRIM(NAME)),3,2)
The SUBSTR function returns a portion of a string.

Conversion Functions:
TO_CHAR: TO_CHAR (SALES )
TO_CHAR (DATE_PROMISED, 'MON DD YYYY' )
TO_DATE: DATE( DATE_PROMISED, 'MM/DD/YY' )
TO_DECIMAL: TO_DECIMAL( value [, scale ] )
TO_FLOAT: TO_FLOAT( value )
TO_INTEGER: TO_INTEGER( IN_TAX )

Date Functions:
ADD_TO_DATE: ADD_TO_DATE( DATE_SHIPPED, 'MM', 1 )
DATE_COMPARE: DATE_COMPARE ( DATE_PROMISED, DATE_SHIPPED )
DATE_DIFF: DATE_DIFF ( DATE_PROMISED, DATE_SHIPPED, 'D' )
GET_DATE_PART: GET_DATE_PART( date, format )
LAST_DAY :LAST_DAY( ORDER_DATE )
MAX :MAX( ORDERDATE, ITEM_NAME='Flashlight' )
MIN: MIN( ORDER_DATE, ITEM_NAME='Flashlight' )
ROUND: ROUND( DATE_SHIPPED, 'MM' )
SET_DATE_PART :SET_DATE_PART( DATE_PROMISED, 'MM', 6 )
TRUNC :TRUNC( date [, format ] )

Special Functions

DECODE function searches a port for the specified value. It is available in the
Designer and the Workflow Manager.

The IIF function returns one of two values we specify, based on the results of a
condition. It is available in the Designer and the Workflow Manager.

The ERROR function causes the Informatica Server to skip a record and throws an
error message defined by the user. It is available in the Designer.

The LOOKUP function searches for a particular value in a lookup source column. It
is available in the Designer.

Test Functions
ISNULL
IS_SPACES

Most Used Commands

Show All Table

Oracle - SELECT * FROM CAT
Teradata - SELECT *FROM [Link] / SELECT *FROM [Link]

Show Table Info

Oracle - DESC tablename / DESCRIBE teblename
Teradata - show table tablename / HELP TABLE tablename

COUNT
Table - SELECT COUNT (*) FROM tablename
Specific Column - SELECT COUNT(ColumnName)FROM tablename
Group Column - SELECT ColumnName, COUNT (*) FROM tablename GROUP BY
ColumnName

Returning TOP N Records

Oracle - SELECT * FROM tablename WHERE ROWNUM <= 10
SQL Server / Teradata - SELECT TOP 10 * FROM tablename

DISTINCT
SELECT DISTINCT ColumnName FROM tablename

Copy Table
Oracle - CREATE TABLE New_tablename AS SELECT * FROM Old_tablename
Teradata - CREATE TABLE Old_tablename AS New_tablename WITH DATA;

Delete Table
Oracle - DELETE FROM tablename
Teradata - DELETE TABLE tablename

Dummy Table
Oracle - SELECT 5*7 FROM DUAL
Teradata - SELECT 5*7 FROM tablename (Use existing any table name)

SELECT PROD_DESC, COST FROM PRODUCTS_TBL WHERE PROD_ID = ‘119’

SELECT PROD_DESC,COST FROM PRODUCTS_TBL WHERE COST < 20 ORDER BY

PROD_DESC ASC

SELECT EMP_ID, CITY FROM EMPLOYEE_TBL GROUP BY CITY, EMP_ID

HAVING

SELECT CITY, AVG(PAY_RATE), AVG(SALARY)

FROM EMP_PAY_TMP
WHERE CITY <> ‘GREENWOOD’
GROUP BY CITY
HAVING AVG(SALARY) > 20000
ORDER BY 3;"

Order of Clause - Select / From / where / Group by / having / Order by

SELECT
CITY_NAME, COUNT (CUST_ID) AS TOTALCUSTOMER
FROM CUSTOMER_TABLE
WHERE CITY_NAME LIKE 'N%'
GROUP BY CITY_NAME
HAVING COUNT (CUST_ID) <=3
ORDER BY COUNT (CUST_ID)

Eg: Table Name - CUSTOMER_TABLE / Columns - CITY_NAME and CUST_ID

INSERT
INSERT INTO SAMPLES.EMPLOYEE_PAY_TBL
(EmpName, EmpNo, DeptNo, Sex, DOB)
VALUES ('John Smith', 10001, NULL, 'M', 560231)

UPDATE
UPDATE ORDERS_TBL
SET QTY = 1,
CUST_ID = ‘221’
WHERE ORD_NUM = ‘23A16’;

Delete Rows
DELETE FROM ORDERS_TBL
WHERE ORD_NUM ='23A16'

ALTER
ALTER TABLE EMPLOYEE_TBL MODIFY
EMP_ID VARCHAR(10)

ALIAS - SELECT HOTELNAME AS HOTEL_NAME FROM HOTEL

SELECT * FROM TD_ORDERS WHERE EMPLOYEEID BETWEEN '4' AND ' 5'

SELECT * FROM TD_ORDERS WHERE EMPLOYEEID IN ('4','5')

SELECT * FROM TD_ORDERS WHERE CUSTOMERID LIKE '%AV%'

SELECT * FROM EMPLOYEE_TBL WHERE FIRST_NAME ='JOHN' OR LAST_NAME

='GLAS'"

SELECT * FROM EMPLOYEE_TBL WHERE FIRST_NAME ='JOHN' AND LAST_NAME

='SMITH'

WHERE SALARY <> ‘20000’

WHERE SALARY != ‘20000’"
WHERE Salary NOT BETWEEN ‘20000’ AND ‘30000’
WHERE SALARY NOT IN (‘20000’, ‘30000’, ‘40000’)
WHERE SALARY NOT LIKE ‘200%’
WHERE SALARY IS NOT NULL
SELECT SALARY + BONUS FROM EMPLOYEE_PAY_TBL

SELECT SUM(COST) FROM PRODUCTS_TBL

SELECT CITY, TRANSLATE(CITY,’IND’,’ABC’)FROM EMPLOYEE_TBL

SELECT CITY, REPLACE(CITY,’I’,’Z’) FROM EMPLOYEE_TBL

SELECT LOWER(LAST_NAME)FROM EMPLOYEE_TBL

SELECT EMP_ID, SUBSTR(EMP_ID,1,3) FROM EMPLOYEE_TBL

SELECT PROD_DESC, INSTR(PROD_DESC,’A’,1,1) FROM PRODUCTS_TBL

SELECT POSITION, LTRIM(POSITION,’SALES’) FROM EMPLOYEE_PAY_TBL

SELECT CITY, DECODE(CITY,’INDIANAPOLIS’,’INDY’,

‘GREENWOOD’,’GREEN’,’OTHER’)
FROM EMPLOYEE_TBL

SELECT PROD_DESC, LENGTH(PROD_DESC) FROM PRODUCTS_TBL;

SELECT PAGER, IFNULL(PAGER,9999999999) FROM EMPLOYEE_TBL

SELECT LPAD(PROD_DESC,30,’.’) PRODUCT FROM PRODUCTS_TBL

SELECT RPAD(PROD_DESC,30,’.’) PRODUCT FROM PRODUCTS_TBL

Subqueries

SELECT E.EMP_ID, E.LAST_NAME, E.FIRST_NAME, EP.PAY_RATE

FROM EMPLOYEE_TBL E, EMPLOYEE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID
AND EP.PAY_RATE < (SELECT PAY_RATE
FROM EMPLOYEE_PAY_TBL
WHERE EMP_ID = 220984332)
Most used Unix Commands

man - manual pages - Ex: man ls (displays the help for the command ls)

passwd - changing passwords

date - system date

who - lists all users logged in

whoami - lists who you are.

cal - displays the calendar - Ex: cal 1990 cal 6 1994

pwd - print working directory

cd [dir] - change directory

cd ../ (moves back one directory)
cd (goes to your default directory)

ls [options] [names] - list contents

ls -l (long listing)
ls -lg (long listing including group)
ls -a (list all files including dot files)
ls -lt (sort files by timestamp)
ls -l [Link] (displays long listing for the file [Link])

more - displays a file a screenful at a time

more tempfile Note: On many att machines you may have to use pg instead

cat - displays/types an entire file Ex: cat -b file1

mkdir - make a new directory Ex: mkdir -p /tmp/usr1/fred (create intermediate

directories also)

mv [options] sources target - move a file

mv -i (prompt in case you are overwriting a file)

cp - copy a file
cp -i (prompt in case you are overwriting a file)
cp -r (recursive copy for directories)

rm - remove a file
rm -i (prompt before deletion)
rm -r (remove files recursively)

rmdir - remove a directory. Ex: rmdir tempdir

chmod - change the permission of a file.

chmod +x tempfile (add execute permission)
chmod u+x tempfile (add execute for user only)
chmod 4755 oracle (set the setuid bit on)

grep - search a file for a string or expression.

grep -n (print line numbers)
grep -i (ignore case sensitivity)

find - search for files.

find . -name sqlplus -print (find the full pathname of sqlplus starting from the
current directory)
find . -name '*sql*' -print (find the full pathname of file where 'sql' is in its name)

wc - word count
wc -l (line count)
wc -w (word count)
wc -c (character count)

ps - display process status

ps -aux (ucb) ps -ef (att)

kill - send a signal to terminate a process.

kill -9 (signal will always be caught)
id - print name, ID, group and group ID.

df - disk space on file systems.

du - disk usage.

lpr - send a job to the printer

uname - prints o/s release information

uname -a (list all info)

nm - print symbol name list for object files

ar - create library archives, add or extract files

ar d (delete archive)
ar x (extract archive)
ar t (list contents of archive)

ranlib - makes table of contents for an archive

ipcs - interprocess communication facilities status

ipcs -s (print semaphore information)
ipcs -m (print shared memory information)
ipcs -b (print size information)

ipcrm - delete ipc facilities

ipcrm -s
ipcrm -m

chown - change ownership of a file

chown joe myfile (make joe the owner of myfile)
chown -R joe mydir (recursive change owner)

chgrp - change group

newgrp - new group

newgrp dba (Add a new group called dba)
file - lists file type. Note: the type of file may be misleading.

ln - links (ln -s: create a soft link - saves space)

su - super-user or set user

dd - file conversion and copy utility Ex: dd if=myfile of=newfile conv=ucase

(converts to uppercase)

diff - file differences.

umask - sets default permission for new files and directories

stty - terminal settings

stty erase h (resets erase character to 'h')
stty -a (list all current settings)

tty - lists terminal

cpio - copy file archives Ex: cpio -icBdvmu < /dev/rmt0 45. tar - tape archives Ex:
tar xvt /dev/rmt0

telnet - use TELNET protocol to access another machine

rlogin - remote login

echo - echo command

ulimit - (att) - defines the max size of files on some systems.

vmstat - report virtual memory statistics

pstat - do determine resource such as swap etc ..

make - this is a command generator.

env - list environment variables (printenv on some machines.)

logname - lists login id from env variable LOGNAME.

hostname - lists host name

pstat -s reports system swap area (BSD) swap -l reports system swap area (AT&T)

head -100 (first 100 rows)

tail - 100

Basic Unix Command

Directory

ls List all files and directory of current working directory.

ls –a, -l , -s Lists all files (hidden files) / -l - Detail / –s - Size
ls -ltr List files , newest last
ls list* list all files in the current directory starting with list
ls *list list all files in the current directory ending with ....list The ? wildcard
mkdir Make directory
Pwd Print working directory
cd Change directory
cd ~ Go to specific dir from any place
cd . The current directory (.)
cd .. The parent directory (..)

Files

cp file1 file2 Copy file1 and call it file2

mv file1 file2 move or rename file1 to file2
rm file Remove a file
cat file Display a file
less file Display a file a page at a time
head file Display the first few lines of a file
head -5 file Show only First 5 lines of a file
tail file Display the last few lines of a file
more file Shows the first part of a file, just as much as will fit on one screen
diff file1 file2 Compares files, and shows where they differ
wc File count number of lines/words/characters in file
cat > file Add content to new file
Ctr D Save and Exit
cat >> file File append standard output to a file
cat < Redirect standard input from a file
cat file1 file2 > file3 Concatenate file1 and file2 to file3
sort -n file Sort file by Numaric

Search

grep 'keyword' file search a file line for keywords

-i for Ignore case grep -i 'keyword' file (grep -i 'keyword' file)
-v display those lines that do NOT match
-n precede each matching line with the line number
-c print only the total count of matched lines

Find

find . -name "*.txt" -print Search for all files with the extention .txt, starting at
the current directory (.)
find . -size +1M -ls find files over 1Mb in size, and display the result as a long
listing
history show command history list

Access rights

u-user /g-group /o-other/a-all/r-read

w-write (and delete)/ x-execute (and access directory)
+add permission / -take away permission
chmod go-rwx biglist remove read write and execute permissions on the file
biglist
chmod a+rw biglist give read and write permissions on the file biglist to all
Processes & Jobs
4 for r, 2 for w, 1 for x
Triplet for u: rwx => 4 + 2 + 1 = 7
Triplet for g: r-x => 4 + 0 + 1 = 5
Tripler for o: r-x => 4 + 0 + 1 = 5
chmod 755 filename

gzip

gzip [Link] compress the file and place it in a file called [Link]
gunzip [Link] To expand the file, use the gunzip command

zcat

zcat [Link] will read gzipped files without needing to uncompress

file * file classifies the named files according to the type of data

Jobs

jobs list along with a job number

fg %jobnumber To restart (foreground) a suspended processes
fg %1 For example, to restart sleep 1000, type

Killing a process kill a job running in the foreground, type ^C

kill %1 kill job number 1

kill 26152 kill process number 26152
kill -9 26152 If a process refuses to be killed, uses the -9 option

Print
lpr file Use the -P option to specify the printer name (other than default printer)
lpq check out the printer queue
lprm jobnumber Remove something from the printer queue.

General

Who list users currently logged in

who > [Link] One method to get a sorted list of names
sort <> One method to get a sorted list of names
who sort will give the same result as above, but quicker and cleaner
who wc -l To find out how many users are logged on
df space left on the file system
du number of kilobyes used by each subdirectory
du -s * -s display only a summary / * means all files and directories
history Show command history list

Help

man wc more about the wc (word count) command

whatis wc brief description of a command
apropos keyword match commands with keyword in their man pages

VI Editor

Unix Vi Editor

vi filename - Open a file When you start up vi, you are in "command mode." -
Default mode

File management commands

:w name Write edit buffer to file name

:wq Write to file and quit
:q! Quit without saving changes
ZZ Same as :wq
:sh Execute shell commands (d)

Moving the Cursor

j or [or down-arrow] move cursor down one line

k [or up-arrow] move cursor up one line
h or [or left-arrow] move cursor left one character
l or [or right-arrow] move cursor right one character
0 (zero) move cursor to start of current line (the one with the cursor)
$ move cursor to end of current line
w move cursor to beginning of next word
b move cursor back to beginning of preceding word
:0 (zero) or 1G move cursor to first line in file
:n or nG move cursor to line n
:$ or G move cursor to last line in file

Screen Manipulation

^f move forward one screen

^b move backward one screen
^d move down (forward) one half screen
^u move up (back) one half screen
^l redraws the screen
^r redraws the screen, removing deleted lines

Inserting or Adding Text

i insert text before cursor, until hit

I insert text at beginning of current line, until hit
a append text after cursor, until hit
A append text to end of current line, until hit
o open and put text in a new line below current line, until hit
O open and put text in a new line above current line, until hit
Deleting Text

x delete single character under cursor

Nx delete N characters, starting with character under cursor
dw delete the single word beginning with character under cursor
dNw delete N words beginning with character under cursor; e.g., d5w deletes 5
words
D delete the remainder of the line, starting with current cursor position
dd delete entire current line
Ndd or dNd delete N lines, beginning with the current line; e.g., 5dd deletes 5
lines

Cutting and Pasting Text

yy copy (yank, cut) the current line into the buffer

Nyy or yNy copy (yank, cut) the next N lines, including the current line, into the
buffer
p put (paste) the line(s) in the buffer into the text after the current line

Searching Text

/ string search forward for occurrence of string in text

? string search backward for occurrence of string in text
n move to next occurrence of search string
N move to next occurrence of search string in opposite direction

Determining Line Numbers

:.= returns line number of current line at bottom of screen

:= returns the total number of lines at bottom of screen
^g provides the current line number, along with the total number of lines,
in the file at the bottom of the screen
Saving and Reading Files

:r filename read file named filename and insert after current line (the line with
cursor)
:w write current contents to file named in original vi call
:w newfile write current contents to a new file named newfile
:12,35w smallfile write the contents of the lines numbered 12 through 35 to a
new file named smallfile
:w! prevfile write current contents over a pre-existing file named prevfile

Shell Scripting

$ echo '#!/bin/sh' > [Link]

$ echo 'echo Hello World' >> [Link]
$ chmod 755 [Link]
$ ./[Link]
Hello World
$

Variables
$ x="hello"
$ y=`expr $x + 1`
expr: non-numeric argument
$

#!/bin/sh
echo What is your name?
read MY_NAME
echo "Hello $MY_NAME - hope you're well."

[Link]
#!/bin/sh
echo "What is your name?"
read USER_NAME
echo "Hello $USER_NAME"
echo "I will create you a file called ${USER_NAME}_file"
touch "${USER_NAME}_file"

If you want to add some character after the variable use curly brackets

Wildcards
Think first how you would copy all the files from /tmp/a into /tmp/b. All the .txt
files? All the .html files?

$ cp /tmp/a/* /tmp/b/
$ cp /tmp/a/*.txt /tmp/b/
$ cp /tmp/a/*.html /tmp/b/

So how do we display: Hello "World" ?

$ echo "Hello \"World\""

Loops

For
[Link]
#!/bin/sh
for i in 1 2 3 4 5
do
echo "Looping ... number $i"
done

While Loops
#!/bin/sh
INPUT_STRING=hello
while [ "$INPUT_STRING" != "bye" ]
do
echo "Please type something in (bye to quit)"
read INPUT_STRING
echo "You typed: $INPUT_STRING"
done

#!/bin/sh
The colon (:) always evaluates to true

while :
do
echo "Please type something in (^C to quit)"
read INPUT_STRING
echo "You typed: $INPUT_STRING"
done

If

The syntax for if...then...else... is:

if [ ... ]
then
# if-code
else
# else-code
fi
Note that fi is if backwards! This is used again later with case and esac.
Also, be aware of the syntax - the "if [ ... ]" and the "then" commands must be on
different lines. Alternatively, the semicolon ";" can seperate them:
if [ ... ]; then
# do something
fi
You can also use the elif, like this:
if [ something ]; then
echo "Something"
elif [ something_else ]; then
echo "Something else"
else
echo "None of the above"
fi
Unix Interview Questions

How do you find out the current directory you are in?
pwd

How do you find out what's your shell?

echo $SHELL

How do you list currently running process?

ps

How do you stop a process?

kill pid

How do you stop all the processes, except the shell window?
kill 0 (Zero)

What's the command to find out users on the system?

who

How do you count words, lines and characters in a file?

wc filename

What's the command to find out today's date?

date

How do you find out your own username?

whoami

How do you remove a file?

rm filename

How do you search for a string inside a given file?

grep string filename
How do you search for a string inside a directory?
grep string *

How do you search for a string in a directory with the subdirectories recursed?
grep -r string *

What's the conditional statement in shell scripting?

if {condition} then fi

How do you do number comparison in shell scripts?

-eq, -ne, -lt, -le, -gt, -ge

How do you fire a process in the background?

./process-name &

How do you refer to the arguments passed to a shell script?

$1, $2 and so on. $0 is your script name.

How do you find out the number of arguments passed to the shell script?
$#

How do you capture the return code?

$?

How do you know about running processes of a particular user?

ps -u username

Unions

Unions

Unions are used to retrieve records from multiple tables or to get multiple record
sets from a single table.
select cust_id, age
from financial.customer_name
UNION
select cust_id, age
from [Link] - we can add more union ( Same columns name to be
selected )

By default, all duplicates are removed in UNIONs. To include duplicates, use

UNION ALL in place of UNION.

select cust_id, age

from financial.customer_name
UNION ALL
select cust_id, age
from [Link]

Unions Rules• Each query must return the same number of columns.
• The columns must be in the same order.
• Column datatypes must be compatible.
• In Oracle, you can only ORDER BY columns that have the same name in every
SELECT clause in the UNION.

UNION ALL

select
customer_name.cust_id,customer_name.age, 'young'

from financial.customer_name
Where customer_name.age < 30
UNION ALL is used instead of just UNION because no duplicates need to be
removed

Functions

Absolute value --- ABS

Capitalize first letters of words --- INITCAP
Char for char translation in string --- TRANSLATE
Convert ASCII to char --- CHR
Convert binary to hex --- RAWTOHEX
Convert date to number --- TO_NUMBER(TO_CHAR(d))
Convert date to string --- TO_CHAR
Convert hex to binary --- HEXTORAW
Convert number to string --- TO_CHAR
Convert string to date --- TO_DATE
Convert string to number --- TO_NUMBER
Current date --- SYSDATE
Current user --- USER
Date addition --- ADD_MONTH or +
Date round --- ROUND
Date subtraction --- MONTHS_BETWEEN or -
Date truncate --- TRUNC
Find pattern in string --- INSTR
Find string in string --- INSTR
Formatting numbers to two decimal places --- TO_CHAR(num,'9.00')
Get substring from string --- SUBSTR
If statement in an expression --- DECODE
Last day of month --- LAST_DAY
Max or min number or string in list --- GREATEST, LEAST
Modulus --- MOD
Next specified weekday after date --- NEXT_DAY
Pad string with blanks --- LPAD, / RPAD
Power --- POWER
Replace chars in string --- REPLACE
Return NULL if two values are equal --- DECODE
Round --- ROUND
Round down to nearest integer --- FLOOR
Smallest integer >= n --- CEIL
Smallest integer >= value --- CEIL
Square root --- SQRT
String concatenation --- CONCAT(str1,str2)
String length --- LENGTH
Time zone conversion --- NEW_TIME
Translate NULL to n --- NVL
Trim leading or trailing chars other than blanks --- LTRIM(str,chars), /
RTRIM(str,chars)
Truncate number --- TRUNC
User's database id number or name --- UID, USER
User's login id number or name --- UID, USER

SQL Overview

What Is ANSI SQL?

The American National Standards Institute (ANSI) is an organization that approves

certain standards in many different industries

The Relational Database

A relational database is a database divided into logical units called tables, where
tables are related to one another within the database. A relational database
allows data to be broken down into logical, smaller, manageable units, allowing
for easier maintenance and providing more optimal database performance
according to the level of organization.

Types of SQL Commands

. Data Definition Language (DDL)

. Data Manipulation Language (DML)
. Data Query Language (DQL)
. Data Control Language (DCL)
. Data administration commands
. Transactional control commands

Data Definition Language (DDL)

CREATE TABLE
ALTER TABLE
DROP TABLE
CREATE INDEX
ALTER INDEX
DROP INDEX
CREATE VIEW
DROP VIEW

Data Manipulation Language (DML)

INSERT
UPDATE
DELETE

Data Query Language (DQL)

SELECT

Data Control Language (DCL)

ALTER PASSWORD
GRANT
REVOKE
CREATE SYNONYM

Data administration commands

START AUDIT
STOP AUDIT

Transactional control commands

. COMMIT—Saves database transactions
. ROLLBACK—Undoes database transactions
. SAVEPOINT—Creates points within groups of transactions in which to
. ROLLBACK
. SET TRANSACTION—Places a name on a transaction

SQL tuning –

The objective is to reduce the operational overhead of executing the query on the
database.

The order of tables in the FROM clause

The placement of the most restrictive conditions in the WHERE clause
The placement of join conditions in the WHERE clause

The base table is the main table that most or all tables are joined to in a query.
The column from the base table is normally placed on the right side of a join
operation in the WHERE clause. The tables being joined to the base table are
normally in order from smallest to largest, similar to the tables listed in the FROM
clause.

Other Performance Considerations

Using the LIKE operator and wildcards

Avoiding the OR operator
Avoiding the HAVING clause
Avoiding large sort operations
Using stored procedures

Order of Clause - Select / From / where / Group by / having / Order by

SQL Join

Inner join Return records that match in both Table A and Table B

SELECT
EMPLOYEE_TBL.EMP_ID,
EMPLOYEE_TBL.LAST_NAME,
EMPLOYEE_PAY_TBL.SALARY
FROM EMPLOYEE_TBL, EMPLOYEE_PAY_TBL
WHERE EMPLOYEE_TBL.EMP_ID = EMPLOYEE_PAY_TBL.EMP_ID

SELECT
CUSTOMER_NAME.CUST_ID,
CUSTOMER_NAME.INCOME,
[Link]
FROM CUSTOMER_NAME
JOIN CUSTOMER
ON ( CUSTOMER_NAME.CUST_ID = CUSTOMER.CUST_ID)
WHERE [Link] > 30"

Full outer join Return all records in Table A and Table B, with matching records
from both sides where available. If there is no match, the missing side will contain
null.
Left outer join Return all records from Table A, with the matching records (where
available) in Table B. If there is no match, the right side will contain null.

SELECT
CUSTOMER_NAME.CUST_ID,
CUSTOMER_NAME.AGE,
CUSTOMER_NAME.INCOME,
[Link],
CUSTOMER.CUST_ID
FROM [Link]
LEFT JOIN CUSTOMER_NAME ON
( CUSTOMER_NAME.CUST_ID = CUSTOMER.CUST_ID)

Cross Join "everything to everything", resulting in 4 x 4 = 16 rows, far more than

we had in the original sets.
SELECT * FROM TableA
CROSS JOIN TableB

Self Joins is used to join a table to itself, as if the table were two tables,
temporarily renaming at least one table in the SQL statement using a table alias.

SELECT A.LAST_NAME, B.LAST_NAME, A.FIRST_NAME

FROM EMPLOYEE_TBL A,
EMPLOYEE_TBL B
WHERE A.LAST_NAME = B.LAST_NAME;"

Using Table Alias - The use of table aliases means to rename a table in a particular
SQL statement.

SELECT E.EMP_ID, [Link]

FROM EMPLOYEE_TBL E,
EMPLOYEE_PAY_TBL EP
WHERE E.EMP_ID = EP.EMP_ID
AND [Link] > 20000;

Multi Table joins – Connect table one by one table 1 = table 2 and table 2 = table
3

SELECT
ACCTS.ACCT_END_DATE,CHECKING_ACCT.ACCOUNT_ACTIVE,CHECKING_TRAN.AC
CT_NBR
FROM ACCTS
JOIN CHECKING_ACCT ON (ACCTS.CUST_ID = CHECKING_ACCT.CUST_ID)
JOIN CHECKING_TRAN ON (CHECKING_ACCT.ACCT_NBR =
CHECKING_TRAN.ACCT_NBR)
WHERE FINANCIAL.CHECKING_ACCT.ACCOUNT_ACTIVE = 'Y'

Transformation Notes
Informatica Transformation

Source qualifier: - Read data from flat file and relational source. It has capability
to override this default query by changing the default settings.

Expression: - Perform row level calculation

Aggregator:- Performing aggregate calculations on group. Various group by are:

AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN.
Aggregator performance - send sorted input / Increase aggregator cache size/
Give input/output what you need in the transformation.
When you run a workflow that uses an Aggregator transformation, the
Informatica Server creates index and data caches in memory to process the
transformation.

Filter - Drop rows conditionally

Router -Splits rows conditionally – one or more condition - Multiple Targets

1. Input group - where we have ports from the source, it has input port only.
2. User defined group: here we can create n number of groups according to our
Business requirements. it has output port only.
3. Default grope: whatever the records are not matching the user defined group
automatically captured by this group and send back to the user for analysis.

When you use a Router transformation in a mapping, the Integration Service

processes the incoming data only once. When you use multiple Filter
transformations in a mapping, the Integration Service processes the incoming
data for each transformation.

You cannot modify or delete output ports or their properties.

You can connect one group to one transformation or target.
You can connect one output port in a group to multiple transformations or
targets.
You cannot connect more than one group to one target.

Sorter - Sorts Data

Sequence Generator – Generate unique ID values. IF the table has billion rows use
alternative transformation for sequence generator, get max (seq_no) +1 for the
remaining row, use exp transformation.

Rank– Filter top or bottom range of records

Update Strategy Generally we use update strategy before target. To Insert or

update or delete or reject rows coming from the source to target depending upon
some condition. The constants we use in update strategy transformation are:
DD_INSERT OR 0
DD_UPDATE OR 1
DD_DELETE OR 2
DD_REJECT OR 3

We can handle this update Strategy in Session level also Within a session. Session
– Mapping - Target – Select

Joiner: - Joins Heterogeneous source a) Two relational tables existing in separate

databases. / b) Two flat files in potentially different file systems. / c) Two Different
ODBC Sources. /d) A relational table and a Flat file source.
Homogeneous Joins can be performed within a source qualifier- writing sql

Normal Join: With a Normal join, the Informatica Server discards all rows of data
from the master and detail source that do not match, based on the condition.

Master Outer Join: A Master Outer Join keeps all 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 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 sources.

Union – Merges date from multiple pipelines into one pipeline

Custom – Call compiled code for multiple rows

Stored Procedure - Calls a database stored procedure A Stored Procedure

transformation can be used to execute PL/SQL Scripts.

External Procedure - Call compiled code for each row. External procedure is
calling a component...externally, and stored procedure is called from the
transformation. It is set of sql statements.

Look up - Look up the values from a relational table/view or a flat file.

Connected / Unconnected
Part of Mapping Data Flow / Separate from the mapping data flow
Return multiple Values / Return one value
Receive input value directly Receive input value / from the pipeline. from the
result of a:LKP expression in another transformation

use a dynamic or static cache / U can use a static cache.

Support user defined values / Does not support user defined default values

Unconnected lookup should be used when we need to call same lookup multiple
times in one mapping. For example, in a parent child relationship you need to
pass multiple child ids’s to get respective parent id's.

If u need to calculate a value for all the rows or for the maximum rows coming out
of the source then go for a connected lookup, Or, if it is not so then go for
unconnected lookup

Normalizer - Normalizes records from relational or VSAM source / COBOL.

The Normalizer transformation normalizes records from COBOL and relational
sources, allowing you to organize the data according to your own needs. A
Normalizer transformation can appear anywhere in a data flow when you
normalize a relational source. Use a Normalizer transformation instead of the
Source Qualifier transformation when you normalize a COBOL source. When you
drag a COBOL source into the Mapping Designer workspace, the Normalizer
transformation automatically appears, creating input and output ports for every
column in the source.

Transaction Control - Allows user defined commits

XML Parser - Reads XML from database table or message queue
XML Generator – writes XML to database table or message queue
XML Source Qualifier – Read from XML, message queues and applications.

Active Transformation –No. of Input records need not be same as Output

records.
1) Filter
2) Joiner
3) Aggregator
4) Rank
5) Update Strategy
6) Formalizer

Passive Transformation - No. of Input records are equal to Output records.

1) Source Qualifier
2) Expression;
3) Lookup ( It send Null Value if there is no value;
4) Sequence Generator;
5) External Stored Procedure
6) Advanced External Stored Procedure.

Ports- Input, Output, Variable, Return/Rank, Lookup and Master? Variable ports
are used to store intermediate results. Variable ports can reference input ports
and variable ports, but not output ports.

Cache

Informatica Cache

Cache -Cache is stored in the cache directory Informatica server. For Aggregator,
Joiner and Lookup transformations cache Values are stored in the cache directory.
For sorter transformation cache values stored in the temp Directory.
Dynamic lookup cache - can be used when the target table is also the lookup
table. When a dynamic cache is used, the Informatica Server updates the lookup
cache as it passes rows to the target.

Persistent cache - If the cache files need to be saved and reused, it can be used
when the lookup table does not change between session runs.

Suppose we have a lookup table with same lookup condition and return/output
ports and the lookup table is used many times in multiple mappings. Let us say a
Customer Dimension table is used in many mappings to populate the surrogate
key in the fact tables based on their source system keys. Now if we cache the
same Customer Dimension table multiple times in multiple mappings that would
definitely affect the SLA loading timeline.

In the first mapping we will create the Named Persistent Cache file by setting
three properties in the Properties tab of Lookup transformation.

Lookup cache persistent: To be checked i.e. a Named Persistent Cache will be

used.

Cache File Name Prefix: user_defined_cache_file_name i.e. the Named Persistent

cache file name that will be used in all the other mappings using the same lookup
table. Enter the prefix name only. Do not enter .idx or .dat

Re-cache from lookup source: To be checked i.e. the Named Persistent Cache file
will be rebuilt or refreshed with the current data of the lookup table.

Next in all the mappings where we want to use the same already built Named
Persistent Cache we need to set two properties in the Properties tab of Lookup
transformation

Data Profiling

Informatica Data profiling

Data profiling is the process of analyzing the source data and identifying the data
type, unique values, distinct values etc. Profiling is basically the search for pattern
in the data.
To take an example you might have a column called country name which is
coming from one source system. Before you populate it in your data warehouse
you want to identify the values in the column and standardize the values. For this
you might do data profiling. By data profiling you might find out, say US, is
represented as United States, USA, U.S.A. in your source data. Then you might
write rules to convert all the values to the desired value.

Three-Dimensional Data Profiling

• Column profiling analyzes all the values within each column or attribute and
discovers metadata and content quality problems

• Single-table structural profiling examines each attribute/column in relation to

every other attribute/column within a table, looking for dependency relationships
and uncovering functional dependencies, primary keys, and data structure quality
problems

• Cross-table structural profiling compares data between tables, determining

which attributes contain overlapping or identical sets of values, and it discovers
duplicate data across systems; foreign keys, synonyms, and homonyms; and
values corrupting data integrity

Data Quality

Informatica Data Quality

Informatica Data Quality provides robust data analysis, data cleansing, data
matching, exception handling, and reporting and monitoring capabilities so that
data quality can be managed as an enterprise-wide initiative.

Defining data quality dimensions, building data quality scorecards, collecting data
quality business rules, reviewing data quality results, and managing exceptions.
To solve common name and address data problems, Informatica Data Quality
provides address cleansing and identity matching rules, which reduce project risk
and make the product quick and easy to use.

Data Cleansing - Process of removing errors and resolving inconsistencies in

source data before loading data into targets.

Data scrubbing - Process of filtering, merging, decoding and translating the source
data to create the validation data for data warehouse.

Differences Version

Informatica Version Differences

6 to 7

union transformation
lookup on flat files
can connect workflow through designer itself

7 to 8

java transformation
sql transformation
HTML transformation
8 versions contains integration services
Web based administration
User defined functions
7x is client server architecture
8x is Server based architecture
Single console to administer power center
The Repository Service and Integration Service (as replacement for Rep Server
and Informatica Server) can be run on different computers in a network (so called
nodes), even redundantly.
It has a support for unstructured data which includes spreadsheets, email,
Microsoft Word files, presentations and .PDF documents. It provides high
availability, seamless fail over, eliminating single points of failure.
It has added performance improvements (To bump up systems performance,
Informatica has added "push down optimization" which moves data
transformation processing to the native relational database I/O engine whenever
its is most appropriate.)
PowerCenter 8 release has "Append to Target file" feature.

DTM

Informatica Data Transformation Manager - DTM

After the load manager performs validations for the session, it creates the DTM
process. The DTM process is the second process associated with the session run.
The primary purpose of the DTM process is to create and manage threads that
carry out the session tasks.
When the PowerCenter Server runs a session, the DTM performs the following
tasks:

1. Fetches session and mapping metadata from the repository.

2. Creates and expands session variables.

3. Creates the session log file.

4. Validates session code pages if data code page validation is enabled. Check
query conversions if data code page validation is disabled.

5. Verifies connection object permissions.

6. Runs pre-session shell commands.

7. Runs pre-session stored procedures and SQL.

8. Creates and runs mapping, reader, writer, and transformation threads to

extract, transform, and load data.

9. Runs post-session stored procedures and SQL.

10. Runs post-session shell commands.

11. Sends post-session email.

DTM Buffer Memory

The DTM allocates buffer memory to the session based on the DTM Buffer Size
setting in the session properties.

By default, the Integration Service determines the DTM buffer size at run time.
The Workflow Manager allocates a minimum of 12 MB for DTM buffer memory.

You can specify auto or a numeric value. If you enter 2000, the Integration Service
interprets the number as 2000 bytes. Append KB, MB, or GB to the value to
specify other units. For example, you can specify 512MB.

Increase the DTM buffer size in the following circumstances:

- A session contains large amounts of character data and you configure it to run
in Unicode mode. Increase the DTM buffer size to 24MB.
-
A session contains n partitions. Increase the DTM buffer size to at least n times
the value for the session with one partition.

Informatica recommends you allocate no more than 1 GB for DTM buffer

memory

Errror handling

Informatica Error Handling

There are 2 types of Error Data Error and Process Error

Data Error -Capture bad data into the error table’s then analysis remove the bad
data and reload.

Process Error -To handle Process errors we can configure an email task to notify
the event of a session failure.
Error on 0 in session properties, log 3 session logs, send alarm point – email to
prod support

Error handling Logic

Bad files contain column indicator and row indicator.
Row indicator: It generally happens when working with update strategy
transformation. The writer/target rejects the rows going to the target

Column indicator:
D -valid
o - overflow
n - null
t - truncate

When the data is with nulls or overflow it will be rejected to write the data to the
target
The reject data is stored on

Incremental Aggregation

Incremental Aggregation

Whenever a session is created for a mapping Aggregate Transformation, the

session option for Incremental Aggregation can be enabled. When power Center
performs incremental aggregation, it passes new source data through the
mapping and uses historical cache data to perform new aggregation calculations
incrementally.

Incremental aggregation is a technique by which we can capture the aggregated

data incrementally. For this we have to sort the data before sending it to
aggregator then we have to enable the property incremental aggregation in the
workflow level inside the session.

Load Manager
Informatica Load Manager

While running a Workflow, the PowerCenter Server uses the Load Manager
process and the Data Transformation Manager Process (DTM) to run the
workflow and carry out workflow tasks.

When the PowerCenter Server runs a workflow, the Load Manager performs the
following tasks

1. Locks the workflow and reads workflow properties

2. Reads the parameter file and expands workflow variables.
3. Creates the workflow log file.
4. Runs workflow tasks.
5. Distributes sessions to worker servers.
6. Starts the DTM to run sessions.
7. Runs sessions from master servers.
8. Sends post-session email if the DTM terminates abnormally.

Mapplet

Informatica Mapplet

Mapplet is a reusable object that is created using mapplet designer. The mapplet
contains set of transformations and it allows us to reuse that transformation logic
in multiple mappings.

Ex: Same lookup logic is used in multiple mappings.

The following should not include in a mapplet.

Normalizer transformations, COBOL sources, XML Source Qualifier
transformations, XML sources, Target definitions, Pre- and post- session stored
procedures, Other Mapplets

A mapplet should have a mapplet input transformation which recives input

values, and a output transformation which passes the final modified data to back
to the mapping.
when the mapplet is displayed with in the mapping only input & output ports are
displayed so that the internal logic is hidden from end-user point of view.

Reusable transformation

A reusable transformation is a single transformation that can be reusable.

The reusable transform ation is stored as a metadata separate from any other
mapping that uses the transformation. Whenever any changes to a reusable
transformation are made, all the mappings where the transformation is used will
be invalidated

Reusable Transformation and Mapplets

If u create a variables or parameters in maplet that cannot be used in another
mapping or maplet. Unlike the variables that are created in a reusable
transformation can be useful in any other mapping or maplet.

We cannot include source definitions in reusable transformations. But we can add

sources to a maplet.

We can’t use COBOL source qualifier, joiner, normalizer transformations in

maplet. Where as we can make them as reusable transformations.

Parameters and Variables

Informatica Parameters and Variables

Mapping Parameters: Mapping parameter represents a constant value that you

can define before running a session. A mapping parameter retains the same value
throughout the entire session.

Mapping variable: Mapping variable represents a value that can be changed

during the mapping run. Mapping variable can be used in incremental loading
process.

For define the value you can give the initial value or you can create mapping
parameter file. But in the file you should specify the folder name. Session name.
Ex: [[Link]] $$parameter1=1; Otherwise Informatica server
cannot recognize the session.

Mapping variables have two identities:

Start value and Current value

Start value = Current value ( when the session starts the execution of the
undelying mapping)

Start value <> Current value ( while the session is in progress and the variable
value changes in one ore more occasions)

Current value at the end of the session is nothing but the start value for the
subsequent run of the same session.

Local Variables: You can use local that you create within a mapping in any
transformation expression.

System variable: $$$Sess Start Time these are constant throughout the mapping
and cannot be changed.

Parameter File
Parameter file is any text file where u can define a value for the parameter
defined in the Informatica session. This parameter file can be referenced in the
session properties When the informatica sessions run the values for the
parameter is fetched from the specified file. For eg : $$ABC

For UNIX shell users, enclose the parameter file name in single quotes:
-paramfile '$PMRootDir/[Link]'

For Windows command prompt users, the parameter file name cannot have
beginning or trailing spaces. If the name includes spaces, enclose the file name in
double quotes:
-paramfile ?$PMRootDirmy [Link]
Note: When you write a pmcmd command that includes a parameter file located
on another machine, use the backslash () with the dollar sign ($). This ensures
that the machine where the variable is defined expands the server variable.

pmcmd startworkflow -uv USERNAME -pv PASSWORD -s SALES:6258 -f east -w

wSalesAvg -paramfile '$PMRootDir/[Link]'

Note File Syntex : [ Folder Name .WF:Work Flow Name. ST:Session Name]

Two $ sign for user create variables – Mapping -> Parameter and Variable

We can enter parameter file both Workflow and session properties

If you entered parameter file in the work this will applicable for all the session
included in that work flow otherwise this will be used only that session.

Pmcmd

Informatica Pmcmd

Pmcmd is command line utility to communicate with the Informatica server.

It performs the following tasks

1) Start and stop batches and sessions

2) Recovery sessions

3) Stops the Informatica server

4) Schedule the sessions by shell scripting

5) Schedule the sessions by using operating system schedule tools like CRON

Pmrep
pmrep command is used to connect to a repository and run PowerCenter
commands.

pmrep connect -r Repository name -d Domain name -n user -x passwd

pmrep run -f [Link] -s -o [Link]

The basic difference of pmcmd and pmrep are, pmcmd used for running
workflows, creating loops in running workflows, mapping related stuffs, stopping
workflows and many more.

pmrep is basically program repository used for repository management e.g. taking
backup of repository, connecting to repository, creating folders, export
cmappings/sessions/workflows into xml, importing the same etc.

Repository

Informatica Repository

The place where you store the metadata is called repository. Repository is a
relational database that stores metadata, used by the Informatica Server.

Informatica Metadata contains all the information about the source tables target
tables the transformations to perform transformations during the ETL process.

The repository also stores administrative information such as usernames and

passwords, permissions privileges, and product version.

Type of repositories can be created using Informatica Repository Manager

Standalone Repository: A repository that functions individually and this is

unrelated to any other repositories.

Global Repository: This is a centralized repository in a domain. This repository can

contain shared objects across the repositories in a domain. The objects are shared
through global shortcuts.
Local Repository: Local repository is within a domain and it’s not a global
repository. Local repository can connect to a global repository using global
shortcuts and can use objects in it’s shared folders.

Versioned Repository: This can either be local or global repository but it allows
version control for the repository. A versioned repository can store multiple
copies, or versions of an object. This features allows to efficiently develop, test
and deploy metadata in the production environment.

Repository is a relational database. You can view all metadata information in the
database Views. This is called MX Views

Tables Like
REP_ALL_MAPPINGS
REP_ALL_MAPPLETS
REP_ALL_TRANSFORMS

PowerCenter Metadata Reporter provides many standard reports to analyze the

repository. The source to target dependency report outlines for each mapping
which source fields are going into the target. Although the report is great, it
doesn’t show all source fields that have been used in the data flow. Or in other
words: all source fields that left the source qualifier. Typical examples are ID fields
that may “disappear” in active transformations like Filters, Lookups, Joiners, etc.
Therefore, I put my hands on SQL to get the wanted result.

select distinct rep_reposit_info.repository_name, rep_fld_mapping.subject_area,

rep_fld_mapping.mapping_name, rep_fld_mapping.source_name,
rep_fld_mapping.source_field_name, rep_fld_mapping.target_name,
rep_fld_mapping.target_column_name as target_field_name
from rep_fld_mapping, rep_reposit_info

union
select distinct temp4.repository_name, temp2.subject_area,
temp3.mapping_name, temp2.source_name, temp2.source_field_name, null as
target_name, null as target_field_name
from (
select distinct temp1a.mapping_id ,temp1a.widget_id, temp1a.from_field_id
from (
select rep_widget_dep.from_instance_id, rep_widget_dep.from_field_id,
rep_widget_dep.to_instance_id, rep_widget_dep.to_field_id,
rep_widget_inst.mapping_id, rep_widget_inst.widget_id
from rep_widget_inst inner join rep_widget_dep on
(rep_widget_inst.mapping_id=
rep_widget_dep.mapping_id and rep_widget_inst.instance_id=
rep_widget_dep.from_instance_id)
where rep_widget_inst.widget_type=1
) temp1a,

(
select distinct rep_widget_dep.from_instance_id, rep_widget_dep.from_field_id
from rep_widget_inst inner join rep_widget_dep on
(rep_widget_inst.mapping_id=
rep_widget_dep.mapping_id and rep_widget_inst.instance_id=
rep_widget_dep.from_instance_id)
where rep_widget_inst.widget_type=3
) temp1b

where temp1a.to_instance_id=temp1b.from_instance_id
and temp1a.to_field_id=temp1b.from_field_id
) temp1

left join rep_all_source_flds temp2 on

(temp1.from_field_id=temp2.source_field_id)
inner join rep_all_mappings temp3 on (temp1.mapping_id=temp3.mapping_id),
rep_reposit_info temp4
where temp2.source_id not in (
select rep_fld_mapping.source_id
from rep_fld_mapping
)
Note: SQL statement has been developed and tested with PowerCenter 7.1.2
repository (on Oracle). I recommend executing the query in PowerCenter
Metadata Reporter (runs on PowerAnalyzer). Depending on the amount of
objects in your repository, you are doing well in defining a filter (e.g. a specific
folder) since the query is designed to run over the whole repository.

Version

Informatica Version

Version control means, if you want to modify the mapping, we can use this
concept and in this the version numbers are like 1,2 etc, but not like 1.1, 1.2. in
this we have this following function like check in, check out, undo check out, view
history, delete, recovery, purge.

Power center versioning is a repository option. When repository is createing we

can enabling this option and after created repository also we can enable it, once
enable this option we can not do disable again.

Workflow Manager

Informatica Workflow Manager

How to execute tasks such as sessions emails and shell commands.

Workflow tasks

Workflow tasks perform functions of extracting, transforming, and loading data.

Workflow tasks include commands, decisions, timers, and email notification

Worklets

Worklet is reusable workflows. This has been used in many workflows

Worklet is a set of tasks. If a certain set of task has to be reused in many
workflows then we use worklets. To execute a Worklet, it has to be placed inside
a workflow.
Task Developer - It can be re used in any workflow. Good practice use Task
Developer work instead of workflow manager

Work flow manager / Task developer = Called generally session manager.

Informatica Notes

Informatica Notes

Test Plan and Validation – In Informatica we create some test SQL to compare
the number or records and validate scripts if the data in the warehouse is loaded
for the logic incorporated.

Cached lookup - For a cached lookup the entire rows (lookup table) will be put in
the buffer, and compare these rows with the incomming rows.

Uncached lookup - for every input row the lookup will query the lookup table and
get the rows.

Can we create index and drop index in existing table while using informatica

4 ways in INFORMATICA

1) Source Analyzer window- (source table, Using key ports (enable, disable)).
2) Source qualifier Trans- (Sql override)
3) Target override
4) Pre sql, Post sql

What is the method of loading 5 flat files of having same structure to a single
target and which transformations I can use?

1. Write all file paths of five files in one file and use this file in session properties
as indirect.
2. Union Transformation

SCD
[Link]-type1:-it maintains current(updated) data.
[Link]-type2:-it maintains historical data.
[Link]-type3:-it maintains present n previous(partial history) data.

When we create a target as flat file and source as oracle.. how can i specify first
rows as column names in flat files...

use a pre sql statement....but this is a hardcoding method...if you change the
column names or put in extra columns in the flat file, you will have to change the
insert statement
You can also achive this by changing the setting in the Informatica Repository
manager to display the columns heading. The only disadvantage of this is that it
will be applied on all the files that will be generated by This server

Pros: Loading, Sorting, Merging operations will be faster as there is no index

concept and Data will be in ASCII mode.
Cons: There is no concept of updating existing records in flat file.
As there is no indexes, while lookups speed will be lesser.

How the informatica server increases the session performance through

For a relational sources informatica server creates multiple connections for each
parttion of a single source and extracts seperate range of data for each
[Link] server reads multiple partitions of a single source
[Link] for loading also informatica server creates multiple
connections to the target and loads partitions of data concurently.

For XML and file sources,informatica server reads multiple files [Link]
loading the data informatica server creates a seperate file for each partition(of a
source file).U can choose to merge the targets.

Why we use partitioning the session in informatica?

Performance can be improved by processing data in parallel in a single session by
creating multiple partitions of the pipeline.

Informatica server can achieve high performance by partitioning the pipleline and
performing the extract , transformation, and load for each partition in parallel.
We can use the following Mapping for slowly Changing dimension table.

? Expression
? Lookup
? Filter
? Sequence Generator
? Update Strategy

To set the target load order:

[Link] a mapping that contains multiple target load order groups.
[Link] Mappings > Target Load Plan.
The Target Load Plan dialog box lists all Source Qualifier transformations in the
mapping and the targets that receive data from each source qualifier.
[Link] a source qualifier from the list.
[Link] the Up and Down buttons to move the source qualifier within the load
order.
[Link] steps 3 to 4 for other source qualifiers you want to reorder.
[Link] OK.

Delta Load is the process to load only changes in the source table(s) to the
Warehouse database,ie new, changed (& deleted) rows.

Transformation Connected
Lookup Tips
1) If lookups contain a good deal of data (meaning that it would take far too long
to cache and/or will not fit in memory), moving the fields to the source qualifier,
and joining with the main table should be considered. The ONLY exception to this
should be if when much data is not processed through the lookup. In such a case
using an uncached lookup may be considered.
2) If the cache files need to be saved and reused, the transformation can be
configured to use a persistent cache. A persistent cache can be used when the
lookup table does not change between session runs. The first time the
Informatica Server runs a session using a persistent lookup cache, it saves the
cache files to disk instead of deleting them. The next time the Informatica Server
runs the session, it builds the memory cache from the cache files. If the lookup
table changes occasionally, the session properties need to be overridden to re-
cache the lookup from the database.
3) Speaking of initial loading overhead, all data read into a cache is read in by the
order of the fields listed in the lookup ports. If there is an index that is even
partially in this order, the loading of these lookups can be sped up substantially if
there is a large amount of data. Also, as many fields as are used in the mapping
need to be kept. Deleting unused columns can save a substantial amount of time.
4) If a lookup is used several times in a mapping, and has not been changed, it
will, by default, keep the cache built earlier for subsequent loads in the mapping.
If this feature is not needed (for example, the first transform may have updated
the data, and now the second uses it in a lookup), ‘Recache from Database’ option
needs to be checked.
5) Dynamic lookup cache can be used when the target table is also the lookup
table. When a dynamic cache is used, the Informatica Server updates the lookup
cache as it passes rows to the target. The Informatica Server builds the cache
when it processes the first lookup request. It queries the cache based on the
lookup condition for each row that passes into the transformation. When the
Informatica Server reads a row from the source, it updates the lookup cache by
either Inserting the row into the cache or by Updating the row in the cache or by
Making no change to the cache.
6) If the lookup table is on the same database as the source table in the mapping
and there is a lot of data which is not feasible to cache, the tables can be joined in
the source database rather than using a Lookup transformation.
7) The Informatica Server generates an ORDER BY statement for a cached lookup
by default that contains all lookup ports. To increase performance, the default
ORDER BY statement can be suppressed and an override ORDER BY with fewer
columns can be written.
8) If a Lookup transformation specifies several conditions, the lookup
performance can be improved by placing all the conditions that use the equality
operator first in the list of conditions that appear under the Condition tab.

Transformation Unconnected
Tips

1 Tips In Informatica
1.1 LOOKUP TIPS
1.2 JOINER TIPS
1.3 FILTER TIPS
1.4 AGGREGATOR TIPS
1.5 SORTER TIPS
1.6 UPDATE STRATEGY TIPS
1.7 SEQUENCE GENERATOR TIPS
1.8 STORED PROCEDURE TIPS
1.9 SHORTCUT TIPS
1.10 MAPPLET TIPS
1.11 ROUTER TIPS
1.12 GENERIC TIPS
2 Transformations In Informatica
2.1 TRANSACTION CONTROL TRANSFORMATION
2.2 STORED PROCEDURE TRANSFORMATION
2.3 RANK TRANSFORMATION

1.1 Lookup Tips

1) If lookups contain a good deal of data (meaning that it would take far too long
to cache and/or will not fit in memory), moving the fields to the source qualifier,
and joining with the main table should be considered. The ONLY exception to this
should be if when much data is not processed through the lookup. In such a case
using an uncached lookup may be considered.
2) If the cache files need to be saved and reused, the transformation can be
configured to use a persistent cache. A persistent cache can be used when the
lookup table does not change between session runs. The first time the
Informatica Server runs a session using a persistent lookup cache, it saves the
cache files to disk instead of deleting them. The next time the Informatica Server
runs the session, it builds the memory cache from the cache files. If the lookup
table changes occasionally, the session properties need to be overridden to re-
cache the lookup from the database.
3) Speaking of initial loading overhead, all data read into a cache is read in by the
order of the fields listed in the lookup ports. If there is an index that is even
partially in this order, the loading of these lookups can be sped up substantially if
there is a large amount of data. Also, as many fields as are used in the mapping
need to be kept. Deleting unused columns can save a substantial amount of time.
4) If a lookup is used several times in a mapping, and has not been changed, it
will, by default, keep the cache built earlier for subsequent loads in the mapping.
If this feature is not needed (for example, the first transform may have updated
the data, and now the second uses it in a lookup), ‘Recache from Database’ option
needs to be checked.
5) Dynamic lookup cache can be used when the target table is also the lookup
table. When a dynamic cache is used, the Informatica Server updates the lookup
cache as it passes rows to the target. The Informatica Server builds the cache
when it processes the first lookup request. It queries the cache based on the
lookup condition for each row that passes into the transformation. When the
Informatica Server reads a row from the source, it updates the lookup cache by
either Inserting the row into the cache or by Updating the row in the cache or by
Making no change to the cache.
6) If the lookup table is on the same database as the source table in the mapping
and there is a lot of data which is not feasible to cache, the tables can be joined in
the source database rather than using a Lookup transformation.
7) The Informatica Server generates an ORDER BY statement for a cached lookup
by default that contains all lookup ports. To increase performance, the default
ORDER BY statement can be suppressed and an override ORDER BY with fewer
columns can be written.
8) If a Lookup transformation specifies several conditions, the lookup
performance can be improved by placing all the conditions that use the equality
operator first in the list of conditions that appear under the Condition tab.

1.2 Joiner Tips

1) A joiner transformation can be implemented only when both input pipelines

begin with the different data source and when both input pipelines originate from
different Source Qualifier transformation and when both input pipelines originate
from different Normalizer transformation and when both input pipelines originate
from the different Joiner transformation and when both input pipelines do not
contain an Update Strategy transformation and when both input pipelines do not
contain a connected or unconnected Sequence Generator transformation.
2) Performing a joiner in database is much faster than performing a join in the
mapping. Performing a database join may not be possible when the sources are
flat files or when data is coming from different databases. In all other cases,
database join can be implemented by the following ways depending on the
requirement.
• Writing all the join conditions in the Source Qualifier. This can be done
simultaneously on more than two input tables.
• Creating a pre-session stored procedure to join the tables in a database.
3) Always make the source which is expected to get less number of records as the
master source. This will reduce the size of the data cache thus reducing the time
taken for fetching the data.

1.3 Filter Tips

1) In order to maximize session performance, whenever filter transformation is

being used, care should be taken such that it lies as close as possible to the
sources in the mapping. Rather than processing the rows that need to be
discarded through the mapping it is recommended to filter unwanted data early
in the flow from sources to targets.
2) The Source Qualifier transformation can be used as an alternative to filter
transformation. Rather than filtering rows from within a mapping, the Source
Qualifier transformation can be used to filter rows when read from a source. The
main difference is that the source qualifier limits the row set extracted from a
source, while the Filter transformation limits the row set sent to a target. Since a
source qualifier reduces the number of rows used throughout the mapping, it
provides better performance. However, the source qualifier can only be used to
filter rows from relational sources, while the Filter transformation filters rows
from any type of source. Also, standard SQL queries need to used in Source
Qualifier since it runs in the database. The Filter transformation can define a
condition using any statement or transformation function that returns either a
TRUE or FALSE value.
3) Using complex expressions should be avoided since using simple integer or
true/false expressions in the filter condition will highly optimize the Filter
transformation’s performance.
4) A Filter transformation can be used to drop rejected rows from an Update
Strategy transformation if it is not needed to keep rejected rows.

1.4 Aggregator Tips

1) While using Aggregator transformations the group columns should be as simple

as possible. Numbers should be used as GROUP BY ports instead of string and
dates ports if possible since this will increase the performance. Also using
complex expressions in the Aggregator expressions should be avoided.
2) Aggregator expressions performance can be improved by using sorting data as
input and using the Aggregator Sorted Input option on. The Sorted Input
decreases the use of aggregate caches since the Informatica Server assumes all
data is sorted by group. As the Informatica Server reads rows for a group, it
performs aggregate calculations and stores group information in memory. The
Sorted Input option reduces the amount of data cached during the session and
improves performance. The order by option can be specified in Source Qualifier
passing the sorted data to the Aggregator transformation.
3) If changes from the source that changes less than half the target can be
captured, Incremental Aggregation can be used to optimize the performance of
Aggregator transformations. When using incremental aggregation, captured
changes in the source can be applied to aggregate calculations in a session. The
Informatica Server updates target incrementally, rather than processing the entire
source and recalculate the same calculations every time the session is run.
4) The number of connected input/output or output ports should be as less as
possible in order to reduce the amount of data the Aggregator transformation
stores in the data cache.
5) If a Filter transformation is being used in the mapping, the same needs to be
placed before the Aggregator transformation to reduce unnecessary aggregation.
6) Aggregate Transformation object can be replaced with an Expression
Transformation object and an Update Strategy Transformation for certain types of
Aggregations.

1.5 Sorter Tips

1) The Sorter transformation should be avoided whenever possible by

implementing the same in Source qualifier transformation using GROUP BY
clause.
2) The Sorter transformation can be configured for performing duplicate removal,
by selecting the Distinct Output Rows option the Mapping Designer automatically
configures all ports as part of the sort key alongside discarding duplicate rows
compared during the sort operation.
3) The Sorter Cache Size property is used to indicate the maximum amount of
memory that is to be allocated to perform the sort operation which can be set to
any value between one megabyte and four gigabytes. The Informatica Server
need to pass all the incoming data into the Sorter transformation before
performing the sort operation and will fail if it cannot allocate enough memory
for the sort operation. Informatica recommends allocating at least 8,000,000
bytes of physical memory to sort data using the Sorter transformation which is
also the default size. If the amount of incoming data is greater than the amount of
Sorter cache size, the data is temporarily stored in the Sorter transformation work
directory. The Informatica Server requires disk space of at least twice the amount
of incoming data when storing data in the work directory. If the amount of
incoming data is significantly greater than the Sorter cache size, the Informatica
Server may require much more than twice the amount of disk space available to
the work directory.

1.6 Update Strategy Tips

1) Rejected rows from an Update Strategy are logged to the Bad File. Filtering of
these rows should be considered if retaining these rows is not critical because
logging causes extra overhead on the engine.
2) When all the input data needs to be inserted into a target table, Insert option
should be selected for the Treat Source Rows As session property. Also, Insert
option should be selected for all target instances in the session.
3) When all the input data needs to be deleted, Delete option should be selected
for the Treat Source Rows As session property. Also, Delete option should be
selected for all target instances in the session.
4) When the input data needs to be updated on the contents of a target table,
Update option should be selected for the Treat Source Rows As session property.
Also, Update option should be selected for each target instance.
5) When different database operations need to be performed with different rows
destined for the same target table, either the DECODE or IIF function can be used
to flag rows for different operations (insert, delete, update, or reject). Also, Data
Driven should be selected for the Treat Source Rows As session property. Insert,
Delete, or one of the Update options should be selected for each target table
instance.
6) When the input data needs to be rejected, DECODE or IIF function can be used
to specify the criteria for rejecting the row. Also, Data Driven option should be
selected for the Treat Source Rows As session property.

1.7 Sequence Generator Tips

1) Whenever ports CURRVAL and NEXTVAL are connected to an output

transformation, CURRVAL gets the NEXTVAL value plus one value. If NEXTVAL is
not used and CURRVAL alone is connected to a target, CURRVAL with fetch the
same constant value for each output record.
2) An update object hooked to a sequence number generator should generally go
to one target table. If it is attached to two tables, no matter what objects
intervene, each table will have unique numbers. If a different table should have
the same sequence number, one table should be read to get the number for the
other tables.
3) If there is more than one Informatica mappings which write to the same table
(where a sequential value is needed), they should use the sequence number
generator as a reusable object or a shortcut. If there are non-Informatica routines
which write to the same table, using a trigger or some database method should
be considered. (Such as the Transact-SQL identity option).

1.8 Stored Procedure Tips

1) Each time a stored procedure runs during a mapping, the session must wait for
the stored procedure to complete in the database. In order to reduce the time
taken for this, the input count of rows sent to Stored procedure should be
reduced. For this an active transformation can be placed prior to the Stored
Procedure transformation to reduce the number of rows that must be passed to
the stored procedure or else an expression that tests the values before passing
them to the stored procedure can be created to make sure that the value does
not really need to be passed.
2) Instead of calculating complex logics in Stored procedure, an expression
transformation can be created and most of the logic used in stored procedures
can be easily replicated using expressions in the Designer.
3) Whenever changes are made to the Stored procedure in the database or if the
input/output parameters or return value is changed in a stored procedure, the
Stored Procedure transformation becomes invalid. In such cases the stored
procedure definition should either be imported again or should be configured
manually by adding, removing, or modifying the appropriate ports.
4) Whenever a stored procedure runs, it issues a status code that notifies whether
or not the stored procedure completed successfully. This code is used by the
Informatica Server to determine whether to continue running the session or stop.
The Workflow Manager can be configured to continue or stop the session in the
event of a stored procedure error.

1.9 Shortcut Tips

1) Shared objects should always be kept in centralized folders since this will make
the maintenance simple apart from simplifying the process of copying folders into
a production repository.
2) Shortcuts need to be created only when it is a finalized object since making
changes to an object referenced by shortcuts can invalidate the mappings or
mapplets using the shortcut, as well as any sessions using these objects. To avoid
invalidating repository objects, only create shortcuts objects in their finalized
version.
3) Whenever a referenced shortcut object is changed, care should be taken to
check whether the change has affected the associated mappings or not. If any
object referenced by a shortcut needs to be changed, the Analyze Dependencies
features in the Repository Manager can be used to view affected mappings. After
editing the object, all the associated mappings need to be opened and validated
again.
4) When changes are made to shortcuts where more than one user is working on
the same environment, changes made by one user will not be reflected in the
other user’s workspace until the workspace is refreshed by either choosing Edit-
Revert To Saved option or by closing down the workspace and then reopening it.

1.10 Mapplet Tips

1) A mapplet should be created when there is need to use a standardized set of

transformation logic in several mappings. For example, if there are several fact
tables that require a series of dimension keys, a mapplet can be created
containing a series of Lookup transformations to find each dimension key. The
mapplet can then be used in each fact table mapping, rather than recreating the
same lookup logic in each mapping.
2) Whenever changes are being done to a mapplet, care should be taken to avoid
changing the following since all these will make the mappings using this mapplet
invalid.
• Connected ports in an Input or Output transformation should not be deleted.
• The data type, precision, or scale of connected ports in an Input or Output
transformation should not be changed.
• A passive mapplet should not be changed to an active mapplet or an active
mapplet should not be changed to a passive mapplet.
3) If a reusable Sequence Generator transformation is included in a mapplet and
this mapplet in used in several mappings and each mapping expends a large
number of values in a session, cache size need to be configured for a reusable
Sequence Generator to limit the number of unused values.
4) Whenever there is a need to create mapplets from existing mappings since it
can be re-used at several other mappings, all the needed transformation objects
can be copied from the mapping into the Mapplet Designer instead of re-creating
them again.
5) A mapplet can be used to create an Output transformation for each needed
output group. Data can be passed from each mapplet output group to a different
mapping pipeline. An Output transformation can be created for each needed
output group.
6) Mapplets should not be reused if one or two transformations of the mapplet
are needed while all other calculated ports and transformations are obsolete.

1.11 Router Tips

1) A Router Transformation should be used to separate data flows instead of

multiple Filter Transformations whenever possible.
2) The Informatica Server uses the group condition to evaluate each row of
incoming data and determines the order of evaluation for each condition based
on the order of the connected output groups. The Informatica Server processes
user-defined groups that are connected to a transformation or a target in a
mapping first. The Informatica Server only processes user-defined groups that are
not connected in a mapping if the default group is connected to a transformation
or a target. If a row meets more than one group filter condition, the Informatica
Server passes this row multiple times.

1.12 Generic Tips

1) Debugging of a mapping will not start sometimes showing the following

message à WSADDRESSINUSE and debugging will not start. This can be fixed by
changing the port number to be used by informatica debugger in Designer client
that is available at Tools -> Options -> Debug, Port to any value between 5001 and
32000.

2) By default, the Informatica Server uses the format string in the following
format MM/DD/YYYY HH24:MI:SS and this is not case-sensitive. So all the
date/time fields and string fields storing date values should be taken in
MM/DD/YYYY format in order to avoid wrong conversion of dates during internal
calculations.

3) Whenever session variables are being parameterized, the parameterized

names should all be named as either $OutputFile_ or $InputFile_ since the session
will not be able to recognize parameter names if they have a different naming
standard.

4) Calculating or testing the same value over and over should be avoided. The
needed value should be calculated once in an expression, and by setting a
True/False flag can be used several times. Only needed ports to be connected. All
unnecessary links between transformations should be deleted to minimize the
amount of data moved, particularly in the Source Qualifier.

5) When DTM bottlenecks are identified and session optimization has not helped,
tracing levels should be used to identify which transformation is causing the
bottleneck (by using the Test Load option in session properties).

6) Operations and Expression Optimizing Tips

• Numeric operations are faster than string operations.
• Optimize char-varchar comparisons (i.e., trim spaces before comparing).
• Operators are faster than functions (i.e., || vs. CONCAT).
• Optimize IIF expressions.
• Avoid date comparisons in lookup; replace with string.
• Test expression timing by replacing with constant

7) Use Flat Files

• Using flat files located on the server machine loads faster than a database
located in the server machine
• Fixed-width files are faster to load than delimited files because delimited files
require extra parsing
• If processing intricate transformations, consider loading first to a source flat file
into a relational database, which allows the PowerCenter mappings to access the
data in an optimized fashion by using filters and custom SQL Selects where
appropriate

8) If an update override is necessary in a load, using a lookup transformation just

in front of the target to retrieve the primary key should be considered. The
primary key update will be much faster than the non-indexed lookup override.

9) Utilize single-pass reads.

• Single-pass reading is the server’s ability to use one Source Qualifier to populate
multiple targets.
• For any additional Source Qualifier, the server reads this source. If you have
different Source Qualifiers for the same source (e.g., one for delete and one for
update/insert), the server reads the source for each Source Qualifier.
• Remove or reduce field-level stored procedures.
• If field-level stored procedures are used, PowerMart has to make a call to that
stored procedure for every row so performance will be slow.

2. Transformations In Informatica

2.1 Transaction Control Transformation

The Transaction Control transformation is used to define conditions to commit
and rollback transactions from relational and dynamic MQSeries targets. These
parameters need to defined in a transaction control expression on the Properties
tab. A transaction is the row or set of rows bound by commit or rollback rows. The
number of rows may vary for each transaction.

Properties
While configuring a Transaction Control transformation, the following
components need to be defined:
• Transformation tab. The transformation can be renamed and a description can
be added on the Transformation tab.
• Ports tab. Only input/output ports can be added to a Transaction Control
transformation.
• Properties tab. The transaction control expression can be defined to flag
transactions for commit, rollback, or no action.
• Metadata Extensions tab. The metadata stored in the repository by associating
information with the Transaction Control transformation can be extended.

Available Options
The following built-in variables can be used in the Expression Editor when a
transaction control expression is created:
• TC_CONTINUE_TRANSACTION. The Informatica Server does not perform any
transaction change for this row. This is the default value of the expression.
• TC_COMMIT_BEFORE. The Informatica Server commits the transaction, begins a
new transaction, and writes the current row to the target. The current row is in
the new transaction.
• TC_COMMIT_AFTER. The Informatica Server writes the current row to the
target, commits the transaction, and begins a new transaction. The current row is
in the committed transaction.
• TC_ROLLBACK_BEFORE. The Informatica Server rolls back the current
transaction, begins a new transaction, and writes the current row to the target.
The current row is in the new transaction.
• TC_ROLLBACK_AFTER. The Informatica Server writes the current row to the
target, rolls back the transaction, and begins a new transaction. The current row is
in the rolled back transaction.

Things to note
1) A Transaction Control is used to commit the data written to the output as per
our requirement.
2) The Informatica Server rolls back transactions in the following circumstances:
• Rollback evaluation. The transaction control expression returns a rollback value.
• Open transaction. You choose to roll back at the end of file.
• Roll back on error. You choose to roll back commit transactions if the
Informatica Server encounters a non-fatal error.
• Roll back on failed commit. If any target connection group in a target connection
unit fails to commit, the Informatica Server rolls back all uncommitted data to the
last successful commit point.
3) In order to overcome the above rollback, Transaction control can be set up
such that data is available even after any of the above situation arises.
4) A Transaction control will become ineffective if it is used before an active
transformation.
5) When there are more than one target in a mapping and when all of these
targets are not connected to an effective Transaction control then the mapping
will become invalid.

2.2 Stored Procedure Transformation

A Stored Procedure transformation is used for populating and maintaining

databases. A stored procedure is used to automate time-consuming tasks that are
too complicated for standard SQL statements. A stored procedure consists of a
pre-defined set of SQL statements and is equal to running an executable script.
Using Stored procedure, user-defined variables, conditional statements, and
other powerful programming features can be implemented.

The following properties need to set while creating a stored procedure.

• Stored Procedure Name. The name of the stored procedure in the database
which is used to call the stored procedure if the name of the transformation is
different than the actual stored procedure name in the database. This field can be
blank if the transformation name matches the stored procedure name. When
using the Import Stored Procedure feature, this name matches the stored
procedure automatically.
• Connection Information. used to specify the database containing the stored
procedure. The database location either be hard coded or else parameters
$Source or $Target can be used.
• Call Text. This text is used to call the stored procedure and can be specified only
when the Stored Procedure Type is not Normal. Appropriate input parameters
can be passed to the stored procedure within the call text
• Stored Procedure Type. Used to indicate to the Informatica Server when to call
the stored procedure. The options include Normal (during the mapping) or pre- or
post-load on the source or target database.
• Execution Order. used to indicate the order in which the Informatica Server calls
the stored procedure relative to any other stored procedures in the same
mapping. Used only when the Stored Procedure Type is set to anything except
Normal and more than one stored procedure exists in the same mapping.

Things to note
1) A stored procedures transformation is used to: Check the status of a target
database before loading data into it or to Determine if enough space exists in a
database or to Perform a specialized calculation or to Drop and recreate indexes.
2) The stored procedure must exist in the database before creating a Stored
Procedure transformation.
3) A stored procedure can be used to perform a query or calculation that you
would otherwise make part of a mapping.
4) The Stored Procedure transformation can be set up in one of two modes, either
connected or unconnected.
5) A stored procedure can be imported from the database, by which the Designer
creates ports based on the stored procedure input and output parameters.

2.3 Rank Transformation

The Rank transformation allows selecting only the top or bottoming rank of data.
Rank transformation can be used to return the largest or smallest numeric value
in a port or group. Rank transformation can also be used to return the strings at
the top or the bottom of a session sort order.
The Rank transformation differs from the transformation functions MAX and MIN,
in that it allows selecting a group of top or bottom values, not just one value.

Properties
While creating a Rank transformation, the following properties need to be
configured:
• Cache directory: Used to store data temporarily while sorting
• Top/Bottom: Used to indicate whether to select top or bottom ranked data.
• Number of Ranks: Used to indicate the number of ranked records to be fetched.
• The input/output port that contains values used to determine the rank need to
be selected along with selecting one port to define a rank.
• Groups can be specified for ranks if data needs to be ranked based on each
group.

Things to note
1) A port RANKINDEX will be created for each Rank transformation which is used
by the Informatica Server to Rank Index port in order to store the ranking position
for each row in a group.
2) Local variables can be created inside a rank transformation and non-aggregate
expressions can be used.
3) Rank transformation changes the number of output rows in two different ways
à By filtering all but the rows falling within a top or bottom rank or by defining
groups which create one set of ranked rows for each group.
4) If two rank values match, they receive the same value in the rank index and the
transformation skips the next value.
Image here rnk_mapping.bmp

Transformation Notes

Informatica Transformation

Source qualifier: - Read data from flat file and relational source. It has capability
to override this default query by changing the default settings.

Expression: - Perform row level calculation

Aggregator:- Performing aggregate calculations on group. Various group by are:

AVG, COUNT, FIRST, LAST, MAX, MEDIAN, MIN.
Aggregator performance - send sorted input / Increase aggregator cache size/
Give input/output what you need in the transformation.
When you run a workflow that uses an Aggregator transformation, the
Informatica Server creates index and data caches in memory to process the
transformation.

Filter - Drop rows conditionally

Router -Splits rows conditionally – one or more condition - Multiple Targets

1. Input group - where we have ports from the source, it has input port only.
2. User defined group: here we can create n number of groups according to our
Business requirements. it has output port only.
3. Default grope: whatever the records are not matching the user defined group
automatically captured by this group and send back to the user for analysis.

When you use a Router transformation in a mapping, the Integration Service

processes the incoming data only once. When you use multiple Filter
transformations in a mapping, the Integration Service processes the incoming
data for each transformation.

You cannot modify or delete output ports or their properties.

You can connect one group to one transformation or target.
You can connect one output port in a group to multiple transformations or
targets.
You cannot connect more than one group to one target.

Sorter - Sorts Data

Sequence Generator – Generate unique ID values. IF the table has billion rows use
alternative transformation for sequence generator, get max (seq_no) +1 for the
remaining row, use exp transformation.

Rank– Filter top or bottom range of records

Update Strategy Generally we use update strategy before target. To Insert or

update or delete or reject rows coming from the source to target depending upon
some condition. The constants we use in update strategy transformation are:
DD_INSERT OR 0
DD_UPDATE OR 1
DD_DELETE OR 2
DD_REJECT OR 3

We can handle this update Strategy in Session level also Within a session. Session
– Mapping - Target – Select

Joiner: - Joins Heterogeneous source a) Two relational tables existing in separate

databases. / b) Two flat files in potentially different file systems. / c) Two Different
ODBC Sources. /d) A relational table and a Flat file source.
Homogeneous Joins can be performed within a source qualifier- writing sql

Normal Join: With a Normal join, the Informatica Server discards all rows of data
from the master and detail source that do not match, based on the condition.

Master Outer Join: A Master Outer Join keeps all 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 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 sources.

Union – Merges date from multiple pipelines into one pipeline

Custom – Call compiled code for multiple rows

Stored Procedure - Calls a database stored procedure A Stored Procedure

transformation can be used to execute PL/SQL Scripts.

External Procedure - Call compiled code for each row. External procedure is
calling a component...externally, and stored procedure is called from the
transformation. It is set of sql statements.

Look up - Look up the values from a relational table/view or a flat file.

Connected / Unconnected
Part of Mapping Data Flow / Separate from the mapping data flow
Return multiple Values / Return one value
Receive input value directly Receive input value / from the pipeline. from the
result of a:LKP expression in another transformation

use a dynamic or static cache / U can use a static cache.

Support user defined values / Does not support user defined default values

Unconnected lookup should be used when we need to call same lookup multiple
times in one mapping. For example, in a parent child relationship you need to
pass multiple child ids’s to get respective parent id's.

If u need to calculate a value for all the rows or for the maximum rows coming out
of the source then go for a connected lookup, Or, if it is not so then go for
unconnected lookup

Normalizer - Normalizes records from relational or VSAM source / COBOL.

The Normalizer transformation normalizes records from COBOL and relational
sources, allowing you to organize the data according to your own needs. A
Normalizer transformation can appear anywhere in a data flow when you
normalize a relational source. Use a Normalizer transformation instead of the
Source Qualifier transformation when you normalize a COBOL source. When you
drag a COBOL source into the Mapping Designer workspace, the Normalizer
transformation automatically appears, creating input and output ports for every
column in the source.

Transaction Control - Allows user defined commits

XML Parser - Reads XML from database table or message queue
XML Generator – writes XML to database table or message queue
XML Source Qualifier – Read from XML, message queues and applications.

Active Transformation –No. of Input records need not be same as Output

records.
1) Filter
2) Joiner
3) Aggregator
4) Rank
5) Update Strategy
6) Formalizer

Passive Transformation - No. of Input records are equal to Output records.

1) Source Qualifier
2) Expression;
3) Lookup ( It send Null Value if there is no value;
4) Sequence Generator;
5) External Stored Procedure
6) Advanced External Stored Procedure.

Ports- Input, Output, Variable, Return/Rank, Lookup and Master? Variable ports
are used to store intermediate results. Variable ports can reference input ports
and variable ports, but not output ports.

Staging & Operational Data Store

Staging Area and Operational Data Store

Staging area is also called 'Operational Data Store.

It is the work place where raw data is brought in, cleaned, combined, archived and exported to
one or more data marts. The purpose of data staging area is to get data ready for loading into a
presentation layer.

Slowly Changing Dimensions -SCD

Slowly Changing Dimensions

SCD stands for slowly changing dimensions. Slowly changing dimensions are of three types

Type 1: only maintained updated values.

Ex: a customer address modified we update existing record with new address.

Type 2: maintaining historical information and current information by using

A) Effective Date
B) Versions
C) Flags
Or combination of these

Type 3: by adding new columns to target table we maintain historical information and current
information

Dimension Fact Table

Dimension Table is master tables. It is a descriptive data about the facts (business).
Dimension tables are usually smaller and hold descriptive data that reflects the dimensions, or
attributes, of a business.

Fact table A pure fact table is collection of foreign keys. A fact table contains measurable or
factual data about an organization.

Fact tables contain the factual data about a business can consist of many columns and millions or
billions of rows

Aggregate tables are pre-stored summarized tables. Usage of Aggregates can increase the
performance of Queries by several times.
Lookup table is the one which is used when updating a warehouse. It just updates the table by
allowing only new records or updated records based on the lookup condition.

SDLC

Analysis:
Analysis Source and Target.
Source – It could be External or Internal – Study Data (person, financial), Structure and
Frequency
Target – Ours or ftp –what is going to add or modified.
Find out natural and sour gate keys

Design -
How to extract / pull the data from source
How to load / insert /update delete target side
Incremental logic
What is the flow between tables?
Sequence of load of tables
Sequence of jobs

Development
Data mapping source to target
Transformation logic involved
Start real informatica mapping
Review code / JAD
Test with cases – Sql comments
Re-development / fix bugs

Implementation
How to deploy from Dev / Test / Prod
Follow SDLC process
Bug Fixing
Get Data certification from users

Data Profiling:
Profile data of the source
If target table needs to profiled bring it as source definition
It create the mapping

Data Validation
Source – Analysis
Target – Auditing the level
Taken certifying by users

Schema

Schema

Schema is a Collection of Database object of a user that includes - Tables, Views, Synonyms,
Sequences, Clusters, Triggers, Procedures, and Packages.
The snowflake and star schema are methods of storing data which are multidimensional in nature

Star Schema is the collection of fact and dimensions where one fact is surrounded by several
dimension. This is the best way to get result by queries.

Snowflake schema -Collection of Star Schema. One dimension table will be connected to
another dimension table and so on.

Snowflake Schema, each dimension has a primary dimension table, to which one or more
additional dimensions can join.

The primary dimension table is the only table that can join to the fact table
Differences between star and snowflake schemas?

Star schema - all dimensions will be linked directly with a fact table.
Snow schema - dimensions maybe interlinked or may have one-to-many relationship with other
tables.

BUS Schema is composed of a master suite of confirmed dimension and standardized definition
if facts.

View

View is set of sql statements together which join single or multiple tables and shows the data.
Views do not contain data.

Materialized view .These views contain the data itself. Reason being it is easier/faster to access
the data. Mainly used in Data warehousing.

The main purpose of Materialized view is to do calculations and display data from multiple
tables using joins.
Teradata – Loaders

Teradata – Loaders
The Teradata load utilities are designed to load massive amounts of data in a short amount of
time. Loading using ODBC should be considered for very small tables only.

Fastload:
FastLoad inserts large volumes of data very rapidly into Teradata tables.
It can load one table from multiple input files.
Table being loaded must be empty.

Multiload:
Multiload supports insert, update, delete, and upsert operations for up to five target tables.
It can apply conditional logic to determine what updates to apply.
Its speed approaches that of Fastload. Multiload is limited to one input file.

Tpump:
Tpump is generally used for low volume maintenance of large tables, and/or near real-time
maintenance.
It does row-at-a-time processing using SQL
It is slower than Fastload and Multiload.
Tpump does not support multiple input files.

We run the script ( Fastload / Multiload / Tpump) thru Informatica sessions and edit if there is
any reserve word like month, type, cycle - wrap up with double quotes thru VI editor

OLTP / OLAP

OLTP

On Line Transaction Processing which contains a normalized tables and online data which have
frequent insert/updates/delete.
Current data
Short database transactions
Online update/insert/delete
Normalization is promoted
High volume transactions
Transaction recovery is necessary

Since in OLTP, tables are normalized and hence query response will be slow for end user.

OLAP

Online Analytical Programming contains the history of OLTP data is used for creating
forecasting reports
Current and historical data
Long database transactions
Batch update/insert/delete
Demoralization is promoted
Low volume transactions
Transaction recovery is not necessary

Normalization

Normalization

It is a technique for designing relational database tables to minimize duplication of information.

Types of Normalization are

1st Normal form - Eliminate duplicative columns from the same table. Create separate tables for
each group

2nd Normal form Meet all the requirements of the first normal form.

Remove subsets of data that apply to multiple rows of a table and place them in separate tables.

Create relationships between these new tables through the use of foreign keys.

3rd Normal form

Meet all the requirements of the second normal form.

Remove columns that are not dependent upon the primary key.

in the 5NF if and only if every join dependency in it is implied by the candidate keys.

Denormalized

A demoralized database is one that has been meticulously normalized to eliminate redundancies,
only to have redundancies deliberately put back in to meet other needs.

Normalization is the process of removing redundancies.

Demoralization is the process of allowing redundancies.

Data warehouse

Data warehouse - is an architecture constructed by integrating data from multiple heterogeneous

sources to support structured, analytical reporting and decision making.

Data Mart – A data mart is a focused subset of a data warehouse that deals with a single area
(like different department) of data and is organized for quick analysis
E.g.: sales, marketing etc.

Differences between DWH & Data Mart: DWH is used on an enterprise level, while data
marts are used on a business division / department level.

Conformed Dimension

Conformed dimension The dimension which is used more than one fact table is called
conformed dimensions. Confirmed dimensions are the dimensions which can be used in multiple
star schemas

Cardinality

Cardinality describes a join between 2 tables by stating how many rows of one table will match
with rows of another table.