Informatica

FTP COMMANDS

2013-10-16T20:05:00.000-07:00

Hi All,

If there is any requirement for transferring files from UNIX to Window shared path and vice versa through FTP,
Please use the below commands for achieving this

UNIX to Windows Shared path:

cd <Local UNIX directory where Input files are available>
ftp -n <<EOF
            open <FTP Server Name>
            user <FTP User Name> <FTP Password>
            cd <Windows Shared path where you want to copy the files>
            mput <File Name*>
EOF

Windows Shared path to UNIX:

cd < Local UNIX directory where you want to copy the files >
ftp -n <<EOF
            open <FTP Server Name>
            user <FTP User Name> <FTP Password>
            cd <Windows Shared path where the Input files are available>
            mget <File Name*>
EOF

Best Regards,
Moorthy. G

2013-02-05T09:27:00.001-08:00

Load Data Oracle to Ms Access

MS ACCESS:

==========

1. Open a new Microsoft Access File.

2. Go for option File--> Get External Data -->Import

3. It opens one dialog box.

4. Select Files of type as

"ODBC databases"

5. Select the Machine Data Source.

6. Select the ODBC Driver(DSN) which is capable of access that required database data. (Then double click on the ODBC)

7. Enter the Password for that connection.

It loads all the data from Oracle to Access.

Step1:

DSN CREATION:

Select Start»Settings»Control Panel»Administrative Tools»Data Sources (ODBC) in Windows 2000/XP or Start»Settings»Control Panel»Data Sources (ODBC) in Windows 9x/Me.

Select either the User DSN or System DSN tab.

Select Add»Microsoft Access Driver (*.mdb)»Finish.

Give it a Data Source Name and a good description.

Choose the Select button in the Database field.

Navigate to the location of your Access database and select OK.

Select OK on the ODBC Microsoft Access Setup window.

You should now see your DSN in the User/System Data Source window.

Informaitca

========

Create a relation connection using odbc driver in Worklflow Manager.

Connection Name: ODBC connection

User Name: pmnulluser

Password: pmnullpasswd

Connection String: NAME of ODBC connecti

How to make reusable transformation into nonreusable transformation?

2012-12-17T04:00:00.002-08:00

Reusable transformation into nonreusable transformation
=====================================
1) Drag the reusable transformation from Repository Navigator into Mapping Designer
by pressing the left button of the mouse
2 ) Then press the Ctrl key before releasing the left button of mouse.
3) Release the left button of mouse.

To Find a CHEK CONSTRATINT in DB2
================================
SELECT
TBNAME,
TBOWNER,
CHECKCONDITION
FROM SYSIBM.SYSCHECKS
WHERE
TBNAME='TABLE_NAME'
AND TBOWNER='SCHEMA'

Best Regards,
Moorthy. G

2012-11-26T03:26:00.000-08:00

1. How To Find Primary Key and Foreign Key Relationship in a table in DB2?
Ans:

SELECT A.CREATOR, A.TBNAME, A.RELNAME, B.COLNAME, B.COLSEQ,
A.REFTBCREATOR, A.REFTBNAME
FROM SYSIBM.SYSRELS A, SYSIBM.SYSFOREIGNKEYS B
WHERE A.CREATOR = 'SOURCE'
AND B.CREATOR = 'SOURCE'
AND A.TBNAME = 'TABLE_NAME'
-- AND B.TBNAME = 'PROJ'
AND A.RELNAME = B.RELNAME
ORDER BY A.RELNAME, B.COLSEQ

2. How to find index in a Table in DB2?
Ans:
SELECT DISTINCT
A.TBNAME,
B.COLNAME,
A.INDEXSPACE,
A.DBNAME,
A.CLUSTERING,
A.CLUSTERED
FROM SYSIBM.SYSINDEXES A, SYSIBM.SYSKEYS B
WHERE
A.CREATOR = 'SOURCE'
AND TBNAME = 'TABLE_NAME'
AND A.NAME =B.IXNAME

Best Regards,
Moorthy. G

2012-10-27T21:05:00.001-07:00

STORE PROCEDURES:
==================

COPY TABLE
==========

create or replace procedure table_copy(
GM_tab_from varchar2,
GM_tab_to varchar2)
Is

Begin

execute immediate 'insert into '||GM_tab_to||' (select * from '||GM_tab_from||')';

End;
/

CREATE_INDEX:
=============

Create or replace procedure CREATE_INDEX

As

Begin

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

End;

/

DROP_INDEX:
===========
Create or replace procedure DROP_INDEX

As

Begin

Execute immediate 'drop index unconn_dept';

End;

/

2012-07-10T10:46:00.000-07:00

Informatica workflow status SQL Query

SELECT WORKFLOW_NAME,
SERVER_NAME,
RUN_ERR_CODE,
RUN_ERR_MSG,
SUBJECT_AREA,
decode (RUN_STATUS_CODE,1 , 'Succeeded',
2,'Disabled',
3,'Failed',
4,'Stopped',
5,'Aborted',
6,'Running',
15,'Terminated') Status
from infa_metadata_schema.REP_WFLOW_RUN
where START_TIME>= sysdate-2 and END_TIME<=sysdate
order by SUBJECT_AREA,WORKFLOW_NAME,RUN_STATUS_CODE;

2012-07-10T09:12:00.003-07:00

If file exists then send email with attachment ?

if
[[ -f $your_file ]]; then uuencode $your_file $your_file|mailx -s "$your_file exists..." your_email_address
fi

Best Regards,
Moorthy. G

2012-07-02T04:53:00.001-07:00

Unconnected Lookup Transformation -- Returning Multiple Values

> We can achieve Multiple values from Unconnected Lookup-Tr. We just need to override the default lookup sql query, there in one of the return port we can concatnate the values using ||.

> The concatnated multiple values will be returned from lookup to calling expression. In the expression we can extract / separate the concatnated string value (multiple values concatnated in lookup) and can pass to the target…

SELECT DEPT.DNAME||' '||DEPT.LOC as OUT_MUL_Values, DEPT.DNAME as DNAME, DEPT.LOC as LOC, DEPT.DEPTNO as DEPTNO FROM DEPT

> Before overrideing we should create one return port in the lookup-tr (here OUT_MUL_Values )

> Devide that OUT_MUL_Values in an expression

(Variable) OUT_MUL_Values :LKP.LKP_UNCONN(DEPTNO)

O_DNAME = REG_EXTRACT(OUT_MUL_Values,'(\w+)\s+(\w+)',1)

O_LOC = REG_EXTRACT(OUT_MUL_Values,'(\w+)\s+(\w+)',2)

> Connect to target...

Best Regards,

Moorthy. G

2012-06-24T18:52:00.001-07:00

SQL QUESTIONS AND ANSWERS

1. Select duplicate value from the table?

select no, name from tt group by no, name having count(no) > 1;

2. Delete duplicate record from the table.

delete from tt a where rowid <> (select max(rowid) from tt b where a.no=b.no);

3. Delete duplicate record from the table which is recent

delete from tt a where rowid <> (select min(rowid) from tt b where a.no=b.no);

4. Delete duplicate record from the table which is old fear.

delete from tt a where rowid <> (select max(rowid) from tt b where a.no=b.no);

5. select top 5 salary

SELECT ENAME, SAL FROM ( SELECT ENAME, SAL FROM EMP ORDER BY SAL DESC) WHERE ROWNUM <=5;

6. select senior 5 people

SELECT ENAME, HIREDATE FROM EMP WHERE ROWNUM <=5

7. select top 2 salary for each dept

SELECT ENAME, SAL, DEPTNO FROM( SELECT ROW_NUMBER() OVER(PARTITION BY DEPTNO ORDER BY SAL DESC) RN, ENAME, SAL, DEPTNO FROM EMP) WHERE RN <=2;

8. select top 2 salary using rank()

SELECT ENAME, SAL FROM(SELECT RANK() OVER(ORDER BY SAL DESC) RN, ENAME, SAL FROM EMP) WHERE RN<=2;

9. What is rank and dense rank?
rank()

1 100
2 50
2 50
4 30

DENSE_rank()

1 100
2 50
2 50
3 30

10. how many employes are working in each deptment table

SELECT D.DEPTNO, COUNT(E.EMPNO) "Number Of Employees" FROM EMP E, DEPT D WHERE E.DEPTNO=D.DEPTNO GROUP BY D.DEPTNO;

12. select name, manager_Name for each employee.

SELECT E.ENAME, E1.ENAME FROM EMP E, EMP E1 WHERE E.MGR=E1.EMPNO;

SELECT E.ENAME, E1.ENAME FROM EMP E LEFT OUTER JOIN EMP E1 ON E.MGR=E1.EMPNO;

13. How to convert column to column in oracle?

SELECT *FROM (
SELECT NAME,
CASE PIVOT
WHEN 1
THEN CUST_VALUE_1
WHEN 2
THEN CUST_VALUE_2
WHEN 3
THEN CUST_VALUE_3
END SALARY FROM TR1,(SELECT ROWNUM PIVOT FROM DUAL CONNECT BY LEVEL <=3))
ORDER BY NAME;

14. Select First and Second Half Record:

select *from emp where rownum <= (select count(*)/2 from emp) -------------- First Half

select *from emp minus select *from emp where rownum <= (select count(*)/2 from emp) --------------- Second Half

2012-06-09T06:43:00.002-07:00

How to create sequence number using trigger?

create sequence SEQ_NAME start with 1;

Sequence created.

SQL> create or replace trigger TRIGG_NAME
2 before insert on TABLE_NAME
3 for each row
4 begin
5 select SEQ_NAME.nextval
6 into :new.COLUMN
7 from dual;
8 end;
9 /

Trigger created.

Regards,
Moorthy. G

How to load Workflow using PMCMD Command

2012-05-07T00:58:00.000-07:00

#!/bin/ksh
DATE=$1
# Reads the variables from the environment file.
. ./.salesenv

/stage_area/pm/script/RemoveDate.sh -d $DATE /stage_area/pm/SrcFiles/basdb A.TNB2.dat B.TNB2.dat C.TNB2.dat
errorcode="$?"
if [ $errorcode -ne 0 ]; then
        echo "Couldn't load with date $DATE, Please chek the files!"
fi
$PMDIR/pmcmd startworkflow -sv ${PMSERVICE} -d ${PMDOMAIN} -u ${PMUSER} -p ${PMLOSEN} -f ${PMFOLDER} -wait bLoadSaleDomainMan
errorcode="$?"
if [ $errorcode -ne 0 ]; then
        echo "Batch failed loading the file with date $ DATE"
        exit 1
fi
rm $LOADDIR/A.TNB2.dat
rm $LOADDIR/B.TNB2.dat
rm $LOADDIR/C.TNB2.dat

What is the use of "plus sign" in where clause?

2011-12-05T23:58:00.000-08:00

Ex:
column = 'String_Value'
column (+) = 'String_Value'

SQL> select d.deptno, d.dname, count(e.empno) cnt
2 from dept d, emp e
3 where e.deptno (+) = d.deptno
4    and e.ename      = 'SMITH'
5 group by d.deptno, d.dname
6 order by d.deptno
7 /
    DEPTNO DNAME                 CNT
---------- -------------- ----------
        20 RESEARCH                1

1 row selected.

SQL> select d.deptno, d.dname, count(e.empno) cnt
2 from dept d, emp e
3 where e.deptno (+) = d.deptno
4    and e.ename (+) = 'SMITH'
5 group by d.deptno, d.dname
6 order by d.deptno
7 /
    DEPTNO DNAME                 CNT
---------- -------------- ----------
        10 ACCOUNTING              0
        20 RESEARCH                    1
        30 SALES                            0
        40 OPERATIONS               0

4 rows selected.
Best Regards,
Moorthy. G

ORA-27101 SHARED MEMORY REALM DOES'NT EXIST

2011-11-26T23:56:00.000-08:00

When you get this error message ->
ERROR:
ORA-01034: ORACLE not available
ORA-27101: shared memory realm does not exist

check that oracle service has been started -
For Windows machine go to run -> type services.msc -> start the respective oracle sevice .
Evan after this the same error comes then manually start the service in command prompt.

For Windows machine go to run -> type cmd

Type the following commands
C:\set oracle_sid = sidname
C:\sqlplus
SQL*Plus: Release 8.1.7.0.0 - Production on Mon Oct 19 14:58:50 2009

(c) Copyright 2000 Oracle Corporation. All rights reserved.

Enter user-name: sys/sys as sysdba
Connected to an idle instance.
SQL> startup
ORACLE instance started.

Total System Global Area 237856796 bytes
Fixed Size 75804 bytes
Variable Size 80416768 bytes
Database Buffers 157286400 bytes
Redo Buffers 77824 bytes
Database mounted.
Database opened.
SQL> exit
Then you can start oracle. It will work.

Best Regards,
Moorthy. G

How to create a table and insert values using script?

2011-11-09T23:46:00.000-08:00

#! /bin/sh
ORACLE_VALUE=`sqlplus -s schema_name/pwd@server_name << EOFSQL
set head off
CREATE TABLE TEST20(NO NUMBER NOT NULL, NAME VARCHAR2(20));
INSERT INTO test20 VALUES (10,'A');
INSERT INTO test20 VALUES (20,'B');
INSERT INTO test20 VALUES (30,'C');
INSERT INTO test20 VALUES (40,'D');
COMMIT;
exit;
EOFSQL`

Informatica Interview Questions And Answers_Part 1

2011-10-23T07:30:00.000-07:00

1. How to get UNIQUE Record?

S-SQ-SRT-EXP-FLT OR RTR – TGT

In Expression:

flag = Decode(true,eid=pre_eid, 'Y','N')

flag_out = flag

pre_eid=eid

2. How to get sequence value using Expression?

v_temp= v_temp+1

o_seq=IIF(ISNULL(v_temp), 0, v_temp)

3. How will restrict values in 0-9, A-Z, a-z and special character. Only allowed these chars otherwise we will reject the records?

Answer:

Using RegMatch in Informatica to validate

IIF(reg_match(in_Email,'^[a-zA-Z][\w\.-]*[a-zA-Z0-9]@[a-zA-Z0-9][\w\.-]*[a-zA-Z0-9]\.[a-zA-Z][a-zA-Z\.]*[a-zA-Z]$'),'Valid','Invalid')

4. What is diff between Decode and case? Give some Example?

In case, you can use logic as >, <, <=,>= , whereas in decode you can only use =.

Example:1

select

case snum

when snum > 10 then 'High'

when snum>5 then 'Low'

end

from sales..

Ex:2

select decode(snum,10,'high',5,'low') from sales...

5. What is the difference between bitmap and btree index?

Bitmap index is used for repeating values.

Ex: Gender: male/female

Account status: Active/Inactive

Btree index is used for unique values.

Ex: Emp_id.

2011-10-13T06:51:00.000-07:00

Renaming a file and append it with a date

$ touch temp
$ mv temp temp_`date +"%Y%m%d"`

Output: temp_20111013

2011-10-13T06:49:00.000-07:00

FTP File Transfer

#! /usr/bin/ksh
ftp -v -n "servername" << cmd
user "username" "pwd"
cd /stage_area/pm/sales/script
lcd /stage_area/pm/sales/script get loadCash_new
put loadCash_new
quit
cmd

Create A Deployment Group In Informatica Power center

2011-10-12T06:14:00.000-07:00

Create A Deployment Group In Informatica Power Center

Step 1. Create a label

In Informatica Power Repository Manager

Click on Versioning à Labels à New

Choose a name like INITXXXXXXX (depending on which INIT this label is for) and write a comment like “Due to Release 10.1”.

Step 2. Applying Labels to Objects

In Informatica Power Center Designer.

Choose the object that you want to apply a label on. For example a mapping like m_LoadSalesDetail

Right click on the mapping à Versioning à View History

Choose the mapping.

Click on Tools à Labels à Apply Labels

If there is a totally new mapping/object you should choose both “Label all Children” and “Label all Parents” and if it´s only an update of a current mapping you only choose “Label all Children”.

Step 3. Create a Deployment Group

You can create two types of deployment groups; Static and Dynamic.

Static – You populate a static deployment group by manually selecting objects. Create a static deployment group if you do not expect the set of deployment objects to change. For example, you might group objects for deployment on a certain date and deploy all objects at once.

Dynamic. You use the result set from an object query to populate the deployment group. Create a dynamic deployment group if you expect the set of deployment objects to change frequently. For example, you can use a dynamic deployment group if you develop multiple objects to deploy on different schedules. You can run the dynamic deployment group query multiple times and add new objects to the group each time you run the query.

Most commonly you choose to create a Static Deployment group.

To create a deployment group:

In the Repository Manager, choose the first mapping.
Click Versioning à Deployment à Groups to view the existing deployment groups in the Deployment Group Browser.

2. Click New to configure the deployment group in the Deployment Group Editor.

3. Enter a name for the deployment group like INITXXXXXXX

4. Select whether to create a static or dynamic deployment group.

5. If you are creating a dynamic deployment group, click Queries to select a query from the Query Browser, and then click Close to return to the Deployment Group Editor.

6. Optionally, enter a comment for the deployment group for ex. Due to Release 10.1

7. Click OK.

After you create a deployment group, it appears in the Deployment Groups node in the Navigator of the Repository

Step 4. Add objects to the deployment group.

In the Repository Manager

Right-click an object you want to add to the deployment group.

Click Versioning à View History à Tools à Add to Deployment group

Choose the deployment group that you want to add the object to.

Loop this step until you have moved all objects that you want to have in the deployment group.

If this was done for a release, don´t forget to update the release notes with the path and name of the deployment group that you have created.

Important SQL Questions And Answers

2011-10-11T03:57:00.000-07:00

1) My boss has changed his mind. Now he doesn't want to pay more than 10,000.so revoke that salary constraint.

SQL>alter table emp modify constraint chk_001 enable;

2) Add column called as mgr to your emp table;

SQL>alter table emp add(mgr number(5));

3) Oh! This column should be related to empno. Give a command to add this constraint.

SQL>ALTER TABLE EMP ADD CONSTRAINT MGR_DEPT FOREIGN KEY(MGR) REFERENCES EMP(EMPNO)

4) Add deptno column to your emp table;

SQL>alter table emp add(deptno number(5));

5) This deptno column should be related to deptno column of dept table;

SQL>alter table emp add constraint dept_001 foreign key(deptno)

reference dept(deptno) [deptno should be primary key]

6) Give the command to add the constraint.

SQL>alter table <table_name) add constraint <constraint_name>

7) Create table called as newemp. Using single command create this table as well as get data into this table(use create table as);

SQL>create table newemp as select * from emp;

SQL>Create table called as newemp. This table should contain only

empno,ename,dname.

SQL>create table newemp as select empno,ename,dname from emp,dept where 1=2;

8) Delete the rows of employees who are working in the company for more than 2 years.

SQL>delete from emp where (sysdate-hiredate)/365>2;

9) Provide a commission(10% Comm Of Sal) to employees who are not earning any commission.

SQL>select sal*0.1 from emp where comm is null

10) If any employee has commission his commission should be incremented by 10% of his salary.

SQL>update emp set comm=sal*.1 where comm is not null;

21) Display employee name and department name for each employee.

SQL>select empno,dname from emp,dept where emp.deptno=dept.deptno

22)Display employee number,name and location of the department in which he is working.

SQL>select empno,ename,loc,dname from emp,dept where emp.deptno=dept.deptno;

23) Display ename,dname even if there are no employees working in a particular department(use outer join).

SQL>select ename,dname from emp,dept where emp.deptno=dept.deptno(+)

24) Display employee name and his manager name.

SQL>select p.ename,e.ename from emp e,emp p where e.empno=p.mgr;

25) Display the department name and total number of employees in each department.

SQL>select dname,count(ename) from emp,dept where emp.deptno=dept.deptno group by dname;

26)Display the department name along with total salary in each department.

SQL>select dname,sum(sal) from emp,dept where emp.deptno=dept.deptno group by dname;

27) Display itemname and total sales amount for each item.

SQL>select itemname,sum(amount) from item group by itemname;

28) Write a Query To Delete The Repeated Rows from emp table;

SQL>Delete from emp where rowid not in(select min(rowid)from emp group by ename)

29) TO DISPLAY 5 TO 7 ROWS FROM A TABLE

SQL>select ename from emp where rowid in(select rowid from emp where rownum<=7 minus select rowid from empi where rownum<5)

30) DISPLAY TOP N ROWS FROM TABLE?

SQL>SELECT * FROM (SELECT * FROM EMP ORDER BY ENAME DESC)WHERE ROWNUM <10;

31) DISPLAY TOP 3 SALARIES FROM EMP;

SQL>SELECT SAL FROM ( SELECT * FROM EMP ORDER BY SAL DESC ) WHERE ROWNUM <4

32) DISPLAY 9th FROM THE EMP TABLE?

SQL>SELECT ENAME FROM EMPWHERE ROWID=(SELECT ROWID FROM EMP WHERE ROWNUM<=10 MINUS SELECT ROWID FROM EMP WHERE ROWNUM <10)

33) select second max salary from emp;

select max(sal) fromemp where sal<(select max(sal) from emp);