Friday, January 20, 2017

Informatica Metadata Queries - 4 SOURCE TABLES



4.1         List of source tables


SELECT
B.SUBJ_NAME,
C.DBDNAM,
D.DBTYPE_NAME,
A.SOURCE_NAME AS TABLE_NAME,
A.FILE_NAME SCHEMA_NAME,
A.OWNERNAME
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND A.SOURCE_NAME <> A.FILE_NAME
ORDER BY 1,2,3,4,5

4.2         List and count of tables in each folder by db type


SELECT
B.SUBJ_NAME,
D.DBTYPE_NAME,
count(*)
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND A.SOURCE_NAME <> A.FILE_NAME
group by B.SUBJ_NAME,D.DBTYPE_NAME
order by 1,2,3

4.3         List and count of tables overall used


SELECT SOURCE_NAME, COUNT(SOURCE_NAME) FROM REP_TBL_MAPPING
GROUP BY SOURCE_NAME
ORDER BY 1,2 ASC

4.4         List of source tables used in mapping


SELECT SUBJECT_AREA,SOURCE_NAME,MAPPING_NAME FROM REP_SRC_MAPPING
ORDER BY 1,2,3

4.5         List of source tables using as shortcuts


SELECT DISTINCT
B.SUBJ_NAME,
C.DBDNAM,
D.DBTYPE_NAME,
A.SOURCE_NAME AS TABLE_NAME,
A.FILE_NAME SCHEMA_NAME,
A.OWNERNAME
FROM
OPB_SRC A,OPB_SUBJECT B, OPB_DBD C,OPB_MMD_DBTYPE D
WHERE A.SUBJ_ID = B.SUBJ_ID
AND A.DBDID = C.DBDID
AND C.DBTYPE = D.DBTYPE_ID
--AND A.SOURCE_NAME <> A.FILE_NAME
and A.SOURCE_NAME like 'sc_%'
ORDER BY 1,2,3,4,5



4.6         List Source table where all its used in Mapping with Source Database Name


This view provides a list of the latest version of sources defined in each folder of a repository. Sources include both relational sources and non-relational sources such as XML files and flat files.

SELECT REP_ALL_SOURCES.SOURCE_NAME,
       REP_SRC_MAPPING.MAPPING_NAME,
       REP_ALL_SOURCES.SOURCE_DATABASE_NAME
  FROM REP_SRC_MAPPING, REP_ALL_SOURCES
 WHERE REP_SRC_MAPPING.SOURCE_ID(+) = REP_ALL_SOURCES.SOURCE_ID
       AND REP_SRC_MAPPING.SOURCE_NAME(+) =
              REP_ALL_SOURCES.PARENT_SOURCE_NAME
       AND REP_SRC_MAPPING.SUBJECT_ID(+) = REP_ALL_SOURCES.SUBJECT_ID
ORDER BY 1,2




4.7         List All Columns, DB, Length of a Source Table


SELECT
  SUBJECT_AREA,
  PARENT_SOURCE_DATABASE_NAME,
  PARENT_SOURCE_DATABASE_TYPE,
  PARENT_SOURCE_NAME as SOURCE_TABLE_NAME,
  SOURCE_FIELD_NUMBER,
  SOURCE_FIELD_NAME,
  SOURCE_FIELD_DATATYPE,
  SOURCE_FIELD_PHYSICAL_LENGTH,
  SOURCE_FIELD_KEY_TYPE,
  PARENT_SOURCE_LAST_SAVED
from REP_ALL_SOURCE_FLDS
where parent_source_name = 'SRC_TABLE_NAME'
and parent_subject_area = 'FOLDER_NAME'
ORDER BY SOURCE_FIELD_NUMBER

4.8         List all Flat files used as Source


This view provides a list of all file definitions in the repository. Use FIRST_FIELD_ID to retrieve the fields belonging to a non-relational source by following the links in the REP_SRC_FILE_FLDS view. Any flat file imported through the Source Analyzer has an entry.


SELECT *
FROM
REP_SRC_FILES
WHERE
subject_area = 'FOLDER_NAME'




4.9         List all Relational Tables used as Source


This view provides a list of relational database table sources that have been analyzed through the Source Analyzer tool or imported from a DDL (Data Definition Language) file.


SELECT *
FROM
REP_SRC_TBLS
WHERE
subject_area = 'FOLDER_NAME'
ORDER BY 1

4.10      Check where all a column is used in different tables and Folders.


SELECT *
FROM
REP_SRC_TBL_FLDS
WHERE
Column_Name = 'PH_DIMSN_KEY'
order by 1


6 comments:

  1. Replies
    1. IEEE Final Year Project centers make amazing deep learning final year projects ideas for final year students Final Year Projects for CSE to training and develop their deep learning experience and talents.

      IEEE Final Year projects Project Centers in India are consistently sought after. Final Year Students Projects take a shot at them to improve their aptitudes, while specialists like the enjoyment in interfering with innovation.

      corporate training in chennai corporate training in chennai

      corporate training companies in india corporate training companies in india

      corporate training companies in chennai corporate training companies in chennai

      I have read your blog its very attractive and impressive. I like it your blog. Digital Marketing Company in Chennai

      Delete
  2. I’m excited to uncover this page. I need to to thank you for ones time for this particularly fantastic read !! I definitely really liked every part of it and i also have you saved to fav to look at new information in your site.
    data science course in guwahati

    ReplyDelete
  3. I see some amazingly important and kept up to length of your strength searching for in your on the site
    data science course in guwahati

    ReplyDelete
  4. Really an awesome blog, Informative content. Keep sharing more stuff like this. Thank you.
    Data Science Institute in Hyderabad

    ReplyDelete