Informatica Version Upgrade

Informatica Upgrade Process:

Stages across upgrade can be categorized as below:

1. Upgrading the domain and server file: run the Informatica server installer and select the upgrade option. The domain upgrade wizard installs the server files and configures the domain. If the domain has multiple nodes, you must upgrade on all the nodes.

The following table describes the actions that the installer performs when you upgrade Informatica:

Tasks	Description
1. Installs Informatica.	Installs Informatica directories and files into the new Directory.
2. Copies infa_shared directory.	Copies the contents of the infa_shared directory from the existing installation directory into the new installation Directory.
3. Copies mm_files directory.	Copies the contents of the mm_files directory from the default location in the existing installation directory into the New installation directory.
4. Upgrades the domain.	Upgrades the domain to run version 9.0.1 application Services. The upgrade retains the user and administrator accounts in The domain.
5. Starts Informatica Services.	Starts Informatica Services on the node.

2.Upgrading the application services: After you upgrade the domain and server files, log in to the Administrator Tool and upgrade the application services. The service upgrade wizard provides a list of all application that must be upgraded. It upgrades the services based on the order required by the dependent objects.

3.Upgrading the Informatica client: To upgrade the Informatica client, run the Informatica client installer and Select the upgrade option

Pre-Upgrade Tasks

Before you upgrade the domain and server files, complete the following tasks:

1. Review the prerequisites.

2. Verify the file descriptor settings.

3. Verify the configuration of the environment variables used by the installer.

4. Clear the configuration of environment variables that pertain to previous installations of Informatica.

5. Prepare the domain.

6. Prepare the Power Center repository.

7. Prepare the Power Center Profiling warehouse.

8. Prepare for upgrade from Power Center 8.6.1

• Export Reference Table Manager Data.
• Prepare Metadata Manager.
• Prepare the Data Analyzer repository.

9. Shut down the domain.

Upgrading the Domain and Server in Graphical Mode:

You can upgrade the Informatica domain and server files in graphical mode on Windows or UNIX.

1. Verify that your environment meets the minimum system requirements and complete the pre-upgrade tasks.

2. Log in to the machine with the same user account that you used to install the previous version.

3. Close all other applications.

4. To begin the upgrade on Windows, run install.bat from the root directory.

To begin the upgrade on UNIX, use a shell command line to run install.sh from the root directory, and then Select the option for graphical mode installation.

5. In the Installation Type window, select Upgrade to Informatica 9.0.1 and click next.

• The Upgrade Pre-Requisites window displays the upgrade system requirements. Verify that all requirements are met before you continue the upgrade.

6. Click Next.

7. In the Upgrade Directory window, enter the following directories

Directory	Description
1.Directory of the Informatica Product to upgrade.	Directory that contains the previous version of Power Center that you want to upgrade.
2.Directory for Informatica 9.0.1	Directory in which to install Informatica 9.0.1. Enter the absolute path for the installation directory. The directory cannot be the same as the directory that contains the previous version of Power Center. The directory names in the path must not contain spaces or the following special characters: @|* $ # ! % ( ) { } [ ] , ; ' On Windows, the installation directory must be on the current machine.

Click Next.

The upgrade wizard displays a warning to shut down the Informatica domain before you continue the upgrade.

9. Click OK.

10. In the Pre-Installation Summary window, review the upgrade information, and click Install to continue.

The upgrade wizard installs the Informatica server files to the Informatica 9.0.1 installation directory.

11. In the Domain Configuration Upgrade window, the upgrade wizard displays the database and user account information for the domain configuration repository to be upgraded.

Property	Description
Database type	Database for the domain configuration repository.
Database user ID	Database user account for the domain configuration repository.
User password	Password for the database user account.
Tablespace	Displayed for IBM DB2 only. Name of the tablespace for the upgraded domain configuration repository tables. If the database of the domain configuration repository that you are upgrading does not use a 32 K tablespace, this property is blank. Enter the name of a tablespace with a page size of 32 K. In a single-partition database, if you do not specify a tablespace name, the installer writes the upgraded tables in the default tablespace. The default tablespace must be 32 K. In a multi-partition database, you must specify a 32 K tablespace.

The upgrade wizard displays the database connection string for the domain configuration repository based on how the connection string of the previous version was created at installation:

• If the previous version used a JDBC URL at installation, the upgrade wizard displays the JDBC connection properties, including the database address and service name.
• If the previous version used a custom JDBC connection string at installation, the upgrade wizard displays the custom connection string.
• Optionally, you can specify additional JDBC parameters to include in the connection string. To provide Additional JDBC parameters, select JDBC parameters and enter a valid JDBC parameter string.

12. Click Test Connection to verify that you can connect to the database, and then click OK to continue.

13. Click Next.

On the Port Configuration Upgrade window, the upgrade wizard displays the default port numbers assigned to the domain and node components.

14. You can specify new port numbers or use the default port numbers.

The following table describes the ports that you can specify:

Port	Description
Service Manager port	Port number used by the Service Manager in the node. Client applications and the Informatica command line programs use this port to communicate to the services in the domain.
Informatica Administrator port	Port number used by the Administrator tool. Available if you upgrade a gateway node.
Informatica Administrator shutdown port	Port number used by the Administrator tool to listen for shut down commands. Available if you upgrade a gateway node.

15. Click Next.

On Windows, the upgrade wizard creates a service to start Informatica. By default, the service runs under the same user account as the account used for installation. You can run the Windows service under a different User account.

16. Select whether to run the Windows service under a different user account.

The following table describes the properties that you set:

Property	Description
Run Informatica under a different user account	Indicates whether to run the Windows service under a different user account.
User name	User account with which to run the Informatica Windows service. Use the following format: DomainName\UserAccount This user account must have the Act as operating system permission.
Password	Password for the user account with which to run the Informatica Windows service.

17. Click Next.

The Post-Upgrade Summary window indicates whether the upgrade completed successfully.

18. Click Done.

Upgrade the Application Services:

1. Configure Informatica Environment Variables

You can configure the INFA_JAVA_OPTS, INFA_DOMAINS_FILE, and INFA_HOME environment variables to store memory, domain, and location settings.

(i) INFA_JAVA_OPTS

For example, to configure 1 GB of system memory for the Informatica daemon on UNIX in a C shells.

setenv INFA_JAVA_OPTS “-Xmx1024m”

(ii) INFA_DOMAINS_FILE

Set the value of the INFA_DOMAINS_FILE variable to the path and file name of the domains.infa file. If you configure the INFA_DOMAINS_FILE variable, you can run infacmd and pmcmd from a directory other than /server/bin.

Configure the INFA_DOMAINS_FILE variable on the machine where you install the Informatica services. On Windows, configureINFA_DOMAINS_FILE as a system variable.

(iii) INFA_HOME

Use a softlink in UNIX for any of the Informatica directories. To configure INFA_HOME so that any Informatica application or service can locate the other Informatica components it needs to run, set INFA_HOME to the location of the Informatica installation directory.

2. Configure Locale Environment Variables

Use the following command to verify that the value for the locale environment variable is compatible with the Language settings for the machine and the type of code page you want to use for the repository:

Eg : locale –a

Locale for Oracle Database Clients

if the value is american_america.UTF8, set the variable in a C shell with the following command:

setenv NLS_LANG american_america.UTF8

Service Upgrade:

Use the service upgrade wizard to upgrade services.

1. In the Informatica Administrator header area click Manage > Upgrade.

2. Select the objects to upgrade.

3. Click Next.

4. If dependency errors exist, the Dependency Errors dialog box appears. Review the dependency errors and click OK. Then, resolve dependency errors and click next.

5. Enter the repository login information. Optionally, choose to use the same login information for all Repositories.

6. Click Next.

The service upgrade wizard upgrades each service and displays the status and processing details.

7. When the upgrade completes, the Summary section displays the list of services and their upgrade status.

Click each service to view the upgrade details in the Service Details section.

8. Optionally, click Save Report to save the upgrade details to a file.

If you choose not to save the report, you can click Save Previous Report the next time you launch the Service upgrade wizard.

9. Click Close.

10. Restart upgraded services.

After you upgrade the PowerCenter Repository Service, you must restart the service and its dependent Services.

Informatica Client Upgrade

1. Close all applications.

2. Run install.bat from the root directory.

The Upgrade Pre-Requisites window displays the system requirements. Verify that all installation Requirements are met before you continue the installation.

3. Click Next.

On the Select Component window, select the Informatica client you want to upgrade.

You can upgrade the following Informatica client applications:

• Informatica Developer
• PowerCenter Client

If both Informatica Developer and PowerCenter Client are installed on the machine, you can upgrade the tools in the same process.

4. On the Upgrade Directory window, enter the following directories:

Directory	Description
Directory of the Informatica client to upgrade	Directory that contains the previous version of the Informatica client tool that you want to upgrade
Directory for Informatica 9.0.1 client tools	Directory in which to install the Informatica 9.0.1 client tools Enter the absolute path for the installation directory. The installation directory must be on the current machine. The directory names in the path must not contain spaces or the following special characters: @|* $ # ! % ( ) { } [ ] , ; '

5. Click Next.

6. On the Pre-Installation Summary window, review the installation information, and click Install.

The installer copies the Informatica client files to the installation directory.

The Post-installation Summary window indicates whether the upgrade completed successfully.

7. Click Done.

Post Upgrade Tasks:

Informatica Domain

• Configure LDAP Connectivity.
• Update the Log Events Directory.
• Update ODBC Data Sources.
• Update Statistics for the Domain Configuration Repository.
• View Log Events from the Previous Informatica Version.

Metadata Manager Service

• Reload Metadata Manager Resources
• Update the Metadata Manager Properties File
• Reference Table Manager

For detailed study on version Upgrade, you may go through Documents from informatica corporation on upgrade.

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

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 1998

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

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.

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?

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.