INFORMATICA REPO QUERIES


POWERCENTER METADATA QUERIES
    1        Overview

    2        FOLDER
    2.1        List folder details
    2.2        List of shared folders
    2.3        List of Users and Groups having Privileges on Folders
    2.4        List of Folder Owners


    3        SOURCE
    3.1        List of source tables
    3.2        List and count of tables in each folder by db type
    3.3        List and count of tables overall used
    3.4        List of source tables used in mappings
    3.5        List of Sources tables using as Shortcuts
    3.6        List Sequence Generator Value
    3.7        List Source tables, Mapping Names and Source Database Name      
    4        TARGET
    4.1        List of Target Tables
    4.2        List and count of tables in each folder by db type
    4.3        List and count of table overall used
    4.4       List Target table used in session level
    4.5       Truncate target Table Option

    5        TRANSFORMATION
    5.1        List of filter transformations
    5.2        List of Sequence transformations
    5.3        List of tables used as lookups
    5.4        List of transformations using sql overrides
    5.5        List all transformations
    5.6        List all Expression transformations using ‘concat’ function
    5.7        List of all port details of an Expression transformations
    5.8        List of all Expression transformation port links
    5.9        List of LKP transformation port links used in mappings
    5.10    Identify ports in EXP (I, IO, O,V Ports)
    5.11    SQ and Lookup SQL
    5.12    B2B Transformation List
    6        MAPPING
    6.1        List mapping names
    6.2        List total count of mappings
    6.3        List last saved user for a mapping
    6.4        List Mapping parameters and variables
    6.5        List all Mappings using PARALLEL hints

    7        MAPPLET
    7.1        List Mapplets in all folders 
    7.2        List Mapplet parameters and variables

    8        SESSION
    8.1        List session names
    8.2        List save session log count
    8.3        List stop on errors count
    8.4        List hardcoded paths
    8.5        List parameter file paths
    8.6        List session log names
    8.7        List commit intervals
    8.8        List total source partitions
    8.9        List total target partitions
    8.10     List DTM Buffer Size
    8.11     List collect performance data
    8.12     List Incremental Aggregation
    8.13     List Reinitialize aggregate cache
    8.14     List Enable high precision
    8.15     List Session retry on deadlock
    8.16     List write backward compatible check
    8.17     List over ride tracing
    8.18     List save session log by
    8.19     List load type
    8.20     List 'post_session_success_command' in session
    8.21     List of all emails with attachment
    8.22     List Invalid Sessions and Workflows
    8.23    List of session run history – Long running sessions
    8.24    List Bad file dir name of a session
    8.25    List Throughput of the session
    8.26    list the current memory settings (buffer block size, etc.)
    8.27    Pushdown Optimization
    8.28    Persistent Cache

    9        TASKS
    9.1        List command tasks
    9.2        List decision tasks
    9.3        List Event Wait tasks
    9.4       List Event Wait tasks

    10     WORKLET
    10.1     List worklet names
    10.2     List hierarchies of all workflows and its worklets


              INFORMATICA REPOSITORY QUERIES - PART II

    11     WORKFLOW
    11.1     List workflow names
    11.2     List save workflow log count
    11.3     List workflow log names
    11.4     List write backward compatible check
    11.5     List fail_parent_if_task_fails objects
    11.6     List fail_parent_if_task_dont_run objects
    11.7     List is_task_enabled objects
    11.8     List treat_input_links_as objects
    11.9     List all workflows whose server is not assigned
    11.10   List of workflow run details
    11.11   List of sessions last run details
    11.12   List of workflows currently running
    11.13   Check Stats of wf run (cpu usage)


    12     CONNECTIONS
    12.1     List of cnxs using alter in env sql
    12.2     List of cnxs used in session levels
    12.3     List Lotus connection details
    12.4     ODBC / SQL Server Connection details
    12.5     List of sessions used by a connection
    12.6     List all Connections with User and Privileges
    12.7    Query to fetch connection details users list and privileges
    12.8    Connections with Attributes
    12.9    List of Source and Target used in Session Level
    12.10   List of connection names with Attribute details
    12.11   Query used for connection cleanup
    12.12   List sessions last run by using a connection
    12.13   Kill User Connection

      INFORMATICA REPOSITORY QUERIES - PART III
      13 GROUPS & USERS
           13.1 User , Group  and Status of User
           13.2 User, Group and Status of User for PC 9.1
           13.3 Kill User
       
      INFORMATICA REPOSITORY QUERIES - PART IV
      14 REPOSITORY
          14.1 Repository Info
          14.2 List of objects which are Not Valid
          14.3 List of objects which are failed in last 5 days
          14.4 List where all a table is used
          14.5 List all source and target tables of mapping
          14.6 List comments of all objects
          14.7 List of Database used in Repository 


      15 MISLENIOUS
         15.1 Query to find list of objects last saved by user
         15.2 List the name of the object, type, date and last saved
         15.3 List Folder,wf,sess,maping,src and trg
         15.4 List Locks of objects in reposit
         15.5 How to Remove Node
         15.6 How to convert binary log to text format
         15.7 PMSTACK

      INFORMATICA REPOSITORY QUERIES - PART V

      16 SCHEDULER
      16.1 Check Scheduled workflows using pmcmd command
      16.2 Check Scheduled workflows using queries 
      17 INTEGRATION SERVICES
      17.1 Count of workflows running on each Integration Service
      17.2 List of workflows running on each Integration Service
      17.3 How to update connections in Mass


            List of users connected to client tools - PART VI




    11 comments:

    1. Hi, very usefull thx!! Do you how to get impacted mappings quering the metadata repo?

      ReplyDelete
    2. Hi, Do you have query to find lineage/dependencies between workflows

      ReplyDelete
    3. This comment has been removed by the author.

      ReplyDelete
    4. do you have any query to get cyclic and non cyclic jobs

      ReplyDelete
    5. how to get list of scheduled jobs

      ReplyDelete
    6. Hi I'm trying to extract the list of mapping name , session name , workflow name using source table name

      ReplyDelete
    7. The very best document I found some time, generate anything concerning this in this webpage. glock 35 mos

      ReplyDelete
    8. it's very interesting blog.thanks for sharing these information with all of us. Kinemaster Gold

      ReplyDelete

    9. I like your all post. You have done really good work. Thank you for the information you provide.
      XCrack.org
      Sublime Text Crack
      AnyToISO Crack
      Mixcraft Crack
      Adobe After Effects CC Crack

      ReplyDelete
    10. such a significant topic. Keep producing blogs like this since your content always inspires and attracts us and aids in the growth of our company..ERP Software in Chennai

      ReplyDelete