Wednesday, 17 August 2011

Working with the Command Prompt in informatica

PMCMD:

Use pmcmd commands with operating system scheduling tools like cron, or you can embed pmcmd commands into shell or Perl scripts.

When you run pmcmd in command line mode, you enter connection information such as domain name, Integration Service name, user name and password in each command. For example, to start the workflow “wf_SalesAvg” infolder “SalesEast,” The user, seller3, with the password “jackson” sends the request to start the workflow.

syntax:

pmcmd startworkflow -sv MyIntService -d MyDomain -u seller3 -p jackson -f SalesEast wf_SalesAvg

Command Line Mode:

1. At the command prompt, switch to the directory where the pmcmd executable is located.

By default, the PowerCenter installer installs pmcmd in the \server\bin directory.

2. Enter pmcmd followed by the command name and its required options and arguments:

pmcmd command_name [-option1] argument_1 [-option2] argument_2...

Interactive Mode :

1. At the command prompt, switch to the directory where the pmcmd executable is located. By default, the PowerCenter installer installs pmcmd in the \server\bin directory.

2. At the command prompt, type pmcmd.This starts pmcmd in interactive mode and displays the pmcmd> prompt. You do not have to type pmcmd before each command in interactive mode.

3. Enter connection information for the domain and Integration Service.

For example:

connect -sv MyIntService -d MyDomain -u seller3 -p jackson

4. Type a command and its options and arguments in the following format:command_name [-option1] argument_1 [-option2] argument_2...pmcmd runs the command and displays the prompt again.

5. Type exit to end an interactive session.

For example, the following commands invoke the interactive mode, establish a connection to Integration Service“MyIntService,” and start workflows “wf_SalesAvg” and “wf_SalesTotal” in folder “SalesEast”:

pmcmd

pmcmd> connect -sv MyIntService -d MyDomain -u seller3 -p jackson

pmcmd> setfolder SalesEast

pmcmd> startworkflow wf_SalesAvg

pmcmd> startworkflow wf_SalesTotal

Scripting pmcmd Commands :

For example, the following UNIX shell script checks the status of Integration Service “testService,” and if it is running, gets details for session “s_testSessionTask”:

#!/usr/bin/bash

# Sample pmcmd script

# Check if the service is alive

pmcmd pingservice -sv testService -d testDomain

if [ "$?" != 0 ]; then

# handle error

echo "Could not ping service"

exit

fi

# Get service properties

pmcmd getserviceproperties -sv testService -d testDomain

if [ "$?" != 0 ]; then

# handle error

echo "Could not get service properties"

exit

fi

# Get task details for session task "s_testSessionTask" of workflow

# "wf_test_workflow" in folder "testFolder"

pmcmd gettaskdetails -sv testService -d testDomain -u Administrator -p adminPass -folder testFolder -

workflow wf_test_workflow s_testSessionTask

if [ "$?" != 0 ]; then

# handle error

echo "Could not get details for task s_testSessionTask"

exit

fi

Schedule Workflow

Instruct the Integration Service to schedule a workflow. Use this command to reschedule a workflow that has been removed from the schedule.

The ScheduleWorkflow command uses the following syntax in the command line mode:

pmcmd ScheduleWorkflow

<<-service|-sv> service [<-domain|-d> domain] [<-timeout|-t> timeout]>

<<-user|-u> username|<-uservar|-uv> userEnvVar>

<<-password|-p> password|<-passwordvar|-pv> passwordEnvVar>

[<<-usersecuritydomain|-usd> usersecuritydomain|<-usersecuritydomainvar|-usdv>

userSecuritydomainEnvVar>]

[<-folder|-f> folder]

workflow

For further study on this topic one can always refer to Informatica 9.1.0 Command Reference

Sunday, 31 July 2011

Functions in Informatica

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

Leonardo

NULL

Edwin Britto

1.2 LPAD:

RETURN VALUE

8

NULL

12

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

1

250

1.3 LTRIM:

RETURN VALUE

00001

00250

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

006

0803

RETURN VALUE

6

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

Date

Time

1.5 RTRIM:

RETURN VALUE

Date:

Time:

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

0610

380

RETURN VALUE

06

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

1800.03

-22.57891

RETURN VALUE

'1800.03'

'-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

Apr 1 1998 12:00:10AM

RETURN VALUE

'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

'12/28/81'

NULL

RETURN VALUE

Dec 28 1981 00:00:00

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

'15.6789'

NULL

'A12.3Grove'

RETURN VALUE

15.678

NULL

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

'15.6789'

NULL

2.5 TO_INTEGER:

RETURN VALUE

15.6789

NULL

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

'15.6789'

'60.2'

RETURN VALUE

16

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

Jan 12 1998 12:00:30AM

RETURN VALUE

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

Jan 1 1997 12:00:30AM

RETURN VALUE

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

Jan 1 1997 12:00:30AM

RETURN VALUE

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

Jan 31 1998 6:24:45PM

3.2 DATE_COMPARE

RETURN VALUE

Apr 30 1998 6:24:45PM

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

Jan 1 1997

Feb 1 1997

Dec 22 1997

3.3 DATE_DIFF

DATE_SHIPPED

Jan 13 1997

Feb 1 1997

Dec 15 1997

RETURN VALUE

-1

0

1

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

Jan 1 1997 12:00:00AM

Mar 29 1997 12:00:00PM

DATE_SHIPPED

Mar 29 1997 12:00:00PM

Jan 1 1997 12:00:00AM

RETURN VALUE

-87.5

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

Mar 13 1997 12:00:00AM

June 3 1997 11:30:44PM

NULL

3.5 LAST_DAY

RETURN VALUE

13

3

NULL

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

Jan 6 1998 12:00:00AM

Apr 30 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

Flashlight

Regulator System

Flashlight

Diving Hood

Halogen Flashlight

Flashlight

RETURN VALUE: Oct 10 1998

3.7 MIN

ORDER_DATE

Apr 20 1998

May 15 1998

Sep 21 1998

Aug 18 1998

Feb 1 1998

Oct 10 1998

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

Flashlight

Regulator System

Flashlight

Diving Hood

ORDER_DATE

Apr 20 1998

May 15 1998

Sep 21 1998

Aug 18 1998clip_image004[8]

Halogen Flashlight

Flashlight

RETURN VALUE: Feb 1 1998

3.8 ROUND

Feb 1 1998

Oct 10 1998

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

Jan 15 1998 2:10:30AM

RETURN VALUE

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

Jan 1 1997 12:15:56AM

NULL

RETURN VALUE

Jun 1 1997 12:15:56AM

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

Jan 15 1998 2:10:30AM

RETURN VALUE

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

10

14

17

4.2 IIF

RETURN VALUE

Flashlight

Regulator

NONE

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 < 100, 0, SALARY )

SALES

150

50

SALARY

50,000.00

20,000.00

RETURN VALUE

50,000

0

NULL

50,000.41

50,000

IIF functions can be nested if there is more than one condition to be tested. But it is always a better option to go for DECODE function when the number of conditions is large since DECODE function is less costlier compared to IIF function.

For example consider the following expression

IIF(MARKS>=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 < 0, ERROR ('Error. Negative salary found. Row skipped.', EMP_SALARY )

SALARY RETURN VALUEclip_image004[11]

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('Invalid

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 :TD.SALES for a specific item ID and price, and returns the item name if both searches find a match:

LOOKUP( :TD.SALES.ITEM_NAME, :TD.SALES.ITEM_ID, 10, :TD.SALES.PRICE, 15.99 )

ITEM_NAME

Regulator

Flashlight

5 Test Functions:

5.1 ISNULL

ITEM_ID

5

10

PRICE

100.00

15.99

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

Flashlight

NULL

''

5.2 IS_DATE

RETURN VALUE

0 (FALSE)

1 (TRUE)

0 (FALSE) Empty string is not NULL

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

NULL

180

'04/01/98'

'04/01/1998 00:12:15'

'02/31/1998 12:13:55'

'John Smith'

RETURN VALUE

NULL

0 (FALSE)

0 (FALSE)

1 (TRUE)

0 (FALSE) (February does not have 31 days)

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

123.00

-3.45e+3

''

+123abc

ABC

-ABC

NULL

RETURN VALUE

1 (True)

1 (True)

0 (False) Empty string

0 (False)

0 (False)

0 (False)

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

Flashlight

Regulator

system

RETURN VALUE

0 (False)

1 (True)

0 (False)

Tuesday, 7 June 2011

Wishing Thanks to My Readers.

Hi reader’s,

I’m very much happy to see that lot of people from across the globe visited my blog and visitor’s count has crossed 3000 + with in 40 days of starting the blog. I would request my readers to start posting your valuable comments and requests so that I can make the blog more informative &  interactive . For viewers information who visit my Informatica blog, to visit my SQL or Data warehousing blogs too as I spent a lot of time posting the information, which is very useful for the job seekers or Data warehousing professionals in your day today activities at work or can also be used as the quick reference guide before taking up any interview.

I’m planning to publish a book on informatica soon which will cover all major concepts of the data warehousing in detail, including the SQL concepts used by an ETL consultants, Basic Unix Commands and Introduction to the Scheduler tools like Autosys, Tivoli and off course Informatica with simple examples.

I would highly appreciate if few people come forward and post the interview questions or the scenarios which they have encountered in there day to day work for discussion at the discussion board.

Wishing Thanks to My Readers.

Hi reader’s,

I’m very much happy to see that lot of peoples from all part of globe have visited my blog and visitor’s count has crossed 3000 + with in 40 days of starting the blog. I would request my readers to start posting there valuable comments and requests so that we can make much more solicit & interactive blog. I had also noticed that the number of viewers who visiting my SQL or Data warehousing blog is pretty low as compared to informatica. I would also request my visitors to look in to them as I had spent a lot of time in making a content for it and which is very useful when your are at work or can also be used as the quick reference guide before taking up any interview.

I’m planning to publish a book on informatica soon which will all major concepts of the data warehousing in detail and Sql concepts used by an ETL consultants, Basic Unix Commands and Introduction to the Scheduler tools like autosys, Tivoli and off course Informatica which has brought me all this fame.

I would highly appreciate if few people come forward and post the interview questions or the scenarios which they have encountered in there day to day work for discussion at the discussion board.

Wednesday, 25 May 2011

Informatica Experienced Interview Questions- Part4

76.While importing the relational source definition from database, what are the meta data of source U import?

77.Difference between Power mart & Power Center?

78.What kinds of sources and of targets can be used in Informatica?

79.If a sequence generator (with increment of 1) is connected to (say) 3 targets and each target uses the NEXTVAL port, what value will each target get?

80.What do you mean by SQL override?

81.What is a shortcut in Informatica?

82.How does Informatica do variable initialization? Number/String/Date

83.How many different locks are available for repository objects

84.What are the transformations that use cache for performance?

85.What is the use of Forward/Reject rows in Mapping?

86.How many ways you can filter the records?

87.How to delete duplicate records from source database/Flat Files? Can we use post sql to delete these records. In case of flat file, how can you delete duplicates before it starts loading?

88.You are required to perform “bulk loading” using Informatica on Oracle, what action would perform at Informatica + Oracle level for a successful load?

89.What precautions do you need take when you use reusable Sequence generator transformation for concurrent sessions?

90.Is it possible negative increment in Sequence Generator? If yes, how would you accomplish it?

91.Which directory Informatica looks for parameter file and what happens if it is missing when start the session? Does session stop after it starts?

92.Informatica is complaining about the server could not be reached? What steps would you take?

93.You have more five mappings use the same lookup. How can you manage the lookup?

94.What will happen if you copy the mapping from one repository to another repository and if there is no identical source?

95.How can you limit number of running sessions in a workflow?

96.An Aggregate transformation has 4 ports (l sum (col 1), group by col 2, col3), which port should be the output?

97.What is a dynamic lookup and what is the significance of NewLookupRow? How will use them for rejecting duplicate records?

98.If you have more than one pipeline in your mapping how will change the order of load?

99.When you export a workflow from Repository Manager, what does this xml contain? Workflow only?

100. Your session failed and when you try to open a log file, it complains that the session details are not available. How would do trace the error? What log file would you seek for?

101.You want to attach a file as an email attachment from a particular directory using ‘email task’ in Informatica, How will you do it?

102. You have a requirement to alert you of any long running sessions in your workflow. How can you create a workflow that will send you email for sessions running more than 30 minutes. You can use any method, shell script, procedure or Informatica mapping or workflow control?

Tuesday, 24 May 2011

Informatica Real time Scenarios.

Scenario 1: How can we load first and last record from a flat file source to target?

Solution:

Create two pipelines in a mapping

1st pipeline would capture the first record, and 2nd one for last record.

1st Pipeline:

src-> sq-> exp(take a variable port with numeric data type and pass through a output port 'O_Test')->filter(pass if only

O_Test =1)->tgt

2nd pipeline:

src->sq->agg(No group it will pass only last entry)->tgt

In session for 2nd instance of target enable 'Append if Exists' option

Scenario 2: How to find out nth row in flat file...we used to do top N analysis by using  rownum & some other functionalities  by using rowid when source is table .and my query is how to achieve the same functionalities when my source is flat file?

Solution: In the Mapping designer, go to Mappings-> Parameters and Variables.

Here we have two things - Parameters(constant values passed to the mapping) and variables which are dynamic and can be stored as a metadata for future runs(for example you want to do an incremental load into a table B from table A. So you can define a variable which holds the seqid from source. Before you write the data into target , create an expression and source the seqid from source as input and create a variable Max_seqid as output. Now update this value for each row. when the session finishes informatica saves the last read seqid and you can use this in your source qualifier when you run the mapping next time. Please see Infa doc for setmaxvaribale and setminvariables.

In this case, we have to just make use parameters to find the nth row.

Create a parameter(type) - Last_row_number and select datatype as integer or double.

Now you have to create a parameter file on unix box before you call the workflow.

something like this

echo '[<FOLDERNAME>.WF:<WorkflowName>.ST:<SessionName>]'

count=`wc -l filename`

echo "\$\$MappingVariable="$count

Name the parameter file as <workflowname>.par and copy the complete path of the file name and update the "Parameter filename" field under Properties tab in workflow edit tasks.

You can then use this variable in your mapping wherever you want. Just proceed it with two $$.

Scenario 3: How to create flat file dynamically?

SRC FILE             TRGT 1                                  Trgt 2

---------------- --------------------------------     --------------------------------------

Eid Name Sal      Eid Name Sal                          Eid Name Sal

10 a 100             10 a 100                                 20 b 100

20 b 100             10 c 200                                 20 d 300

10 c 200

20 d 300

Solution :

1. Sort the data coming from the source based on EID.

2. Create a variable in an expression transformation that would track the change in EID e.g. in your case if the data is sorted based on EID then it would look like

EID     Name      SAL     

10     a               100

10     c               200

20     b               100

20     d               300

Whenever there is a change in EID the variable would track it

variable1= IIF(EID = PREV_EID, 0, 1)

3. Add a transaction control transformation in the map with a similar condition

IIF(variable1 = 1, TC_COMMIT_BEFORE, TC_CONTINUE_TRANSACTION)

this would create a new file whenever there is a change in the EID value.

4. Add a "filename" port in the target and then pass on a value as per your requirement so that the filenames get generated dynamically as per your requirement.

Scenario 4: I HAVE A SOURCE FILE CONTAINING

1|A,1|B,1|C,1|D,2|A,2|B,3|A,3|B

AND IN TARGET I SHOULD GET LIKE

1|A+B+C+D

2|A+B

3|A+B

Solution:

Follow the logic given below in the expression and you will get your output.

Please ensure that all the ports you mentioned below are variable ports and the incoming data should be sorted by key,data

 

VarPorts

Assigned

Row1

Row2

Row3

Row4

V_CURNT

KEY

1

1

1

2

V_CURNT_DATA

DATA

a

b

c

d

v_OUT

(variable port)

IIF(isnull(v_PREV_DATA) or length(v_PREV_DATA)=5,v_CURNT_DATA,iif(V_CURNT = V_PREV, V_PREV_DATA||'~'||V_CURNT_DATA,NULL)

a

a~b

a~b~c

d

o_OUT

v_OUT

a

a~b

a~b~c

d

V_PREV

V_CURNT

null

1

1

1

V_PREV_DATA

v_OUT

null

a

a~b

a~b~c

And After the expression transformation, you have to add an aggregator tx with group by port as 'key'. this will return the last record with the key.

Hi all the above scenario’s have been taken from informatica communities.Incase any one needs any info about the scenarios discussed then they may contact for clarifications.

Monday, 23 May 2011

Data warehousing Concepts Based Interview Questions

1. What is a data-warehouse?

2. What are Data Marts?

3. What is ER Diagram?

4. What is a Star Schema?

5. What is Dimensional Modelling?

6. What Snow Flake Schema?

7. What are the Different methods of loading Dimension tables?

8. What are Aggregate tables?

9. What is the Difference between OLTP and OLAP?

10. What is ETL?

11. What are the various ETL tools in the Market?

12. What are the various Reporting tools in the Market?

13. What is Fact table?

14. What is a dimension table?

15. What is a lookup table?

16. What is a general purpose scheduling tool? Name some of them?

17. What are modeling tools available in the Market? Name some of them?

18. What is real time data-warehousing?

19. What is data mining?

20. What is Normalization? First Normal Form, Second Normal Form , Third Normal Form?

21. What is ODS?

22. What type of Indexing mechanism do we need to use for a typical

Data warehouse?

23. Which columns go to the fact table and which columns go the dimension table? (My user needs to see <data element<data element broken by <data element<data element>

All elements before broken = Fact Measures

All elements after broken = Dimension Elements

24. What is a level of Granularity of a fact table? What does this signify?(Weekly level summarization there is no need to have Invoice Number in the fact table anymore)

25. How are the Dimension tables designed? De-Normalized, Wide, Short, Use Surrogate Keys, Contain Additional date fields and flags.

26. What are slowly changing dimensions?

27. What are non-additive facts? (Inventory,Account balances in bank)

28. What are conformed dimensions?

29. What is VLDB? (Database is too large to back up in a time frame then it's a VLDB)

30. What are SCD1, SCD2 and SCD3?

Informatica Experienced Interview Questions – part 3

Hi readers. These are the questions which normally I would expect by interviewee to know when i sit in panel. So what i would request my reader’s to start posting your answers to this questions in the discussion forum under informatica technical interview guidance tag and i’ll review them and only valid answers will be kept and rest will be deleted.

51.Can u copy the session in to a different folder or repository?

52.What is tracing level and what are its types?

53.What is a command that used to run a batch?

54.What are the unsupported repository objects for a mapplet?

55.If your workflow is running slow, what is your approach towards performance tuning?

56.What are the types of mapping wizards available in Informatica?

57.After dragging the ports of three sources (Sql server, oracle, Informix) to a single source qualifier, can we map these three ports directly to target?

58.Why we use stored procedure transformation?

59.Which object is required by the debugger to create a valid debug session?

60.Can we use an active transformation after update strategy transformation?

61.Explain how we set the update strategy transformation at the mapping level and at the session level?

62.What is exact use of 'Online' and 'Offline' server connect Options while defining Work flow in Work flow monitor? The system hangs when 'Online' Server connect option. The Informatica is installed on a Personal laptop.

63.What is change data capture?

64.Write a session parameter file which will change the source and targets for every session. i.e different source and targets for each session run ?

65.What are partition points?

66.What are the different threads in DTM process?

67.Can we do ranking on two ports? If yes explain how?

68.What is Transformation?

69.What does stored procedure transformation do in special as compared to other transformation?

70.How do you recognize whether the newly added rows got inserted or updated?

71.What is data cleansing?

72.My flat file’s size is 400 MB and I want to see the data inside the FF with out opening it? How do I do that?

73.Difference between Filter and Router?

74.How do you handle the decimal places when you are importing the flat file?

75.What is the difference between $ & $$ in mapping or parameter file? In which case they are generally used?

Thursday, 19 May 2011

Informatica Experienced Interview Questions - Part2

Hi readers. These are the questions which normally I would expect by interviewee to know when i sit in panel. So what i would request my reader’s to start posting your answers to this questions in the discussion forum under informatica technical interview guidance tag and i’ll review them and only valid answers will be kept and rest will be deleted.

26.What is Data driven?

27.What is batch? Explain the types of the batches?

28.What are the types of meta data repository stores?

29.Can you use the mapping parameters or variables created in one mapping into another mapping?

30.Why did we use stored procedure in our ETL Application?

31.When we can join tables at the Source qualifier itself, why do we go for joiner transformation?

32.What is the default join operation performed by the look up transformation?

33.What is hash table Informatica?

34.In a joiner transformation, you should specify the table with lesser rows as the master table. Why?

35.Difference between Cached lookup and Un-cached lookup?

36.Explain what DTM does when you start a work flow?

37.Explain what Load Manager does when you start a work flow?

38.In a Sequential batch how do i stop one particular session from running?

39.What are the types of the aggregations available in Informatica?

40.How do I create Indexes after the load process is done?

41.How do we improve the performance of the aggregator transformation?

42.What are the different types of the caches available in Informatica? Explain in detail?

43.What is polling?

44.What are the limitations of the joiner transformation?

45.What is Mapplet?

46.What are active and passive transformations?

47.What are the options in the target session of update strategy transformation?

48.What is a code page? Explain the types of the code pages?

49.What do you mean rank cache?

50.How can you delete duplicate rows with out using Dynamic Lookup? Tell me any other ways using lookup delete the duplicate rows?

Informatica Interview based Scenarios.

 

Hi readers on daily basis i’ll be posting few scenarios discussed in IT toolbox here in my blog as most of my readers wrote to me regarding few interview based scenario’s i’m posting the same here. In case any one needs any help with these scenarios please feel free to contact me .

Skipped record count:

Scenario 1: My source files as 5, 00,000 records. While fetching its skipping records due to data type and other issues. Finally it fetches only 1, 00,000 records. Through session properties we are considering 100000 as the source count.
But actually we are loosing 400000 records. How can I find the number or records that are skipped?

Solution: OPB_SESS_TASK_LOG there is a count for SRC_FAILED_ROWS

Scenario 2: Please provide different ways to achieve this..
How to normalize the following data:

id date

101 2/4/2008

101 4/4/2008

102 6/4/2008

102 4/4/2008

103 4/4/2008

104 8/4/2008

O/P - should have only one id with the min(date)

How to create a mapping for this ?

1 ---> Using Transformations

2 ---> Using SQ Override method..

Solution: You can use the rank transformation and select rank port for id and group by on date. In properties tab select bottom and number of ranks as '1' .

Scenario 3: My scenario is like I am loading records on a daily basis, target is not a truncate load suppose from source I am loading records like

ID|Name

101|Apple

102|Orange

102|Banana

but in target I am already having target record(ID 102 of 10 records), scenario I need is like I have to delete only Empid 102 of yesterday record and load today record(2 records)

How to achieve this in Informatica?

Solution: You can achieve your goal by taking the Look up on your target table and match on the basis of ID column. Then take an expression after your lookup and add a FLAG column. In that FLAG column check for the NULL value return from Look up. After expression take 2 filters and in one filter pass the records with NULL values and Insert those records into Target.

If the Value is not NULL then you can take a UPDATE strategy and Update the old row with the new one.

Scenario 4:

Informatica Sequence Generator Scenarios

1) I have following set of records

Id | Name

101 | ABC

102 | DEF

101 | AMERICA

103 | AFRICA

102 | JAPAN

103 | CHINA

I need to generate sequence and populate in Target in the following manner

SID | ID | NAME

1   |101 | ABC

2   |101 | AMERICA

1   |102 | DEF

2   |102 | JAPAN

1   |103 | AFRICA

2   |103 | CHINA

How to implement the same in Informatica?

Solution:

1 sort on Id

2 use expression t/f like below

V_cur_id -> v_pre_id

V_pre_id -> i_id -- i_id is input

V_seq_id -> iif(v_cur_id = v_pre_id, v_seq_id+1, 1) --default 0

O_seq_id -> v_seq_id.

Scenario 5 : I have my input as below : ( Using Expression)

10

10

10

20

20

30

O/P :

1

2

3

1

2

1

How do we obtain this using the Informatica?

Solution: first import source, then use a sorter transformation . sort it by ur column, then use a expression.

in expression make this column .

like this

1.column_num(coming from sorter)

2.current_num= check if column_num=previous_num,then add (first_value +1),else 1

3.first_value=current_num.

4.previous_num(new column)= column_num

pass current_num to target.

Wednesday, 18 May 2011

Informatica Experienced Interview Questions – part 1

  1. Difference between Informatica 7x and 8x?
  2. Difference between connected and unconnected lookup transformation in Informatica?
  3. Difference between stop and abort in Informatica?
  4. Difference between Static and Dynamic caches?
  5. What is Persistent Lookup cache? What is its significance?
  6. Difference between and reusable transformation and mapplet?
  7. How the Informatica server sorts the string values in Rank transformation?
  8. Is sorter an active or passive transformation? When do we consider it to be active and passive?
  9. Explain about Informatica server Architecture?
  10. In update strategy Relational table or flat file which gives us more performance? Why?
  11. What are the out put files that the Informatica server creates during running a session?
  12. Can you explain what are error tables in Informatica are and how we do error handling in Informatica?
  13. Difference between constraint base loading and target load plan?
  14. Difference between IIF and DECODE function?
  15. How to import oracle sequence into Informatica?
  16. What is parameter file?
  17. Difference between Normal load and Bulk load?
  18. How u will create header and footer in target using Informatica?
  19. What are the session parameters?
  20. Where does Informatica store rejected data? How do we view them?
  21. What is difference between partitioning of relational target and file targets?
  22. What are mapping parameters and variables in which situation we can use them?
  23. What do you mean by direct loading and Indirect loading in session properties?
  24. How do we implement recovery strategy while running concurrent batches?
  25. Explain the versioning concept in Informatica?

Tuesday, 17 May 2011

Informatica Real time Interview Questions

 

 

Hi readers. These are the questions which normally i would expect by interviewee to know when i sit in panel. So what i would request my reader’s to start posting your answers to this questions in the discussion forum under informatica technical interview guidance tag and i’ll review them and only valid answers will be kept and rest will be deleted.

  1. Explain your Project?
  2. What are your Daily routines?
  3. How many mapping have you created all together in your project?
  4. In which account does your Project Fall?
  5. What is your Reporting Hierarchy?
  6. How many Complex Mapping’s have you created? Could you please me the situation for which you have developed that Complex mapping?
  7. What is your Involvement in Performance tuning of your Project?
  8. What is the Schema of your Project? And why did you opt for that particular schema?
  9. What are your Roles in this project?
  10. Can I have one situation which you have adopted by which performance has improved dramatically?
  11. Where you Involved in more than two projects simultaneously?
  12. Do you have any experience in the Production support?
  13. What kinds of Testing have you done on your Project (Unit or Integration or System or UAT)? And Enhancement’s were done after testing?
  14. How many Dimension Table are there in your Project and how are they linked to the fact table?
  15. How do we do the Fact Load?
  16. How did you implement CDC in your project?
  17. How does your Mapping in File to Load look like?
  18. How does your Mapping in Load to Stage look like?
  19. How does your Mapping in Stage to ODS look like?
  20. What is the size of your Data warehouse?
  21. What is your Daily feed size and weekly feed size?
  22. Which Approach (Top down or Bottom Up) was used in building your project?
  23. How do you access your source’s (are they Flat files or Relational)?
  24. Have you developed any Stored Procedure or triggers in this project? How did you use them and in which situation?
  25. Did your Project go live? What are the issues that you have faced while moving your project from the Test Environment to the Production Environment?
  26. What is the biggest Challenge that you encountered in this project?
  27. What is the scheduler tool you have used in this project? How did you schedule jobs using it?

Wednesday, 27 April 2011

Working with XML files

                                                                

 

We have three types of the xml transformations and they are listed below:

XML Source Qualifier Transformation: You can add an XML Source Qualifier transformation to a mapping by dragging an XML source definition to the Mapping Designer workspace or by manually creating one.

clip_image001 We can link one XML source definition to one XML Source Qualifier transformation.

clip_image001[1] We cannot link ports from more than one group in an XML Source Qualifier transformation to ports in the same target transformation.

XML Parser Transformation: The XML Parser transformation lets you extract XML data from messaging systems, such as TIBCO or MQ Series, and from other sources, such as files or databases.

clip_image001[2] Used when we need to extract XML data from a TIBCO source and pass the data to relational targets.

clip_image001[3] The XML Parser transformation reads XML data from a single input port and writes data to one or more output ports.

XML Generator Transformation: The XML Generator transformation lets you read data from messaging systems, such as TIBCO and MQ Series, or from other sources, such as files or databases.

clip_image001[4] Used when we need to extract data from relational sources and passes XML data to targets.

clip_image001[5] The XML Generator transformation accepts data from multiple ports and writes XML through a single output port.

Stage 1 (Oracle to XML)

clip_image001[6] We are gonna generate an xml file as output with the oracle emp table as source.

Step 1: Generate the XML target file.

  • Import the same emp table as source table
  • Go the targets and click on import the XML definition.
  • Later choose the Non XML source from the left hand pane.
  • Move the emp table (source table) from all sources to the Selected Sources.
  • After which, we got to click on open to have the target table in the target designer.
  • Sequential Steps to generate the xml target table is shown in below snap shots.

clip_image003

Fig a: Importing the XML definition

clip_image005

Fig b: stage 1 for generating file just got to click on OK in this window.

clip_image007

Fig c: Here specify the name for the target table and click next.

clip_image009

Fig d: finally we got to click on Finish in this window.

Step 2: Design the mapping, connect the SQ straight away to the target table.

clip_image011

  • Create the name of the mapping as per the naming convention.
  • Save the changes.

Step 3: Create task and the work flow.

clip_image013

  • Double click on the work flow and go to the mapping tab and here we got to specify the output file directory. (C :/) ….
  • Run the work flow ,check in the C drive and look for an file by name emp.xml …

Stage 2 (XML to Oracle)

clip_image001[7] Here source is gonna be the xml file and the target file is the oracle file.

Step 1: Importing the source xml file and import the target transformation.

  • Go the sources and click on the import XML definition.
  • Browse for the emp.xml file and open the same.
  • The first three windows are gonna be same as in previous case.
  • Target table is gonna be the same EMP table.

Step 2: Design the mapping.

  • Connections for this mapping is gonna be the following way.

clip_image015

  • Save the mapping.

Step 3: Create the task and work flow.

  • Create the task and the work flow using the naming conventions.
  • Go to the mappings tab and click on the Source on the left hand pane to specify the path for the input file.

clip_image017

Step 4: Preview the output on the target table.

clip_image019

Tuesday, 26 April 2011

SCD Type 3

SCD –Type 3:

                                                                 

This Method has limited history preservation, and we are goanna use skey as the Primary key here.

Source table: (01-01-2011)

Empno

Ename

Sal

101

102

103

A

B

C

1000

2000

3000

Target Table: (01-01-2011)

Empno

Ename

C-sal

P-sal

101

102

103

A

B

C

1000

2000

3000

-

-

-

Source Table: (01-02-2011)

Empno

Ename

Sal

101

102

103

A

B

C

1000

4566

3000

Target Table (01-02-2011):

Empno

Ename

C-sal

P-sal

101

102

103

102

A

B

C

B

1000

4566

3000

4544

-

Null

-

4566

So hope u got what I’m trying to do with the above tables:

Step 1: Initially in the mapping designer I’m goanna create a mapping as below. And in this mapping I’m using lookup, expression, filter, update strategy to drive the purpose. Explanation of each and every Transformation is given below.

clip_image002

Step 2: here we are goanna see the purpose and usage of all the transformations that we have used in the above mapping.

Look up Transformation: The look Transformation looks the target table and compares the same with the source table. Based on the Look up condition it decides whether we need to update, insert, and delete the data from being loaded in to the target table.

  • As usually we are goanna connect Empno column from the Source Qualifier and connect it to look up transformation. Prior to this Look up transformation has to look at the target table.
  • Next to this we are goanna specify the look up condition empno =empno1.
  • Finally specify that connection Information (Oracle) and look up policy on multiple mismatches (use last value) in the Properties tab.

Expression Transformation:

We are using the Expression Transformation to separate out the Insert-stuff’s and Update- Stuff’s logically.

  • Drag all the ports from the Source Qualifier and Look up in to Expression.
  • Add two Ports and Rename them as Insert, Update.
  • These two ports are goanna be just output ports. Specify the below conditions in the Expression editor for the ports respectively.

Insert: isnull(ENO1 )

Update: iif(not isnull(ENO1) and decode(SAL,Curr_Sal,1,0)=0,1,0)

clip_image004

Filter Transformation: We are goanna use two filter Transformation to filter out the data physically in to two separate sections one for insert and the other for the update process to happen.

Filter 1:

  • Drag the Insert and other three ports which came from source qualifier in to the Expression in to first filter.
  • In the Properties tab specify the Filter condition as Insert.

clip_image006

Filter 2:

  • Drag the update and other four ports which came from Look up in to the Expression in to Second filter.
  • In the Properties tab specify the Filter condition as update.

clip_image008

Update Strategy: Finally we need the update strategy to insert or to update in to the target table.

Update Strategy 1: This is intended to insert in to the target table.

  • Drag all the ports except the insert from the first filter in to this.
  • In the Properties tab specify the condition as the 0 or dd_insert.clip_image010

Update Strategy 2: This is intended to update in to the target table.

  • Drag all the ports except the update from the second filter in to this.
  • In the Properties tab specify the condition as the 1 or dd_update.

clip_image012

Finally connect both the update strategy in to two instances of the target.

Step 3: Create a session for this mapping and Run the work flow.

Step 4: Observe the output it would same as the second target table

clip_image014

Monday, 25 April 2011

Mapping Templates

Mapping Templates Overview :

                                                                 

A mapping template is a drawing in Visio that represents a PowerCenter mapping. You can configure rules and parameters in a mapping template to specify the transformation logic.

Use the Informatica Stencil and the Informatica toolbar in the Mapping Architect for Visio to create a mapping template. The Informatica Stencil contains shapes that represent mapping objects that you can use to create a mapping template. The Informatica toolbar contains buttons for the tasks you can perform on mapping template.

You can create a mapping template manually, or you can create a mapping template by importing a Power Center mapping.

Creating a Mapping Template Manually:

You can use the Informatica Stencil and the Informatica toolbar to create a mapping template. Save and publish a mapping template to create the mapping template files.

To create a mapping template manually, complete the following steps:

1. Start Mapping Architect for Visio.

2. Verify that the Informatica Stencil and Informatica toolbar are available.

3. Drag the mapping objects from the Informatica Stencil to the drawing window:- Use the mapping objects to create visual representation of the mapping.

4. Create links:- Create links to connect mapping objects.

5. Configure link rules:- Configure rules for each link in the mapping template to indicate how data moves from one mapping object to another. Use parameters to make the rules flexible.

6. Configure the mapping objects:- Add a group or expression required by the transformations in the mapping template. To create multiple mappings, set a parameter for the source or target definition.

7. Declare mapping parameters and variables to use when you run sessions in Power Center:- After you import the mappings created from the mapping template into Power Center, you can use the mapping parameters and variables in the session or workflow.

8. Validate the mapping template.

9. Save the mapping template:- Save changes to the mapping template drawing file.

10. Publish the mapping template:- When you publish the mapping template, Mapping Architect for Visio generates a mapping template XML file and a mapping template parameter file (param.xml).If you edit the mapping template drawing file after you publish it, you need to publish again. Do not edit the mapping template XML file.

Importing a Mapping Template from a Power Center:

If you have a Power Center mapping that you want to use as a basis for a mapping template, export the mapping to a mapping XML file and then use the mapping XML file to create a mapping template.

Note: Export the mapping XML file within the current Power Center release. Informatica does not support imported objects from a different release.

To import a mapping template from a Power Center mapping, complete the following steps:

1. Export a Power Center mapping. In the Designer, select the mapping that you want to base the mapping template on and export it to an XML file.

2. Start Mapping Architect for Visio.

3. Verify that the Informatica stencil and Informatica toolbar are available.

4. Import the mapping. On the Informatica toolbar, click the Create Template from Mapping XML button. Mapping Architect for Visio determines the mapping objects and links included in the mapping and adds the appropriate objects to the drawing window.

5. Verify links. Create or verify links that connect mapping objects.

6. Configure link rules. Configure rules for each link in the mapping template to indicate how data moves from one mapping object to another. Use parameters to make the rules flexible.

7. Configure the mapping objects. Add a group or expression required by the transformations in the mapping template. To create multiple mappings, set a parameter for the source or target definition.

8. Declare mapping parameters and variables to use when you run the session in Power Center. After you import the mappings created from the mapping template into Power Center, you can use the mapping parameters and variables in the session or workflow.

Note: If the Power Center mapping contains mapping parameters and variables, it is possible that the mapping parameters and variables ($$ParameterName) may not work for all mappings you plan to create from the mapping template. Modify or declare new mapping parameters and variables appropriate for running the new mappings created from the mapping template.

9. Validate the mapping template.

10. Save the mapping template. Save changes to the mapping template drawing file.

11. Publish the mapping template. When you publish the mapping template, Mapping Architect for Visio generates a mapping template XML file and a mapping template parameter file (param.xml).

If you make any change to the mapping template after publishing, you need to publish the mapping template again. Do not edit the mapping template XML file.

Note: Mapping Architect for Visio fails to create a mapping template if you import a mapping that includes an unsupported source type, target type, or mapping object.

Grid Processing

Grid Processing Overview:

                                                              
When a Power Center domain contains multiple nodes, you can configure workflows and sessions to run on a grid. When you run a workflow on a grid, the Integration Service runs a service process on each available node of the grid to increase performance and scalability. When you run a session on a grid, the Integration Service distributes session threads to multiple DTM processes on nodes in the grid to increase performance and scalability.
You create the grid and configure the Integration Service in the Administration Console. To run a workflow on a grid, you configure the workflow to run on the Integration Service associated with the grid. To run a session on a grid, configure the session to run on the grid.

The Integration Service distributes workflow tasks and session threads based on how you configure the workflow or session to run:

  • Running workflows on a grid. The Integration Service distributes workflows across the nodes in a grid. It also distributes the Session, Command, and predefined Event-Wait tasks within workflows across the nodes in a grid.
  • Running sessions on a grid. The Integration Service distributes session threads across nodes in a grid.

Note: To run workflows on a grid, you must have the Server grid option. To run sessions on a grid, you must have the Session on Grid option.

Running Workflows on a Grid:

When you run a workflow on a grid, the master service process runs the workflow and all tasks except Session, Command, and predefined Event-Wait tasks, which it may distribute to other nodes. The master service process is the Integration Service process that runs the workflow, monitors service processes running on other nodes, and runs the Load Balancer. The Scheduler runs on the master service process node, so it uses the date and time for the master service process node to start scheduled workflows.

The Load Balancer is the component of the Integration Service that dispatches Session, Command, and predefined Event-Wait tasks to the nodes in the grid. The Load Balancer distributes tasks based on node availability. If the Integration Service is configured to check resources, the Load Balancer also distributes tasks based on resource availability.

For example, a workflow contains a Session task, a Decision task, and a Command task. You specify a resource requirement for the Session task. The grid contains four nodes, and Node 4 is unavailable. The master service process runs the Start and Decision tasks. The Load Balancer distributes the Session and Command tasks to

nodes on the grid based on resource availability and node availability.

Running Sessions on a Grid:

When you run a session on a grid, the master service process runs the workflow and all tasks except Session, Command, and predefined Event-Wait tasks as it does when you run a workflow on a grid. The Scheduler runs on the master service process node, so it uses the date and time for the master service process node to start scheduled workflows. In addition, the Load Balancer distributes session threads to DTM processes running on different nodes.

When you run a session on a grid, the Load Balancer distributes session threads based on the following factors:

  •  Node availability :- The Load Balancer verifies which nodes are currently running, enabled, and available for task dispatch.
  •  Resource availability :- If the Integration Service is configured to check resources, it identifies nodes that have resources required by mapping objects in the session.
  •  Partitioning configuration. The Load Balancer dispatches groups of session threads to separate nodes based on the partitioning configuration.

You might want to configure a session to run on a grid when the workflow contains a session that takes a long time to run.

Grid Connectivity and Recovery

When you run a workflow or session on a grid, service processes and DTM processes run on different nodes. Network failures can cause connectivity loss between processes running on separate nodes. Services may shut down unexpectedly, or you may disable the Integration Service or service processes while a workflow or session is running. The Integration Service failover and recovery behavior in these situations depends on the service process that is disabled, shuts down, or loses connectivity. Recovery behavior also depends on the following factors:

  • High availability option:-When you have high availability, workflows fail over to another node if the node or service shuts down. If you do not have high availability, you can manually restart a workflow on another node to recover it.
  • Recovery strategy:- You can configure a workflow to suspend on error. You configure a recovery strategy for tasks within the workflow. When a workflow suspends, the recovery behavior depends on the recovery strategy you configure for each task in the workflow.
  • Shutdown mode:- When you disable an Integration Service or service process, you can specify that the service completes, aborts, or stops processes running on the service. Behavior differs when you disable the Integration Service or you disable a service process. Behavior also differs when you disable a master service process or a worker service process. The Integration Service or service process may also shut down unexpectedly. In this case, the failover and recovery behavior depend on which service process shuts down and the configured recovery strategy.
  • Running mode:-If the workflow runs on a grid, the Integration Service can recover workflows and tasks on another node. If a session runs on a grid, you cannot configure a resume recovery strategy.
  • Operating mode:- If the Integration Service runs in safe mode, recovery is disabled for sessions and workflows.

Note: You cannot configure an Integration Service to fail over in safe mode if it runs on a grid.