8 Replies Latest reply on May 2, 2013 4:49 AM by Srikanth Vishnuvajhala

    OPB or REP table to identify All CheckedOut Objects in Informatica

    New Member

      Hello Everyone..

       

      I am looking for OPB or REP tabls to identify all Checked out objects in informatica.

      All though we can get this from Repository Manager Queries, I wanted this from back end tables.

       

      It would be great if anyone can help on this.

       

      Regards

      Chaitanya

        • 1. Re: OPB or REP table to identify All CheckedOut Objects in Informatica
          Srikanth Vishnuvajhala Guru

          Chaitanya,

           

          Use the below pmrep command.

           

          findcheckout

          [-o <object_type>]

          [-f <folder_name>]

          [-u (all_users)]

          [-c <column_separator>]

          [-r <end-of-record_separator>]

          [-l <end-of-listing_indicator>]

          [-b (verbose)]

           

          Example:

           

          pmrep find checkout -u all_users -b verbose

           

          You can also use REP_VERSION_PROPS view and REP_USERS views to get the checked out users information.

           

          Thanks,

          Srikanth

          • 2. OPB or REP table to identify All CheckedOut Objects in Informatica
            Srikanth Vishnuvajhala Guru

            In case your PowerCenter is on Unix, you can try the following script to find checked out objects in the Repository. This script will redirect the output to a text file containing the list of checked out objects.

             

            ********************************

            #!/bin/ksh

            . ${HOME}/.profile

             

            cd $INFA_HOME/server/bin

             

            pmrep findcheckout -u all_users -b verbose > chk.txt

             

            sed '/Informatica/,/Invoked/d;/^$/d;$d' chk.txt > chk1.txt

            sed '$d' chk1.txt > checkoutlist.txt

            rm chk.txt chk1.txt

             

            ********************************

             

            Thanks,

            Srikanth

            • 3. OPB or REP table to identify All CheckedOut Objects in Informatica
              New Member

              Srikanth,

               

              Thanks for the quick response.

               

              But I am looking to acheive this from back end repo tables.

              I have gone through REP_VERSION_PROPS view and REP_USERS. But didnt got much information to fetch all the checked out objects.

              Could you please elaborate on this.

               

              Regards

              Chaitanya

              • 4. OPB or REP table to identify All CheckedOut Objects in Informatica
                Guru

                Hi Krishna,

                 

                As correctly suggested by Srikanth, you can get the checkout information using findcheckout command..

                 

                Any specific reason , why you are looking for this information from repository tables only?

                • 5. OPB or REP table to identify All CheckedOut Objects in Informatica
                  New Member

                  Hi Yogesh,

                   

                  Yes. Already I have a repo query which generates the report from back end.

                  Now I wanted to include new fucntionality to the existing query to identify whether the object was checkin/out. So i am looking for the tables so that I can join it to existing code and not through "findcheckout" option.

                   

                  Regards

                  Chaitanya

                  • 6. OPB or REP table to identify All CheckedOut Objects in Informatica
                    Srikanth Vishnuvajhala Guru

                    Hi Krishna,

                     

                    I tried the following query in PowerCenter 861 and it gave the results. Please try from your side and let me know.

                     

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, O.IS_REUSABLE FROM OPB_TASK O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.TASK_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = O.TASK_TYPE  AND O.IS_REUSABLE=1

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_SESSION_CONFIG O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.CONFIG_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 72

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, O.IS_REUSABLE FROM OPB_SCHEDULER O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.SCHEDULER_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 69  AND O.IS_REUSABLE=1

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_MACRO O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.MACRO_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 106

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_SHORTCUT O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.OBJECT_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = O.OBJECT_TYPE   AND P.OBJECT_TYPE IN (25,1,24,23,44,30,31,21,20,106,69,72,82)

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_MAPPING O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.MAPPING_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 21  AND O.REF_WIDGET_ID=0

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_MD_DIMENSION O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.DIMENSION_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 31

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_MD_CUBE O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.CUBE_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 30

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, O.IS_REUSABLE FROM OPB_WIDGET O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJECT_ID AND P.OBJECT_ID = O.WIDGET_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = O.WIDGET_TYPE  AND O.IS_REUSABLE=1

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_TARG O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJ_ID AND P.OBJECT_ID = O.TARGET_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 2  AND TBL_TYPE = 0

                    union

                    SELECT P.OBJECT_ID, P.VERSION_NUMBER, P.SUBJECT_ID, P.USER_ID, P.COMMENTS, P.SAVED_FROM, P.LAST_SAVED, P.OBJECT_NAME, P.GROUP_NAME, P.OBJECT_TYPE, P.OBJECT_SUBTYPE, P.IS_SHORTCUT, S.SUBJ_NAME, P.UTC_LAST_SAVED, P.UTC_CHECKOUT, U.NAME, P.UTC_CHECKIN, O.IS_VISIBLE, O.VERSION_STATUS, 1 FROM OPB_SRC O, OPB_VERSION_PROPS P, OPB_SUBJECT S, OPB_USER_GROUP U WHERE O.IS_VISIBLE = 2 AND P.SUBJECT_ID = S.SUBJ_ID AND P.USER_ID = U.ID AND U.TYPE = 1 AND P.SUBJECT_ID = O.SUBJ_ID AND P.OBJECT_ID = O.SRC_ID AND P.VERSION_NUMBER = O.VERSION_NUMBER AND P.OBJECT_TYPE = 1

                     

                    Thanks,

                    Srikanth

                    • 7. OPB or REP table to identify All CheckedOut Objects in Informatica
                      New Member

                      Great Thanks Srikanth...

                      its working good for me..

                       

                      Really appreciable...

                       

                      Regards

                      Chaitanya

                      • 8. OPB or REP table to identify All CheckedOut Objects in Informatica
                        Srikanth Vishnuvajhala Guru

                        You are welcome. Glad to know that it is working for you.

                         

                        Thanks,

                        Srikanth