INFORMATICA METADATA REPOSITORY TABLES AND INFORMATION

INFORMATICA METADATA REPOSITORY TABLES:-



WHILE BROWSING THROUGH THE INFORMATICA REP AND OPB TABLES, WE ARE USUALLY STUCK UP AS WE DO NOT UNDERSTAND
WHAT THE WIDGET_ID AND WIDGET_TYPES ARE FOR.

BELOW IS THE LIST OF WIDGET_TYPES. THIS DATA WOULD HELP US EASE THE HANDLING OF METADATA.

WIDGET IDS AND TRANSFORMATION TYPES
WIDGET_TYPE    TYPE OF TRANSFORMATION
1    SOURCE   
2    TARGET   
3    SOURCE QUALIFIER   
4    UPDATE STRATEGY   
5    EXPRESSION   
6    STORED PROCEDURES   
7    SEQUENCE GENERATOR   
8    EXTERNAL PROCEDURES   
9    AGGREGATOR   
10    FILTER   
11    LOOKUP   
12    JOINER   
14    NORMALIZER   
15    ROUTER   
26    RANK   
44    MAPPLET   
46    MAPPLET INPUT   
47    MAPPLET OUTPUT   
55    XML SOURCE QUALIFIER   
80    SORTER   
97    CUSTOM TRANSFORMATION   
WHAT ARE WIDGET IDS AND WHY DO WE REQUIRE THEM?
INFORMATICA MAINTAINS METEDATA REGARDING THE MAPPINGS AND ITS TRANFORMATIONS, SESSIONS, WORKFLOWS AND THEIR STATISTICS.
THESE DETAILS ARE MAINTAINED IN A SET OF TABLES CALLED OPB TABLES AND REP TABLES.

THE WIDGET REFERS TO THE TYPES OF TRANSFORMATION DETAILS STORED IN THESE TABLES.

PORT TYPES IN A TRANSFORMATION
AS THE ABOVE SECTION DETAILS, WIDGET IS A TRANSFORMATION IN METADATA TABLES. TO GET THE PORT TYPE FROM REPOSITORY TABLE,
BELOW IS THE SQL SNIPPET TO USE

SELECT A.WIDGET_ID, DECODE(A.PORTTYPE, 1, 'INPUT', 3, 'IN-OUT', 2, 'OUT', 32, 'VARIABLE', 8, 'LOOKUP', 10, 'OUT-LOOKUP',
TO_CHAR(A.PORTTYPE)) PORT_TYPE FROM OPB_WIDGET_FIELD A;
IF YOU WANT TO KNOW THE MAPPING NAME, THEN MATCH THE WIDGET_ID AGAINST THE WIDGET_ID OF OPB_WIDGET_INST AND THEN PULL
THE MAPPING_ID WHICH CAN BE MAPPED AGAINST MAPPING_ID IN OPB_MAPPINGS TABLE. IF YOU WANT TO KNOW THE FOLDER NAME, THEN
MAP THE SUBJECT_ID FROM OPB_MAPPINGS TO THAT OF SUBJ_ID IN OPB_SUBJECTS TABLE TO GET THE SUBJECT_NAME.

EXPRESSIONS AND SQL OVERRIDES IN A TRANSFORMATION
OPB_EXPRESSION IS THE TABLE THAT STORES ALL THE EXPRESSIONS IN METADATA. TO ASSOCIATE AN EXPRESSION TO A FIELD IN A
TRANSFORMATION, OPB_WIDG_EXPR IS THE TABLE TO BE USED.

SELECT G.EXPRESSION FROM OPB_WIDGET_EXPR F, OPB_EXPRESSION G WHERE F.EXPR_ID = G.EXPR_ID
SQL OVERRIDES CAN BE IN SOURCE QUALIFIERS AND LOOKUP TRANSFORMATIONS. TO GET THE SQL OVERRIDE FROM METADATA,
CHECK REP_WIDGET_ATTR.ATTR_VALUE COLUMN.


SEARCH FOR TRUNCATE TABLE OPTION
------------------------------------

SELECT
   TASK_NAME,
   'TRUNCATE TARGET TABLE' ATTR,
   DECODE(ATTR_VALUE,1,'YES','NO') VALUE
FROM OPB_EXTN_ATTR OEA,
     REP_ALL_TASKS RAT 
WHERE
      OEA.SESSION_ID=RAT.TASK_ID
      AND ATTR_ID=9
     
FIND TRACING LEVELS FOR INFORMATICA SESSIONS
----------------------------------------------

SELECT
       TASK_NAME,
       DECODE (ATTR_VALUE,
                0,'NONE',
                1,'TERSE',
                2,'NORMAL',
                3,'VERBOSE INITIALISATION',
                4,'VERBOSE DATA','') TRACING_LEVEL
FROM
     REP_SESS_CONFIG_PARM CFG,
     OPB_TASK TSK
WHERE
      CFG.SESSION_ID=TSK.TASK_ID
      AND TSK.TASK_TYPE=68
      AND ATTR_ID=204
      AND ATTR_TYPE=6

 FIND NAME OF ALL STORED PROCEDURE USED IN STORED PROCEDURE TRANSFORMATION
 -------------------------------------------------------------------------
 SELECT
        ATTR_VALUE
 FROM
       OPB_WIDGET_ATTR
 WHERE
       WIDGET_TYPE=6
      AND ATTR_ID=1


 FIND WHO MODIFIED (SAVED) A MAPPING LAST TIME
 -------------------------------------------------

 SELECT
        SUBSTR(RPL.EVENT_TIME,7,4) || SUBSTR(RPL.EVENT_TIME,6,1) ||
        SUBSTR(RPL.EVENT_TIME,1,5) ||  ' ' SUBSTR(RPL.EVENT_TIME,12,11) "EVENTTIMESTAMP" ,
        USR.USER_NAME "USERNAME",
        DECODE(RPL.OBJECT_TYPE_ID,21,S21.SUBJ_NAME,('('RPL.OBJECT_TYPE_ID')')) "FOLDER",
        OBT.OBJECT_TYPE_NAME "TYPE",
        DECODE(RPL.OBJECT_TYPE_ID,21,MAP.MAPPING_NAME,('('RPL.OBJECT_TYPE_ID')')) "OBJECT"
 FROM
        OPB_REPOSIT_LOG RPL,
        OPB_OBJECT_TYPE OBT,
        OPB_SUBJECT FLD,
        OPB_MAPPING MAP,
        OPB_USERS USR,
        OPB_SUBJECT S21
 WHERE
        OBT.OBJECT_TYPE_NAME = 'MAPPING'
        AND RPL.OBJECT_TYPE_ID = OBT.OBJECT_TYPE_ID
        AND RPL.OBJECT_ID = MAP.MAPPING_ID(+)
        AND RPL.OBJECT_ID = FLD.SUBJ_ID(+)
        AND RPL.EVENT_UID = USR.USER_ID
        AND MAP.SUBJECT_ID = S21.SUBJ_ID(+)
 ORDER BY
       1 DESC
      
      
FIND LOOKUP INFORMATION FROM REPOSITORY
-----------------------------------------
SELECT DISTINCT
       WID.WIDGET_ID,
       ALL_MAP.MAPPING_NAME,
       WID.INSTANCE_NAME LKP_NAME,
       DECODE(WIDAT.ATTR_ID,2,WIDAT.ATTR_VALUE) TABLE_NAME,
       DECODE (WIDAT.ATTR_ID,6,WIDAT.ATTR_VALUE) SRC_TGT
FROM 
       REP_ALL_MAPPINGS ALL_MAP,
       REP_WIDGET_INST WID, 
       OPB_WIDGET_ATTR WIDAT
WHERE
       ALL_MAP.MAPPING_ID=WID.MAPPING_ID
       AND WID.WIDGET_ID=WIDAT.WIDGET_ID
       AND WID.WIDGET_TYPE=11
       AND WIDAT.WIDGET_TYPE=11
       AND WIDAT.ATTR_ID IN (2,6)


--------------------------------------------------------------
SELECT SUBJECT_AREA,MAPPING_NAME,WIDGET_TYPE_NAME,INSTANCE_NAME FROM REP_WIDGET_INST A,REP_ALL_MAPPINGS C
WHERE WIDGET_TYPE IN (1,2) AND  VERSION_NUMBER=(SELECT MAX(VERSION_NUMBER) FROM REP_WIDGET_INST B WHERE
A.MAPPING_ID=B.MAPPING_ID AND A.WIDGET_ID=B.WIDGET_ID)  AND A.MAPPING_ID=C.MAPPING_ID AND A.SUBJECT_ID=C.SUBJECT_ID