How to make reusable transformation into nonreusable transformation?

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

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

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;

/

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;

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

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

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

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

#!/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