Clarity PPM

Expand all | Collapse all

DWH - Incremental Load job failure

matej25601-09-2018 07:04 AM

  • 1.  DWH - Incremental Load job failure

    Posted 05-18-2017 07:41 AM
    CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_X_RES_AVAIL_PER_FACTS. SQLERRM : ORA-06535: statement string in OPEN is NULL or 0 length ORA-06512: at "PPM_DWH.DWH_X_RES_AVAIL_PER_FACTS_LOAD", line 118 ORA-06512: at line 1


    What would be the reason for this?

    Regards
    NJ


  • 2.  Re: DWH - Incremental Load job failure

    Posted 05-18-2017 07:54 AM

    Hi NJ,

     

    Can you share the bg-dwh logs, the error looks like some problem with inserting into the facts table DWH_X_RES_AVAIL_PER_FACTS_LOAD. 

     

    Also what version of the PPM you are using and the DWH options checked in PPM under system option --> Data warehouse options 

     

     

    Regards

    Suman Pramanik



  • 3.  Re: DWH - Incremental Load job failure

    Posted 01-09-2018 03:20 AM

    Hi Both, 

     

    Could you please share how you resolved this issue? I'm facing the same (Full load executed successfully before, now incremental failed). Error from bg-dwh log:

     

    ERROR 2018-01-09 08:02:38,558 [dwh_x_res_avail_per_facts UUID: f87999dc-23cf-4c77-8a50-0eca68172487] dwh.event ClarityDB - isOracle? - An error occurred executing this job entry :
    Couldn't execute SQL: BEGIN DWH_X_RES_AVAIL_PER_FACTS_LOAD(P_DBLINK => 'PPMDBLINK',P_LAST_LOAD_DATE => to_date('2017/11/28 11:45:43', 'yyyy/MM/dd HH24:mi:ss'),P_CURRENT_DIM_LOAD_DATE => to_date('2018/01/09 07:42:49', 'yyyy/mm/dd HH24:MI:SS'), P_ARRAY_SIZE => 50000);END;
    [CA Clarity][Oracle JDBC Driver][Oracle]ORA-20100: ENCOUNTERED EXCEPTION WHILE INSERTING INTO DWH_X_RES_AVAIL_PER_FACTS. SQLERRM : ORA-06535: statement string in OPEN is NULL or 0 length
    ORA-06512: at "PPM_DWH.DWH_X_RES_AVAIL_PER_FACTS_LOAD", line 118
    ORA-06512: at line 1

     

    We are on PPM 15.3. 

    DWH settings:

     

    Thanks
    Matej



  • 4.  Re: DWH - Incremental Load job failure

    Posted 01-09-2018 05:36 AM

    HI Matej

     

    Can I have the logs please 

     

    Regards

    Suman Pramanik 



  • 5.  Re: DWH - Incremental Load job failure

    Posted 01-09-2018 07:04 AM
      |   view attached

    It's attached. 

     

    Matej

    Attachment(s)

    zip
    bg-dwh.txt.zip   26K 1 version


  • 6.  Re: DWH - Incremental Load job failure

    Posted 01-09-2018 07:10 AM

    Hi Matej,

     

    Can you check  the options and then run the job

     

    Check the following options:

    Include Weekly Period Data
    Include Monthly Period Data
    Include Fiscal Period Data

    - Run the Time Slice job
    - Then run the Update Report Tables job
    - Then run the then the Full Load of the DWH job, followed by a few instances of the incremental job.

     



  • 7.  Re: DWH - Incremental Load job failure

    Posted 01-09-2018 11:20 AM

    Hi Matej

     

    Here is the information about those check box https://docops.ca.com/ca-ppm/15-2/en/administration/configure-the-data-warehouse-and-advanced-reporting-domains/ 

     

     

    Regards

    Suman Pramanik 



  • 8.  Re: DWH - Incremental Load job failure

    Posted 01-09-2018 11:21 AM

    Also for the time slice, have a look at time slice page to see if it got updated 



  • 9.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 03:00 AM

    Hi Suman, 

     

    Time Slicing job now finished successfully in a few minutes. Seems to be some problems with Database. 

    Anyway I know something about those "Include" options. My question was only - Why I should check those options when I don't want to include these data? And why I should check it to run job successfully? It should by optional isn't it?  

     

    Thanks

    Matej



  • 10.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 03:03 AM

    HI Matej,

     

    May i know why you don't want to check those? The product is designed to work by checking those and hence its required.

     

    Regards

    Suman Pramanik



  • 11.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 04:59 AM
      |   view attached

    Hi Suman, 

     

    It was just a question of a design - when it's designed to have it checked, then it doesn't need to be "optional" there...  
    Nevertheless, full and incremental load finished successfully now. The second is I'm still facing this error I can't solve myself (when creating simple ad-hoc report). Do you know what can it be? (Log is attached)

     

     

    Thanks 

    Matej

    Attachment(s)

    zip
    app2-js.log.zip   6K 1 version


  • 12.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 05:12 AM

    Hi Matej,

     

    Great to know its solved now. The error what you see in Adhoc is could be due to Create Jaspersoft user not run. If you had run the job and still getting the error then you can execute 

     

    1. Restore Domain Job 

    2. Then from command prompt run admin jaspersoft syncPPMContext -userName superuser -password superuser

     

    Regards
    Suman Pramanik 



  • 13.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 05:20 AM

    Hi Suman,

     

    Hmm, so this Jaspersoft user should be created by DWH job? 

    When tried to run Restore Domain job I got error:

    Restore Domains Job Failed:  :: Error in importing contentConnection reset

     

    Error in BG Log:

         
    ERROR 2018-01-10 10:15:50,494 [Dispatch Restore Domains 10.1 : bg@dedussv-sjsdev1.group.net (tenant=clarity)] restAdapter.JasperRestAdapter (clarity:admin:90978847__11E03F6B-7DEC-407A-9042-09669BD4E272:Restore Domains 10.1) sendRequest Error: Connection reset
    ERROR 2018-01-10 10:15:50,494 [Dispatch Restore Domains 10.1 : bg@dedussv-sjsdev1.group.net (tenant=clarity)] jaspersoft (clarity:admin:90978847__11E03F6B-7DEC-407A-9042-09669BD4E272:Restore Domains 10.1) Content import failed.Connection reset
    ERROR 2018-01-10 10:15:50,495 [Dispatch Restore Domains 10.1 : bg@dedussv-sjsdev1.group.net (tenant=clarity)] jaspersoft (clarity:admin:90978847__11E03F6B-7DEC-407A-9042-09669BD4E272:Restore Domains 10.1) Restore Domains Job Failed: ::
    com.niku.union.reporting.jaspersoft.exception.JasperDomainException: Error in importing contentConnection reset
    at com.niku.reporting.jasper.ClarityJasperAdmin.jasperContent(ClarityJasperAdmin.java:917)
    at com.niku.reporting.jobs.jasper.RestoreJaspersoftDomainsJob.scheduledEventFired(RestoreJaspersoftDomainsJob.java:80)
    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:652)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
    Caused by: com.ca.jasper.restAdapter.JasperException: Connection reset
    at com.ca.jasper.restAdapter.JasperRestAdapter.sendRequest(JasperRestAdapter.java:861)
    at com.ca.jasper.restAdapter.JasperRestAdapter.postServiceFile(JasperRestAdapter.java:677)
    at com.ca.jasper.restAdapter.services.RestServiceBase.restServiceFilePost(RestServiceBase.java:460)
    at com.ca.jasper.restAdapter.services.ImportService.importContent(ImportService.java:219)
    at com.ca.jasper.restAdapter.services.ImportService.importContent(ImportService.java:146)
    at com.ca.jasper.restAdapter.services.ImportService.importContent(ImportService.java:106)
    at com.niku.reporting.jasper.ClarityJasperAdmin.importContent(ClarityJasperAdmin.java:1204)
    at com.niku.reporting.jasper.ClarityJasperAdmin.jasperContent(ClarityJasperAdmin.java:802)
    ... 5 more
    ERROR 2018-01-10 10:15:50,510 [Dispatch Restore Domains 10.1 : bg@dedussv-sjsdev1.group.net (tenant=clarity)] niku.njs (clarity:admin:90978847__11E03F6B-7DEC-407A-9042-09669BD4E272:Restore Domains 10.1) Error executing job: 7019347
    java.lang.Exception: Restore Domains job Failed: Error in importing contentConnection reset
    at com.niku.reporting.jobs.jasper.RestoreJaspersoftDomainsJob.scheduledEventFired(RestoreJaspersoftDomainsJob.java:88)
    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:652)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)

     

    Note: our domains are OOTB, we didn't modify them. 

    Matej



  • 14.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 05:23 AM

    Hi Matej,

     

    The very 1st time did you import out of box content using command admin content-jaspersoft csk -userName superuser -password superuser and was it successful. If it was not then you can delete the jaspersoft organization and import it again 

     

    Regards

    Suman Pramanik 



  • 15.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 05:33 AM

    Hi Suman, 

     

    Yes, that command was successfull. I've applied it when installed JS, as it was part of installation process when followed install guide from CA. 

     

    Matej



  • 16.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 06:18 AM

    Very strange, try to re sync the connect admin jaspersoft syncPPMContext -userName superuser -password superuser and see if you get error. 

     

    The last error in domain because of inconsistent Jaspersoft content



  • 17.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 07:05 AM

    Hi Suman, 

     

    I run the command:

     

    Then I've restarted the server.

     

    However I got the same error in JS...Can we somehow fix that "null" error to see what is the error message? 

    Or do you have another idea? 

     

    Thanks

    Matej



  • 18.  Re: DWH - Incremental Load job failure

    Posted 01-10-2018 12:10 PM

    Hi Matej,

     

    See if this helps, seems something relating to the Jaspersoft Property.

    https://support.ca.com/us/knowledge-base-articles.TEC1386656.html



  • 19.  Re: DWH - Incremental Load job failure

    Posted 01-11-2018 05:11 AM

    Hi Viral, SumanPramanik

     

    Forgot to mention I've tried it before but without any success. I still got an above error when updated properties and restarted Tomcat service at JS server. 

     

    EDIT: I've just found out there are some logs on Tomcat server where JS is running. This is much more helpful than Clarity logs, that's a pitty I didn't know about them earlier... So the main error message is: Error in loading properties of keystore- the file- 'Henkel.properties' does not exist.

     

    Caused by: com.jaspersoft.jasperserver.api.JSExceptionWrapper: Error in loading properties of keystore- the file- 'Henkel.properties' does not exist.
    Arguments: at  com.ca.jasper.jdbc.JaaSProxyJDBCPoolDataSourceFactory.createService(JaaSProxyJDBCPoolDataSourceFactory.java:425)
    at  com.ca.jasper.jdbc.JaaSProxyJDBCPoolDataSourceFactory.createService(JaaSProxyJDBCPoolDataSourceFactory.java:149)
    ... 184 more
    2018-01-11 07:17:40,060 ERROR JaaSProxyJDBCPoolDataSourceFactoryHelper,http-bio-8080-exec-13:1237 [Henkel|admin] - JaaSProxyJDBCPoolDataSourceFactoryHelper.createService(){Tenant=Henkel,User=admin}-Error in decrypting attribute with name:dwhDBVendor_enc and value=yXWths/W1fzQ9CQvPbkOlO5bwxgi9v/aSRKqYmCp6kE=::java.security.GeneralSecurityException: Error in loading properties of keystore- the file- 'Henkel.properties' does not exist.
    at com.ca.jasper.encryption.PPMEncryptionService.loadFromProperties(PPMEncryptionService.java:107)
    at com.ca.jasper.encryption.PPMEncryptionService.init(PPMEncryptionService.java:182)
    at com.ca.jasper.encryption.EncryptionFactory.getEncryptionService(EncryptionFactory.java:71)
    etc..

     

    However properties file has been generated properly and copied to <Tomcat>/webapps/<jasperwebcontext>/WEB-INF/config some time ago according to JS Install and Setup guide:

     

    Once thing I noticed is that properties file starts with lower "h" however in logs starts with upper "H".

    When tried renaming properties file to start with "H", restarted Tomcat, then JS reports didn't worked in Clarity at all. 

    Any ideas? For me very strange from where comes properties file starting with upper H in logs...Moreover, those files are generated automatically...

     

    Thanks

    Matej



  • 20.  Re: DWH - Incremental Load job failure

    Posted 01-11-2018 05:29 AM

    You dont need to rename the property file, try this 

     

    1. Login to PPM 

    2. Go to Advance Reporting --> Users and search for jasperadmin

    3. Edit the user and go to attributes tab 

    4. Delete all the attribute 

    4. Run the admin update jasperParameters

    5. Then run the Restore Domain job and see if it works 



  • 21.  Re: DWH - Incremental Load job failure

    Posted 01-11-2018 05:50 AM

    Hi Suman, 

     

    1.) - 4.) steps: DONE

    5.) I got the same error like before:

     

    ERROR 2018-01-11 10:44:28,256 [Dispatch Restore Domains 11.1 : bg@dedussv-sjsdev1.henkelgroup.net (tenant=clarity)] jaspersoft (clarity:admin:90982416__AF661A28-F1CA-415A-BD31-C4F28176A69C:Restore Domains 11.1) Content import failed.Connection reset
    ERROR 2018-01-11 10:44:28,256 [Dispatch Restore Domains 11.1 : bg@dedussv-sjsdev1.henkelgroup.net (tenant=clarity)] jaspersoft (clarity:admin:90982416__AF661A28-F1CA-415A-BD31-C4F28176A69C:Restore Domains 11.1) Restore Domains Job Failed: ::
    com.niku.union.reporting.jaspersoft.exception.JasperDomainException: Error in importing contentConnection reset
    at com.niku.reporting.jasper.ClarityJasperAdmin.jasperContent(ClarityJasperAdmin.java:917)
    at com.niku.reporting.jobs.jasper.RestoreJaspersoftDomainsJob.scheduledEventFired(RestoreJaspersoftDomainsJob.java:80)
    at com.niku.njs.Dispatcher$BGTask.run(Dispatcher.java:652)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(ThreadPoolExecutor.java:1149)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(ThreadPoolExecutor.java:624)
    at java.lang.Thread.run(Thread.java:748)
    Caused by: com.ca.jasper.restAdapter.JasperException: Connection reset
    ... 5 more

     

     

    Matej



  • 22.  Re: DWH - Incremental Load job failure

    Posted 01-11-2018 06:03 AM

    At this time its best to log a case and my team can take a look

     

    Regards

    Suman Pramanik