Tuesday, January 5, 2021

Query to check owner of each table in Redshift

 

Query to check owner of each table in Redshift

SELECT n.nspname AS schema_name,

       pg_get_userbyid(c.relowner) AS table_owner,

       c.relname AS table_name,

       CASE

         WHEN c.relkind = 'v' THEN 'view'

         ELSE 'table'

       END AS table_type,

       d.description AS table_description

FROM pg_class AS c

  LEFT JOIN pg_namespace n ON n.oid = c.relnamespace

  LEFT JOIN pg_tablespace t ON t.oid = c.reltablespace

  LEFT JOIN pg_description AS d

         ON (d.objoid = c.oid

        AND d.objsubid = 0)

WHERE c.relkind IN ('r','v')

ORDER BY n.nspname,

         c.relname


List Metadata Exchange Connections

 



SELECT REPOSITORY_ID,

       REPOSITORY_NAME,

       REPO_TYPE_NAME,

       IMW_REPOSITORY.DELETED_FLAG,

       EFF_FROM_DT,

       EFF_TO_DT

FROM IMW_REPOSITORY,

     IMW_REPO_TYPE

WHERE IMW_REPOSITORY.REPO_TYPE_UID = IMW_REPO_TYPE.REPO_TYPE_UID

AND   IMW_REPOSITORY.DELETED_FLAG = 'N'

AND   REPOSITORY_ID <>('IMM_MASTER_REPOSITORY')

ORDER BY REPOSITORY_UID

Data Quality Elements (DQ Mapplets within PowerCenter)


SELECT OPB_SUBJECT.SUBJ_NAME AS PROJ,

       OPB_MAPPING.MAPPING_NAME AS MAPPING,

       OPB_WIDGET_INST.INSTANCE_NAME AS TRANSFORMATION,

       OPB_WIDGET.LAST_SAVED AS LAST_SAVED

FROM OPB_WIDGET_INST

  INNER JOIN OPB_MAPPING ON OPB_WIDGET_INST.MAPPING_ID = OPB_MAPPING.MAPPING_ID

  INNER JOIN OPB_SUBJECT ON OPB_MAPPING.SUBJECT_ID = OPB_SUBJECT.SUBJ_ID

  INNER JOIN OPB_WIDGET ON OPB_WIDGET_INST.WIDGET_ID = OPB_WIDGET.WIDGET_ID

WHERE OPB_WIDGET.TEMPLATE_ID IN (SELECT PLG_ID FROM OPB_MMD_CATALOG WHERE PLG_DESC LIKE '%Data Quality%');





List Data Quality Mappings


Execute the following query in your Model Repositories (“MRS Repository”) Schema.


 SELECT PRD_ATTR_VALUE,

       PRR_TYPENAME

FROM PR_RESOURCE,

     PR_ATTRIBUTE

WHERE PR_RESOURCE.PRR_ID = PR_ATTRIBUTE.PRA_RESOURCE

AND   PR_ATTRIBUTE.PRD_ATTR_NAME = 'REPO.SERVICE_PROP.UTC_CREATION_TIME_ATTR'

AND   PR_RESOURCE.PRR_TYPENAME LIKE '%TokenStandardiserTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%AssociationTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%AvTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%CaseConverterTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%ClassifierTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%ConsolidationTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%DecisionTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%GroupTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%LabelerTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%MatcherTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%MergeTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%ParserTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%StringDistanceTxImpl'

OR    PR_RESOURCE.PRR_TYPENAME LIKE '%WeightBasedAnalyserTxImpl'