Need to report of all scheduled reports with partial result

Hi,

We have a few scheduled reports and lately some of them return with partial result.

My questions are:

  1. How can I report of all those scheduled reports that has partial result (in pending_jobs I see them with status success (code 0 althought they were not completely successful)?

  2. Where can I see the reason for the partial results?
    Those reports used to run without problems, more than that- when I refresh them manually I get a good response.

Notes: We don’t have any limitation on size or time for query on the universe, I also know the query did not exceed the limit on the DB.

Thanks,
Liat


liatt (BOB member since 2004-03-17)

Liat,

The information you are looking for is not stored anywhere in the repository. Keep in mind that documents are stored as BLOBs in the document domain.

If you can refresh them manually and get full results, then I suspect that something on the network or database caused the query to stop running while BCA was handling the job. Check with your network folks or DBA and ask if they are aware of any issues during that time. Try moving the run times a little later or earlier and see if that helps.


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

Thanks :slight_smile:

You wrote :

The report is watching for a trigger file and only then it starts running so changing the time will not help.

I know there is a priority value in the pending jobs table,
do you know how can I change the priority in order to move the report to the top/end of the list?


liatt (BOB member since 2004-03-17)

Open up the BCA Console on the server. Double click on the document, and in the properties dialogue box, I believe you can change the priority.


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

Thanks again, I’ll try that.

:?: I have another question:

On the “retrieve from corporate” window, When I click on properties of the document I can see under the “data provider” tab the information about the last run and there is a column called “partial result”.

This information must be stored somewhere right?
We have an event log tables, maybe it is saved there?


liatt (BOB member since 2004-03-17)

Hmm, I’m not sure where that is coming from, but I’ll try to figure it out and let you know.


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

I was under the impression that was stored in the report file, rather than in a column somewhere in the repo…

Is there maybe some way of doing it through the SDK…? I don’t know a lot about the SDK, but would it not be possible to retrieve the report in a macro that is attached to a dummy report, check the dataprovider to see the number of rows returned…?

That’s complete speculation above by the way, and I don’t have a copy of the SDK guide to hand so can’t tell if that’s something you can check.

But I am pretty certain that the dataprovider info is in the report and not in the repository…

Cheers,

Mart


mart (BOB member since 2004-02-10)

Yes the Data provider information is stored in the repository in OBJ_M_DOCATVAR table in M_DOCATV_N_PARTIAL column.

Here is the query(For Oracle Repository) which tells you the status of data providers for reports in BCA


SELECT
  OBJ_M_DOCATVAR.M_DOCATV_C_DPNAME,
  OBJ_M_DOCATVAR.M_DOCATV_N_NBROWS,
OBJ_M_DOCATVAR.M_DOCATV_N_PARTIAL,
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (OBJ_M_DOCATVAR.M_DOCATV_N_REF/86400)),'GMT','CDT'),
  OBJ_M_DOCATVAR.M_DOCATV_N_REFDUR/60,
  OBJ_M_DOCATVAR.M_SRC_C_NAME,
  OBJ_M_DOCUMENTS.M_DOC_C_NAME,
  OBJ_M_ACTOR.M_ACTOR_C_NAME,
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (DS_PENDING_JOB.BEGIN_TIME/86400)),'GMT','CDT'),
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (DS_PENDING_JOB.BEGIN_DATE /86400)),'GMT','CDT'),
  decode(DS_PENDING_JOB.JOB_STATUS,'0','Success','1','Failure','2','Waiting','3','Running','4','Suspended','1001','Retrying(1)','1002','Retrying(2)','1003','Retrying(3)','1004','Retrying(4)','1005','Retrying(5)','1006','Expired'),
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (DS_PENDING_JOB.Submit_DATETIME /86400)),'GMT','CDT'),
  decode(DS_PENDING_JOB.FREQUENCY,'4','Once','6','Once','8','Hourly','10','Hourly','16','Daily','18','Daily','32','Weekly','34','Weekly','64','Monthly','66','Monthly','128','Monthly Interval','130','Monthly Interval','256','User Defined','258','User Defined'),
  DS_PENDING_JOB.PRIORITY
FROM
  OBJ_M_DOCATVAR,
  OBJ_M_DOCUMENTS,
  OBJ_M_ACTOR,
  DS_PENDING_JOB
WHERE
  ( OBJ_M_DOCUMENTS.M_DOC_N_ID=DS_PENDING_JOB.DOCUMENT_ID  )
  AND  ( OBJ_M_ACTOR.M_ACTOR_N_ID=DS_PENDING_JOB.USER_SUBMIT_ID  )
  AND  ( OBJ_M_DOCATVAR.M_DOC_N_ID=OBJ_M_DOCUMENTS.M_DOC_N_ID  )



JaiGupta (BOB member since 2002-09-12)

Jai thanks.
the sql you posted don’t display the “partial result” status (that’s what I’m looking for).
I run it and although the report had partial results, it still appears as “success” status.

important note is:
I maybe totaly wrong but since the data about the partial result can be seen for every report (not only scheduled ones) I believe it should be stored is obj_m_documents table or as you wrote mart:

Anyway, I’m familiar with a few of the repository tables but I’m not an expert (hardly a “smart” user).

I will be happy to get some instructions in order to check about your suggestions.


liatt (BOB member since 2004-03-17)

OK, I did some research. Jai is correct. The information you are looking for is stored in OBJ_M_DOCATVAR.M_DOCATV_N_PARTIAL

Here is what that column contains:

Check that out and see if it helps.


MichaelWelter :vatican_city: (BOB member since 2002-08-08)

Hi Liatt,
Column which returns values like sucess , failure etc is for displaying the sucess or failure of the Scheduled job.

You need to check for column OBJ_M_DOCATVAR.M_DOCATV_N_PARTIAL

I have put decode for displaying partila result flag.

Note : Every Document present in the repository has its data provider properties stored in the OBJ_M_DOCATVAR table.

Hope it helps. Let me know if you have further questions


SELECT 
  OBJ_M_DOCATVAR.M_DOCATV_C_DPNAME, 
  OBJ_M_DOCATVAR.M_DOCATV_N_NBROWS, 
decode(OBJ_M_DOCATVAR.M_DOCATV_N_PARTIAL,0,'No Partial results',1,'Partial Results') PartialReultFlag, 
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (OBJ_M_DOCATVAR.M_DOCATV_N_REF/86400)),'GMT','CDT'), 
  OBJ_M_DOCATVAR.M_DOCATV_N_REFDUR/60, 
  OBJ_M_DOCATVAR.M_SRC_C_NAME, 
  OBJ_M_DOCUMENTS.M_DOC_C_NAME, 
  OBJ_M_ACTOR.M_ACTOR_C_NAME, 
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (DS_PENDING_JOB.BEGIN_TIME/86400)),'GMT','CDT'), 
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (DS_PENDING_JOB.BEGIN_DATE /86400)),'GMT','CDT'), 
  decode(DS_PENDING_JOB.JOB_STATUS,'0','Success','1','Failure','2','Waiting','3','Running','4','Suspended','1001','Retrying(1)','1002','Retrying(2)','1003','Retrying(3)','1004','Retrying(4)','1005','Retrying(5)','1006','Expired'), 
  New_Time((to_date('12/15/1970','mm/dd/yyyy') +  (DS_PENDING_JOB.Submit_DATETIME /86400)),'GMT','CDT'), 
  decode(DS_PENDING_JOB.FREQUENCY,'4','Once','6','Once','8','Hourly','10','Hourly','16','Daily','18','Daily','32','Weekly','34','Weekly','64','Monthly','66','Monthly','128','Monthly Interval','130','Monthly Interval','256','User Defined','258','User Defined'), 
  DS_PENDING_JOB.PRIORITY 
FROM 
  OBJ_M_DOCATVAR, 
  OBJ_M_DOCUMENTS, 
  OBJ_M_ACTOR, 
  DS_PENDING_JOB 
WHERE 
  ( OBJ_M_DOCUMENTS.M_DOC_N_ID=DS_PENDING_JOB.DOCUMENT_ID  ) 
  AND  ( OBJ_M_ACTOR.M_ACTOR_N_ID=DS_PENDING_JOB.USER_SUBMIT_ID  ) 
  AND  ( OBJ_M_DOCATVAR.M_DOC_N_ID=OBJ_M_DOCUMENTS.M_DOC_N_ID  ) 

JaiGupta (BOB member since 2002-09-12)

The query works great for me, that’s exactly what I needed

Thanks a lot!!!
:smiley:


liatt (BOB member since 2004-03-17)