Ads 468x60px

Pages

Subscribe:

Labels

Informatica (18) Integration Service (10) Siebel Business Intelligence (6) ETL (5) Informatica PowerCenter (4) Informatica PowerCenter 8x (4) Oracle (4) Metadata (3) DTM (2) Data Transformation Manager (2) Hexaware Technologies (2) OUD (2) Oracle Unified Directory (2) PowerCenter (2) XML (2) business (2) ASCII (1) Administration Console (1) Application Services (1) Automated Migration (1) BFSI (1) Binary (1) Bulk Load (1) Business Intelligence (1) Business Intelligence Challenge (1) Business Intelligence Company (1) Business Intelligence Consulting (1) Business Objects (1) ByTree (1) CDC (1) CNBC News (1) CNBCTV18’s Shreya Roy (1) COBOL (1) Change Data Capture (1) Collaborative (1) Collaborative Data Management (1) Computing Expression Evaluator (1) Convert Rows To Columns In Inforamtica (1) Data (1) Data Governance (1) Data Management (1) Data Mart (1) Data Type (1) Data Virtualization Services (1) Database (1) Datawarehouse (1) ETL Developers (1) Expression Evaluator (1) Expression Evaluator Debugging (1) Extract (1) FTP (1) File List (1) Flash or Java Applets (1) Flat Files (1) Function (1) HP Diagnostics-Identify (1) HP Diagnostics-Identify bottlenecks (1) HTTP Headers (1) Hexaware Technologies Limited (1) IT Metrics (1) IT companies (1) IT company (1) Index (1) Indirect Source (1) Informatica 8.6 (1) Informatica 8.x (1) Informatica Data Integration Service (1) Informatica Debugger (1) Informatica Debugging Transformation (1) Informatica Development (1) Informatica File Transfer (1) Informatica Power Center (1) Informatica Powercenter 8x Key Concepts (1) Informatica Process Control Audit (1) Informatica Repository Restoration (1) Informatica Server Re-Installation (1) Informatica Server Recovery System (1) Informatica Upgrade Challenge (1) Informatica Workflow (1) Informatica Workflow Process Control (1) Integration Services (1) Integration and Repository services (1) Invalid Objects (1) JavaScript Functions (1) Joiner Transformation (1) LDAP (1) LDAP Directory (1) LDAP Replication (1) Load Balancer (1) LoadRunner 11.5 (1) Looping (1) Manual Correlation (1) Mappings (1) NTLM Resource (1) NTLM authentication (1) Native Driver (1) New Column (1) ODBC (1) ODBC Driver (1) OID (1) OUD Configuration (1) OUD Directory Server (1) OUD Replication (1) Oracle Applications (1) Oracle Client (1) Oracle Hints In SQL (1) Oracle Internet Director (1) Oracle Optimizer (1) Oracle R12 (1) Oracle Solutions (1) Oracle loadrunner (1) OracleErrorActionFile (1) PeopleSoft Jobs (1) PeopleSoft Jobs In Hexaware (1) Peoplesoft Tester In Chennai (1) Peoplesoft Tester Jobs In Chennai (1) Performance (1) Performance Testing (1) PowerCenter 8.5 (1) PowerCenter Server (1) PowerCenter Server Support (1) Powercenter 8.5.1 (1) Pushdown Optimization (1) Re-Import (1) Relational (1) Remote Filename (1) Repository Services (1) Reverse Of A Normalizer In Informatica (1) Rows Read (1) SFTP (1) SJSDS (1) SMP (1) SQL Statement (1) SSH2 (1) SUBSTR Function (1) Session Failed (1) Source Data (1) Source Definition (1) Source Row (1) Store Procedure (1) SuppressNilContentMethod (1) Symmetric Multi-Processing (1) Target Definition (1) Target Row (1) Task Developer (1) Text Flags (1) Transfer Protocol (1) Transformation (1) Transformation Logics (1) Transpose Records (1) UDF (1) User Defined Functions (1) WriteNullXMLFile (1) XML File (1) XML Optimization (1) XML Target (1) XML Tuning (1) XMLSendChildFirst (1) XMLWarnDupRows (1) Zero byte XML file (1) accelerate application (1) employee performance (1) mid-cap it (1) web_reg_save_param (1)

Labels

Blogroll

About

Blogger templates

Blogger news

Showing posts with label Oracle. Show all posts
Showing posts with label Oracle. Show all posts

Thursday 23 December 2010

Leveraging Metadata in Informatica Workflow-Session/Analysis

We can leverage the metadata collected in the Informatica repository for many interesting analysis, few of the scenarios where I have leveraged the Informatica Metadata are as following.
This SQL Queries can be executed in Oracle database with no changes and requires little modification with other databases.
Failed Sessions
The following query lists the failed sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n
QUERY:
SELECT SUBJECT_AREA AS FOLDER_NAME,
SESSION_NAME,
LAST_ERROR AS ERROR_MESSAGE,
DECODE (RUN_STATUS_CODE,3,’Failed’,4,’Stopped’,5,’Aborted’) AS STATUS,
ACTUAL_START AS START_TIME,
SESSION_TIMESTAMP
FROM REP_SESS_LOG
WHERE RUN_STATUS_CODE != 1
AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)
RESULT:
Long running Sessions
The following query lists long running sessions. To make it work for the last ‘n’ days, replace SYSDATE-1 with SYSDATE – n
QUERY:
SELECT SUBJECT_AREA AS FOLDER_NAME,
SESSION_NAME,
SUCCESSFUL_SOURCE_ROWS AS SOURCE_ROWS,
SUCCESSFUL_ROWS AS TARGET_ROWS,
ACTUAL_START AS START_TIME,
SESSION_TIMESTAMP
FROM REP_SESS_LOG
WHERE RUN_STATUS_CODE = 1
AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -1) AND TRUNC(SYSDATE)
AND (SESSION_TIMESTAMP – ACTUAL_START) > (10/(24*60))
ORDER BY SESSION_TIMESTAMP
RESULT:
Invalid Tasks
The following query lists folder names and task name, version number, and last saved for all invalid tasks.
QUERY:
SELECT SUBJECT_AREA AS FOLDER_NAME,
DECODE(IS_REUSABLE,1,’Reusable’,’ ‘) || ‘ ‘ ||TASK_TYPE_NAME AS TASK_TYPE,
TASK_NAME AS OBJECT_NAME,
VERSION_NUMBER,
LAST_SAVED
FROM REP_ALL_TASKS
WHERE IS_VALID=0
AND IS_ENABLED=1
ORDER BY SUBJECT_AREA,TASK_NAME
RESULT:
Thanks for reading, do you have other scenarios where Workflow Metadata has been effective …wish you a very happy new year 2011.

Monday 30 August 2010

Informatica Development Best Practice – Workflow


Workflow Manager default properties can be modified to improve the overall performance and few of them are listed below.    This properties can impact the ETL runtime directly and needs to configured based on :

i)  Source Database
ii) Target Database
iii) Data Volume


Category Technique
Session Properties While loading Staging Tables for FULL LOADS,  Truncate target table option should be checked. Based on the Target database and the primary key defined, Integration Service fires TRUNCATE or DELETE statement.Database                  Primary Key Defined                   No Primary KeyDB2                             TRUNCATE                                       TRUNCATE
INFORMIX                 DELETE                                              DELETE
ODBC                         DELETE                                                DELETE
ORACLE                    DELETE UNRECOVERABLE            TRUNCATE
MSSQL                       DELETE                                               TRUNCATE
SYBASE                     TRUNCATE                                        TRUNCATE Workflow Property “Commit interval” (Default value : 10,000) should be increased for increased for Volumes more than 1 million records.  Database Rollback Segment size should also be updated, while increasing “Commit Interval”.
Insert/Update/Delete options should be set as determined by the target population method.
Target Option                                   Integration Service
Insert                                                   Uses Target update Option
Update as Update
Update as Insert
Update else Insert
Update as update                             Updates all rows as Update
Update as Insert                               Inserts all rows
Update else Insert                            Updates existing rows else Insert
Partition
Maximum number of partitions for a session should be 1.5 times the number of processes in the Informatica server. i.e. 1.5 X 4 Processors = 6 partitions.
Key Value partitions should be used only when an even Distribution of data can be obtained.  In other cases, Pass Through partitions should be used.
A Source filter should be added to evenly distribute the data between Pass through Partitions. Key Value should have ONLY numeric values. MOD(NVL(<Numeric Key Value>,0),# No of Partitions defined)  Ex: MOD(NVL(product_sys_no,0),6)
If a session contains “N” partition, increase the DTM Buffer Size to at least “N” times the value for the session with One partition
If the Source or Target database is of MPP( Massively Parallel Processing ), enable Pushdown Optimization.  By enabling this, Integration Service will push as much Transformation Logic to Source database or Target database or FULL ( both ) , based on the settings.  This property can be ignored for Conventional databases.

Friday 25 July 2008

Handling Oracle Exceptions


There may be requirements wherein certain oracle exceptions need to be treated as Warnings and certain exceptions need to be treated as Fatal.
Normally, a fatal Oracle error may not be registered as a warning or row error and the session may not fail, conversely a non-fatal error may cause a PowerCenter session to fail.This can be changed with few tweaking in
A. Oracle Stored Procedure
B. The Oracle ErrorActionFile and
C. Server Settings
Let us see this with an example.
An Oracle Stored Procedure under certain conditions returns the exception NO_DATA_FOUND. When this exception occurs, the session calling the Stored Procedure does not fail.
Adding an entry for this error in the ora8err.act file and enabling the OracleErrorActionFile option does not change this behavior (Both ora8err.act and OracleErrorActionFile are discussed in later part of this blog).
When this exception (NO_DATA_FOUND) is raised in PL/SQL it is sent to the Oracle client as an informational message not an error message and the Oracle client sends this message to PowerCenter. Since the Oracle client does not return an error to PowerCenter the session continues as normal and will not fail.
A. Modify the Stored Procedure to return a different exception or a custom exception. A custom exception number (only between -20000 and -20999) can be sent using the raise_application_error PL/SQL command as follows:
raise_application_error (-20991,’<stored procedure name> has raised an error’, true);
Additionally add the following entry to the ora8err.act file:
20991, F
B. Editing the Oracle Error Action file can be done as follows:
1. Go to the server/bin directory under the Informatica Services installation directory (8.x) or the Informatica Server installation directory (7.1.x).
E.g.,
For Infa 7.x
C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act
For Infa 8.x
C:\Informatica\PowerCenter8.1.1\server\bin
2. Open the ora8err.act file.
3. Change the value associated with the error.
“F” is fatal and stops the session.
“R” is a row error and writes the row to the reject file and continues to the next row.
Examples:
To fail a session when the ORA-03114 error is encountered change the 03114 line in the file to the following:
03114, F
To return a row error when the ORA-02292 error is encountered change the 02292 line to the following:
02292, R
Note that the Oracle action file only applies to native Oracle connections in the session. If the target is using the SQL*Loader external loader option, the message status will not be modified by the settings in this file.
C. Once the file is modified, following changes need to be done in the server level.
Infa 8.x
Set the OracleErrorActionFile Integration Service Custom Property to the name of the file (ora8err.act by default) as follows:
1. Connect to the Administration Console.
2. Stop the Integration Service.
3. Select the Integration Service.
4. Under the Properties tab, click Edit in the Custom Properties section.
5. Under Name enter OracleErrorActionFile.
6. Enter ora8err.act for the parameter under Value.
7. Click OK.
8. Start the Integration Service.
PowerCenter 7.1.x
In PowerCenter 7.1.x do the following:
UNIX
For the server running on UNIX:
1. Using a text editor open the PowerCenter server configuration file (pmserver.cfg).
2. Add the following entry to the end of the file:
OracleErrorActionFile=ora8err.act
3. Re-start the PowerCenter server (pmserver).
Windows
For the server running on Windows:
  1. Click Start, click Run, type regedit, and click OK.
  2. Go to the following registry key:
HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\
PowerMart\Parameters\Configuration
Select Edit; New; String Value. Enter the “OracleErrorActionFile” for the string value.
Select Edit; Modify.
Enter the directory and the file name of the Oracle error action file:
\ora8err.act
Example:
The default entry for PowerCenter 7.1.3 would be:
C:\Program Files\Informatica PowerCenter 7.1.3\Server\ora8err.act
And for PowerCenter8.1.1 it would be
C:\Informatica\PowerCenter8.1.1\server\bin
Click OK

Thursday 5 June 2008

Exceptions in Informatica


There exists no product/tool without strange exceptions/errors, we will see some of those exceptions.

1. You get the below error when you do “Generate SQL” in Source Qualifier and try to validate it.
“Query should return exactly n field(s) to match field(s) projected from the Source Qualifier”
Where n is the number of fields projected from the Source Qualifier.

Possible reasons for this to occur are:

1. The order of ports may be wrong
2. The number of ports in the transformation may be more/less.
3. Sometimes you will have the correct number of ports and in correct order too but even then you might face this error in that case make sure that Owner name and Schema name are specified correctly for the tables used in the Source Qualifier Query.
E.g., TC_0002.EXP_AUTH@TIMEP

2. The following error occurs at times when an Oracle table is used

“[/export/home/build80/zeusbuild/vobs/powrmart
/common/odl/oracle8/oradriver.cpp] line [xxx]”
Where xxx is some line number mostly 241, 291 or 416.

Possible reasons are

1. Use DataDirect Oracle ODBC driver instead of the driver “Oracle in
2. If the table has been imported using the Oracle drivers which are not supported, then columns with Varchar2 data type are replaced by String data type and Number columns are imported with precision Zero(0).

3. Recently I encountered the below error while trying to save a Mapping.

Unexpected Condition Detected
Warning: Unexpected condition at: statbar.cpp: 268
Contact Informatica Technical Support for assistance

When there is no enough memory in System this happens. To resolve this we can either

1. Increase the Virtual Memory in the system
2. If continue to receive the same error even after increasing the Virtual Memory, in Designer, go to ToolsàOptions, go to General tab and clear the “Save MX Data” option.