INFORMATICA REPO QUERIES

POWERCENTER METADATA QUERIES

INFORMATICA REPOSITORY QUERIES - PART I

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