My Journey to my ML Learning

Built in Data Structures, Functions and File handling in Python

Data Structures and Sequence¶

• Tuple {tup = (1,2,3)}
  • UNPACKING TUPLES {tup = (4, 5, 6); a, b, c = tup}
  • TUPLE METHODS
    • count { a = (1, 2, 2, 2, 3, 4, 2); a.count(2)
• List {mylist = [1,2,3,4]}
  • ADDING AND REMOVING ELEMENTS { b_list.append('dwarf'); b_list.insert(1, 'red');
    • insert is computationally expensive compared with append
  • The inverse operation to insert is pop { b_list.pop(2)}
  • Elements can be removed by value with remove { b_list.remove('foo')}
  • Check if a list contains a value using the in keyword: { 'dwarf' in b_list}
  • CONCATENATING AND COMBINING LISTS { [4, None, 'foo'] + [7, 8, (2, 3)]}
  • you can append multiple elements to it using the extend method: { x = [4, None, 'foo']; x.extend([7, 8, (2, 3)])}
  • Using extend to append elements to an existing list, especially if you are building up a large list, is usually preferable
  • SORTING {a = [7, 2, 5, 1, 3];a.sort()}
  • Sort has a few options that will occasionally come in handy. One is the ability to pass a secondary sort key—that is, a function that produces a value to use to sort the objects { b = ['saw', 'small', 'He', 'foxes', 'six']; b.sort(key=len)}
  • BINARY SEARCH AND MAINTAINING A SORTED LIST { import bisect; c = [1, 2, 2, 2, 3, 4, 7]; bisect.bisect(c, 2)}
  • bisect.insort actually inserts the element into that location: { bisect.insort(c, 6)}
  • SLICING
    • start:stop passed to the indexing operator [], start index is included, the stop index is not included
    • A step can also be used after a second colon to, say, take every other element
    • A clever use of this is to pass -1, which has the useful effect of reversing a list or tuple:
• Built-in Sequence Functions
  • ENUMERATE: Python has a built-in function, enumerate, which returns a sequence of (i, value) tuples: { for i, value in enumerate(collection):; #do something with value}
  • SORTED: The sorted function returns a new sorted list from the elements of any sequence:
  • ZIP: zip “pairs” up the elements of a number of lists, tuples, or other sequences to create a list of tuples {seq1 = ['foo', 'bar', 'baz']; seq2 = ['one', 'two', 'three']; zipped = zip(seq1, seq2)}
    • A very common use of zip is simultaneously iterating over multiple sequences, possibly also combined with enumerate: {for i, (a, b) in enumerate(zip(seq1, seq2)):;print('{0}: {1}, {2}'.format(i, a, b))}
  • REVERSED: reversed iterates over the elements of a sequence in reverse order:
• Dict: dict is likely the most important built-in Python data structure. A more common name for it is hash map or associative array ( empty_dict = {}; d1 = {'a' : 'some value', 'b' : [1, 2, 3, 4]} )
  • You can delete values either using the del keyword or the pop method ( d1 = {5:'Some other','a' : 'some value', 'b' : [1, 2, 3, 4]}; del d1[5] or ret = d1.pop(5) )
  • You can merge one dict into another using the update method:( d1.update({'b' : 'foo', 'c' : 12}) )
  • CREATING DICTS FROM SEQUENCES { mapping = dict(zip(range(5), reversed(range(5)))) }
  • DEFAULT VALUES
    • the dict methods get and pop can take a default value to be returned {value = some_dict.get(key, default_value)}
    • The built-in collections module has a useful class, defaultdict { from collections import defaultdict; by_letter = defaultdict(list);
  • VALID DICT KEY TYPES
    • The keys generally have to be immutable objects like scalar types (int, float, string) or tuples (all the objects in the tuple need to be immutable, too) - term here is hashability {hash('string'); hash((1, 2, [2, 3])) # fails because lists are mutable}
• set : A set is an unordered collection of unique elements {set([2, 2, 2, 1, 3, 3])}
  • Sets support mathematical set operations like union, intersection, difference, and symmetric difference. Consider these two example sets { a = {1, 2, 3, 4, 5}; b = {3, 4, 5, 6, 7, 8}; a.union(b); a | b; a.intersection(b); a & b;}
• List, Set, and Dict Comprehensions
  • List comprehension: [expr for val in collection if condition] { strings = ['a', 'as', 'bat', 'car', 'dove', 'python']; x.upper() for x in strings if len(x) > 2}
  • Dict comprehension: dict_comp = {key-expr : value-expr for value in collection if condition}
  • Set comprehension: set_comp = {expr for value in collection if condition}
  • NESTED LIST COMPREHENSIONS {result = [name for names in all_data for name in names if name.count('e') >= 2] }
• Functions - Functions are declared with the def keyword and returned from with the return keyword: def my_function(x, y, z=1.5): if z > 1:

    return z * (x + y)

  else:

    return z / (x + y)

• There is no issue with having multiple return statements. If Python reaches the end of a function without encountering a return statement, None is returned automatically.
• Each function can have positional arguments and keyword arguments. Keyword arguments are most commonly used to specify default values or optional arguments
• The main restriction on function arguments is that the keyword arguments must follow the positional arguments (if any).
• Namespaces, Scope, and Local Functions
  • Assigning variables outside of the function’s scope is possible, but those variables must be declared as global via the global keyword: def bind_a_variable(): global a a = [] bind_a_variable()
  • generally discourage use of the global keyword
• Returning Multiple Values def f():

    a = 5
    b = 6
    c = 7
    return a, b, c

  a, b, c = f()
• Functions Are Objects { clean_ops = [str.title, str.strip]}
• MAP function: Used to map function with object { for x in map(str.title, value):;print(x)}
• Anonymos(Lambda) functions: way of writing functions consisting of a single statement, the result of which is the return value def short_function(x):

    retrun x*2

  equiv_anon = lambda x:x*2

NumPy Basics: Arrays and Vectorized Computation

• NumPy is better (10 - 100 times) for vector computation from performance prospective

The NumPy ndarray: A Multidimensional Array Object

• Creating ndarrays

Cisco Workload Automation (CWA) Metadata Queries

-- Data warehouse group dependencies - VQA
SELECT
    jdp.jobdep_id,
    jm1.jobmst_id,
    jm1.jobmst_name "JOB_NAME/GROUP_NAME",
    jm2.jobmst_name JOB_DEPENDS_ON_JOB,
    '"' + jm2.jobmst_name + '"->"' + jm1.jobmst_name + '";' FOR_GRAPHVIZ,
    jm1.jobmst_prntname JOB_FULL_PATH,
    REVERSE(SUBSTRING(REVERSE(jm1.jobmst_prntname), 0, PATINDEX('%\%',REVERSE(jm1.jobmst_prntname)))) JOB_GROUP_NAME,
    SUBSTRING (jd.jobdtl_cmd,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd) + 1,
               200) AS PROJECT_FOLDER,
    SUBSTRING (jd.jobdtl_cmd,
               0,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd)) AS WORKFLOW
FROM
    dbo.jobmst jm1
    LEFT JOIN
    dbo.jobdep jdp
    ON jm1.jobmst_id = jdp.jobmst_id
    LEFT JOIN
    dbo.jobmst jm2
    ON jm2.jobmst_id = jdp.jobdep_jobmst
    left join
    dbo.jobdtl jd
    ON jd.jobdtl_id = jm1.jobmst_id
WHERE jm1.jobmst_active = 'Y' AND
    (jm1.jobmst_prntname LIKE '%VQA - Warehouse US Group%' )
    -- and jd.servicemst_id is not null
-- Data warehouse group dependencies
SELECT
    jdp.jobdep_id,
    jm1.jobmst_id,
    jm1.jobmst_name "JOB_NAME/GROUP_NAME",
    jm2.jobmst_name JOB_DEPENDS_ON_JOB,
    '"' + jm2.jobmst_name + '"->"' + jm1.jobmst_name + '";' FOR_GRAPHVIZ,
    jm1.jobmst_prntname JOB_FULL_PATH,
    REVERSE(SUBSTRING(REVERSE(jm1.jobmst_prntname), 0, PATINDEX('%\%',REVERSE(jm1.jobmst_prntname)))) JOB_GROUP_NAME,
    SUBSTRING (jd.jobdtl_cmd,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd) + 1,
               200) AS PROJECT_FOLDER,
    SUBSTRING (jd.jobdtl_cmd,
               0,
               PATINDEX ('%@%',
                         jd.jobdtl_cmd)) AS WORKFLOW
FROM
    dbo.jobmst jm1
    LEFT JOIN
    dbo.jobdep jdp
    ON jm1.jobmst_id = jdp.jobmst_id
    LEFT JOIN
    dbo.jobmst jm2
    ON jm2.jobmst_id = jdp.jobdep_jobmst
    left join
    dbo.jobdtl jd
    ON jd.jobdtl_id = jm1.jobmst_id
WHERE jm1.jobmst_active = 'Y' AND
    (jm1.jobmst_prntname LIKE '%Test BCV Data Warehouse Group A%' )
    -- and jd.servicemst_id is not null


-- ABS Job dependencies

SELECT
    jdp.jobdep_id,
    jm1.jobmst_name Job_name,
    jm2.jobmst_name depends_on,
    '"' + jm2.jobmst_name + '"->"' + jm1.jobmst_name + '";',
    jm2.jobmst_prntname
FROM
    dbo.jobdep jdp
    LEFT JOIN
    dbo.jobmst jm1
    ON jm1.jobmst_id = jdp.jobmst_id
    LEFT JOIN
    dbo.jobmst jm2
    ON jm2.jobmst_id = jdp.jobdep_jobmst
WHERE jm1.jobmst_active = 'Y' and
jm1.jobmst_prntname LIKE '%ABS%'

How to find Date Gap Issue in Type-2 data?

How to find Multiple Open records:

Multiple active records in the table is not good for EDW. Users will be very much affected by these. So, it is very much important for testers to test properly and carefully for multiple open records. For any change in the table always validate multiple open records. Below is the sample query to test them:

SELECT ACCT_NBR, COUNT(1)

FROM DW_MART.ACCT_PAYOFF_QUOTE_DIM

WHERE CLOSE_DT_ID = 99991231

GROUP BY ACCT_NBR

HAVING COUNT(1)>1;

How to find Date Gaps or Date Overlapping:

Date gaps and data overlapping means the records is not closing properly in the warehouse.

ACCT_NBR	OPEN_DT_ID	CLOSE_DT_ID
864175344	19000101	20130115
864175344	20130120	20130130	The record in the previous row is expiring at 2013-01-15, but in the next row it is opening at 2013-01-20, that means there is a date gap of 5 days which is not correct.
864175344	20130130	99991231	The record in the previous row is expiring at 2013-01-15, but in the next row it is opening at 2013-01-30 again, that means there is a date overlapping on the same day,  which is not correct.

The below is sample query, based on the natural keys, and table name please change the query appropriately.

SELECT

    *

FROM

    (SELECT

        ACCT_NBR,

        OPEN_DT_ID,

        TO_DATE (CLOSE_DT_ID, 'YYYYMMDD') - TO_DATE(LEAD(OPEN_DT_ID,1,CLOSE_DT_ID)

            OVER (PARTITION BY ACCT_NBR ORDER BY CLOSE_DT_ID ASC), 'YYYYMMDD') BOUNDARY_CHECK

    FROM

        DW_MART.ACCT_PAYOFF_QUOTE_DIM)

WHERE

    BOUNDARY_CHECK NOT IN (0, -1, 2917854);

How to find Logical duplicate records in Type-2?

Compile following procedure using procedure code provided below.

Procedure Code: LOGICAL_DUP_CHECKER

CREATE OR REPLACE PROCEDURE DW_DF.LOGICAL_DUP_CHECKER

    (

        schema_name_i IN VARCHAR2,

        table_name_i IN VARCHAR2,

        natural_keylist_i IN VARCHAR2,-- comma delimited column list

        metadata_collist_i IN VARCHAR2, -- comma delimited column list

        ocolumn_name_i IN VARCHAR2

    )

    AS

        TYPE Sql_Lines IS TABLE OF VARCHAR2(1000);

        v_ld_select_sql_lines Sql_Lines;

        v_curr_line number(3);

        v_partition_clause varchar2(1000);

        -- UPPER all input fields

        schema_name_in VARCHAR2(100):=UPPER(schema_name_i);

        table_name_in VARCHAR2(100):=UPPER(table_name_i);

        natural_keylist_in VARCHAR2(1000):=UPPER(natural_keylist_i);

        metadata_collist_in VARCHAR2(1000):=UPPER(metadata_collist_i);

        ocolumn_name_in VARCHAR2(100):=UPPER(ocolumn_name_i);

        CURSOR c_ColumnNames IS

            SELECT

                ATC.COLUMN_NAME,

                ATC.DATA_TYPE

            FROM

                ALL_TAB_COLUMNS ATC

            WHERE

                ATC.OWNER = schema_name_in AND

                ATC.TABLE_NAME = table_name_in AND

                ATC.COLUMN_NAME NOT IN (select regexp_substr(metadata_collist_in,'[^,]+', 1, level) from dual

                    connect by regexp_substr(metadata_collist_in, '[^,]+', 1, level) is not null) AND

                ATC.COLUMN_NAME NOT IN (select regexp_substr(natural_keylist_in,'[^,]+', 1, level) from dual

                    connect by regexp_substr(natural_keylist_in, '[^,]+', 1, level) is not null)

            ORDER BY

                ATC.COLUMN_ID;


        COLUMN_NAMES c_ColumnNames%ROWTYPE;

        BEGIN

            dbms_output.enable(1000000);

            v_curr_line:= 1;

            v_ld_select_sql_lines:= Sql_Lines('---- Logical Duplicate Query for table ' || schema_name_in ||'.'|| table_name_in ||'----- ');


            -- Provide output info on Input

            dbms_output.put_line ('Schema Name: ' || schema_name_in);

            dbms_output.put_line ('Table Name: ' || table_name_in);

            dbms_output.put_line ('Natural Column List Starts');

            FOR

                i IN

                (SELECT REGEXP_SUBSTR (natural_keylist_in,

                                       '[^,]+',

                                       1,

                                       LEVEL) column_name

                 FROM dual

                 CONNECT BY REGEXP_SUBSTR (natural_keylist_in,

                                           '[^,]+',

                                           1,

                                           LEVEL) IS NOT NULL) LOOP

                dbms_output.put_line ('Natural Column : ' || i.column_name);

            END LOOP;

            dbms_output.put_line ('Natural Column List Completed');

            dbms_output.put_line ('Order Column Name: ' || ocolumn_name_in);



            dbms_output.put_line ('========================Logical Dups SQL========================');


            -- Started creating sql

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'SELECT * FROM ';


            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= '(';


            -- Natural Key part

            -- Also creating partition clause with same loop

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'SELECT ';

            FOR

                i IN

                (SELECT REGEXP_SUBSTR (natural_keylist_in,

                                       '[^,]+',

                                       1,

                                       LEVEL) column_name

                 FROM dual

                 CONNECT BY REGEXP_SUBSTR (natural_keylist_in,

                                           '[^,]+',

                                           1,

                                           LEVEL) IS NOT NULL) LOOP

                v_curr_line:= v_curr_line+1;

                v_ld_select_sql_lines.extend;

                v_ld_select_sql_lines(v_curr_line):= i.column_name || ',';

                v_partition_clause:= v_partition_clause || i.column_name || ',';

            END LOOP;

            v_partition_clause:= substr(v_partition_clause,0,length(v_partition_clause)-1);

            v_partition_clause:= 'PARTITION BY ' || v_partition_clause || ' ORDER BY ' || ocolumn_name_in || ' ASC ';

            dbms_output.put_line('Partition cluase: ' || v_partition_clause);

            -- Natural Key part completed


            -- Add Order key for select part

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'N_' || ocolumn_name_in || ' AS ' || ocolumn_name_in;


            -- Add compare part to select sql

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= ',CASE WHEN ';


            OPEN c_ColumnNames;

            FETCH c_ColumnNames INTO COLUMN_NAMES;

            LOOP

            EXIT WHEN c_ColumnNames%NOTFOUND;


            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'C_' || COLUMN_NAMES.COLUMN_NAME || ' =  ' || 'N_' || COLUMN_NAMES.COLUMN_NAME || ' AND ';


            FETCH c_ColumnNames INTO COLUMN_NAMES;

            END LOOP;

            CLOSE c_ColumnNames;


            -- Remove AND from last select line

            v_ld_select_sql_lines(v_curr_line):= replace(v_ld_select_sql_lines(v_curr_line),' AND ',' ');


            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'THEN ''LOGICAL DUPS'' ';


            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'ELSE ''NO DUPS'' END DUP_OR_NOT ';


            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'FROM ( ';

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'SELECT ';

            FOR

                i IN

                (SELECT REGEXP_SUBSTR (natural_keylist_in,

                                       '[^,]+',

                                       1,

                                       LEVEL) column_name

                 FROM dual

                 CONNECT BY REGEXP_SUBSTR (natural_keylist_in,

                                           '[^,]+',

                                           1,

                                           LEVEL) IS NOT NULL) LOOP

                v_curr_line:= v_curr_line+1;

                v_ld_select_sql_lines.extend;

                v_ld_select_sql_lines(v_curr_line):= i.column_name || ',';

            END LOOP;


            -- SQL Top part completed


            -- Order by column current and next value

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= 'NVL (' || ocolumn_name_in || ', -1) C_' || ocolumn_name_in;


            -- Going forward sql will use , before columns details

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):= ',NVL(LEAD(' || ocolumn_name_in || ', 1, -1) OVER (' || v_partition_clause || '), -1) N_' || ocolumn_name_in ;


            -- based on column type apply NVL for each column that needs to be compared for logical dups

            OPEN c_ColumnNames;

            FETCH c_ColumnNames INTO COLUMN_NAMES;

            LOOP

            EXIT WHEN c_ColumnNames%NOTFOUND;


            -- Order by column current and next value



            -- Going forward sql will use , before columns details


            --dbms_output.put_line('For column: ' || COLUMN_NAMES.COLUMN_NAME || ' Data_type is ' ||COLUMN_NAMES.DATA_TYPE);


            CASE

                WHEN COLUMN_NAMES.DATA_TYPE = 'NUMBER' THEN

                BEGIN

                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL (' || COLUMN_NAMES.COLUMN_NAME || ', -1000) C_' || COLUMN_NAMES.COLUMN_NAME;


                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL(LEAD(' || COLUMN_NAMES.COLUMN_NAME || ', 1, -1000) OVER (' || v_partition_clause || '), -1000) N_' || COLUMN_NAMES.COLUMN_NAME ;

               END;

               WHEN COLUMN_NAMES.DATA_TYPE = 'CHAR' OR COLUMN_NAMES.DATA_TYPE = 'VARCHAR2' THEN

               BEGIN

                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL (' || COLUMN_NAMES.COLUMN_NAME || ', ''$####$'' ) C_' || COLUMN_NAMES.COLUMN_NAME;


                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL(LEAD(' || COLUMN_NAMES.COLUMN_NAME || ', 1, ''$####$'') OVER (' || v_partition_clause || '), ''$####$'') N_' || COLUMN_NAMES.COLUMN_NAME ;

               END;

               WHEN COLUMN_NAMES.DATA_TYPE = 'DATE' THEN

               BEGIN

                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL (' || COLUMN_NAMES.COLUMN_NAME || ', ''01-JAN-1899'' ) C_' || COLUMN_NAMES.COLUMN_NAME;


                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL(LEAD(' || COLUMN_NAMES.COLUMN_NAME || ', 1, ''01-JAN-1899'') OVER (' || v_partition_clause || '), ''01-JAN-1899'') N_' || COLUMN_NAMES.COLUMN_NAME ;

               END;
             
               WHEN COLUMN_NAMES.DATA_TYPE = 'TIMESTAMP(6)' THEN

               BEGIN

                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL (' || COLUMN_NAMES.COLUMN_NAME || ', ''01-JAN-1899'' ) C_' || COLUMN_NAMES.COLUMN_NAME;


                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= ',NVL(LEAD(' || COLUMN_NAMES.COLUMN_NAME || ', 1, ''01-JAN-1899'') OVER (' || v_partition_clause || '), ''01-JAN-1899'') N_' || COLUMN_NAMES.COLUMN_NAME ;

               END;

               ELSE

               BEGIN

                     v_curr_line:= v_curr_line+1;

                     v_ld_select_sql_lines.extend;

                     v_ld_select_sql_lines(v_curr_line):= 'Got Confuse with Column''s datatype';

              END;

            END CASE;


            FETCH c_ColumnNames INTO COLUMN_NAMES;

            END LOOP;

            CLOSE c_ColumnNames;


            -- Add end part

            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):=' FROM ' || schema_name_in ||'.'|| table_name_in || '))';


            v_curr_line:= v_curr_line+1;

            v_ld_select_sql_lines.extend;

            v_ld_select_sql_lines(v_curr_line):=' WHERE DUP_OR_NOT = ''LOGICAL DUPS'';';

            dbms_output.put_line('Output using collection');

            FOR i IN v_ld_select_sql_lines.FIRST .. v_ld_select_sql_lines.LAST

            LOOP

                dbms_output.put_line(v_ld_select_sql_lines(i));

            END LOOP;

            dbms_output.put_line('Output using collection');

            -- Create

        END;

/

GRANT EXECUTE ON DW_DF.LOGICAL_DUP_CHECKER TO DW_TESTER

/

To find logical duplicate:

Logical duplicates are the duplicates that occur in Data warehouse as a result of unnecessary daily column updates from source , which are not required for our tables. Apart from metadata columns like (FROM_DT_ID, THRU_DT_ID, OPEN_DT_ID, CLOSE_DT_ID, CREATE_DT, MOD_DT, CREATE_BY_NM, MOD_BY_NM or any columns that are driven out of Informatica )all columns look alike with in immediate rows for particular Natural key combination. To find those rows, please follow below instructions.

In ODSU or in DIMU, there is a procedure package name DW_DF.LOGICAL_DUP_CHECKER. Right click on that procedure and click Execute. Procedure execution window appears. For any questions regarding the procedure execution or errors, please ask Chirag Patel, author of the Procedure.

*Note:  If there is a refresh of Databases, this procedure will be lost, please ask to redeploy this procedure in both ODSU and DIMU.

1.      Type the schema name: DW_ODS or DW_MART – Which ever table schema you are using. For

2.      Type the Table name you want to find the logical duplicates.

3.      Type in the Natural key combination list for the table that you want to run against.

4.      Type the metadata column list for that particular table. For eg: FROM_DT_ID, THRU_DT_ID, OPEN_DT_ID, CLOSE_DT_ID, CREATE_DT, MOD_DT, CREATE_BY_NM, MOD_BY_NM

5.      Ocolumn_name is the order by column name list that you want to sort.

Click Execute button.

A query will be provided with which we can find the logical duplicates.

Show comma seperated values as rows

Problem: I would like to show comma separated values as rows. How can I do it?

For example, I have following comma separated string and I want to show it as rows in tables.

String value: 'ABC,DEF,HIJ'

Expected output:




Solution:

Oracle -


SELECT EXTRACT (VALUE (d), '//row/text()').getstringval () AS MyColumn
  FROM (SELECT XMLTYPE (   '<rows><row>'
                        || REPLACE ('ABC,DEF,HIJ', ',', '</row><row>')
                        || '</row></rows>'
                       ) AS xmlval
          FROM DUAL) x,
       TABLE (XMLSEQUENCE (EXTRACT (x.xmlval, '/rows/row'))) d;

Powershell - TFS workspace move

tf workspaces /updateComputerName:MyOldComputerName 
/s:"http://MyServer:8080/tfs/MyCollection"

Informatica Metadata Queries

-- Get number of session count by hour within last 24 hours
select hour_range.start_time,hour_range.end_time, count(1) from
(select trunc(sysdate,'HH') - (level+1)/24 start_time, trunc(sysdate,'HH') - level/24 end_time from dual
connect by level <= 3*24) hour_range
left join ((select DISTINCT
        --Workflow
        S.SUBJ_NAME         as FOLDER_NAME
        ,WR.WORKFLOW_NAME   as WF_NAME
        ,WR.START_TIME      as WF_START
        ,TRUNC(WR.START_TIME) as RUN_DATE
        --Session
        ,OTIR.INSTANCE_NAME as SES_NAME
        ,OTIR.START_TIME as SES_STIME
        /*
        ,OSTL.MAPPING_NAME
        ,OSTL.SRC_SUCCESS_ROWS
        ,OSTL.TARG_SUCCESS_ROWS
        ,OTIR.START_TIME
        ,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME,
        TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') AS SES_ELAPSED_TIME_IN_SEC
        */
        FROM    DWPC95.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC95.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
    INNER JOIN
        DWPC95.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
        AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
    INNER JOIN
        DWPC95.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
        AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
        AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
        AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
        AND OTIR.TASK_TYPE = 68
    WHERE
         WR.START_TIME between sysdate-3  and  sysdate)
    ) session_run
    on session_run.SES_STIME BETWEEN hour_range.start_time and hour_range.end_time
    group by hour_range.start_time, hour_range.end_time

-- Get QC Number and deployment time FROM TEST to VCT
select substr(dep_group_name,14,instr(dep_group_name,'_',1,3)-14) "QC", to_date(creation_time,'MM-DD-YYYY hh24:mi:ss')  "DeploymentTime" from DWPC.REP_DEPLOY_GROUP
where query_id = 0
order by to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') desc
/
-- Get QC Number and deployment time FROM VCT to UAT
select substr(dep_group_name,14,instr(dep_group_name,'_',1,3)-14) "QC", to_date(creation_time,'MM-DD-YYYY hh24:mi:ss')  "DeploymentTime" from DWPC_VC.REP_DEPLOY_GROUP
where query_id = 0
order by to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') desc
/
-- Get QC Number and deployment time FROM UAT to PROD
select substr(dep_group_name,14,instr(dep_group_name,'_',1,3)-14) "QC", to_date(creation_time,'MM-DD-YYYY hh24:mi:ss')  "DeploymentTime" from DWPC_UAT.REP_DEPLOY_GROUP
where query_id = 0
order by to_date(creation_time,'MM-DD-YYYY hh24:mi:ss') desc
/
-- Failed rows within specific time period
select DISTINCT
        --Workflow
        S.SUBJ_NAME         as FOLDER_NAME
        ,WR.WORKFLOW_NAME   as WF_NAME
        ,WR.START_TIME      as WF_START
        --Session
        ,OTIR.INSTANCE_NAME as SES_NAME
        ,OSTL.MAPPING_NAME
        ,OSTL.SRC_SUCCESS_ROWS
        ,OSTL.TARG_SUCCESS_ROWS
        ,OSTL.SRC_FAILED_ROWS
        ,OSTL.TARG_FAILED_ROWS
        ,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME
        FROM    DWPC95_QA.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC95_QA.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
    INNER JOIN
        DWPC95_QA.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
        AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
    INNER JOIN
        DWPC95_QA.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
        AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
        AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
        AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
        AND OTIR.TASK_TYPE = 68
        AND S.SUBJ_NAME IN ('DW ODS', 'DW Staging')
        AND WR.START_TIME BETWEEN TO_DATE('03/23/2015 06:00 AM','MM/DD/YYYY HH:MI AM') AND TO_DATE('03/23/2015 09:00 AM','MM/DD/YYYY HH:MI AM')
        AND (OSTL.SRC_FAILED_ROWS > 0 OR OSTL.TARG_FAILED_ROWS > 0 );
       
-- Query to get session statistics for Enterprise DW Dimension folder
select DISTINCT
        --Workflow
        S.SUBJ_NAME         as FOLDER_NAME
        ,WR.WORKFLOW_NAME   as WF_NAME
        ,WR.START_TIME      as WF_START
        --Session
        ,OTIR.INSTANCE_NAME as SES_NAME
        ,OSTL.MAPPING_NAME
        ,OSTL.SRC_SUCCESS_ROWS
        ,OSTL.TARG_SUCCESS_ROWS
        ,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME FROM    DWPC.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
    INNER JOIN
        DWPC.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
        AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
    INNER JOIN
        DWPC.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
        AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
        AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
        AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
        AND OTIR.TASK_TYPE = 68
        AND S.SUBJ_NAME = 'Enterprise DW ODS'
-- Query to get Workflow ran during specific time period
  SELECT S.SUBJ_NAME         AS FOLDER_NAME
        ,WR.WORKFLOW_NAME   AS WF_NAME
        ,WR.START_TIME      AS WF_START 
        ,WR.END_TIME AS WF_END,
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00')   AS WF_TIME    
  FROM    DWPC95_DEV.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC95_DEV.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
  WHERE WR.START_TIME BETWEEN to_date('09/25/2014 03:10:00 PM','MM/DD/YYYY HH:MI:SS AM') AND to_date('09/25/2014 03:35:00 PM','MM/DD/YYYY HH:MI:SS AM')
        ORDER BY WR.START_TIME

-- Query to get workflow run time during perticular day in specific folders
  SELECT S.SUBJ_NAME         AS FOLDER_NAME
        ,WR.WORKFLOW_NAME   AS WF_NAME
        ,WR.START_TIME      AS WF_START 
        ,WR.END_TIME AS WF_END,
TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00')   AS WF_TIME    
  FROM    DWPC.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
  WHERE S.SUBJ_NAME IN ('Enterprise DW Dimension',
'Enterprise DW ODS',
'Enterprise DW Staging','Elvis Datamart')
AND WR.START_TIME BETWEEN TRUNC(SYSDATE-1) AND TRUNC(SYSDATE)
        ORDER BY WR.START_TIME

-- connections being used at VCI
SELECT
    DISTINCT
   WF.SUBJECT_AREA AS FOLDER_NAME,
    WF.WORKFLOW_NAME AS WORKFLOW_NAME,
    T.INSTANCE_NAME AS SESSION_NAME,
    T.TASK_TYPE_NAME,
    C.CNX_NAME AS VARIABLE_NAME,
    V.CONNECTION_NAME,
    V.CONNECTION_SUBTYPE,
    V.HOST_NAME,
    V.USER_NAME,
    --C.INSTANCE_NAME,
    C.READER_WRITER_TYPE--,
    --C.SESS_EXTN_OBJECT_TYPE
FROM
    dwpc95.REP_TASK_INST T
    INNER JOIN dwpc95.REP_SESS_WIDGET_CNXS C ON C.SESSION_ID = T.TASK_ID
    INNER JOIN dwpc95.REP_WORKFLOWS WF ON WF.WORKFLOW_ID = T.WORKFLOW_ID
    LEFT JOIN dwpc95.V_IME_CONNECTION V ON UPPER(CASE WHEN C.CNX_NAME = '$DBConnection_MDR_REPO_READER' THEN 'REPO_READER'
WHEN C.CNX_NAME = '$DBConnection_MSQL_FISERV_BF' THEN 'USVCITST031_cf_50_backfill'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_STAGING' THEN 'ODS_DW_STAGING_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_ODS' THEN 'ODS_DW_ODS_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_MART' THEN 'DIM_DW_MART_ETL'
WHEN C.CNX_NAME = '$DBConnection_SYB_DW_EXTRACT1' THEN 'lp_prod_BCV_DW_STAGING'
WHEN C.CNX_NAME = '$DBConnection_SYB_DW_EXTRACT' THEN 'lp_prod_DW_STAGING'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS' THEN 'Elvis_DW_USER_Daily'
WHEN C.CNX_NAME = '$DBConnection_SQLS_FISERV' THEN 'FISERV_DW_USER'
WHEN C.CNX_NAME = '$DBConnection_ORA_CROWE' THEN 'WLSP_CROWE'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS_FS' THEN 'Elvis_Elvis_FS'
WHEN C.CNX_NAME = '$DBConnection_ORA_VDWP_ODS' THEN 'VDWP_DW_DATA'
WHEN C.CNX_NAME = '$DBConnection_MSQL_FISERV' THEN 'USVCILVS008_cf'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_ODS_AUTODEPLOY' THEN 'ODS_DEPLOYMENT'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_DIM_AUTODEPLOY' THEN 'DIM_DEPLOYMENT'
WHEN C.CNX_NAME = '$DBConnection_ORA_DIM_DW_DF_ETL' THEN 'DIM_DW_DF_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_ODS_DW_DF_ETL' THEN 'ODS_DW_DF_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_ABS' THEN 'ABS_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DE' THEN 'FSPORTALAPP_ETL'
WHEN C.CNX_NAME = '$AppConnection_OMNI_Local' THEN 'OmnitureLocalService'
WHEN C.CNX_NAME = '$DBConnection_ORA_FSP' THEN 'FSP_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_MART_BACKFILL' THEN 'DIM_DW_MART_ETL_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_ODS_BACKFILL' THEN 'ODS_DW_ODS_ETL_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS_BACKFILL' THEN 'Elvis_Elvis_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_ORA_MAPS' THEN 'MAPP_DWUSER'
WHEN C.CNX_NAME = '$DBConnection_ORA_DW_EXCEPTION' THEN 'DIM_DW_EXCEPTION_ETL'
WHEN C.CNX_NAME = '$DBConnection_ORA_IVR' THEN 'IVR_DWUSER'
WHEN C.CNX_NAME = '$DBConnection_ORA_ELVIS_ALT' THEN 'Elvis_Elvis_BACKFILL'
WHEN C.CNX_NAME = '$DBConnection_MSQL_FISERV_BACKFILL' THEN 'USVCITST031_cf_50_backfill'
WHEN C.CNX_NAME = '$AppConnection_Vehicle_Info' THEN 'MM_AutoData_Web_Service_VehicleInfo'
WHEN C.CNX_NAME = '$DBConnection_ORA_VC_BATCH' THEN 'RMK_DW_ETL' END) = UPPER(V.CONNECTION_NAME)
-- Query to get connection details for sessions
SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
       T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
       C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
       V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
       C.SESS_EXTN_OBJECT_TYPE
  FROM DWPC95_TEST.REP_TASK_INST T,
       DWPC95_TEST.REP_SESS_WIDGET_CNXS C,
       DWPC95_TEST.REP_WORKFLOWS WF,
       DWPC95_TEST.V_IME_CONNECTION V
 WHERE T.TASK_ID = C.SESSION_ID
   AND WF.WORKFLOW_ID = T.WORKFLOW_ID
   AND C.CNX_NAME = V.CONNECTION_NAME
   AND WF.SUBJECT_AREA = 'Cloverleaf Extractions'

/* Get Details of all connections stings in VCI Prod.
 */
SELECT
  cnx.object_id         AS "OBJECT_ID",
  cnx.object_name       AS "CONNECTION_NAME",
  case cnx.object_subtype
    WHEN 101 THEN 'Oracle'
    WHEN 102 THEN 'Sybase'
    WHEN 106 THEN 'ODBC'
    WHEN 304000 THEN 'AS400/8.6'
    ELSE 'Other'
  END                   AS "DATABASE_TYPE",
  cnx.connect_string    AS "CONNECT_STRING",
  cnx.user_name         AS "USER_NAME",
  attribute1.attribute  AS "AS400_SERVER",
  attribute9.attribute  AS "AS400_DATABASE",
  attribute10.attribute AS "ADDITIONAL_INFORMATION",
  attribute11.attribute AS "SERVER_NAME"
  FROM dwpc.opb_cnx      cnx
  LEFT JOIN (
    SELECT object_id, attr_value AS attribute
      FROM dwpc.opb_cnx_attr
     WHERE attr_id = 1
  ) attribute1
    ON cnx.object_id         = attribute1.object_id
  LEFT JOIN (
    SELECT object_id, attr_value AS attribute
      FROM dwpc.opb_cnx_attr
     WHERE attr_id = 9
  ) attribute9
    ON cnx.object_id         = attribute9.object_id
  LEFT JOIN (
    SELECT object_id, attr_value AS attribute
      FROM dwpc.opb_cnx_attr
     WHERE attr_id = 10
  ) attribute10
    ON cnx.object_id         = attribute10.object_id
  LEFT JOIN (
    SELECT object_id, attr_value AS attribute
      FROM dwpc.opb_cnx_attr
     WHERE attr_id = 11
  ) attribute11
    ON cnx.object_id         = attribute11.object_id
/*********************************************************/

-- ALL OBJECT TYPES IN INFORMATICA
SELECT * FROM DWPC.OPB_OBJECT_TYPE

-- Query for Rich to get mapping using batch date - logic
select
RAM.subject_area AS FOLDER_NAME,
RAM.mapping_name,
RWI.WIDGET_TYPE_NAME AS TRANSFORMATION_TYPE,
RWI.INSTANCE_NAME AS TRANSFORMATION_NAME,
RWA.ATTR_NAME,
RWA.ATTR_VALUE
from
dwpc_uat.rep_all_mappings RAM,
dwpc_uat.rep_widget_attr RWA,
dwpc_uat.rep_widget_inst RWI
where
RWI.MAPPING_ID=RAM.MAPPING_ID
AND
RWA.WIDGET_ID = RWI.WIDGET_ID
AND
(RWA.attr_name='Sql Query'
or
RWA.attr_name='Source Filter' or 1=1)
and
(
RWA.attr_value like '%$$BatchControlDtId-%'
or
RWA.attr_value like '%$$BatchControlDtId -%'
or
RWA.attr_value like '%$$BatchControlDtId  -%'
)

ORDER BY RAM.SUBJECT_AREA,RAM.MAPPING_NAME,RWI.INSTANCE_NAME


--This query returns all mappings that write to a particular table and column (Query from Alex)
SELECT
    RMCP.subject_area as folder_name,
    RMCP.mapping_name,
    RMCP.to_object_name as target_instance_name
FROM
    DWPC_UAT.rep_mapping_conn_ports RMCP,
    dwpc_uat.rep_all_targets RAL
WHERE
    --BEGIN PARAMETERS
    RAL.TARGET_NAME='ASSET'
    AND
    RMCP.to_object_field_name='MODEL_YEAR_NBR'
    --END PARAMETERS
    AND
    RAL.PARENT_TARGET_ID=RMCP.TO_OBJECT_ID
    AND
    RMCP.to_object_type_name='Target Definition'
ORDER BY
    RMCP.SUBJECT_AREA,RMCP.MAPPING_NAME,RMCP.TO_OBJECT_NAME
       
-- Query to get workflow and session statistics
select DISTINCT
        --Workflow
        S.SUBJ_NAME         as FOLDER_NAME
        ,WR.WORKFLOW_NAME   as WF_NAME
        ,WR.START_TIME      as WF_START
        ,WR.END_TIME        as WF_END
        ,WR.RUN_ERR_CODE    as WF_ERR_CODE
        ,WR.RUN_ERR_MSG     as WF_ERR_MSG
        ,TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00') AS WF_ELAPSED_TIME
        --Session
        ,OTIR.INSTANCE_NAME as SES_NAME
        ,OSTL.MAPPING_NAME
        ,OSTL.SRC_FAILED_ROWS
        ,OSTL.SRC_SUCCESS_ROWS
        ,OSTL.TARG_FAILED_ROWS
        ,OSTL.TARG_SUCCESS_ROWS
        ,OTIR.START_TIME as SES_START
        ,OTIR.END_TIME as SES_END
        ,OSTL.FIRST_ERROR_CODE as SES_ERR_CODE
        ,OTIR.RUN_ERR_MSG as SES_ERR_MSG
        ,DECODE(OTIR.RUN_STATUS_CODE, 1,'SUCCEEDED',3,'FAILED') AS SES_STATUS_CODE
        ,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME FROM    DWPC_UAT.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC_UAT.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
    INNER JOIN
        DWPC_UAT.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
        AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
    INNER JOIN
        DWPC_UAT.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
        AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
        AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
        AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
        AND OTIR.TASK_TYPE = 68
        AND S.SUBJ_NAME IN ('Elvis Datamart UAT')
        AND OTIR.INSTANCE_NAME = 's_m_WRITE_AccountAssetUsageAddress'
 ORDER BY S.SUBJ_NAME,WR.WORKFLOW_NAME, OTIR.START_TIME desc

SELECT
SUBJECT_AREA AS FOLDER_NAME,
WORKFLOW_NAME,
SESSION_NAME,
SUCCESSFUL_SOURCE_ROWS AS SOURCE_ROWS,
FAILED_ROWS,
SUCCESSFUL_ROWS AS TARGET_ROWS,
ACTUAL_START AS START_TIME,
SESSION_TIMESTAMP
FROM DWPC_UAT.REP_SESS_LOG
WHERE RUN_STATUS_CODE = 1
--AND TRUNC(ACTUAL_START) BETWEEN TRUNC(SYSDATE -6) AND TRUNC(SYSDATE)
--AND (SESSION_TIMESTAMP – ACTUAL_START) > (10/24*60))
AND WORKFLOW_NAME = 'wf_PROD_DAILY'
and SESSION_NAME = 's_m_WRITE_AccountAssetUsageAddress'
ORDER BY trunc(SESSION_TIMESTAMP) desc,SUBJECT_AREA,
WORKFLOW_NAME,
SESSION_NAME

--
SELECT  DISTINCT
        --Workflow
        S.SUBJ_NAME         as FOLDER_NAME
        ,WR.WORKFLOW_NAME   as WF_NAME
        ,WR.START_TIME      as WF_START
        ,WR.END_TIME        as WF_END
        ,WR.RUN_ERR_CODE    as WF_ERR_CODE
        ,WR.RUN_ERR_MSG     as WF_ERR_MSG
        ,TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((WR.END_TIME - WR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(WR.END_TIME, 'SSSSS') - TO_CHAR(WR.START_TIME, 'SSSSS') , 60)),'00') AS WF_ELAPSED_TIME
        --Session
        ,OTIR.INSTANCE_NAME as SES_NAME
        ,OSTL.MAPPING_NAME
        ,OSTL.SRC_FAILED_ROWS
        ,OSTL.SRC_SUCCESS_ROWS
        ,OSTL.TARG_FAILED_ROWS
        ,OSTL.TARG_SUCCESS_ROWS
        ,OTIR.START_TIME as SES_START
        ,OTIR.END_TIME as SES_END
        ,OSTL.FIRST_ERROR_CODE as SES_ERR_CODE
        ,OTIR.RUN_ERR_MSG as SES_ERR_MSG
        ,DECODE(OTIR.RUN_STATUS_CODE, 1,'SUCCEEDED',3,'FAILED') AS SES_STATUS_CODE
        ,TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 ,24)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD((OTIR.END_TIME - OTIR.START_TIME) * 24 * 60 , 60)),'00') || ':' ||
        TO_CHAR(TRUNC(MOD(TO_CHAR(OTIR.END_TIME, 'SSSSS') - TO_CHAR(OTIR.START_TIME, 'SSSSS') , 60)),'00') AS SES_ELAPSED_TIME
FROM    DWPC_UAT.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC_UAT.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
    INNER JOIN
        DWPC_UAT.OPB_SESS_TASK_LOG OSTL ON WR.WORKFLOW_ID = OSTL.WORKFLOW_ID
        AND WR.WORKFLOW_RUN_ID = OSTl.WORKFLOW_RUN_ID
    INNER JOIN
        DWPC_UAT.OPB_TASK_INST_RUN OTIR ON WR.SUBJECT_ID = OTIR.SUBJECT_ID
        AND OSTL.INSTANCE_ID = OTIR.INSTANCE_ID
        AND OSTL.WORKFLOW_ID = OTIR.WORKFLOW_ID
        AND OSTL.WORKFLOW_RUN_ID = OTIR.WORKFLOW_RUN_ID
        AND OTIR.TASK_TYPE = 68
      
WHERE   1=1
AND     TO_NUMBER(TO_CHAR(WR.START_TIME,'YYYYMMDD')) = 20100305
--AND     (TO_NUMBER(TO_CHAR(WR.START_TIME,'HH')) BETWEEN 9 AND 10 OR TO_NUMBER(TO_CHAR(WR.END_TIME,'HH')) BETWEEN 9 AND 10)
AND     S.SUBJ_NAME IN ('Enterprise DW Dimension','Enterprise DW ODS','Enterprise DW Staging')
--AND     (OSTL.SRC_FAILED_ROWS > 0 OR OSTL.TARG_FAILED_ROWS > 0)
--AND     WR.WORKFLOW_NAME LIKE '%WF_CUSTOMER%'
ORDER BY WR.START_TIME
        ,OTIR.START_TIME       
;

/*

SELECT  S.SUBJ_NAME
        ,T.TASK_ID
        ,TSK_TYPE.OBJECT_TYPE_ID as TASK_TYPE_ID
        ,TSK_TYPE.OBJECT_TYPE_NAME as TASK_TYPE_NM 
        ,T.TASK_NAME
        ,TSK_ATTR.ATTR_ID as TASK_ATTR_TYPE_ID
        ,TSK_ATTR_TYPE.OBJECT_TYPE_NAME as TASK_ATTR_TYPE_NAME   
        ,TSK_ATTR.ATTR_NAME as TASK_ATTR_NAME
        ,TA.ATTR_VALUE as TASK_ATTR_VALUE 
       
FROM    DWPC_UAT.OPB_SUBJECT S
    INNER JOIN
        DWPC_UAT.OPB_TASK T ON S.SUBJ_ID = T.SUBJECT_ID
    INNER JOIN
        DWPC_UAT.OPB_TASK_ATTR TA ON T.TASK_ID = TA.TASK_ID
    INNER JOIN
        DWPC_UAT.OPB_OBJECT_TYPE TSK_TYPE ON TA.TASK_TYPE = TSK_TYPE.OBJECT_TYPE_ID
    INNER JOIN
        DWPC_UAT.OPB_MMD_TASK_ATTR TSK_ATTR ON TA.TASK_TYPE = TSK_ATTR.OBJECT_TYPE_ID
        AND TA.ATTR_ID = TSK_ATTR.ATTR_ID
    INNER JOIN
        DWPC_UAT.OPB_OBJECT_TYPE TSK_ATTR_TYPE ON TSK_ATTR.ATTR_TYPE = TSK_ATTR_TYPE.OBJECT_TYPE_ID       

WHERE   1=1
--AND     S.SUBJ_NAME IN ('Enterprise DW Dimension','Enterprise DW ODS','Enterprise DW Staging')       
--AND     T.TASK_ID = 341
AND     TSK_ATTR.ATTR_ID = 17 --Write Backward Compatible Session Log File
AND     TSK_TYPE.OBJECT_TYPE_ID = 68 --Session

ORDER BY S.SUBJ_NAME
        ,T.TASK_NAME
        ,TSK_TYPE.OBJECT_TYPE_NAME
        ,TSK_ATTR_TYPE.OBJECT_TYPE_NAME
;



*/

--How to find all the mappings and sessions which have PARALLEL hints in the SQL Override

SELECT   S.SUBJ_NAME, M.MAPPING_NAME, W.WIDGET_NAME, A.WIDGET_ID, W.VERSION_NUMBER,
         SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM     OPB_WIDGET_ATTR A, OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_INST I, OPB_MAPPING M
WHERE    A.WIDGET_ID = W.WIDGET_ID
AND      W.IS_VISIBLE = 1
AND      A.VERSION_NUMBER = W.VERSION_NUMBER
AND      A.WIDGET_TYPE IN(2, 3, 11)                     --Limit to Src/Tgt/Lkp Transformations
AND      W.WIDGET_ID = I.WIDGET_ID
AND      W.VERSION_NUMBER = I.VERSION_NUMBER
AND      I.MAPPING_ID = M.MAPPING_ID
AND      I.VERSION_NUMBER = M.VERSION_NUMBER
AND      W.SUBJECT_ID = S.SUBJ_ID
AND      UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
UNION
SELECT   S.SUBJ_NAME, W.TASK_NAME||'.'||TI.INSTANCE_NAME TASK_NAME, 'SQL Override' WIDGET_NAME,
         A.SESS_WIDG_INST_ID, TI.VERSION_NUMBER, SUBSTR(A.ATTR_VALUE, 1, 60) ATTR_VALUE
FROM     OPB_SWIDGET_ATTR A, OPB_TASK_INST TI, OPB_TASK W, OPB_SUBJECT S
WHERE    A.SESSION_ID = TI.TASK_ID
AND      A.VERSION_NUMBER = TI.VERSION_NUMBER
AND      TI.WORKFLOW_ID = W.TASK_ID
AND      TI.VERSION_NUMBER = W.VERSION_NUMBER
AND      W.IS_VISIBLE = 1
AND      W.SUBJECT_ID = S.SUBJ_ID
AND      UPPER(A.ATTR_VALUE) LIKE '%PARALLEL%'
ORDER BY 1, 2, 3, 4;

--How to turn on Write Backward Compatible Session Log File for all session tasks in a folder

UPDATE OPB_TASK_ATTR A SET A.ATTR_VALUE=1
WHERE A.ATTR_ID=17    -- WBCSLF
  -- 102 Write performance data to repository
  -- 108 Collect performance data
  -- 105 Enable high precision
  AND EXISTS (
  SELECT 0 FROM OPB_TASK T, OPB_SUBJECT S
  WHERE T.TASK_ID = A.TASK_ID
    AND T.VERSION_NUMBER = A.VERSION_NUMBER
    AND T.IS_VISIBLE = 1
    AND T.SUBJECT_ID = S.SUBJ_ID
    AND S.SUBJ_NAME LIKE '%Sample%'
  )
  -- AND A.INSTANCE_ID = 0 -- Reusable Session Only
;

--What are today's long-running tasks

select    -- the SRC_ROWS may look big if joiner is used
    T.SUBJECT_AREA, T.INSTANCE_NAME, TRUNC(AVG(END_TIME-START_TIME)*24, 2) RUN_HOUR,
    MIN(T.START_TIME) START_TIME, 
    SUM(L.SRC_SUCCESS_ROWS) SRC_ROWS, SUM(L.TARG_SUCCESS_ROWS) TGT_ROWS
from REP_TASK_INST_RUN T, OPB_SESS_TASK_LOG L
where T.run_err_code=0 and (T.END_TIME-T.START_TIME)>= 1/24
    and T.START_TIME >= TRUNC(SYSDATE)-2/24
    and T.INSTANCE_ID = L.INSTANCE_ID
GROUP BY T.SUBJECT_AREA, T.INSTANCE_NAME  
Order By RUN_HOUR desc;

--Inside the Expression Transformation

select S.SUBJ_NAME, W.WIDGET_NAME, F.WIDGET_ID, F.FIELD_NAME, F.FIELD_ID, E.EXPR_ID,
    E.VERSION_NUMBER, E.EXPRESSION
from OPB_WIDGET W, OPB_SUBJECT S, OPB_WIDGET_FIELD F,
    OPB_WIDGET_EXPR R, OPB_EXPRESSION E
where W.SUBJECT_ID=S.SUBJ_ID and W.WIDGET_ID=F.WIDGET_ID
    and W.WIDGET_ID=R.WIDGET_ID AND F.FIELD_ID=R.OUTPUT_FIELD_ID
    and W.WIDGET_ID=E.WIDGET_ID AND R.EXPR_ID=E.EXPR_ID
    and W.VERSION_NUMBER = F.VERSION_NUMBER
    and F.VERSION_NUMBER = R.VERSION_NUMBER
    and R.VERSION_NUMBER = E.VERSION_NUMBER
    and W.IS_VISIBLE = 1
    and w.WIDGET_NAME like 'EXP_SAMPLE%'
order by S.SUBJ_ID, W.WIDGET_ID, F.FIELD_ID;

--Which session populates the specific target table

select SUBJECT_AREA, SESSION_NAME,TARGET_NAME,
    MAX(SUCC_ROWS) AS ROWS#, TYPE_ID, COUNT(1) SAMPLE#
from (   
select SUBJECT_AREA, SESSION_INSTANCE_NAME SESSION_NAME,
    TYPE_ID, SUCCESSFUL_AFFECTED_ROWS SUCC_ROWS,
    TABLE_NAME TARGET_NAME
from    PM_REPO.REP_SESS_TBL_LOG
WHERE  TYPE_ID in (2) -- Target Only
    and END_TIME >= TRUNC(SYSDATE-40)
    and SUCCESSFUL_AFFECTED_ROWS > 0
    and TABLE_NAME like :TGT_NAME||'%' ESCAPE '\'
)   
Group By SUBJECT_AREA, SESSION_NAME, TARGET_NAME, TYPE_ID  
Order By SUBJECT_AREA, TARGET_NAME, SESSION_NAME
;

--How to avoid re-generating & re-transporting ABAP program after slightly changing the mapping

CR 29233 and 63134 have been opened to have fix this problem.

-- Find the current LAST_SAVED and UTC_LAST_SAVED
select m.Mapping_ID, m.VERSION_NUMBER, m.Last_Saved, m.UTC_Last_Saved, v.User_ID,
    v.LAST_SAVED, v.UTC_LAST_SAVED, v.COMMENTS, p.PROGRAM_NAME,
    p.INSTALL_TIME, p.VERSION_NUMBER ABAP_VERSION_NUM
from opb_mapping m, OPB_VERSION_PROPS v, opb_program_info p
where m.MAPPING_ID = v.OBJECT_ID and v.OBJECT_TYPE = 21 and
    m.SUBJECT_ID = v.SUBJECT_ID and m.VERSION_NUMBER = v.VERSION_NUMBER and
    m.MAPPING_ID = p.MAPPING_ID(+) and m.SUBJECT_ID = p.SUBJECT_ID(+) and
  m.VERSION_NUMBER = p.VERSION_NUMBER(+) and
  m.MAPPING_NAME = :MAP_NAME
order by m.version_number;

--Then modify the LAST_SAVED, UTC_LAST_SAVED in OPB_VERSION_PROPS and OPB_MAPPING first; then modify the VERSION_NUMBER in OPB_PROGRAM_INFO if necessary.

--The timsstamp below is just a sample
update OPB_VERSION_PROPS v
set last_saved='12/05/2008 09:10:11' ,
UTC_LAST_SAVED=1228363499
where v.OBJECT_ID = :MAP_ID and version_number = :VER_NUM and object_type = 21;

update opb_mapping m
set last_saved='12/05/2008 09:10:11',
UTC_LAST_SAVED=1228363499
where MAPPING_ID = :MAP_ID and version_number = :VER_NUM;

update opb_program_info set VERSION_NUMBER = :VER_NUM
where mapping_id = :MAP_ID and subject_id = :SUB_ID;

--Link from EXP_FROM.PORT_NAME to ???

select S.SUBJ_NAME, WF.INSTANCE_NAME ||'.'|| F.FIELD_NAME FROM_NAME, F.WIDGET_ID, F.FIELD_ORDER, F.FIELD_ID,
    WT.INSTANCE_NAME ||'.'|| T.FIELD_NAME TO_NAME, T.WIDGET_ID, T.FIELD_ORDER, T.FIELD_ID
from OPB_WIDGET Z, OPB_WIDGET_INST WF, OPB_WIDGET_INST WT, OPB_WIDGET_FIELD F,
    OPB_WIDGET_FIELD T, OPB_WIDGET_DEP D, OPB_SUBJECT S 
where Z.SUBJECT_ID = S.SUBJ_ID and Z.IS_VISIBLE = 1
    and Z.WIDGET_ID = F.WIDGET_ID
    and Z.WIDGET_ID = WF.WIDGET_ID
    and Z.RU_VERSION_NUMBER = WF.VERSION_NUMBER
    and WF.REF_VERSION_NUMBER = F.VERSION_NUMBER
    and WF.VERSION_NUMBER = D.VERSION_NUMBER
    and WF.MAPPING_ID = D.MAPPING_ID
    and WF.INSTANCE_ID = D.FROM_INSTANCE_ID and F.FIELD_ID = D.FROM_FIELD_ID
    and D.TO_INSTANCE_ID = WT.INSTANCE_ID and D.TO_FIELD_ID = T.FIELD_ID
    and D.MAPPING_ID = WT.MAPPING_ID
    and D.VERSION_NUMBER = WT.VERSION_NUMBER
    and WT.WIDGET_ID = T.WIDGET_ID
    and WT.REF_VERSION_NUMBER = T.VERSION_NUMBER
    and Z.WIDGET_NAME like 'EXP_FROM%'
    and F.FIELD_NAME like 'PORT_NAME%'
order by F.FIELD_ID;

--How the connection values is set at session level

select x.WORKFLOW_ID , t.TASK_ID, t.TASK_NAME, b.INSTANCE_ID, b.INSTANCE_NAME,
    b.SESS_WIDG_INST_ID, x.CONNECTION_NUM, x.CONNECTION_TYPE, x.CONNECTION_ID,
    x.CONNECTION_VALUE, c.OBJECT_NAME
from opb_session s, opb_task t, OPB_SWIDGET_INST b, OPB_SESS_CNX_VALS x, opb_cnx c
where c.OBJECT_ID(+) = x.CONNECTION_ID
and s.MAPPING_ID = b.MAPPING_ID
and s.SESSION_ID = b.SESSION_ID
and s.SESSION_ID = t.TASK_ID
and s.SESSION_ID = x.SESSION_ID
and b.SESS_WIDG_INST_ID = x.SESS_WIDG_INST_ID
and t.SUBJECT_ID = :SUBJ_ID 
and b.INSTANCE_NAME like :WIDGET_NAME -- Source/Target
and t.TASK_NAME like :SESS_NAME
order by t.task_name, b.SESS_WIDG_INST_ID;

--Find Transformation Attribute Override at Session Level

select f.SUBJ_NAME Folder, t.task_name SESSION_NAME, i.INSTANCE_NAME,
    o.OBJECT_TYPE_NAME, a.*
from opb_swidget_attr a, OPB_SWIDGET_INST i,
    OPB_WIDGET w, OPB_OBJECT_TYPE o,
    opb_task t, opb_subject f
where a.attr_value in (:VALUE1, :VALUE2, :VALUE3)
    and i.SESSION_ID = a.SESSION_ID
    and i.SESS_WIDG_INST_ID = a.SESS_WIDG_INST_ID
    and i.WIDGET_ID    = w.WIDGET_ID
    and i.WIDGET_TYPE = w.WIDGET_TYPE
    and i.WIDGET_TYPE = o.OBJECT_TYPE_ID
    and i.SESSION_ID= t.task_id
    and t.SUBJECT_ID= f.subj_id;

--List all the Transformations in a given Mapping
--Including the transformations within the mapplet

WITH M AS (
    select M.SUBJECT_ID, M.MAPPING_ID
    from OPB_MAPPING M, OPB_SUBJECT S
    where S.SUBJ_ID = M.SUBJECT_ID
    and S.SUBJ_NAME LIKE :Folder_Name
    and M.MAPPING_NAME like :Mapping_Name
)   
select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M
where O.OBJECT_TYPE_ID = W.WIDGET_TYPE
    and O.OBJECT_TYPE_NAME NOT IN ('Mapplet')
    and W.MAPPING_ID = M.MAPPING_ID
union
select O.OBJECT_TYPE_NAME, W.INSTANCE_NAME, W.COMMENTS
from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M
where O.OBJECT_TYPE_ID = W.WIDGET_TYPE
    and O.OBJECT_TYPE_NAME NOT IN ('Mapplet', 'Output Transformation', 'Input Transformation')
    and W.MAPPING_ID in (
    select X.MAPPING_ID
    from OPB_WIDGET_INST W, OPB_OBJECT_TYPE O, M, OPB_MAPPING X
    where W.MAPPING_ID = M.MAPPING_ID
        and O.OBJECT_TYPE_ID = W.WIDGET_TYPE
        and O.OBJECT_TYPE_NAME = 'Mapplet'
        and X.REF_WIDGET_ID = W.WIDGET_ID
    )
order by 1,2    ;

-- Query to identify session that are not checked for property "Fail parent if session failed". Query provied by Rich
SELECT  --Workflow
        DISTiNCT
        S.SUBJ_NAME         as FOLDER_NAME
        ,WR.WORKFLOW_NAME   as WF_NAME
        ,TI.INSTANCE_NAME as SES_NAME
        ,TI.BIT_OPTIONS
FROM    DWPC.OPB_WFLOW_RUN WR
    INNER JOIN
        DWPC.OPB_SUBJECT S ON WR.SUBJECT_ID = S.SUBJ_ID
    INNER JOIN
        DWPC.OPB_TASK_INST TI ON WR.WORKFLOW_ID = TI.WORKFLOW_ID
    INNER JOIN
        (       
        SELECT  MAX(VERSION_NUMBER) as VERSION_NUMBER
                ,TASK_ID
                ,WORKFLOW_ID
                ,INSTANCE_ID
        FROM    DWPC.OPB_TASK_INST
        GROUP BY TASK_ID
                ,WORKFLOW_ID
                ,INSTANCE_ID
        ) Z ON TI.VERSION_NUMBER = Z.VERSION_NUMBER
        AND TI.TASK_ID = Z.TASK_ID
        AND TI.WORKFLOW_ID = Z.WORKFLOW_ID
        AND Ti.INSTANCE_ID = Z.INSTANCE_ID

WHERE   S.SUBJ_NAME IN ('Enterprise DW Staging','Enterprise DW ODS','Enterprise DW Dimension')
AND     TI.BIT_OPTIONS = 1
AND     TO_NUMBER(TO_CHAR(WR.START_TIME,'YYYYMMDD')) = TO_CHAR(SYSDATE - 1,'YYYYMMDD')
AND     TI.INSTANCE_NAME != 'Start'
AND     Ti.IS_ENABLED = 1

ORDER BY S.SUBJ_NAME
        ,WR.WORKFLOW_NAME
        ,TI.INSTANCE_NAME
------ Following queries are from Website: http://www.dwbiconcepts.com/etl/14-etl-informatica/94-useful-informatica-metadata-repository-queries.html
--Search for Truncate Table Option
--Suppose you want to find out on which of your sessions, truncate target table option is set on. Instead of manually opening each task and checking if the truncate table option is on, you may use the below query:

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 Mappings where SQL Override is used
--Below query will give you count of mapping instance where SQL Override has been used. The count is presented folder by folder.

WITH detail AS
(SELECT
       c.subject_area,
       c.mapping_name,
       d.instance_name source_qualifier_name,
       CASE WHEN a.attr_value IS NOT NULL
            THEN 1 ELSE 0
       END as OVR_OK
FROM
       rep_all_mappings c,
       opb_widget_inst d,
       opb_widget_attr a
WHERE
       c.mapping_id = d.mapping_id
       AND c.mapping_version_number = d.version_number
       AND d.widget_type = 3
       AND d.widget_id = a.widget_id
       AND a.widget_type = d.widget_type
       AND a.attr_id = 1
)
SELECT
       subject_area,
        'SQ_OVERIDE' STATUS,
       COUNT (DISTINCT mapping_name) NO_OF_Mapping,
       COUNT (DISTINCT (mapping_name || source_qualifier_name)) NO_OF_SQ_IN_MAPPING,
       COUNT (DISTINCT (source_qualifier_name)) NO_OF_DISTINCT_SQ
FROM detail
WHERE OVR_OK =1
GROUP BY subject_area
UNION
SELECT
       subject_area, 'SQ_NON_OVERIDE',
       COUNT (DISTINCT mapping_name) nb_mapping,
       COUNT (DISTINCT (mapping_name || source_qualifier_name)) nb_map_inst,
       COUNT (DISTINCT (source_qualifier_name)) nb_inst
FROM detail
WHERE OVR_OK =0
GROUP BY subject_area


--Find Tracing Levels for Informatica Sessions
--Sessions can have different tracing levels (Terse to Verbose). Often in Development environment we test our mappings with verbose tracing levels and then forget to reduce the level while promoting to Production environments. This creates issues like abnormal "SessLogs" growth, slower mapping performance etc.
--This query will give tracing information along with session names so that you can quickly identify unintended tracing levels without opening each sessions manually.

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
--This query is helpful when you require to know name of all stored procedure being used in informatica 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
--This information is available under mapping properties. But the below query lists down this information for all the mappings in one place.

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
--This query will give information about lookup transformations like lookup name,Tablename ,Mapping name etc.

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)


--Find all Invalid workflows from Metadata repository
--This query will list of all invalid workflows under the given subject area (folder)

select
       opb_subject.subj_name,
       opb_task.task_name
from 
       opb_task,  
       opb_subject
where
       task_type = 71
       and is_valid = 0
       and opb_subject.subj_id = opb_task.subject_id
       and UPPER(opb_subject.SUBJ_NAME) like UPPER('YOUR_FOLDER_NAME')


--Generate a list of failed sessions from the repository
--Here is a query that will display list of failed / aborted / terminated sessions from each of the folders in the repository

SELECT
       RSL.SUBJECT_AREA AS FOLDER,
       RW.WORKFLOW_NAME AS WORKFLOW,
       RSL.SESSION_NAME AS SESSION_NAME,
       DECODE(RSL.RUN_STATUS_CODE,
              3,'FAILED',
              4,'STOPPED',
              5,'ABORTED',
              15,'TERMINATED','UNKNOWN') AS STATUS,
       RSL.FIRST_ERROR_CODE AS FIRST_ERROR,
       RSL.FIRST_ERROR_MSG AS ERROR_MSG,
       RSL.ACTUAL_START AS START_TIME,
       RSL.SESSION_TIMESTAMP AS END_TIME
FROM
       REP_SESS_LOG RSL,
       REP_WORKFLOWS RW
WHERE
       RSL.RUN_STATUS_CODE IN (3,4,5,14,15)
       AND RW.WORKFLOW_ID = RSL.WORKFLOW_ID
       AND RW.SUBJECT_ID = RSL.SUBJECT_ID AND
       RSL.SUBJECT_AREA ='CDW_FDR2_SERVICEQUALITY'
ORDER BY
       RSL.SESSION_TIMESTAMP DESC
      


 --- Queries from website: http://informaticadoubts.blogspot.com/2013/08/informatica-repository-queries-to-get.html

--Query1:

--Query to get folder name, workflow name, session names and all the connections used in sessions.

SELECT WF.SUBJECT_AREA AS FOLDER_NAME, WF.WORKFLOW_NAME AS WORKFLOW_NAME,
       T.INSTANCE_NAME AS SESSION_NAME, T.TASK_TYPE_NAME,
       C.CNX_NAME AS CONNECTION_NAME, V.CONNECTION_SUBTYPE, V.HOST_NAME,
       V.USER_NAME, C.INSTANCE_NAME, C.READER_WRITER_TYPE,
       C.SESS_EXTN_OBJECT_TYPE
  FROM REP_TASK_INST T,
       REP_SESS_WIDGET_CNXS C,
       REP_WORKFLOWS WF,
       V_IME_CONNECTION V
 WHERE T.TASK_ID = C.SESSION_ID
   AND WF.WORKFLOW_ID = T.WORKFLOW_ID
   AND C.CNX_NAME = V.CONNECTION_NAME
   AND WF.SUBJECT_AREA = <FOLDER NAME>
 
--   Note: If SESS_EXTN_OBJECT_TYPE=78 then its reader, If 79 then its writer
 
--Query2:
  
--Query to get commands used in command tasks including the commands used as pre/post session commands.
 
   SELECT DISTINCT T.SUBJECT_ID, F.SUBJECT_AREA AS FOLDER_NAME,
                W.TASK_NAME AS WORKFLOW_NAME, T.TASK_NAME AS CMD_TASK_NAME,
                CMD.PM_VALUE AS CMD_NAME, CMD.EXEC_ORDER,
                CMD.VAL_NAME AS CMD_NUMBER, T.TASK_ID, T.TASK_TYPE,
                T.RU_PARENT_ID
           FROM OPB_TASK_VAL_LIST CMD, OPB_TASK T, OPB_TASK W, REP_SUBJECT F
          WHERE T.TASK_ID = CMD.TASK_ID
            AND T.SUBJECT_ID = F.SUBJECT_ID
            AND T.TASK_TYPE = 58
            AND T.RU_PARENT_ID = W.TASK_ID
            AND F.SUBJECT_AREA = <FOLDER NAME>
       ORDER BY F.SUBJECT_AREA, W.TASK_NAME, T.TASK_NAME, CMD.EXEC_ORDER
  
--Query3:

--Query to get the event wait file names used in event wait tasks. 
 
    SELECT    DISTINCT F.SUBJECT_AREA AS FOLDER_NAME, W.TASK_NAME AS WORKFLOW_NAME,
                T.TASK_ID, T.TASK_TYPE, T.TASK_NAME AS EVENTWAIT_NAME,
                T.RU_PARENT_ID, W.TASK_ID
           FROM OPB_TASK T, OPB_TASK W, REP_SUBJECT F
          WHERE T.RU_PARENT_ID = W.TASK_ID
            AND T.TASK_TYPE = 60
            AND T.SUBJECT_ID = F.SUBJECT_ID
            AND F.SUBJECT_AREA = <FOLDER NAME>
           
           
-- Losts of Metadata queries from website: http://aambarish.blogspot.com/2012/01/normal-0-false-false-false-en-us-x-none.html