7 Replies Latest reply on Apr 4, 2012 7:42 AM by sheikhmu

    REP_SESS_LOG view seems to be missing some data

    New Member

      I use a query to a couple of MX views to get information regarding session failures.  But I noticed there were times where sessions were failing but not showing up in my query.  Looking into it a bit further, it seems like there are some folders from my repository which do not show up in the REP_SESS_LOG view. 

       

      I took a look at the SQL behind this view and it looks like the issue is with the two joins to the OPB_CFG_ATTR table.  The view joins to this table using the OBP_COMPONENT table on OPB_CFG_ATTR.CONFIG_ID = OPB_COMPONENT.REF_OBJ_ID.  This seems to never match for the folders missing from this view. 

       

      There isn't a whole lot of information regarding OPB tables but any idea what the issue could be here?  Why there wouldn't be a REF_OBJ_ID in OPB_COMPONENT that matches a CONFIG_ID in OPB_CFG_ATTR where the CONFIG_TYPE = 72 and ATTR_ID in (221, 222)?  Or maybe an overall issue known with the REP_SESS_LOG view and a possible workaround?

       

      IPC is version 9.0.1 HF1 and query used to get session failures is below (but seems issue is just with the one view).  Thanks in advance for any help!

       

      SELECT trunc(actual_start) day,

          ses.session_name Session_Name,

          to_char(log.actual_start, 'hh24:mi:ss') Start_Time,

          to_char(log.SESSION_TIMESTAMP, 'hh24:mi:ss') End_Time,

          ROUND((LOG.SESSION_TIMESTAMP - LOG.ACTUAL_START)*24*60) Run_Time,

          log.successful_rows Succesfull_Rows,

          log.failed_rows Failed_Rows,

          substr(log.last_error,1,1000) Last_Error

      FROM rep_load_sessions ses, rep_sess_log log

      WHERE ses.session_id = log.session_id

          --trunc(actual_start) = trunc(sysdate) and

          last_error_code <> 0

          --ses.session_name like 'S_PERSON_ACTION_DIM'

      ORDER BY log.actual_start desc;

       

      [--moved to subforum Metadata Reperter. Regards, Nico]

        • 1. REP_SESS_LOG view seems to be missing some data
          Active Member

          Hi

           

          You might have already looked at this but still wanted to check if you are using a Versioned Repository then Ensure all Objects has been Checked in those Folders some times this creates a issue.

           

          Also you can try modifying the Session and Saving it and check if doing so makes it visible in the MX Views.

           

           

          Mon

          • 2. Re: REP_SESS_LOG view seems to be missing some data
            New Member

            Thanks Mon,

             

            The repository is not versioned and it seems like it is all sessions in some of the folders in the repository that don't show up.  These mappings/workflows have been around a long time but I tried to just do a quick edit on a session and resaved it and reran it but still not seeing in the REP_SESS_LOG view.

             

            It looks like 8 of our 20+ folders that do not show up in the view.  I'm trying to compare the ones that do show up vs. ones that do not to see if there are any noticible differences.  I haven't noticed anything so far but is there anything specific I should be looking for maybe while comparing?

             

            Also this is an old repository that has been upgraded from 8.1 to 9.0.1 recently.  And even before that I think it was upgraded from 7 to 8.1 a few years back.  I wonder if something could have been corrupted during upgrades?  I don't know how it worked under version 7, but I think this issue did exist in the version 8.1 repository.  I just never got a chance to test it as thorougly as now.

            • 3. REP_SESS_LOG view seems to be missing some data
              Guru

              See if this is any better?

              SELECT R.PCSF_DOMAIN, R.REPOSITORY_NAME, TIR.WORKFLOW_RUN_ID,S.SUBJ_NAME,WR.WORKFLOW_NAME,TIR.INSTANCE_NAME,

              TIR.TASK_NAME,IR.START_TIME ,TIR.END_TIME,TARG_SUCCESS_ROWS,TARG_FAILED_ROWS,FIRST_ERROR_MSG,

               

              TIR.RUN_ERR_MSG, TL.LOG_FILE,TIR.RUN_STATUS_CODE,S.SUBJ_ID,TIR.WORKFLOW_ID,TIR.

              TASK_ID

               

              FROM OPB_REPOSIT_INFO R,

              OPB_SESS_TASK_LOG TL,  OPB_TASK_INST_RUN TIR, OPB_SUBJECT S,  OPB_WFLOW_RUN WR

              WHERE

              TIR.SUBJECT_ID = S.SUBJ_ID

              AND

              TIR.WORKFLOW_ID = TL.WORKFLOW_ID

              AND

              TIR.WORKFLOW_RUN_ID = TL.WORKFLOW_RUN_ID

              AND

              TIR.WORKLET_RUN_ID = TL.WORKLET_RUN_ID

              AND

              TIR.INSTANCE_ID = TL.INSTANCE_ID

              AND

              WR.WORKFLOW_RUN_ID = TIR. WORKFLOW_RUN_ID

              AND

              TIR.TASK_TYPE = 68

               

               

               

               

               

              1 of 1 people found this helpful
              • 4. REP_SESS_LOG view seems to be missing some data
                Guru

                What is this CRAP editor!

                • 5. REP_SESS_LOG view seems to be missing some data
                  New Member

                  Thanks!  That does work correctly showing me all folders/sessions that have run in my repository. I can filter by TIR.RUN_STATUS_CODE = 3 to get only sessions that have failed.

                   

                  Still not sure why an MX view would have missing data. Perhaps it is due to the several upgrades this repository has gone through.  But I can work with this query using the underlying OPB tables instead.  Thanks again for your help!

                  • 6. Re: REP_SESS_LOG view seems to be missing some data
                    Guru

                    Please be aware that Informatica may change the structure of the repository tables (and did several times during the past years) at any time without any notice about it. So you might end up using this query in let's say PowerCenter 9.2 and all of a sudden the query doesn't work anymore.

                    That's the reason why I recommend using the MX views under almost all circumstances. And I'm glad that you did this. Too bad that you encounter such trouble, though.

                     

                    Please, open a support ticket in order to find out why the MX view doesn't show all the data, it definitely should. Of course it may be that this is an issue with 9.0.1 HF1 only, I can't tell.

                     

                    Regards,

                    Nico

                    • 7. REP_SESS_LOG view seems to be missing some data
                      New Member

                      Thanks Nico.  I did end up creating a SR for this and was told that that this is known issue in PC9.0.1 and CR275143 has been raised for it.

                       

                      I will use the suggested SQL by user152629 for now until I find something better with the MX views or this issue is resloved.