Complete reference to Informatica

Informatica Version Upgrade

noreply@blogger.com (Naveen) — Mon, 27 Feb 2012 07:35:00 +0000

Informatica Upgrade Process:

Stages across upgrade can be categorized as below:

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

noreply@blogger.com (Naveen) — Thu, 18 Aug 2011 05:04:00 +0000

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

# Get service properties

pmcmd getserviceproperties -sv testService -d testDomain

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

# handle error

echo "Could not get service properties"

exit

# 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

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

noreply@blogger.com (Naveen) — Sun, 31 Jul 2011 18:27:00 +0000

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

NULL

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

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

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

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

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

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.

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

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

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

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

SALARY

50,000.00

20,000.00

RETURN VALUE

50,000

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

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)

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)

0 (False) Empty string

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.

noreply@blogger.com (Naveen) — Tue, 07 Jun 2011 12:58:00 +0000

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.

noreply@blogger.com (Naveen) — Tue, 07 Jun 2011 12:36:00 +0000

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

noreply@blogger.com (Naveen) — Thu, 26 May 2011 05:21:00 +0000

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.

noreply@blogger.com (Naveen) — Tue, 24 May 2011 08:01:00 +0000

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.

Data warehousing Concepts Based Interview Questions

noreply@blogger.com (Naveen) — Tue, 24 May 2011 06:07:00 +0000

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

noreply@blogger.com (Naveen) — Mon, 23 May 2011 11:32:00 +0000

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?

Informatica Experienced Interview Questions - Part2

noreply@blogger.com (Naveen) — Fri, 20 May 2011 05:54:00 +0000

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.

noreply@blogger.com (Naveen) — Thu, 19 May 2011 07:06:00 +0000

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)

O/P :

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.

Informatica Experienced Interview Questions – part 1

noreply@blogger.com (Naveen) — Wed, 18 May 2011 07:01:00 +0000

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

Informatica Real time Interview Questions

noreply@blogger.com (Naveen) — Wed, 18 May 2011 06:57:00 +0000

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.

Explain your Project?
What are your Daily routines?
How many mapping have you created all together in your project?
In which account does your Project Fall?
What is your Reporting Hierarchy?
How many Complex Mapping’s have you created? Could you please me the situation for which you have developed that Complex mapping?
What is your Involvement in Performance tuning of your Project?
What is the Schema of your Project? And why did you opt for that particular schema?
What are your Roles in this project?
Can I have one situation which you have adopted by which performance has improved dramatically?
Where you Involved in more than two projects simultaneously?
Do you have any experience in the Production support?
What kinds of Testing have you done on your Project (Unit or Integration or System or UAT)? And Enhancement’s were done after testing?
How many Dimension Table are there in your Project and how are they linked to the fact table?
How do we do the Fact Load?
How did you implement CDC in your project?
How does your Mapping in File to Load look like?
How does your Mapping in Load to Stage look like?
How does your Mapping in Stage to ODS look like?
What is the size of your Data warehouse?
What is your Daily feed size and weekly feed size?
Which Approach (Top down or Bottom Up) was used in building your project?
How do you access your source’s (are they Flat files or Relational)?
Have you developed any Stored Procedure or triggers in this project? How did you use them and in which situation?
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?
What is the biggest Challenge that you encountered in this project?
What is the scheduler tool you have used in this project? How did you schedule jobs using it?

Working with XML files

noreply@blogger.com (Naveen) — Wed, 27 Apr 2011 18:08:00 +0000

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.

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

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.

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

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.

Used when we need to extract data from relational sources and passes XML data to targets.

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

Stage 1 (Oracle to XML)

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.

Fig a: Importing the XML definition

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

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

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.

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

Step 3: Create task and the work flow.

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)

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.

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.

Step 4: Preview the output on the target table.

SCD Type 3

noreply@blogger.com (Naveen) — Tue, 26 Apr 2011 09:54:00 +0000

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

1000

2000

3000

Target Table: (01-01-2011)

Empno

Ename

C-sal

P-sal

101

102

103

1000

2000

3000

Source Table: (01-02-2011)

Empno

Ename

Sal

101

102

103

1000

4566

3000

Target Table (01-02-2011):

Empno

Ename

C-sal

P-sal

101

102

103

102

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.

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)

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.

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.

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.

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.

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

Mapping Templates

noreply@blogger.com (Naveen) — Mon, 25 Apr 2011 10:06:00 +0000

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

noreply@blogger.com (Naveen) — Mon, 25 Apr 2011 07:23:00 +0000

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.

Workflow Variables

noreply@blogger.com (Naveen) — Mon, 25 Apr 2011 07:11:00 +0000

Workflow Variables Overview:

You can create and use variables in a workflow to reference values and record information. For example, use a Variable in a Decision task to determine whether the previous task ran properly. If it did, you can run the next task.

If not, you can stop the workflow. Use the following types of workflow variables:

Predefined workflow variables. The Workflow Manager provides predefined workflow variables for tasks within a workflow.
User-defined workflow variables. You create user-defined workflow variables when you create a workflow. Use workflow variables when you configure the following types of tasks:
Assignment tasks. Use an Assignment task to assign a value to a user-defined workflow variable. For Example, you can increment a user-defined counter variable by setting the variable to its current value plus 1.
Decision tasks. Decision tasks determine how the Integration Service runs a workflow. For example, use the Status variable to run a second session only if the first session completes successfully.
Links. Links connect each workflow task. Use workflow variables in links to create branches in the workflow. For example, after a Decision task, you can create one link to follow when the decision condition evaluates to true, and another link to follow when the decision condition evaluates to false.
Timer tasks. Timer tasks specify when the Integration Service begins to run the next task in the workflow. Use a user-defined date/time variable to specify the time the Integration Service starts to run the next task.

Use the following keywords to write expressions for user-defined and predefined workflow variables:

AND
OR
NOT
TRUE
FALSE
NULL
SYSDATE

Predefined Workflow Variables:

Each workflow contains a set of predefined variables that you use to evaluate workflow and task conditions. Use the following types of predefined variables:

Task-specific variables. The Workflow Manager provides a set of task-specific variables for each task in the workflow. Use task-specific variables in a link condition to control the path the Integration Service takes when running the workflow. The Workflow Manager lists task-specific variables under the task name in the Expression Editor.
Built-in variables. Use built-in variables in a workflow to return run-time or system information such as folder name, Integration Service Name, system date, or workflow start time. The Workflow Manager lists built-in variables under the Built-in node in the Expression Editor.

Task-Specific Variables	Description	Task Types	Data type
Condition	Evaluation result of decision condition expression. If the task fails, the Workflow Manager keeps the condition set to null. Sample syntax: $Dec_TaskStatus.Condition = <TRUE \| FALSE \| NULL \| any integer>	Decision	Integer
End Time	Date and time the associated task ended. Precision is to the second. Sample syntax: $s_item_summary.EndTime > TO_DATE('11/10/2004 08:13:25')	All tasks	Date/Time
ErrorCode	Last error code for the associated task. If there is no error, the Integration Service sets ErrorCode to 0 when the task completes. Sample syntax: $s_item_summary.ErrorCode = 24013. Note: You might use this variable when a task consistently fails with this final error message.	All tasks	Integer
ErrorMsg	Last error message for the associated task.If there is no error, the Integration Service sets ErrorMsg to an empty string when the task completes. Sample syntax: $s_item_summary.ErrorMsg = 'PETL_24013 Session run completed with failure Variables of type Nstring can have a maximum length of 600 characters. Note: You might use this variable when a task consistently fails with this final error message.	All tasks	Nstring
First Error Code	Error code for the first error message in the session. If there is no error, the Integration Service sets FirstErrorCode to 0 when the session completes. Sample syntax: $s_item_summary.FirstErrorCode = 7086	Session	Integer
FirstErrorMsg	First error message in the session.If there is no error, the Integration Service sets FirstErrorMsg to an empty string when the task completes. Sample syntax: $s_item_summary.FirstErrorMsg = 'TE_7086 Tscrubber: Debug info… Failed to evalWrapUp'Variables of type Nstring can have a maximum length of 600 characters.	Session	Nstring
PrevTaskStatus	Status of the previous task in the workflow that the Integration Service ran. Statuses include: 1.ABORTED 2.FAILED 3.STOPPED 4.SUCCEEDED Use these key words when writing expressions to evaluate the status of the previous task. Sample syntax: $Dec_TaskStatus.PrevTaskStatus = FAILED	All Tasks	Integer
SrcFailedRows	Total number of rows the Integration Service failed to read from the source. Sample syntax: $s_dist_loc.SrcFailedRows = 0	Session	Integer
SrcSuccessRows	Total number of rows successfully read from the sources. Sample syntax: $s_dist_loc.SrcSuccessRows > 2500	Session	Integer
StartTime	Date and time the associated task started. Precision is to the second. Sample syntax: $s_item_summary.StartTime > TO_DATE('11/10/2004 08:13:25')	All Task	Date/Time
Status	Status of the previous task in the workflow. Statuses include: - ABORTED - DISABLED - FAILED - NOTSTARTED - STARTED - STOPPED - SUCCEEDED Use these key words when writing expressions to evaluate the status of the current task. Sample syntax: $s_dist_loc.Status = SUCCEEDED	All Task	Integer
TgtFailedRows	Total number of rows the Integration Service failed to write to the target. Sample syntax: $s_dist_loc.TgtFailedRows = 0	Session	Integer
TgtSuccessRows	Total number of rows successfully written to the target. Sample syntax: $s_dist_loc.TgtSuccessRows > 0	Session	Integer
TotalTransErrors	Total number of transformation errors. Sample syntax: $s_dist_loc.TotalTransErrors = 5	Session	Integer

User-Defined Workflow Variables:

You can create variables within a workflow. When you create a variable in a workflow, it is valid only in that workflow. Use the variable in tasks within that workflow. You can edit and delete user-defined workflow variables.

Use user-defined variables when you need to make a workflow decision based on criteria you specify. For example, you create a workflow to load data to an orders database nightly. You also need to load a subset of this data to headquarters periodically, every tenth time you update the local orders database. Create separate sessions to update the local database and the one at headquarters.

Use a user-defined variable to determine when to run the session that updates the orders database at headquarters.

To configure user-defined workflow variables, complete the following steps:

1. Create a persistent workflow variable, $$WorkflowCount, to represent the number of times the workflow has run.

2. Add a Start task and both sessions to the workflow.

3. Place a Decision task after the session that updates the local orders database.Set up the decision condition to check to see if the number of workflow runs is evenly divisible by 10. Use the modulus (MOD) function to do this.

4. Create an Assignment task to increment the $$WorkflowCount variable by one.

5. Link the Decision task to the session that updates the database at headquarters when the decision condition evaluates to true. Link it to the Assignment task when the decision condition evaluates to false. When you configure workflow variables using conditions, the session that updates the local database runs every time the workflow runs. The session that updates the database at headquarters runs every 10th time the workflow runs.

Creating User-Defined Workflow Variables :

You can create workflow variables for a workflow in the workflow properties.

To create a workflow variable:

1. In the Workflow Designer, create a new workflow or edit an existing one.

2. Select the Variables tab.

3. Click Add.

4. Enter the information in the following table and click OK:

Field	Description
Name	Variable name. The correct format is $$VariableName. Workflow variable names are not case sensitive. Do not use a single dollar sign ($) for a user-defined workflow variable. The single dollar sign is reserved for predefined workflow variables
Data type	Data type of the variable. You can select from the following data types: - Date/Time - Double - Integer - Nstring
Persistent	Whether the variable is persistent. Enable this option if you want the value of the variable retained from one execution of the workflow to the next.
Default Value	Default value of the variable. The Integration Service uses this value for the variable during sessions if you do not set a value for the variable in the parameter file and there is no value stored in the repository. Variables of type Date/Time can have the following formats: - MM/DD/RR - MM/DD/YYYY - MM/DD/RR HH24:MI - MM/DD/YYYY HH24:MI - MM/DD/RR HH24:MI:SS - MM/DD/YYYY HH24:MI:SS - MM/DD/RR HH24:MI:SS.MS - MM/DD/YYYY HH24:MI:SS.MS - MM/DD/RR HH24:MI:SS.US - MM/DD/YYYY HH24:MI:SS.US - MM/DD/RR HH24:MI:SS.NS - MM/DD/YYYY HH24:MI:SS.NS You can use the following separators: dash (-), slash (/), backslash (\), colon (:), period (.), and space. The Integration Service ignores extra spaces. You cannot use one- or three-digit values for year or the “HH12” format for hour. Variables of type Nstring can have a maximum length of 600 characters.
Is Null	Whether the default value of the variable is null. If the default value is null, enable this option.
Description	Description associated with the variable.

5. To validate the default value of the new workflow variable, click the Validate button.

6. Click Apply to save the new workflow variable.

7. Click OK.

Constraint-Based Loading:

noreply@blogger.com (Naveen) — Fri, 22 Apr 2011 07:02:00 +0000

Constraint-Based Loading:

In the Workflow Manager, you can specify constraint-based loading for a session. When you select this option, the Integration Service orders the target load on a row-by-row basis. For every row generated by an active source, the Integration Service loads the corresponding transformed row first to the primary key table, then to any foreign key tables. Constraint-based loading depends on the following requirements:

Active source. Related target tables must have the same active source.
Key relationships. Target tables must have key relationships.
Target connection groups. Targets must be in one target connection group.
Treat rows as insert. Use this option when you insert into the target. You cannot use updates with constraint based loading.

Active Source:

When target tables receive rows from different active sources, the Integration Service reverts to normal loading for those tables, but loads all other targets in the session using constraint-based loading when possible. For example, a mapping contains three distinct pipelines. The first two contain a source, source qualifier, and target. Since these two targets receive data from different active sources, the Integration Service reverts to normal loading for both targets. The third pipeline contains a source, Normalizer, and two targets. Since these two targets share a single active source (the Normalizer), the Integration Service performs constraint-based loading: loading the primary key table first, then the foreign key table.

Key Relationships:

When target tables have no key relationships, the Integration Service does not perform constraint-based loading.

Similarly, when target tables have circular key relationships, the Integration Service reverts to a normal load. For example, you have one target containing a primary key and a foreign key related to the primary key in a second target. The second target also contains a foreign key that references the primary key in the first target. The Integration Service cannot enforce constraint-based loading for these tables. It reverts to a normal load.

Target Connection Groups:

The Integration Service enforces constraint-based loading for targets in the same target connection group. If you want to specify constraint-based loading for multiple targets that receive data from the same active source, you must verify the tables are in the same target connection group. If the tables with the primary key-foreign key relationship are in different target connection groups, the Integration Service cannot enforce constraint-based loading when you run the workflow. To verify that all targets are in the same target connection group, complete the following tasks:

Verify all targets are in the same target load order group and receive data from the same active source.
Use the default partition properties and do not add partitions or partition points.
Define the same target type for all targets in the session properties.
Define the same database connection name for all targets in the session properties.
Choose normal mode for the target load type for all targets in the session properties.

Treat Rows as Insert:

Use constraint-based loading when the session option Treat Source Rows As is set to insert. You might get inconsistent data if you select a different Treat Source Rows As option and you configure the session for constraint-based loading.

When the mapping contains Update Strategy transformations and you need to load data to a primary key table first, split the mapping using one of the following options:

Load primary key table in one mapping and dependent tables in another mapping. Use constraint-based loading to load the primary table.
Perform inserts in one mapping and updates in another mapping.

Constraint-based loading does not affect the target load ordering of the mapping. Target load ordering defines the order the Integration Service reads the sources in each target load order group in the mapping. A target load order group is a collection of source qualifiers, transformations, and targets linked together in a mapping. Constraint based loading establishes the order in which the Integration Service loads individual targets within a set of targets receiving data from a single source qualifier.

Example

The following mapping is configured to perform constraint-based loading:

In the first pipeline, target T_1 has a primary key, T_2 and T_3 contain foreign keys referencing the T1 primary key. T_3 has a primary key that T_4 references as a foreign key.

Since these tables receive records from a single active source, SQ_A, the Integration Service loads rows to the target in the following order:

1. T_1

2. T_2 and T_3 (in no particular order)

3. T_4

The Integration Service loads T_1 first because it has no foreign key dependencies and contains a primary key referenced by T_2 and T_3. The Integration Service then loads T_2 and T_3, but since T_2 and T_3 have no dependencies, they are not loaded in any particular order. The Integration Service loads T_4 last, because it has a foreign key that references a primary key in T_3.After loading the first set of targets, the Integration Service begins reading source B. If there are no key relationships between T_5 and T_6, the Integration Service reverts to a normal load for both targets.

If T_6 has a foreign key that references a primary key in T_5, since T_5 and T_6 receive data from a single active source, the Aggregator AGGTRANS, the Integration Service loads rows to the tables in the following order:

T_1, T_2, T_3, and T_4 are in one target connection group if you use the same database connection for each target, and you use the default partition properties. T_5 and T_6 are in another target connection group together if you use the same database connection for each target and you use the default partition properties. The Integration Service includes T_5 and T_6 in a different target connection group because they are in a different target load order group from the first four targets.

Enabling Constraint-Based Loading:

When you enable constraint-based loading, the Integration Service orders the target load on a row-by-row basis. To enable constraint-based loading:

In the General Options settings of the Properties tab, choose Insert for the Treat Source Rows As property.
Click the Config Object tab. In the Advanced settings, select Constraint Based Load Ordering.
Click OK.

Target Load Order

noreply@blogger.com (Naveen) — Fri, 22 Apr 2011 06:57:00 +0000

Target Load Plan

When you use a mapplet in a mapping, the Mapping Designer lets you set the target load plan for sources within the mapplet.

Setting the Target Load Order

You can configure the target load order for a mapping containing any type of target definition. In the Designer, you can set the order in which the Integration Service sends rows to targets in different target load order groups in a mapping. A target load order group is the collection of source qualifiers, transformations, and targets linked together in a mapping. You can set the target load order if you want to maintain referential integrity when inserting, deleting, or updating tables that have the primary key and foreign key constraints.

The Integration Service reads sources in a target load order group concurrently, and it processes target load order groups sequentially.

To specify the order in which the Integration Service sends data to targets, create one source qualifier for each target within a mapping. To set the target load order, you then determine in which order the Integration Service reads each source in the mapping.

The following figure shows two target load order groups in one mapping:

In this mapping, the first target load order group includes ITEMS, SQ_ITEMS, and T_ITEMS. The second target load order group includes all other objects in the mapping, including the TOTAL_ORDERS target. The Integration Service processes the first target load order group, and then the second target load order group.

When it processes the second target load order group, it reads data from both sources at the same time.

To set the target load order:

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

Type 2

noreply@blogger.com (Naveen) — Thu, 21 Apr 2011 03:02:00 +0000

SCD 2 (Complete):

Let us drive the point home using a simple scenario. For eg., in the current month ie.,(01-01-2010) we are provided with an source table with the three columns and three rows in it like (EMpno,Ename,Sal). There is a new employee added and one change in the records in the month (01-02-2010). We are gonna use the SCD-2 style to extract and load the records in to target table.

The thing to be noticed here is if there is any update in the salary of any employee then the history of that employee is displayed with the current date as the start date and the previous date as the end date.

Source Table: (01-01-11)

Emp no	Ename	Sal
101	A	1000
102	B	2000
103	C	3000

Target Table: (01-01-11)

Skey	Emp no	Ename	Sal	S-date	E-date	Ver	Flag
100	101	A	1000	01-01-10	Null	1	1
200	102	B	2000	01-01-10	Null	1	1
300	103	C	3000	01-01-10	Null	1	1

Source Table: (01-02-11)

Emp no	Ename	Sal
101	A	1000
102	B	2500
103	C	3000
104	D	4000

Target Table: (01-02-11)

Skey	Emp no	Ename	Sal	S-date	E-date	Ver	Flag
100	101	A	1000	01-02-10	Null	1	1
200	102	B	2000	01-02-10	Null	1	1
300	103	C	3000	01-02-10	Null	1	1
201	102	B	2500	01-02-10	01-01-10	2	0
400	104	D	4000	01-02-10	Null	1	1

In the second Month we have one more employee added up to the table with the Ename D and salary of the Employee is changed to the 2500 instead of 2000.

Step 1: Is to import Source Table and Target table.

Create a table by name emp_source with three columns as shown above in oracle.
Import the source from the source analyzer.
Drag the Target table twice on to the mapping designer to facilitate insert or update process.
Go to the targets Menu and click on generate and execute to confirm the creation of the target tables.
The snap shot of the connections using different kinds of transformations are shown below.

In The Target Table we are goanna add five columns (Skey, Version, Flag, S_date ,E_Date).

Step 2: Design the mapping and apply the necessary transformation.

Here in this transformation we are about to use four kinds of transformations namely Lookup transformation (1), Expression Transformation (3), Filter Transformation (2), Sequence Generator. Necessity and the usage of all the transformations will be discussed in detail below.

Look up Transformation: The purpose of this transformation is to Lookup on the target table and to compare the same with the Source using the Lookup Condition.

The first thing that we are gonna do is to create a look up transformation and connect the Empno from the source qualifier to the transformation.
The snapshot of choosing the Target table is shown below.

Drag the Empno column from the Source Qualifier to the Lookup Transformation.
The Input Port for only the Empno1 should be checked.
In the Properties tab (i) Lookup table name ->Emp_Target.

(ii)Look up Policy on Multiple Mismatch -> use Last Value.

(iii) Connection Information ->Oracle.

In the Conditions tab (i) Click on Add a new condition

(ii)Lookup Table Column should be Empno, Transformation port should be Empno1 and Operator should ‘=’.

Expression Transformation: After we are done with the Lookup Transformation we are using an expression transformation to find whether the data on the source table matches with the target table. We specify the condition here whether to insert or to update the table. The steps to create an Expression Transformation are shown below.

Drag all the columns from both the source and the look up transformation and drop them all on to the Expression transformation.
Now double click on the Transformation and go to the Ports tab and create two new columns and name it as insert and update. Both these columns are goanna be our output data so we need to have unchecked input check box.
The Snap shot for the Edit transformation window is shown below.

The condition that we want to parse through our output data are listed below.

Insert : IsNull(EmpNO1)

Update: iif(Not isnull (Skey) and Decode(SAL,SAL1,1,0)=0,1,0) .

We are all done here .Click on apply and then OK.

Filter Transformation: We need two filter transformations the purpose the first filter is to filter out the records which we are goanna insert and the next is vice versa.

If there is no change in input data then filter transformation 1 forwards the complete input to Exp 1 and same output is goanna appear in the target table.
If there is any change in input data then filter transformation 2 forwards the complete input to the Exp 2 then it is gonna forward the updated input to the target table.
Go to the Properties tab on the Edit transformation

(i) The value for the filter condition 1 is Insert.

(ii) The value for the filter condition 2 is Update.

The closer view of the connections from the expression to the filter is shown below.

Sequence Generator: We use this to generate an incremental cycle of sequential range of number.The purpose of this in our mapping is to increment the skey in the bandwidth of 100.

We are gonna have a sequence generator and the purpose of the sequence generator is to increment the values of the skey in the multiples of 100 (bandwidth of 100).
Connect the output of the sequence transformation to the Exp 1.

Expression Transformation:

Exp 1: It updates the target table with the skey values. Point to be noticed here is skey gets multiplied by 100 and a new row is generated if there is any new EMP added to the list. Else the there is no modification done on the target table.

Drag all the columns from the filter 1 to the Exp 1.
Now add a new column as N_skey and the expression for it is gonna be Nextval1*100.
We are goanna make the s-date as the o/p and the expression for it is sysdate.
Flag is also made as output and expression parsed through it is 1.
Version is also made as output and expression parsed through it is 1.

Exp 2: If same employee is found with any updates in his records then Skey gets added by 1 and version changes to the next higher number,F

Drag all the columns from the filter 2 to the Exp 2.
Now add a new column as N_skey and the expression for it is gonna be Skey+1.
Both the S_date and E_date is gonna be sysdate.

Exp 3: If any record of in the source table gets updated then we make it only as the output.

If change is found then we are gonna update the E_Date to S_Date.

Update Strategy: This is place from where the update instruction is set on the target table.

The update strategy expression is set to 1.

Step 3: Create the task and Run the work flow.

Don’t check the truncate table option.
Change Bulk to the Normal.
Run the work flow from task.
Create the task and run the work flow.

Step 4: Preview the Output in the target table.

Update Strategy Transformation

noreply@blogger.com (Naveen) — Thu, 21 Apr 2011 02:54:00 +0000

Active and Connected Transformation

Till now, we have only inserted rows in our target tables. What if we want to update, delete or reject rows coming from source based on some condition?

Example: If Address of a CUSTOMER changes, we can update the old address or keep both old and new address. One row is for old and one for new. This way we maintain the historical data.

Update Strategy is used with Lookup Transformation. In DWH, we create a Lookup on target table to determine whether a row already exists or not. Then we insert, update, delete or reject the source record as per business need.

In Power Center, we set the update strategy at two different levels:

Within a session
Within a Mapping

1. Update Strategy within a session:

When we configure a session, we can instruct the IS to either treat all rows in the same way or use instructions coded into the session mapping to flag rows for different database operations.

Session Configuration:

Edit Session -> Properties -> Treat Source Rows as: (Insert, Update, Delete, and Data Driven). Insert is default. Specifying Operations for Individual Target Tables:

You can set the following update strategy options:

Insert: Select this option to insert a row into a target table.

Delete: Select this option to delete a row from a table.

Update: We have the following options in this situation:

Update as Update. Update each row flagged for update if it exists in the target table.
Update as Insert. Inset each row flagged for update.
Update else Insert. Update the row if it exists. Otherwise, insert it.

Truncate table: Select this option to truncate the target table before loading data.

2. Flagging Rows within a Mapping

Within a mapping, we use the Update Strategy transformation to flag rows for insert, delete, update, or reject.

Operation	Constant	Numeric Value
INSERT	DD_INSERT	0
UPDATE	DD_UPDATE	1
DELETE	DD_DELETE	2
REJECT	DD_REJECT	3

Update Strategy Expressions:

Frequently, the update strategy expression uses the IIF or DECODE function from the transformation language to test each row to see if it meets a particular condition.

IIF( ( ENTRY_DATE > APPLY_DATE), DD_REJECT, DD_UPDATE )

IIF( ( ENTRY_DATE > APPLY_DATE), 3, 2 )

The above expression is written in Properties Tab of Update Strategy T/f.
DD means DATA DRIVEN

Forwarding Rejected Rows:

We can configure the Update Strategy transformation to either pass rejected rows to the next transformation or drop them.

Steps:

Create Update Strategy Transformation
Pass all ports needed to it.
Set the Expression in Properties Tab.
Connect to other transformations or target.

Performance tuning:

Use Update Strategy transformation as less as possible in the mapping.
Do not use update strategy transformation if we just want to insert into target table, instead use direct mapping, direct filtering etc.
For updating or deleting rows from the target table we can use Update Strategy transformation itself.

UNION TRANSFORMATION

noreply@blogger.com (Naveen) — Thu, 21 Apr 2011 02:43:00 +0000

Active and Connected transformation.

Union transformation is a multiple input group transformation that you can use to merge data from multiple pipelines or pipeline branches into one pipeline branch. It merges data from multiple sources similar to the UNION ALL SQL statement to Combine the results from two or more SQL statements.

Union Transformation Rules and Guidelines

we can create multiple input groups, but only one output group.
we can connect heterogeneous sources to a Union transformation.
all input groups and the output group must have matching ports. The Precision, data type, and scale must be identical across all groups.
The Union transformation does not remove duplicate rows. To remove Duplicate rows, we must add another transformation such as a Router or Filter Transformation.
we cannot use a Sequence Generator or Update Strategy transformation upstream from a Union transformation.

Union Transformation Components

When we configure a Union transformation, define the following components:

Transformation tab: We can rename the transformation and add a description.

Properties tab: We can specify the tracing level.

Groups tab: We can create and delete input groups. The Designer displays groups we create on the Ports tab.

Group Ports tab: We can create and delete ports for the input groups. The Designer displays ports we create on the Ports tab.

We cannot modify the Ports, Initialization Properties, Metadata Extensions, or Port Attribute Definitions tabs in a Union transformation.

Create input groups on the Groups tab, and create ports on the Group Ports tab. We can create one or more input groups on the Groups tab. The Designer creates one output group by default. We cannot edit or delete the default output group.

Example: to combine data of tables EMP_10, EMP_20 and EMP_REST

Import tables EMP_10, EMP_20 and EMP_REST in shared folder in Sources.
Create a target table EMP_UNION_EXAMPLE in target designer. Structure should be same EMP table.
Create the shortcuts in your folder.

Creating Mapping:

Open folder where we want to create the mapping.
Click Tools -> Mapping Designer.
Click Mapping-> Create-> Give mapping name. Ex: m_union_example
Drag EMP_10, EMP_20 and EMP_REST from source in mapping.
Click Transformation -> Create -> Select Union from list. Give name and click Create. Now click done.
Pass ports from SQ_EMP_10 to Union Transformation.
Edit Union Transformation. Go to Groups Tab
One group will be already there as we dragged ports from SQ_DEPT_10 to Union Transformation.
As we have 3 source tables, we 3 need 3 input groups. Click add button to add 2 more groups. See Sample Mapping
We can also modify ports in ports tab.
Click Apply -> Ok.
Drag target table now.
Connect the output ports from Union to target table.
Click Mapping -> Validate
Repository -> Save

Create Session and Workflow as described earlier. Run the Workflow and see the data in target table.
Make sure to give connection information for all 3 source Tables.

Sample mapping picture

Transaction Control:

noreply@blogger.com (Naveen) — Thu, 21 Apr 2011 02:36:00 +0000

Power Center lets you control commit and roll back transactions based on a set of rows that pass through a Transaction Control transformation. A transaction is the set of rows bound by commit or roll back rows. You can define a transaction based on a varying number of input rows. You might want to define transactions based on a group of rows ordered on a common key, such as employee ID or order entry date.

In Power Center, you define transaction control at the following levels:

Within a mapping. Within a mapping, you use the Transaction Control transformation to define a transaction. You define transactions using an expression in a Transaction Control transformation. Based on the return value of the expression, you can choose to commit, roll back, or continue without any transaction changes.
Within a session. When you configure a session, you configure it for user-defined commit. You can choose to commit or roll back a transaction if the Integration Service fails to transform or write any row to the target.

When you run the session, the Integration Service evaluates the expression for each row that enters the transformation. When it evaluates a commit row, it commits all rows in the transaction to the target or targets. When the Integration Service evaluates a roll back row, it rolls back all rows in the transaction from the target or targets. If the mapping has a flat file target you can generate an output file each time the Integration Service starts a new transaction. You can dynamically name each target flat file.

Properties Tab

On the Properties tab, you can configure the following properties:

Transaction control expression
Tracing level

Enter the transaction control expression in the Transaction Control Condition field. The transaction control expression uses the IIF function to test each row against the condition. Use the following syntax for the expression:

IIF (condition, value1, value2)

The expression contains values that represent actions the Integration Service performs based on the return value of the condition. The Integration Service evaluates the condition on a row-by-row basis. The return value determines whether the Integration Service commits, rolls back, or makes no transaction changes to the row.

When the Integration Service issues a commit or roll back based on the return value of the expression, it begins a new transaction. Use the following built-in variables in the Expression Editor when you create a transaction control expression:

TC_CONTINUE_TRANSACTION. The Integration Service does not perform any transaction change for this row. This is the default value of the expression.
TC_COMMIT_BEFORE. The Integration Service commits the transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
TC_COMMIT_AFTER. The Integration Service writes the current row to the target, commits the transaction, and begins a new transaction. The current row is in the committed transaction.
TC_ROLLBACK_BEFORE. The Integration Service rolls back the current transaction, begins a new transaction, and writes the current row to the target. The current row is in the new transaction.
TC_ROLLBACK_AFTER. The Integration Service writes the current row to the target, rolls back the transaction, and begins a new transaction. The current row is in the rolled back transaction.

If the transaction control expression evaluates to a value other than commit, roll back, or continue, the Integration Service fails the session.

Mapping Guidelines and Validation

Use the following rules and guidelines when you create a mapping with a Transaction Control transformation:

If the mapping includes an XML target, and you choose to append or create a new document on commit, the input groups must receive data from the same transaction control point.
Transaction Control transformations connected to any target other than relational, XML, or dynamic MQSeries targets are ineffective for those targets.
You must connect each target instance to a Transaction Control transformation.
You can connect multiple targets to a single Transaction Control transformation.
You can connect only one effective Transaction Control transformation to a target.
You cannot place a Transaction Control transformation in a pipeline branch that starts with a Sequence Generator transformation.
If you use a dynamic Lookup transformation and a Transaction Control transformation in the same mapping, a rolled-back transaction might result in unsynchronized target data.
A Transaction Control transformation may be effective for one target and ineffective for another target. If each target is connected to an effective Transaction Control transformation, the mapping is valid.
Either all targets or none of the targets in the mapping should be connected to an effective Transaction Control transformation.

Example to Transaction Control:

Step 1: Design the mapping.

Step 2: Creating a Transaction Control Transformation.

In the Mapping Designer, click Transformation > Create. Select the Transaction Control transformation.
Enter a name for the transformation.[ The naming convention for Transaction Control transformations is TC_TransformationName].
Enter a description for the transformation.
Click Create.
Click Done.
Drag the ports into the transformation.
Open the Edit Transformations dialog box, and select the Ports tab.

Select the Properties tab. Enter the transaction control expression that defines the commit and roll back behavior.

Go to the Properties tab and click on the down arrow to get in to the expression editor window. Later go to the Variables tab and Type IIF(EMpno=7654,) select the below things from the built in functions.

IIF (EMPNO=7654,TC_COMMIT_BEFORE,TC_CONTINUE_TRANSACTION)

Connect all the columns from the transformation to the target table and save the mapping.
Select the Metadata Extensions tab. Create or edit metadata extensions for the Transaction Control transformation.
Click OK.

Step 3: Create the task and the work flow.

Step 4: Preview the output in the target table.

STORED PROCEDURE T/F

noreply@blogger.com (Naveen) — Thu, 21 Apr 2011 02:32:00 +0000

Passive Transformation
Connected and Unconnected Transformation
Stored procedures are stored and run within the database.

A Stored Procedure transformation is an important tool for populating and Maintaining databases. Database administrators create stored procedures to Automate tasks that are too complicated for standard SQL statements.

Use of Stored Procedure in mapping:

Check the status of a target database before loading data into it.
Determine if enough space exists in a database.
Perform a specialized calculation.
Drop and recreate indexes. Mostly used for this in projects.

Data Passes Between IS and Stored Procedure One of the most useful features of stored procedures is the ability to send data to the stored procedure, and receive data from the stored procedure. There are three types of data that pass between the Integration Service and the stored procedure:

Input/output parameters: Parameters we give as input and the parameters returned from Stored Procedure.

Return values: Value returned by Stored Procedure if any.

Status codes: Status codes provide error handling for the IS during a workflow. The stored procedure issues a status code that notifies whether or not the stored procedure completed successfully. We cannot see this value. The IS uses it to determine whether to continue running the session or stop. Specifying when the Stored Procedure Runs

Normal: The stored procedure runs where the transformation exists in the mapping on a row-by-row basis. We pass some input to procedure and it returns some calculated values. Connected stored procedures run only in normal mode.

Pre-load of the Source: Before the session retrieves data from the source, the stored procedure runs. This is useful for verifying the existence of tables or performing joins of data in a temporary table.

Post-load of the Source: After the session retrieves data from the source, the stored procedure runs. This is useful for removing temporary tables.

Pre-load of the Target: Before the session sends data to the target, the stored procedure runs. This is useful for dropping indexes or disabling constraints.

Post-load of the Target: After the session sends data to the target, the stored procedure runs. This is useful for re-creating indexes on the database.

Using a Stored Procedure in a Mapping :

Create the stored procedure in the database.
Import or create the Stored Procedure transformation.
Determine whether to use the transformation as connected or unconnected.
If connected, map the appropriate input and output ports.
If unconnected, either configure the stored procedure to run pre- or post-session, or configure it to run from an expression in another transformation.
Configure the session.

Stored Procedures:

Connect to Source database and create the stored procedures given below:

CREATE OR REPLACE procedure sp_agg (in_deptno in number, max_sal out number,

min_sal out number, avg_sal out number, sum_sal out number)

Begin

select max(Sal),min(sal),avg(sal),sum(sal) into max_sal,min_sal,avg_sal,sum_sal

from emp where deptno=in_deptno group by deptno;

End;

CREATE OR REPLACE procedure sp_unconn_1_value(in_deptno in number, max_sal out number)

Begin

Select max(Sal) into max_sal from EMP where deptno=in_deptno;

End;

1. Connected Stored Procedure T/F

Example: To give input as DEPTNO from DEPT table and find the MAX, MIN, AVG and SUM of SAL from EMP table.

DEPT will be source table. Create a target table SP_CONN_EXAMPLE with fields DEPTNO, MAX_SAL, MIN_SAL, AVG_SAL & SUM_SAL.
Write Stored Procedure in Database first and Create shortcuts as needed.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_SP_CONN_EXAMPLE

4. Drag DEPT and Target table.

5. Transformation -> Import Stored Procedure -> Give Database Connection -> Connect -> Select the procedure sp_agg from the list.

6. Drag DEPTNO from SQ_DEPT to the stored procedure input port and also to DEPTNO port of target.

7. Connect the ports from procedure to target as shown below:

8. Mapping -> Validate

9. Repository -> Save

Create Session and then workflow.
Give connection information for all tables.
Give connection information for Stored Procedure also.
Run workflow and see the result in table.

2. Unconnected Stored Procedure T/F :

An unconnected Stored Procedure transformation is not directly connected to the flow of data through the mapping. Instead, the stored procedure runs either:

From an expression: Called from an expression transformation.
Pre- or post-session: Runs before or after a session.

Method of returning the value of output parameters to a port:

Assign the output value to a local variable.
Assign the output value to the system variable PROC_RESULT. (See Later)

Example 1: DEPTNO as input and get MAX of Sal as output.

DEPT will be source table.
Create a target table with fields DEPTNO and MAX_SAL of decimal data type.
Write Stored Procedure in Database first and Create shortcuts as needed.

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_sp_unconn_1_value

4. Drag DEPT and Target table.

5. Transformation -> Import Stored Procedure -> Give Database Connection -> Connect -> Select the procedure sp_unconn_1_value from the list. Click OK.

6. Stored Procedure has been imported.

7. T/F -> Create Expression T/F. Pass DEPTNO from SQ_DEPT to Expression T/F.

8. Edit expression and create an output port OUT_MAX_SAL of decimal data type.

9. Open Expression editor and call the stored procedure as below:Click OK and connect the port from expression to target as in mapping below:

10. Mapping -> Validate

11. Repository Save.

Create Session and then workflow.
Give connection information for all tables.
Give connection information for Stored Procedure also.
Run workflow and see the result in table.

PROC_RESULT use:

If the stored procedure returns a single output parameter or a return value, we the reserved variable PROC_RESULT as the output variable.

Example: DEPTNO as Input and MAX Sal as output :

:SP.SP_UNCONN_1_VALUE(DEPTNO,PROC_RESULT)

If the stored procedure returns multiple output parameters, you must create variables for each output parameter.

Example: DEPTNO as Input and MAX_SAL, MIN_SAL, AVG_SAL and SUM_SAL

as output then:

1. Create four variable ports in expression VAR_MAX_SAL,VAR_MIN_SAL, VAR_AVG_SAL and iVAR_SUM_SAL.

2. Create four output ports in expression OUT_MAX_SAL, OUT_MIN_SAL, OUT_AVG_SAL and OUT_SUM_SAL.

3. Call the procedure in last variable port says VAR_SUM_SAL.

:SP.SP_AGG (DEPTNO, VAR_MAX_SAL,VAR_MIN_SAL, VAR_AVG_SAL, PROC_RESULT)

Example 2:

DEPTNO as Input and MAX_SAL, MIN_SAL, AVG_SAL and SUM_SAL as O/P Stored Procedure to drop index in Pre Load of Target Stored Procedure to create index in Post Load of Target

DEPT will be source table. Create a target table SP_UNCONN_EXAMPLE with fields DEPTNO, MAX_SAL, MIN_SAL, AVG_SAL & SUM_SAL.
Write Stored Procedure in Database first and Create shortcuts as needed. Stored procedures are given below to drop and create index on target.Make sure to create target table first. Stored Procedures to be created in next example in Target Database:

Create or replace procedure CREATE_INDEX

Begin

Execute immediate 'create index unconn_dept on SP_UNCONN_EXAMPLE(DEPTNO)';

End;

Create or replace procedure DROP_INDEX

Begin

Execute immediate 'drop index unconn_dept';

End;

Creating Mapping:

1. Open folder where we want to create the mapping.

2. Click Tools -> Mapping Designer.

3. Click Mapping-> Create-> Give name. Ex: m_sp_unconn_1_value

4. Drag DEPT and Target table.

5. Transformation -> Import Stored Procedure -> Give Database Connection -> Connect -> Select the procedure sp_agg from the list. Click OK.

6. Stored Procedure has been imported.

7. T/F -> Create Expression T/F. Pass DEPTNO from SQ_DEPT to Expression T/F.

8. Edit Expression and create 4 variable ports and 4 output ports as shown below:

9. Call the procedure in last variable port VAR_SUM_SAL.

10. :SP.SP_AGG (DEPTNO, VAR_MAX_SAL, VAR_MIN_SAL, VAR_AVG_SAL, PROC_RESULT)

11. Click Apply and Ok.

12. Connect to target table as needed.

13. Transformation -> Import Stored Procedure -> Give Database Connection for target -> Connect -> Select the procedure CREATE_INDEX and DROP_INDEX from the list. Click OK.

14. Edit DROP_INDEX -> Properties Tab -> Select Target Pre Load as Stored Procedure Type and in call text write drop_index. Click Apply -> Ok.

15. Edit CREATE_INDEX -> Properties Tab -> Select Target Post Load as Stored Procedure Type and in call text write create_index. Click Apply -> Ok.

16. Mapping -> Validate

17. Repository -> Save

Create Session and then workflow.
Give connection information for all tables.
Give connection information for Stored Procedures also.
Also make sure that you execute the procedure CREATE_INDEX on database before using them in mapping. This is because, if there is no INDEX on target table, DROP_INDEX will fail and Session will also fail.
Run workflow and see the result in table.