4 Replies Latest reply on Aug 21, 2014 1:38 AM by nheinze

    Query to obtain all sequence generators in a INFA folder

    New Member

      Hi,

      Is there a standard query to obtain all sequence generator and their current values in a Informatica folder in v9.1.0 HF6?

      Kind Regards,

        • 1. Re: Query to obtain all sequence generators in a INFA folder
          Sachin Guru

          You can make use of the metaquery tool embedded in support console to get this information

           

          MetaQuery in Support Console

           

          Check point no 18 under transformation for supported properties in metaquery

          • 2. Re: Query to obtain all sequence generators in a INFA folder
            Guru

            And those people not fortunate enough to have Metaquery at hand can query the Metadata Exchange view (MX view) REP_ALL_TRANSFORMS for entries with WIDGET_TYPE_NAME = 'Sequence' AND SUBJECT_AREA = '<your folder name>'. This will give you the WIDGET_ID whch in turn you have to join with attribute WIDGET_ID in the MX view REP_WIDGET_INST (with the additional WHERE clause part AND WIDGET_TYPE_NAME = 'Sequence'); this will return the MAPPING_ID (possibly more than one) which you join with REP_ALL_MAPPINGS.MAPPING_ID to get the mapping name.

            The current value can be retrieved from REP_WIDGET_ATTR with WIDGET_ID = widget ID from REP_ALL_TRANSFORMS (see above) AND WIDGET_TYPE = 7 (that denotes a Sequence Generator) AND ATTR_ID = 4 (that denotes the Current Value); the actual current value of the SEQ is stored in attribute ATTR_VALUE.

             

            Regards,

            Nico

            • 3. Re: Query to obtain all sequence generators in a INFA folder
              New Member

              Hi Nico,

              How do I get the latest version of the object?  The following brings me back all.

              Regards

               

               

              set arraysize 200

              set echo off

              set verify off

              set trimspool on

              set headsep off

              set pagesize 0

              set pages 0

              set feed off

              set linesize 3000

               

               

              spool mike.csv

              select a.subject_area, a.widget_name, b.attr_value from REP_ALL_TRANSFORMS a,REP_WIDGET_ATTR b

              --where WIDGET_TYPE_NAME = 'Sequence'

              where SUBJECT_AREA = <folder name>

              and a.WIDGET_ID in (select WIDGET_ID from REP_WIDGET_ATTR where widget_type = 7

                                                         and attr_id =4);

              spool off

              • 4. Re: Query to obtain all sequence generators in a INFA folder
                Guru

                Sort these entries by widget ID and version number, then take the last record for each widget ID.

                In PowerCenter that's one Sorter (or you have Oracle deliver the data sorted) plus one Aggregator returning LAST().

                 

                Regards,

                Nico