Difference between Informatica 7.1 and 8.6 1.

Target from Transformation: In Informatica 8X we can create target from Transformation by dragging transformation in Target designer 2. Pushdown optimization: Uses increased performance by pushing Transformation logic to the database by analyzing the transformations and Issuing SQL statements to sources and targets. Only processes any Transformation logic that it cannot push to the database. 3. New function in expression editor: New function have been introduced in Informatica 8X like reg_extract and reg_match 4. Repository query available in both versioned and non-versioned Repositories previously it was available only for versioned repository. 5. UDF (User defined function) similar to macro in excel. 6. FTP: We can have partitioned FTP targets and Indirect FTP File source (with file list). 7. Propagating Port Descriptions: In Informatica 8 we can edit a port description and propagate the description to other transformations in the mapping. 8. Environment SQL Enhancements: Environment SQL can still be used to execute an SQL statement at start of connection to the database. We can Use SQL commands that depend upon a transaction being opened during the entire read or write process. For example, the following SQL command Modifies how the session handles characters: Alter session set NLS_DATE_FORMAT='DD/MM/YYYY';". 9. Concurrently write to multiple files in a session with partitioned targets. 10. Flat File Enhancements: Reduced conversion of data types Delimited file performance has improved Flat file now can have integer and double data types Data can be appended to existing flat files Informatica power center 8 is having the following features which makes it more powerful, easy to use and manage when compared to previous versions.

Supports Service oriented architecture Access to structured, unstructured and semi structured data Support for grid computing High availability Pushdown optimization Dynamic partitioning Metadata exchange enhancements Team based Development Global Web-based Admin console

New transformations 23 New functions User defined functions Custom transformation enhancements Flat file enhancements New Data Federation option Enterprise GRID Informatica 9 empowers line-of-business managers and business analysts to identify bad data and fix it faster. Architecture wise there are no differences between Informatica 8 and 9 but there are some new features added in power center 9. New Client tools Informatica 9 includes the Informatica Developer and Informatica Analyst client tools. The Informatica Developer tool is eclipse-based and supports both data integration and data quality for enhanced productivity. From here you can update/refine those same rules, and create composite data objects - e.g. Get customer details from a number of different sources and aggregate these up to a Customer Data Object. The Informatica Analyst tool is a browser-based tool for analysts, stewards and line of business managers. This tool supports data profiling, specifying and validating rules (Scorecards), and monitoring data quality. Informatica Administrator The power center Administration Console has been renamed the Informatica Administrator. The Informatica Administrator is now a core service in the Informatica Domain that is used to configure and manage all Informatica Services, Security and other domain objects (such as connections) used by the new services. The Informatica Administrator has a new interface. Some of the properties and configuration tasks from the power center Administration Console have been moved to different locations in Informatica Administrator. The Informatica Administrator is expanded to include new services and objects. Cache Update in Lookup Transformation we can update the lookup cache based on the results of an expression. When an expression is true, you can add to or update the lookup cache. You can update the dynamic lookup cache with the results of an expression. Database deadlock resilience In previous releases, when the Integration Service encountered a database deadlock during a lookup, the session failed. Effective in 9.0, the session will not fail. When a deadlock occurs, the Integration Service attempts to run the last statement in a lookup. You can configure the number of retry attempts and time period between attempts. Multiple rows return Lookups can now be configured as an Active transformation to return Multiple Rows.We

can configure the Lookup transformation to return all rows that match a lookup condition. A Lookup transformation is an active transformation when it can return more than one row for any given input row. Limit the Session Log You can limit the size of session logs for real-time sessions. You can limit the size by time or by file size. You can also limit the number of log files for a session. Auto-commit We can enable auto-commit for each database connection. Each SQL statement in a query defines a transaction. A commit occurs when the SQL statement completes or the next statement is executed, whichever comes first. Passive transformation We can configure the SQL transformation to run in passive mode instead of active mode. When the SQL transformation runs in passive mode, the SQL transformation returns one output row for each input row. Connection management Database connections are centralized in the domain. We can create and view database connections in Informatica Administrator, Informatica Developer, or Informatica Analyst. Create, view, edit, and grant permissions on database connections in Informatica Administrator. Monitoring We can monitor profile jobs, scorecard jobs, preview jobs, mapping jobs, and SQL Data Services for each Data Integration Service. View the status of each monitored object on the Monitoring tab of Informatica Administrator. Deployment We can deploy, enable, and configure deployment units in the Informatica Administrator. Deploy Deployment units to one or more Data Integration Services. Create deployment units in Informatica Developer. Model Repository Service Application service manages the Model repository. The Model repository is a relational database that stores the metadata for projects created in Informatica Analyst and Informatica Designer. The Model repository also stores run-time and configuration information for applications deployed to a Data. Data Integration Service Application service processes requests from Informatica Analyst and Informatica Developer to preview or run data profiles and mappings. It also generates data previews for SQL data services and runs SQL queries against the virtual views in an SQL data service. Create and enable a Data Integration Service on the Domain tab of Informatica Administrator. XML Parser

The XML Parser transformation can validate an XML document against a schema. The XML Parser transformation routes invalid XML to an error port. When the XML is not valid, the XML Parser transformation routes the XML and the error messages to a separate output group that we can connect to a target. Enforcement of licensing restrictions Power center will enforce the licensing restrictions based on the number of CPUs and repositories. Also Informatica 9 supports data integration for the cloud as well as on premise. You can integrate the data in cloud applications, as well as run Informatica 9 on cloud infrastructure.

Explain Synonym, View and materialized view? Views A view takes the output of a query and makes it appear like a virtual table. We can use a view in most places where a table can be used. All operations performed on a view will affect data in the base table and so are subject to the integrity constraints and triggers of the base table. A View can be used to simplify SQL statements for the user or to isolate an application from any future change to the base table definition and it can also be used to improve security by restricting access to a predetermined set of rows or columns. In addition to operating on base tables, one View can be based on another; a view can also JOIN a view with a table (GROUP BY or UNION). Using views encourages the use of Shared SQL with the benefit of reduced memory usage. Read-Only vs Updatable Views The data dictionary views ALL_UPDATABLE_COLUMNS, DBA_UPDATABLE_COLUMNS, and USER_UPDATABLE_COLUMNS indicate which view columns are updatable. An updatable view lets you insert, update, and delete rows in the view and propagate the changes to the target master table. In order to be updatable, a view cannot contain any of the following constructs: SET or DISTINCT operators, an aggregate or analytic function, a GROUP BY, ORDER BY, CONNECT BY, or START WITH clause, a sub-query (or collection expression) in a SELECT list or finally (with some exceptions) a JOIN. Views that are not updatable can be modified using an INSTEAD OF trigger. Materialized Views Materialized views are schema objects that can be used to summarize, pre-compute, replicate, and distribute data. E.g. To construct a data warehouse. A materialized view provides indirect access to table data by storing the results of a query in a separate schema object. Unlike an ordinary view, this does not take up any storage space or contain any data. The existence of a materialized view is transparent to SQL, but when used for query rewrites

will improve the performance of SQL execution. An updatable materialized view lets you insert, update, and delete. You can define a materialized view on a base table, partitioned table or view and we can define indexes on a materialized view. A materialized view can be stored in the same database as its base table(s) or in a different database. Materialized views stored in the same database as their base tables can improve query performance through query rewrites. Query rewrites are particularly useful in a data warehouse environment. A materialized view log is a schema object that records changes to a master table's data so that a materialized view defined on the master table can be refreshed incrementally. Synonyms A synonym is an alias for any table, view, materialized view, sequence, procedure, function, or package. A public synonym is owned by the user group PUBLIC and every user in a database can access it. A private synonym is in the schema of a specific user who has control over its availability to others.

Synonyms are used to: - Mask the real name and owner of a schema object - Provide global (public) access to a schema object - Provide location transparency for tables, views, or program units of a remote database. - Simplify SQL statements for database users

Dynamically creating XML file name in Target definition? 1. Open target XML definition using XML Editor. 2. Highlight the root node and from menu select XML Views and choose Create Filename Column and save it. 3. Now it shows Filename port in the schema definition.

Steps to add a pass through port in XML Parser? 1. Open and edit XML definition from Midstream XML Parser. 2. Port that need to made as pass through has to be added earlier as input to XML parser transformation and then highlight the object where we want the port to be pass through. 3. Now go to menu and select XML Views and choose Add Reference Port and save it. Now field that we choose become pass through.