How to transpose data in Informatica

Suppose we are having input data coming as

Firstname1
Ph1
Address1

Firstname2
Ph2
Address2

Firstname3
Ph3
Address3

You want data in output like i.e. you want to convert 9 rows into 3 rows with similar group together

Firstname1 Ph1 Address1
Firstname2 Ph2 Address2
Firstname3 Ph3 Address3

You can create a mapping by following these steps

1. Create a SQ with one input field Field1


2. Add a expression transformation after SQ


3. Link Field1 from SQ to expression Transformation


4. Add a new op port name grp =substr(FIELD1,length(FIELD1),1)


5. Add a aggregator transformation after Expression and drag Field1 and Grp field


6. Have grp as group by fieldAdd three new port OP_FLD1=LAST(FIELD1,SUBSTR(FIELD1,1,1)='f')OP_FLD2=LAST(FIELD1,SUBSTR(FIELD1,1,1)='p')OP_FLD=LAST(FIELD1,SUBSTR(FIELD1,1,1)='a')
7. Add OP_FLD1,OP_FLD2,OP_FLD3 to target

Read More

Unix Handy Commands

These are the commands which i use on daily basis in Unix.I found these commands very useful

In point 2 and 6 it is not displaying pipe symbol due to some problem.Please use pipe between commands

1. Too see a particular line For example if you just want to see 180th line in file sed -n '180p' testfile.txt
2. To find a particular column in file cat testfile.txt awk -F"," '{print $2}'
3. To rename file with current date mv test test_`date +%Y-%m-%d
4. This command will take out all those lines which are having 8 at 17th position grep '^.\{16\}8' testfile.txt >testfile_new.txt
5. To remove nth line without openning file sed 'nd' file1>file2 to remove multiple lines sed -e 1d -e 5d
6. To find top 20 files with most spacels -ltrawk -F" " '{print $5 $9}' sort -ntail -20
7. To find record in first file not in second comm -13 testfile1.txt testfile2.txt
8. If you are looking from something that is contained in a file but you don't know which directory it is in do the following: find . -name "*" xargs grep -i something This will find all of the files in the directory and below and grep for the string something in those files!
9. Delete Files Delete all the files starting with name testfile find . -type f -name "testfile*" exec rm -f {} \;
10. Remove blank space from file sed -e "s/ *//g" testfile.txt >testfile.txt_wo_space

Read More

Difference between TRUNCATE and DELETE

This is one of most frequently asked question in Oracle interviews.
I tried to put all the differences i know,you are welcome to put your thoughts and comment on that

Delete

1. It is a DML statement


2. Can Rollback


3. Can delete selective records


4. It fires database triggers.


5. It does not requires disabling of referential constraints


6. Deletes perform normal DML. That is, they take locks on rows, they generate redo (lots of it), and they require segments in the UNDO tablespace. Deletes clear records out of blocks carefully. If a mistake is made a rollback can be issued to restore the records prior to a commit. A delete does not relinquish segment space thus a table in which all records have been deletedretains all of its original blocks.

Truncate

1. It is a DDL statement
2. Can’t Rollback
3. Can’t delete selective records.It will delete all the records in table


4. Doesn't fire database triggers


5. It requires disabling of referential constraints


6. Truncate moves the High Water Mark of the table back to zero.No row-level locks are taken,no redo or rollback is generated.

Read More