How to separate only string data into one target and numeric data or special character data into second target in informatica ?

Example: suppose my Source is a Flat File and having with Alphabetic Character and Contain Non-Alphabetic Character at any Position Like

  (0-9) (!@........so on) . We need to separate the data,while loading into targets

Names 

XXXX 

YYYYY4Y

ZZZZZZ@K

AAAAAA

BBBBB

$CCCCC

DDDDD_

Target should be loaded as..

Target1                 Target2    

XXXX                   YYYYY4Y

AAAAAA              ZZZZZZ@K

BBBBB                  $CCCCC

       $DDDDD_  

Solution:-

i).Use the Informatica data comparision function REGEX_MATCH( ) to achieve this..

ii).Use Filter Condition as  REG_MATCH(Names,'^[a-zA-Z]+$')

Thanks,

Hari

why truncate is faster than delete in sql

When we type DELETE.all the data get copied into the Rollback Tablespace first.then delete operation get performed.Thats why when you type ROLLBACK after deleting a table ,you can get back the data(The system get it for you from the Rollback Tablespace).All this process take time.But when you type TRUNCATE,it removes data directly without copying it into the Rollback Tablespace.Thatswhy TRUNCATE is faster.Once you Truncate you cann't get back the data.

What are the different types of locks?

There are five kinds of locks on repository objects:
Read lock: Created when you open a repository object in a folder for which you do not have write permission. Also created when you open an object with an existing write lock.
Write lock: Created when you create or edit a repository object in a folder for which you have write permission.
 Execute lock: Created when you start a session or batch, or when the Informatica Server starts a scheduled session or batch.
Fetch lock: Created when the repository reads information about repository objects from the database.
Save lock: Created when you save information to the repository.

Things need to remember while using Sorted Input in Aggregator transformation

In general, follow this check list to ensure that you are handling aggregator with sorted inputs correctly:
1. Do not use sorted input if any of the following conditions are true: 

a.The aggregate expression uses nested aggregate functions. 
b.The session uses incremental aggregation. 
c. Input data is data driven. 
You select data driven for the Treat Source Rows as Session Property, or the Update Strategy transformation appears before the Aggregator transformation in the mapping.
If you use sorted input under these circumstances, the Informatica Server reverts to default aggregate behavior, reading all values before performing aggregate calculations. 

Remember, when you are using sorted inputs, you pass sorted data through the Aggregator.
Data must be sorted as follows:
1. By the Aggregator group by ports, in the order they appear in the Aggregator transformation.
2. Using the same sort order configured for the session. 
If data is not in strict ascending or descending order based on the session sort order, the Informatica Server fails the session.

How to create the Email Task (Re-usable)

Why re-usable?. Becuase we’d be using the same email task for all the sessions in this workflow.

1. Go to Workflow Manager and connect to the repository and the folder in which your workflow is present.
2. Go to the Workflow Designer Tab.
3. Click on Workflow > edit (from the Menu ) and create a workflow variable as below (to hold the failure email address).

Failure Email workflow variable

4. Go to the “Task Developer” Tab and click create from the menu.
5. Select “Email Task”, enter “Email_Wkf_Test_Failure” for the name (since this email task is for different sessions in wkf_test).
Click “Create” and then “Done”. Save changes (Repository -> Save or the good old ctrl+S).
6. Double click on the Email Task and enter the following details in the properties tab.

1 2 3 4 5 6 7 8 9 10

Email User Name : $$FailureEmail   (Replace the pre-populated session variable $PMFailureUser,                                     since we be setting this for each workflow as needed). Email subject   : Informatica workflow ** WKF_TEST **  failure notification. Email text      : (see below. Note that the server varibles might be disabled, but will be available during run time). Please see the attched log for Details. Contact ETL_RUN_AND_SUPPORT@XYZ.COM for further information. %g Folder : %n Workflow : wkf_test Session : %s

Create_Email_Task

Emails in the Parameter file :-

We’ve created the workflow variable $$FailureEmail and used it in the email task. But how and when is the value assigned?
You can manage the failure emails by assigning the value in the parameter file.
Here is my parameter file for this example. You can seperate multiple emails using comma.

1 2 3 4 5 6 7 8 9

infa@ DEV /> cat wkf_test.param [rchamarthi.WF:wkf_Test] $$FailureEmail=rajesh@etl-developer.com   [rchamarthi.WF:wkf_Test.ST:s_m_T1] $DBConnection_Target=RC_ORCL102   [rchamarthi.WF:wkf_Test.ST:s_m_T2] $DBConnection_Target=RC_ORCL102

While it might look like a simpler approach initially, hard-coding emails IDs in the email task is a bad idea. Here’s why.

Like every other development cycle, Informatica ETLs go thorugh Dev, QA and Prod and the failure email for each of the environment will be different. When you promote components from Dev to QA and then to Prod, everything from Mapping to Session to Workflow should be identical in all environments. Anything that changes or might change should be handled using parameter files (similar to env files in Unix). This also works the other way around. When you copy a workflow from Production to Development and try to make changes, the failure emails will not go to business users or QA teams as the development parameter file only has the developer email Ids.

If you use parameter files, here is how it would be set up in different environments once.
After the initial set up, you’ll hardly change it in QA and Prod and migrations will never screw this up.

1 2 3

In development   : $$FailureEmail=developer1@xyz.com,developer2@xyz.com" In QA / Testing  : $$FailureEmail=r=developer1@xyz.com,developer2@xyz.com,QA_TEAM@xyz.com In Production    : $$FailureEmail=IT_OPERATIONS@xyz.com,ETL_RUN@xyz.com,BI_USERS@xyz.com